Re: MySQL server configuration for a 8 cpu+20gb RAM server
Posted by:
Rick James
Date: August 11, 2010 08:28AM
You have _plenty_ of RAM. MySQL is using only a fraction of it. And most of the tunables are using it adequately.
Some of the "!!" entries are best dealt with by looking at the offending queries...
Set long_query_time = 1, then (after awhile) see what is in the slowlog. Then, let's discuss how they can be optimized.
SHOW PROCESSLIST (repeatedly); see what queries show up a lot. Then, let's discuss how they can be optimized.
Tables: 288896 -- why so many?
Joins performed without indexes: 169459 -- This may be worth looking into; but if the tables are tiny, it does not really matter. (Again, let's look at the slow queries.)
Table cache hit rate: 0% (431 open / 22M opened) -- I can't tell whether this is a problem. Could you do
SHOW VARIABLES LIKE 'table%';
SHOW GLOBAL STATUS LIKE '%open%';
SHOW GLOBAL STATUS LIKE 'Uptime';
(I don't know which values mysqltunner is using.)
Run OPTIMIZE TABLE to defragment tables for better performance -- Because everything is cached in RAM, this probably won't help any.
Adjust your join queries to always utilize indexes -- Well, we need to see the offending SELECTs.
Increase table_cache gradually to avoid file descriptor limits -- pending my note above.
query_cache_size (>= 8M) -- Might help; might hurt. You could try
query_cache_type = ON
query_cache_size = 50M
Then run for awhile and show me
SHOW VARIABLES LIKE 'query_cache%';
SHOW GLOBAL STATUS LIKE 'Qc%';
It could be that your application can't really use the QC.
These changes may help (hard to say):
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_cache (> 431) -- (discussed above)