Mysql – Database Tuning Issues

myisamMySQLtuning

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.

Best 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.

Related Solutions

Mysql – thesql fine tuning: open_tables, opened_tables

Three points:

I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

With regard "too many connections", try the following: Set max_connections to some reasonable value, like 300, throwing around a number. Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files. Do you have some connection pool which holds many open (idle) connections, with some keepalive? How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set: table_open_cache=2048 table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion. The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.

Mysql – Simple MySQL query randomly takes forever

To speed up the execution of your query, you need to create a compound index:

ALTER TABLE email_messages
ADD INDEX idx1(is_sent, is_cancelled, time_created);

More information: Multiple-Column Indexes

Best Answer

Related Solutions

Mysql – thesql fine tuning: open_tables, opened_tables

Mysql – Simple MySQL query randomly takes forever

Related Question