4

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.

3

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.

  • Hi Rick, I actually only have 200 tables, which is why I don't understand why 1000 are open. I know MyISAM will open a new table for each connection, but the issue is the tables never seem to close. They stay open until it hits the table_open_cache figure. Increasing to 2000 has the same effect. – Stephen91 Jul 13 '15 at 10:44
  • We have 16GB RAM, very little of which is used. How do you want me to provide the variables and status? It's too many chars to post, are there specific variables you need? – Stephen91 Jul 13 '15 at 10:53
  • I would rather have all the variables and status; they interact in many different ways. There are several sites that allow posting arbitrary information (for free), for example: pastebin.com – Rick James Jul 24 '15 at 22:38
  • See also SHOW OPEN TABLES; In_use will say how many connections are actively using each table. (No, this probably won't explain the 200:1000 question you are posing.) – Rick James Jul 24 '15 at 22:46
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.