Databases Reference
In-Depth Information
37359 1.3011 mysqld mysqld MYSQLlex()
35917 1.2508 libpthread libpthread __pthread_mutex_unlock_usercnt
34248 1.1927 mysqld mysqld __intel_new_memcpy
If you said “the query cache,” you were right. This server's query cache was causing far
too much work and slowing everything down. This had happened overnight, a factor
of 50 slowdown, with no other changes to the system. Disabling the query cache re-
turned the server to its normal performance. This is an example of when server internals
are relatively straightforward to interpret.
Another important tool for bottleneck analysis is wait analysis using stack traces from
gdb
. A single thread's stack trace normally looks like the following, which we've for-
matted a bit for printing:
Thread 992 (Thread 0x7f6ee0111910 (LWP 31510)):
#0 0x0000003be560b2f9 in pthread_cond_wait@@GLIBC_2.3.2 () from /libpthread.so.0
#1 0x00007f6ee14f0965 in os_event_wait_low () at os/os0sync.c:396
#2 0x00007f6ee1531507 in srv_conc_enter_innodb () at srv/srv0srv.c:1185
#3 0x00007f6ee14c906a in innodb_srv_conc_enter_innodb () at handler/ha_innodb.cc:609
#4 ha_innodb::index_read () at handler/ha_innodb.cc:5057
#5 0x00000000006538c5 in ?? ()
#6 0x0000000000658029 in sub_select() ()
#7 0x0000000000658e25 in ?? ()
#8 0x00000000006677c0 in JOIN::exec() ()
#9 0x000000000066944a in mysql_select() ()
#10 0x0000000000669ea4 in handle_select() ()
#11 0x00000000005ff89a in ?? ()
#12 0x0000000000601c5e in mysql_execute_command() ()
#13 0x000000000060701c in mysql_parse() ()
#14 0x000000000060829a in dispatch_command() ()
#15 0x0000000000608b8a in do_command(THD*) ()
#16 0x00000000005fbd1d in handle_one_connection ()
#17 0x0000003be560686a in start_thread () from /lib64/libpthread.so.0
#18 0x0000003be4ede3bd in clone () from /lib64/libc.so.6
#19 0x0000000000000000 in ?? ()
The stack reads from the bottom up; that is, the thread is currently executing inside of
the
pthread_cond_wait
function, which was called from
os_event_wait_low
. Reading
down the trace, it looks like this thread was trying to enter the InnoDB kernel
(
srv_conc_enter_innodb
), but got put on an internal queue (
os_event_wait_low
) because
more than
innodb_thread_concurrency
threads were already inside the kernel. The real
value of stack traces is aggregating lots of them together, however. This is a technique
that Domas Mituzas, a former MySQL support engineer, made popular with his “poor
man's profiler” tool. He currently works at Facebook, and he and others there have
developed a wide variety of tools for gathering and analyzing stack traces. You can find
out more about what's available at
http://www.poormansprofiler.org
.
We have an implementation of the poor man's profiler in Percona Toolkit, called
pt-
pmp
. It's a shell and
awk
program that collapses similar stack traces together and does
the usual
sort|uniq|sort
to show the most common ones first. Here's what the full set
of stack traces looks like after crunching it down to its essence. We're going to use
