Menu
Show status like 'Qcache_queries_in_cache' returns:
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_queries_in_cache | 327 |
+-------------------------+----------+
How do I print these 327 queries?
In an attempt to optimize mysql caching I want to trying switching to "on demand" caching. But before I do I want to get a definitive sense of which queries are being cached or discarded. I tried mysql docs, google, and stackoverflow search but no luck.
711
To make sure MySQL Query Cache is enabled use: mysql> SHOW VARIABLES LIKE 'have_query_cache'; To monitor query cache stats use: mysql> SHOW STATUS LIKE 'Qcache%';
The MySQL query cache is a query results cache. It compares incoming queries that start with SEL to a hash table, and if there is a match returns the results from the previous execution of the query. There are some restrictions: The query must match byte-for-byte (the query cache avoids parsing)
MySQL uses its own query cache. Say, we have a table with 1 mil rows, you query it like select count(*) - 1st time will be slow-ish, 2nd time will be instantaneous because it'll pull cached data. Same rule applies to views, with the difference that MySQL doesn't cache subselect results.
You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache. The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.
AFAIK sql queries are not stored into Qcache only their hash. So there is no way to find what queries are now cached exept you execute one of your query and see the changes of Value column.
88
If You enable a Session Specific variable profiling
SET SESSION PROFILING=on;
show profiles;
Now by checking show profile for query your query_id;
mysql> show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000017 |
| checking query cache for query | 0.000005 |
| checking privileges on cached | 0.000003 |
| sending cached result to clien | 0.000005 |
| logging slow query | 0.000002 |
| cleaning up | 0.000001 |
+--------------------------------+----------+
By checking the status column you can see whether the queries cached.
But this is only a session specific you have enable profiling for each session.
21
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
