MySQL Performance: Stop hoarding. Drop unused MySQL databases

Recently, I supported a client with solving MySQL performance issues. There were several areas where MySQL performance issues were addressed. One issue, however, that was overlooked before our communications was that of keeping around unused databases.

There were around 15 gigabytes of unused MySQL data – a mix of MyISAM and InnoDB tables – for which the server had not previously been tuned. 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 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 that stores information about all the other databases. This condition can make it difficult and time-consuming to accurately configure and tune MySQL’s performance.

InnoDB’s “Buffer Pool” is 100% full in the screenshot above. 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 undoubtedly 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. 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 others, there’re usually a set of tables on which you should keep an eye. Make 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 old/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.

Published: Oct 4, 2018 | Updated: September 8th 2023