MySQL tuning on a regular basis without restart

Question

I have a 5GB database, all tables are MyISAM. It runs into heavy load time from 01:30AM to 8:30AM (100+ selects, 150+ updates, 200+ cache hits per second) to do data analysis, during other time, load is moderate (10 selects, 5 inserts per second).

Problem is after a few days, data analysis during heavy load time appears to be slow down maybe due to query cache prunes (iowait increases). Current query cache is set to 1.5G while total RAM is 4G. It runs fast again after manually restart mysql server.

Is there a way to do regular optimization or cleaning up on mysql server to keep it running in a efficiently without a restart

Answer 1

It sounds to me like your application is busy updating the tables and you might have table contention. Do you have mytop running, or does SHOW PROCESSLIST give you any insight as to what part of your application is doing the most work? Have you enabled --slow-query-log setting?

Also, your database table engine might be an issue. Are you using MyISAM or InnoDB? You want to look out for table locking during updates, and how much of a backup that can create.

If you are issuing FLUSH QUERY CACHE, that can lead to badness, many versions of MySQL exhibit near-lockup when running that command.

Also, running top and checking /var/log/cron for cronjobs that might be affecting system load could help. If you are running updatedb or logrotate on your server, that could affect iowait.

Answer 2

It seems like your query cache size is far too large. While the query cache is usually a good thing, if it is too large it can hurt more then it helps.

This behavior is discussed in this article:

The issue here was that the customer had a moderate level of write traffic, and the current query cache implementation invalidates all result sets for a given table whenever that table is updated. As the query cache grows in size, the number of entries that must be invalidated for a given table may grow as well. In addition, the coarse locking on the cache can lead to lock contention that can kill performance, particularly on multi-core hardware.

I would recommend lowering the size of your query cache to somewhere between 16-128MB and see how that effects performance.

Another possibility is that the queries are generating really small result sets which is causing memory fragmentation. More information on this is available here, look for the "query_cache_min_res_unit" setting.