5.7.1.5. Query Cache Probes

The query cache probes are fired when executing any query. The query-cache-hit query is triggered when a query exists in the query cache and can be used to return the query cache information. The arguments contain the original query text and the number of rows returned from the query cache for the query. If the query is not within the query cache, or the query cache is not enabled, then the query-cache-miss probe is triggered instead.

query-cache-hit(query, rows)query-cache-miss(query)

query-cache-hit: Triggered when the query has been found within the query cache. The first argument, query, contains the original text of the query. The second argument, rows, is an integer containing the number of rows in the cached query.
query-cache-miss: Triggered when the query is not found within the query cache. The first argument, query, contains the original text of the query.

The query cache probes are best combined with a probe on the main query so that you can determine the differences in times between using or not using the query cache for specified queries. For example, in the following D script, the query and query cache information are combined into the information output during monitoring:

#!/usr/sbin/dtrace -s#pragma D option quietdtrace:::BEGIN{   printf("%-20s %-20s %-40s %2s %-9s\n", "Who", "Database", "Query", "QC", "Time(ms)");}mysql*:::query-start{   self->query = copyinstr(arg0);   self->connid = arg1;   self->db    = copyinstr(arg2);   self->who   = strjoin(copyinstr(arg3),strjoin("@",copyinstr(arg4)));   self->querystart = timestamp;   self->qc = 0;}mysql*:::query-cache-hit{   self->qc = 1;}mysql*:::query-cache-miss{   self->qc = 0;}mysql*:::query-done{   printf("%-20s %-20s %-40s %-2s %-9d\n",self->who,self->db,self->query,(self->qc ? "Y" : "N"),          (timestamp - self->querystart) / 1000000);}

When executing the script you can see the effects of the query cache. Initially the query cache is disabled. If you set the query cache size and then execute the query multiple times you should see that the query cache is being used to return the query data:

shell> ./query-cache.droot@localhost       test                 select * from t1 order by i limit 10     N  1072root@localhost                            set global query_cache_size=262144       N  0root@localhost       test                 select * from t1 order by i limit 10     N  781root@localhost       test                 select * from t1 order by i limit 10     Y  0