Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

migrated from programmers.stackexchange.com Aug 28 '12 at 19:40

This question came from our site for professional programmers interested in conceptual questions about software development.

    
Have you considered using Connection Pooling: dev.mysql.com/tech-resources/articles/…? –  Emmad Kareem Aug 28 '12 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 '12 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 '12 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 '12 at 4:52
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.