Recently, I supported clients solving MySQL performance issues. There were several areas where MySQL performance issues were addressed. However, one issue, which was overlooked prior to our communication, was the keeping of unused databases.
There was about 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, use innodb_buffer_pool_size, key_buffer_size, table_open_cache, table_definition_cache, and others were significantly reduced. Let’s see why keeping an unused database can hurt the performance of MySQL.
How does MySQL deal with unused databases
To see all the MySQL databases on your server. from
mysql> use command-line
show databases; Permission.
If you cloned your live database for development on the same MySQL server, it means that MySQL will have to allocate about twice the server memory. In short, everything located in MySQL’s data directory will affect MySQL’s performance. All databases and tables, even when not in use, consume the least amount of memory INFORMATION_SCHEMA, INFORMATION_SCHEMA is a database within each MySQL instance that stores information about all other databases. This situation can make it difficult and time-consuming to accurately configure and tune MySQL’s performance.
In the above screenshot InnoDB’s “buffer pool” is 100% full. This is not good because it means there is no available buffer pool left. Thus, you will want to confirm whether the size of the pool is sufficient. For example, if MySQL Check/Repair is used on all databases, you will quickly be pushing both used and unused data into server memory. Although unused data may not fill your server’s memory in an instant, it is quite possible over time and will undoubtedly be the case if you keep your development databases on the same server.
MySQL performance tips to avoid excessive data size
Another optimization in this field would be to remove unused data from specific tables. Even on active databases, you’ll want to sort out the old data. For example, keeping around Matomo’s five years of statistics May not be necessary and should be cut off. Similarly, whether you are using WordPress, Joomla, Drupal, Magento, or others, there is usually a set of tables that you should keep an eye on. Make sure they don’t grow to the point of hindering the performance of MySQL.
As promised, here are some quick MySQL tips related to data size management:
- Backup, Backup, Backup! Before making any recommended changes, back up all DB and related tables.
- Drop unused databases.
- Drop or trim unused tables and old/redundant 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 that there is no sudden increase in data size so that your my.cnf changes remain valid for a long time.
Published: October 4, 2018 | Updated: 3 February 2022