Re: Help tunning MySQL 5.5 (query_cache_size, table_cache, ...)

[Rick James]

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.)