Server System Variables[edit | edit source]
It's important with MySQL (and MariaDB) to use
innodb_file_per_table ON so that you do not end up with an extremely large and ever-growing ibdata1 file. You can't change this setting on an existing database server. You have to set it up initially, and migrate dumps from an old server. If your server is online, you can use replication to a properly configured slave to minimize downtime. Meza and QualityBox use innodb_file_per_table.
Debugging with the General Query Log[edit | edit source]
In the mysql console, you can use a global variable to turn logging on
SET global general_log = 1;
And specify the log file (The MySQL user must have write permission to this file.)
SET global general_log_file = '/var/log/mariadb/mariadb.log';
Then you can do some action that you want to examine the queries for, and TURN OFF the general query log immediately. A busy server will fill up all disk space with a huge log file if you don't turn it off.
SET global general_log = 0;
Query Examples[edit | edit source]
-- what database schema do you want to use? use wiki_demo; -- What would a create database statement look like? show create database wiki_demo; -- How about a table? show create table wiki_demo.l10n_cache; # What character collations are specified for each table and column (that supersede any global declaration or default.) SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM information_schema.columns AS cols WHERE table_schema = 'wiki_demo';
Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'.
SELECT DISTINCT page_title FROM page RIGHT JOIN revision ON page_id = rev_page WHERE rev_text_id IN ( SELECT old_id FROM text WHERE # old_text LIKE '%#icon%' old_text REGEXP('#icon') # regex is easier to deal with )
A more complex query that summarizes some information about an article (You supply the article title)
SELECT p.page_id 'id', p.page_title 'title', p.page_namespace 'namespace', t.old_id 'latest text id', t.old_text 'content', r.rev_id, r.rev_comment 'comment', r.rev_user, r.rev_user_text, r.rev_parent_id 'previous revision' FROM text t RIGHT JOIN revision r ON t.old_id = r.rev_text_id RIGHT JOIN page p ON r.rev_id = p.page_latest WHERE p.page_title = 'Main_Page'
See Also[edit | edit source]
21 Oct 2015 17:57 James Day
The now-GA 5.7 provides a feature to create general tablespaces anywhere you like and store multiple tables in them, as well as move individual tables between tablespaces, including per-file tablespaces and the system tablespace. Described at https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html . As with the shared tablespace, once space has been allocated from the filesystem there is no way to release it. If you need that capability, use per-table tablespaces instead, those do fully release the space allocated to a table when it is dropped.
This provides no way to make the shared tablespace smaller but it can be used to reduce the pain of emptying it by moving tables to a different common tablespace. Combine that with putting the undo log files in a different place, as they are now by default, and with temporary tables going to their own dedicated tablespace and there will be relatively little left in the shared tablespace.
Even so, the process of moving tables and doing a new install to get rid of the disk space allocated to the shared tablespace is painful for installations that do have lots of data or redo there. (emphasis added)
James Day, MySQL Senipr Principal Support Engineer, Oracle