MySQL Forums
Forum List  »  Performance

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MySQL server configuration for a 8 cpu+20gb RAM server
2459
August 11, 2010 08:28AM


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.