From DTraceBook

This chapter uses DTrace for analyzing the MySQL, PostgreSQL and Oracle databases.

Contents 1 Sample One-Liners 1.1 MySQL 1.2 PostgreSQL 2 Scripts 3 Errata 4 Links

Sample One-Liners

See the DTrace book for more one-liners.

MySQL

MySQL: query trace by query string:
dtrace -n 'mysql*:::query-start { trace(copyinstr(arg0)) }'

MySQL: query count summary by host:
dtrace -n 'mysql*:::query-start { @[copyinstr(arg4)] = count(); }'

MySQL server: trace queries:
dtrace -qn 'pid$target::*mysql_parse*:entry { printf("%Y %s\n", walltimestamp, copyinstr(arg1)); }' -p PID

MySQL client: who's doing what (stack trace by query):
dtrace -Zn 'pid$target:libmysql*:mysql_*query:entry { trace(copyinstr(arg1)); ustack(); }' -p PID

PostgreSQL

PostgreSQL: query trace by query string:
dtrace -n 'postgresql*:::query-start { trace(copyinstr(arg0)) }'

PostgreSQL: query count summary by query string:
dtrace -n 'postgresql*:::query-start { @[copyinstr(arg0)] = count(); }'

PostgreSQL server: count queries:
dtrace -n 'pid$target::exec_simple_query:entry { @[copyinstr(arg0)] = count(); }' -p PID

Scripts

• mysqld_pid_qtime.d
• mysqld_qchit.d
• mysqld_qslower.d
• mysqld_qsnoop.d
• libmysql_snoop.d
• pg_pid_qtime.d
• pg_qslower.d
• cpudispqlen.d
• qtime.d
• ssdlatency.d
• sysdispqlen.d

Errata

Links

• MySQL Query Latency with the DTrace pid Provider
• File System latency from MySQL was analyzed in a series of blog posts on dtrace.org: 1, 2, 3, 4, 5
• MySQL Performance Schema and DTrace
• PerconaLive 2011 NYC talk on breaking down MySQL/Percona query latency with DTrace