5.7.1.3. Query Probes

The query-start and query-done probes are triggered when a specific query is received by the server and when the query has been completed and the information has been successfully sent to the client.

query-start(query, connectionid, database, user, host)query-done(status)

query-start: Triggered after the query string has been received from the client. The arguments are:
- query: The full text of the submitted query.
- connectionid: The connection ID of the client that submitted the query. The connection ID equals the connection ID returned when the client first connects and the Id value in the output from SHOW PROCESSLIST.
- database: The database name on which the query is being executed.
- user: The username used to connect to the server.
- host: The hostname of the client.
query-done: Triggered once the query has been executed and the information has been returned to the client. The probe includes a single argument, status, which returns 0 when the query is successfully executed and 1 if there was an error.

You can get a simple report of the execution time for each query using the following D script:

#!/usr/sbin/dtrace -s#pragma D option quietdtrace:::BEGIN{   printf("%-20s %-20s %-40s %-9s\n", "Who", "Database", "Query", "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;}mysql*:::query-done{   printf("%-20s %-20s %-40s %-9d\n",self->who,self->db,self->query,          (timestamp - self->querystart) / 1000000);}

When executing the above script you should get a basic idea of the execution time of your queries:

shell> ./query.dWho                  Database             Query                                    Time(ms)root@localhost       test                 select * from t1 order by i limit 10     0root@localhost       test                 set global query_cache_size=0            0root@localhost       test                 select * from t1 order by i limit 10     776root@localhost       test                 select * from t1 order by i limit 10     773root@localhost       test                 select * from t1 order by i desc limit 10 795