|
|
| (6 intermediate revisions by one other user not shown) |
| Line 1: |
Line 1: |
| − | ==Server System Variables==
| |
| − | It's important with MySQL (and MariaDB) to use <code>innodb_file_per_table ON</code> so that you do not end up with an extremely large and ever-growing ibdata1 file.<ref>https://bugs.mysql.com/bug.php?id=1341 <br />
| |
| − | '''21 Oct 2015 17:57 James Day'''<br />
| |
| − | 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.''
| |
| − | <br /><br />
| |
| − | 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.
| |
| − | <br /><br />
| |
| − | ''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)
| |
| − | <br /><br />
| |
| − | James Day, MySQL Senior Principal Support Engineer, Oracle
| |
| − | </ref> 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==
| |
| − | In the mysql console, you can use a global variable to turn logging on
| |
| − | <source lang="mysql">
| |
| − | SET global general_log = 1;
| |
| − | </source>
| |
| − | And specify the log file (The MySQL user must have write permission to this file.)
| |
| − | <source lang="mysql">
| |
| − | SET global general_log_file = '/var/log/mariadb/mariadb.log';
| |
| − | </source>
| |
| − | 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.
| |
| − | <source lang="mysql">
| |
| − | SET global general_log = 0;
| |
| − | </source>
| |
| − |
| |
| − | ==Query Examples==
| |
| − |
| |
| − | Some database queries that you might want to use and bookmark in your database administration tool such as [https://dev.mysql.com/downloads/workbench/ MySQL Workbench] or SQLyog http://www.webyog.com/en/index.php
| |
| − |
| |
| − | <source lang="mysql">
| |
| − | -- 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';
| |
| − | </source>
| |
| | | | |
| | 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'. | | 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'. |
| − | <source lang="mysql"> | + | <source lang="sql"> |
| − | SELECT DISTINCT | + | SELECT DISTINCT page_title |
| − | page_title
| + | FROM page |
| − | FROM | + | RIGHT JOIN revision ON page_id = rev_page |
| − | page
| + | WHERE rev_text_id |
| − | RIGHT JOIN
| + | IN ( |
| − | revision
| + | SELECT old_id |
| − | ON page_id = rev_page
| + | FROM `text` |
| − | WHERE | + | WHERE old_text LIKE '%#icon%' |
| − | rev_text_id
| + | ) |
| − | IN (
| |
| − | SELECT | |
| − | old_id
| |
| − | FROM | |
| − | text
| |
| − | WHERE | |
| − | -- old_text LIKE '%#icon%'
| |
| − | old_text REGEXP('#icon') # regex is easier to deal with
| |
| − | )
| |
| − | </source>
| |
| − | | |
| − | A more complex query that summarizes some information about an article
| |
| − | (You supply the article title)
| |
| − | <source lang="mysql">
| |
| − | 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'
| |
| | </source> | | </source> |
| − |
| |
| − | ==See Also==
| |
| − |
| |
| − | *[[Collation]]
| |
| − | *[[mw:Manual:Database_access]]
| |
| − |
| |
| − | {{References}}
| |
| | | | |
| | [[Category:Database]] | | [[Category:Database]] |
| | [[Category:Wiki]] | | [[Category:Wiki]] |
| − | [[Category:Development]]
| |
| − | [[Category:Help]]
| |
| − | [[Category:Tools]]
| |
| − | [[Category:MediaWiki]]
| |
