Mysql – Optimizing MySQL –


I've been researching how to optimize MySQL a bit, but I still have a few questions.

MySQL Primer Results

Based on this, the first problem seems to be that the max_connections limit is too low. I had a similar problem with Apache initially, the max connection limit was set to 100, and the web server would frequently lock up and take an excruciatingly long time to deliver pages. Raising the connection limit to 512 fixed this issue, and I read that raising the connection limit on MySQL to match this was considered good practice.

Being that MySQL has actually been "locking up" recently as well (connections have been refused entirely for a few minutes at a time at random intervals) I'm assuming this is the main cause of the issue.

However, as far as table cache goes, I'm not sure what I should set this as. I've read that setting this too high can hinder performance further, so should I raise this to right around 551, 560, 600, or do something else?

Lastly, as far as raising the join_buffer_size value goes, this doesn't even seem to be included in Debian's my.cnf file by default. Assuming there's not much I can do about adding indexes, should I look into raising this? Any suggested values?

Any suggestions in general here would be appreciated as well.

Edit: Here's the number of open tables the MySQL server is reporting. I believe this value is related to my question (Opened_tables: 22574)

Best Answer

When using Apache, lower MaxClients to, say, 20. There is no advantage in having lots of Apache children stuck waiting for MySQL -- latency suffers and throughput does not improve.

Decrease @@global.wait_timeout -- why have idle connections clog the max_connections.

Clients should disconnect when they are through -- reconnecting is cheap.

If Opened_tables / Uptime > 2 (per second), increase table_open_cache. In the past, it was unwise to raise that too much -- the cache was linearly scanned. I think it is now hashed.

Open_tables / table_open_cache (0..100%) -- not as important as Opened_tables per second.

open_files_limit is indirectly controlled by the OS (ulimit -n). Sometimes the OS puts an unreasonably small limit here. 16K is not unreasonable.

"connections refused" -- do you mean "max_connections exceeded"? See SHOW STATUS LIKE 'Max_used_connections';

For your applications the user(s) should not have SUPER privilege. SUPER gets an 'extra' connection so you (the DBA) can connect and see what is going on.