Last year, I was assisting a new client in addressing MySQL performance issues. There were several areas where MySQL performance issues were addressed. One issue overlooked for several years prior to our communications, was that of keeping around unused databases. Over 15GB of unused MySQL data – a mix of MyISAM and Innodb tables – which the 64GB RAM server was previously tuned for. Once removed, usage of innodb_buffer_pool_size, key_buffer_size, table_open_cache, table_definition_cache and others were significantly lowered. Lets take a look at why even unused databases can hurt MySQL’s performance.
How MySQL deals with unused databases
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 2x 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 and it’s contents. For example, if MySQL check/repair is used on all-databases (not recommended, but happens) 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 Piwik’s stats from 5 years ago may not be necessary and should be pruned. Similarly, whether you are using WordPress, Joomla, Drupal, Magento or other, there are 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 great 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’s no sudden increases in data size, so that your my.cnf changes stay valid longer.
Need to see current list of databases on your MySQL server? From mysql> command line use the show databases; command: