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

MySQL Primer Results http://pastie.org/private/lzjukl8wacxfjbjhge6vw

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)

  • Have you considered using Connection Pooling: dev.mysql.com/tech-resources/articles/…?
    – NoChance
    Aug 28, 2012 at 4:07
  • I should have included more information in my original post. The application that is being used here is IP.Board, it's not my own software. It's PHP based forum software, and sadly I only have very limited PHP knowledge. So, simply, I'm not sure how I could go about getting that to work software wise.
    – Josh
    Aug 28, 2012 at 4:10
  • @Josh: Confused, you said you set max_connections to 512, but the dump you posted appears to say it's set to 100. Also, is it correct that the database is using INNODB and MyISAM? As for Opened_tables that appears to just be the number of tables that have been opened since startup; believe Open_tables is what you're looking for, which is the number of tables that are open at the moment. (MySQL is so much fun!)
    – blunders
    Aug 28, 2012 at 4:46
  • @blunders I raised it to 512 in response to MySQL Primer's above results. I haven't restarted the server yet, I was just saying what I've done so far. I ran the query for "opened_tables" in response to this page. However, Open_tables currently reports 399. My main database is using InnoDB and MEMORY (for session tables), though I run a few other (much smaller scale) sites that use MyISAM. Mainly just a few very small wordpress blogs for some people I know. To clarify, the IP.Board/forum database I care about currently uses InnoDB/HEAP
    – Josh
    Aug 28, 2012 at 4:52

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.