MySQL Forums
Forum List  »  Performance

Re: Help tunning MySQL 5.5 (query_cache_size, table_cache, ...)
Posted by: Rick James
Date: February 20, 2013 10:35PM

Please provide the output from
SHOW VARIABLES;
SHOW GLOBAL STATUS; -- after mysqld has been running more than hour (preferable much longer)
(Some of the necessary details are missing from mysqltuner output.)

> 4gb ram
- query_cache_size (> 32M)
[OK] Query cache efficiency: 90.0% (198M cached / 220M selects)
[!!] Query cache prunes per day: 163815

High efficiency (good), but very high prune rate (2/sec). 32M is about as big as is safe on a that sized machine. Don't worry about Query cache settings.

- join_buffer_size (> 512.0K, or always use indexes with joins)

That's a vague statement. Do you have the slowlog turned on? Let's see the worst queries.

- table_cache (> 200)

Need the SHOWs to judge it further.

[!!] InnoDB data size / buffer pool: 791.9M/64.0M
- innodb_buffer_pool_size (>= 791M)

Maybe. It depends on the usage patterns. 64M is probably too small, but >791M may be a waste. However, if you are running just MySQL on this 4GB box, then 800M might be reasonable for innodb_buffer_pool_size. The SHOWs will provide the answer.

> Run OPTIMIZE TABLE to defragment tables for better performance

Don't bother; it rarely helps.

> Increase table_cache gradually to avoid file descriptor limits

It's been called table_open_cache for nearly a decade. I'm surprised that Tuner has not been updated. (The SHOWs will provide more info.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help tunning MySQL 5.5 (query_cache_size, table_cache, ...)
3145
February 20, 2013 10:35PM


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.