10 messages in com.mysql.lists.mysqlRE: query cache question
FromSent OnAttachments
Mazur Worden, Kathy30 Aug 2006 09:43 
Jay Pipes30 Aug 2006 10:06 
Mazur Worden, Kathy30 Aug 2006 10:27 
Philip Hallstrom30 Aug 2006 10:36 
Jay Pipes30 Aug 2006 10:41 
Mazur Worden, Kathy30 Aug 2006 11:42 
Jay Pipes30 Aug 2006 12:11 
Mazur Worden, Kathy30 Aug 2006 12:16 
Jay Pipes30 Aug 2006 12:23 
Mazur Worden, Kathy30 Aug 2006 12:26 
Subject:RE: query cache question
From:Mazur Worden, Kathy (kath@palsnet.info)
Date:08/30/2006 12:16:50 PM
List:com.mysql.lists.mysql

@@global.query_cache_type ON

Variable_name Value Qcache_free_blocks 1 Qcache_free_memory 10477008 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 20318 Qcache_queries_in_cache 0 Qcache_total_blocks 1

Thanks

-----Original Message----- From: Jay Pipes [mailto:ja@mysql.com] Sent: Wednesday, August 30, 2006 2:12 PM To: Mazur Worden, Kathy Cc: mys@lists.mysql.com Subject: RE: query cache question

Please show the output of:

SELECT @@global.query_cache_type;

and then:

SHOW GLOBAL STATUS LIKE 'Qc%';

Thanks!

On Wed, 2006-08-30 at 13:43 -0500, Mazur Worden, Kathy wrote:

The output of that query is:

@@global.query_cache_size 10485760

Kathy Mazur Worden Prairie Area Library System

-----Original Message----- From: Jay Pipes [mailto:ja@mysql.com] Sent: Wednesday, August 30, 2006 12:42 PM To: Mazur Worden, Kathy Cc: mys@lists.mysql.com Subject: RE: query cache question

Those queries look just fine to me.

Could you output the result of the following:

SELECT @@global.query_cache_size;

On Wed, 2006-08-30 at 12:27 -0500, Mazur Worden, Kathy wrote:

Maybe that's my issue, I had though the type of query would not matter.

I've run simple queries in query browser like this: Select * from table t;

And refreshed web pages hit about a hundred times a day containing counting queries like this: select count(BuildingInfo.deliverycode) AS 'Total' from BuildingInfo, AgencyInfo where BuildingInfo.agencyid = AgencyInfo.agencyid AND AgencyInfo.type = 'academic' AND (BuildingInfo.membershipstatus = 'Full' OR BuildingInfo.membershipstatus = 'Developmental')

And select queries at least as long as these: SELECT AgencyInfo.Name, AgencyInfo.agencyid from AgencyInfo where AgencyInfo.Type = 'school' AND (AgencyInfo.membershipstatus = 'Full' OR AgencyInfo.membershipstatus = 'Developmental') ORDER BY AgencyInfo.Name

SELECT AgencyInfo.Name, BuildingInfo.Name as 'BuildingName', AgencyInfo.agencyid, BuildingInfo.buildingid FROM BuildingInfo INNER JOIN AgencyInfo ON BuildingInfo.agencyid = AgencyInfo.agencyid

ORDER

BY

AgencyInfo.Name, BuildingInfo.Name;

There are indexes on the id and name related fields used in the WHERE clauses.

Kathy Mazur Worden Prairie Area Library System

-----Original Message----- From: Jay Pipes [mailto:ja@mysql.com] Sent: Wednesday, August 30, 2006 12:07 PM To: Mazur Worden, Kathy Cc: mys@lists.mysql.com Subject: Re: query cache question

Could you post an example of a query you are expecting to be cached?

On Wed, 2006-08-30 at 11:43 -0500, Mazur Worden, Kathy wrote:

Hi,

I just turned on the query cache by modifying its size and limit and restarting the server. However queries aren't being stored to it:

Variable_name

Value

Qcache_free_blocks

1

Qcache_free_memory

10477008

Qcache_hits

0

Qcache_inserts

0

Qcache_lowmem_prunes

0

Qcache_not_cached

3759

Qcache_queries_in_cache

0

Qcache_total_blocks

1

I've found

http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html

which says the qcache_not_cached variable is the number of queries which were either not SELECT's or have SQL_NO_CACHE specified so I ran several selects (selected records in a few tables using Query Browser and refreshed a few web pages hitting the db). None of the queries

have

the

no cache option specified.

What else would prevent queries from getting into the cache?

Kathy Mazur Worden

Prairie Area Library System