Question :
I’m not a DBA so looking for some advice on tuning a MySQL (MyISAM) database. I’ve found that during peak hours we are getting pretty severe load spikes which are slowing down the web front-end.
I’ve also used JMeter to run tests against the server, and with 20 concurrent users, load spikes to 400%. This doesn’t seem normal.
I’ve ran MySQL Tuner and the following have been flagged up:
[!!] Query cache prunes per day: 4351
[!!] Joins performed without indexes: 155460
[!!] Table cache hit rate: 0% (1K open / 11M opened)
The query cache prunes seems straight forward, and I think I could safely increase this to 100MB without any negative scalability isuses.
I’m confused by ‘joins without indexes’ as all my tables are indexed. Is this more likely to be an issue with queries using non-indexed columns?
The table cache hit rate is also something I’m not sure about. The number of Open_tables never decreases below 1000, suggesting the tables never close. 1000 is the value of table_open_cache in my config.
Answer :
load spikes to 400%
That probably means that you are CPU-bound and not I/O-bound. And hence, you have plenty of RAM, but the queries are inefficient.
[!!] Query cache prunes per day: 4351
In most production systems, the write traffic is so high that the Query cache should be turned off. There are other metrics to further demonstrate that.
[!!] Joins performed without indexes: 155460
For tiny tables, that does not matter. For even small tables, that could matter.
Turn on the slowlog with long_query_time = 1; wait a day; run pt-query-digest. Then show us the first couple of naughty queries, together with SHOW CREATE TABLE and EXPLAIN SELECT .... That is very likely to include the worst offenders of “non-indexed JOINs”.
[!!] Table cache hit rate: 0% (1K open / 11M opened)
You have more than 1000 tables? And you are actively using more than 1000? That sounds like a poor schema design. Please elaborate on why there are so many tables. (Meanwhile, increase table_open_cache to, say, 2000 and set table_open_cache_instances to 16 (assuming you are running at least 5.6.6).
Don’t go past 100M for query_cache_size. It gets inefficient as it gets bigger.
In my opinion, log-queries-not-using-indexes is more clutter than benefit.
For a head-start on fixing the indexes, see my cookbook.
If you would like a lot more tests (more than what that tool did), provide me with
- RAM size
SHOW VARIABLES;SHOW GLOBAL STATUS;
It will include an analysis of whether the QC is being useful.
Your performance issues can likely be solved by fixing the second issue: joins without indexes. If you are making joins against non-indexed columns in your query, it will have to do a lot more work to determine 1) if there are matches and 2) what matches.
You can use the log option log-queries-not-using-indexes in your MySQL configuration (my.cnf) to log these queries. From there, you can create appropriate indexes to cover the join operations or modify the joins to use existing key columns / indexes. EXPLAIN can help you understand what the engine itself is doing when you execute the query.
Once that is solved, tweaking your caching should improve the performance some, but fixing the joins and making them more efficient will allow the server to operate a better speeds even if the cache isn’t warm.