Bureaucrats, confirmed, Administrators
4,558
edits
Changes
Jump to navigation
Jump to search
no edit summary
== 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 Senipr 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
== Query Examples ==
Some database queries that you might want to use and bookmark in your database administration tool such as MySQL Query Browser. http[https://dev.mysql.com/downloads/gui-toolsworkbench/5.0.html MySQL Workbench] or SQLyog http://www.webyog.com/en/index.php
<source lang="mysql">
* [[Collation]]
* [[mw:Manual:Database_access]]
{{References}}
[[Category:Database]]
[[Category:Help]]
[[Category:Tools]]
[[Category:MediaWiki]]
