Re: Optimal Mysql Configuration (my.cnf)
> 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.