CODEWITHSUNDEEP
phpsql-servermysql
BlackMouse
BlackMouse

Reputation: 4572

Query cache efficiency at 2.4 %

I'm trying to optimize my mysql database. I have about 2000 queries / second and 16GB RAM on the VPS, running debian. Just ran MySQL Tuner. It says: 

[!!] Query cache efficiency: 2.4% (1K cached / 51K selects)
... 
query_cache_limit (> 8M, or use smaller result sets)

Does this mean I should increase the query_cache_limit or lower it?

I have these settings right now: 

query_cache_limit   = 8M
query_cache_size    = 256M

Thanks

Upvotes: 0

Views: 820

Answers (3)

Brian van Rooijen
Brian van Rooijen

Reputation: 2016

For us it worked to lower the query_cache_limit. the query cache limit sets how large resultset can be to allow it in the query cache.

If you set the limit lower (in our case 64k) you can store a lot more small queries in the cache.

256M / 8M = 32 queries 256M / 64K = 4096 queries

Upvotes: 0

symcbean
symcbean

Reputation: 48357

You'll get better performance by switching it off with the stats as they are.

It would have been helpful if you'd shown the actual memory usage (as a percent: (query_cache_size - Qcache_free_memory)/query_cache_size)

Throwing memory at it isn't likely to help. It's likely that there are a small number of queries which will benefit from caching - I'm assumiong that you've got query_cache_type set to 1 (automatic). Change this to 2 and add a SQL_CACHE declaration to the selects which should be cacheable (NB MySQL requires the first letter of a query to be 'S' to consider it a candidate for caching).

Depending on the memory usage and number of free blocks, you should probably reduce the size of the cache too.

Upvotes: 2

Wesley van Opdorp
Wesley van Opdorp

Reputation: 14941

You should start by optimizing your queries to make use of the query cache. 2,4% efficiency is really low. Great read here, it will give ALOT of intel on how the query cache actually works.

Query must be deterministic I found to be very interesting..it makes you think about using things as NOW() in your queries. I'm sorry I can't be of more help regarding optimizing your current 51k selects.

Upvotes: 2

Related Questions