MySQL Tuning & Performance via Query_Cache

Question

I am experiencing slow performance with a CMS (WordPress) on my server.

I ran SHOW STATUS LIKE 'Qcache%';

Result:

Variable_name               Value
Qcache_free_blocks          0
Qcache_free_memory          0
Qcache_hits                 0
Qcache_inserts              0
Qcache_lowmem_prunes        0
Qcache_not_cached           0
Qcache_queries_in_cache     0
Qcache_total_blocks         0

I am thinking that values of zero are not good. Does anyone know if these values are okay? If they are not, do you have any recommendations on how I can improve my sites performance relative to MySQL?

Thanks.

AMENDED

Prior to running the command above, I ran SHOW VARIABLES LIKE 'have_query_cache';.

Result:

Variable_name       Value
have_query_cache    YES

AMENDMENT #2

Then I ran SHOW VARIABLES LIKE 'query_cache%';

Result:

Variable_name                   Value
query_cache_limit               1048576
query_cache_min_res_unit        4096
query_cache_size                0
query_cache_type                ON
query_cache_wlock_invalidate    OFF

AMENDMENT #3

Then I ran SHOW VARIABLES LIKE 'log_slow_queries';

Result:

Variable_name       Value
log_slow_queries    OFF

Looks like query cache is not on. Can you run this. SHOW VARIABLES LIKE 'have_query_cache'; — Mike Brant
– Mike Brant, Commented Aug 13, 2012 at 19:32
OK runs SHOW VARIABLES LIKE 'query_cache%' a let us know what you get. — Mike Brant
– Mike Brant, Commented Aug 13, 2012 at 19:41
Sorry I also should have asked you to run SHOW VARIABLES LIKE 'log_slow_queries' to determine if slow query logging is on. This is unrelated to query cache but could be useful in diagnosing problematic queries. — Mike Brant
– Mike Brant, Commented Aug 13, 2012 at 19:48
If the application is using prepared statements to execute it queries (as many developers are fond of using these days), you will never see any benefit of the query cache on the old version of MySQL, as it will not use the query cache for prepared statements. I think the best place to start would be to turn on slow query logs so you can begin to determine the queries which are the worst performance offenders. Check out this link: dev.mysql.com/doc/refman/5.0/en/slow-query-log.html — Mike Brant
– Mike Brant, Commented Aug 13, 2012 at 20:08
Prepared statements are not the same a stored procedures. I believe more recent versions of Wordpress do make extensive use of prepared statements (not sure if everything is prepared statements). You may want to consider upgrading your MySQL version to 5.1.21 or higher (ideally 5.5) if you are able, in order to take advantage of query caching. In the meantime, you can still use slow query logs to look for especially bad queries. — Mike Brant
– Mike Brant, Commented Aug 13, 2012 at 20:20

Alex Monthy · Accepted Answer · 2012-08-13 19:31:43Z

1

Those values show that the query cache is not active. See http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

answered Aug 13, 2012 at 19:31

Alex Monthy

1,8871 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Tuning & Performance via Query_Cache

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related