Database Tuning Issues 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.
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.