MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimal Mysql Configuration (my.cnf)
Posted by: Rick James
Date: July 08, 2011 08:33PM

> i dont believe that i have an ideal configuration
First, let's be clear. There is no ideal configuration. Some tunables can be changed by a factor of 2 without seeing any difference in performance. Or, in a different situation, that tunable is more important.

I asked the most important question about how you are using the system:
"Are you using MyISAM or InnoDB?"
and gave you a link that explains how to turn the answer, plus "8 GB" of RAM, into the answer for how to set the two most important tunables:
key_buffer_size
innodb_buffer_pool_size

thread_cache -- this is one of those that "doesn't matter". Well, 0 is bad; 1000 is bad. Pick 10. After you have been running for a few weeks, look at SHOW GLOBAL STATUS LIKE 'Thread%'; Then (and only then) can we decide if thread_cache needs to be moved from 10.

query_cache -- this is a big "it depends".
Case 1: Lots of queries are repeated _exactly_. And you are not writing often. Then the query cache can be useful.
Case 2: INSERTs/UPDATEs are happening 'continually' to all the tables. Then you are actually better off completely turning OFF the query cache.
Case 3: etc...

"Bigger is better" -- Some exceptions:
* If set things too big, so that _any_ swapping occurs, that is really bad.
* query_cache_size > 50M leads to puzzling pauses while it massages the QC.
* table_cache (table_open_cache) degrades when over, say, 200. This is because of a linear, not hashing, algorithm being used. What's a good value? It depends on how many tables you have, how often you open them, etc. Opened_tables/Uptime gives a clue.
* Increasing one tunable takes RAM; other tunables need to be decreased (to avoid swapping). Where's the optimal balance? Not easy to figure out.

innodb_thread_concurrency -- "it depends", and "it's changing".
* If you are not using InnoDB, it is irrelevant.
* You have 8 cores; some say to set it to twice that.
* Versions 5.6, Percona -- these may make better use of more cores.
* If you are not stressing the CPU, then this setting is not going to have much effect.

I repeat: If you have high CPU, then let's optimize the queries and the indexes. That will help a lot more than tuning. I've seen many cases of a 10-fold improvement in CPU by adding an index or making a 1-line change to a SELECT.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Optimal Mysql Configuration (my.cnf)
3017
July 08, 2011 08:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.