0

On our current MySQL server query cache is enabled.

Qchache_hits: 31913 
Qchache_inserts: 50959
Qchache_lowmem_prunes: 9320
Qchache_not_chached: 209320
Qchache_queries_in_chace: 986

com_update: 0
com_delete: 0
com_Select: 10

I do not fully understand the Query cache - I am reading about it currently and trying to understand it.

Our database holds inventory data, customer data, employee data, sales data and so forth. The query is very rarely run more than once. The possibility of a query being run twice is viewing a specific sales information twice. But basically everything in our system changes constantly. It is always being updated, deleted, insterted and off the top of my head I can't picture users running the same query twice within a week.

Do I even need to have the query cache enabled? I am guessing that the inserts means 51k entries have been added, but only 986 of those are being stored?

Would an idea be to refresh the cache, and watch it for a week and check how many of the queries in cached are accessed maybe on a weekly basis to see if it is actually returning any benefits?

Any help/guidance on this is appreciated, thanks

Jeff
  • 1,089
  • 5
  • 25
  • 46

1 Answers1

2

Yes, 51k entries were added, only 986 are in there now. so there were roughly 50k entries which were added to the cache, but are no longer there, 9k of them because it ran out of memory in the cache and the rest because inserts/updates invalidated cache entries. Com_select is a parameter which is useful to know when trying to decide if you are getting benefits. But really it depends mostly on what queries are being cached.

If you are running select statement that you are sure would not benefit from being cached, you can add SQL_NO_CACHE to the select statment, see the documentation here.

I recommend reading this article (and that blog in general).

stew
  • 9,263
  • 1
  • 28
  • 43
  • I added the com_select variable + value to the question. thanks for the blog link looking over it now. – Jeff Nov 18 '11 at 18:33
  • Also, the com_Select - is that how many cached queries where actually used? – Jeff Nov 18 '11 at 18:33