Recently, I supported a new client with solving MySQL performance issues. There were several areas where MySQL performance issues were addressed. One issue, however, was overlooked prior to our communications, was that of keeping around unused databases.
There was around 15 gigabytes of unused MySQL data – a mix of MyISAM and Innodb tables – for which the server had not been tuned for. So once removed, usage of innodb_buffer_pool_size, key_buffer_size, table_open_cache, table_definition_cache, and others were significantly lowered. Let’s take a look at why keeping around unused databases can hurt MySQL’s performance.
How MySQL deals with unused databases
To view all MySQL databases on your server. From
mysql> command-line use the
show databases; command.
If you’ve created a clone of your live database for development on the same MySQL server, this means that MySQL will have to allocate roughly two times the server memory. In short, everything located in MySQL’s data directory will affect MySQL’s performance. All databases and tables, even if not in use, at the very least consume memory in the INFORMATION_SCHEMA. The INFORMATION_SCHEMA is a database within each MySQL instance, the place that stores information about all the other databases. This condition can make it difficult and time-consuming to configure and tune MySQL’s performance accurately.
In the screenshot above, InnoDB’s “Buffer Pool” is 100% full. This is not good because it means that there’s no available buffer pool left. Thus, you’ll want to confirm if the pool size is adequate. For example, if MySQL check/repair is used on all-databases you will quickly push both used and unused data into server memory. Although unused data may not fill your server’s memory in an instant, it’s quite possible over time and will certainly be the case if you keep your development database on the same server.
MySQL Performance tips to avoid excessive data size
Another optimization in this area would be to delete unused data from specific tables. This means even on active databases, you would want to prune old data. For example, keeping around five years of Matomo’s stats may not be necessary and should be pruned. Similarly, whether you are using WordPress, Joomla, Drupal, Magento, or other, there’re usually a set of tables which you should keep an eye on. Making sure they do not grow to the point of being a hindrance to MySQL performance.
As promised, here are some quick MySQL tips relating to data size management:
- Backup, backup, backup! Before making any of the recommended changes, backup all DBs and tables in question.
- Drop unused databases.
- Drop or prune unused tables and/or old and unnecessary data.
- Avoid running your Dev and Live databases on the same server.
- Use mysqldump if you need to archive MySQL databases/tables.
- Use monitoring tools to ensure there are no sudden increases in data-size, so that your my.cnf changes stay valid longer.
Originally published: Oct 4, 2018