MySQL 几种调式分析利器

pstack

mysql_pid=4522
pstack $mysql_pid>pstack.info

pt-pmp对堆栈信息排序
pt-pmp pstack.info | less

也可以直接执行pt-pmp
pt-pmp --pid 4522
如

     10 __io_getevents_0_4(libaio.so.1),LinuxAIOHandler::collect(os0file.cc:2502),LinuxAIOHandler::poll(os0file.cc:2648),os_aio_linux_handler(os0file.cc:2704),os_aio_handler(os0file.cc:2704),fil_aio_wait(fil0fil.cc:5835),io_handler_thread(srv0start.cc:311),start_thread(libpthread.so.0),clone(libc.so.6)
      3 pthread_cond_wait,wait(os0event.cc:165),os_event::wait_low(os0event.cc:165),srv_worker_thread(srv0srv.cc:2520),start_thread(libpthread.so.0),clone(libc.so.6)
      2 pthread_cond_wait,native_cond_wait(thr_cond.h:140),my_cond_wait(thr_cond.h:140),inline_mysql_cond_wait(thr_cond.h:140),Per_thread_connection_handler::block_until_new_connection(thr_cond.h:140),handle_connection(connection_handler_per_thread.cc:329),pfs_spawn_thread(pfs.cc:2190),start_thread(libpthread.so.0),clone(libc.so.6)

gdb

mysqld_pid 4522
gdb -p $mysqld_pid
(gdb) info thread 显示运行的所有线程
(gdb) thread 4 切换到某个线程
bt 显示调用栈
如

SELECT B.THREAD_OS_ID AS mysqld_os_thread_id,A.ID as processlist_id,A.USER,A.HOST,A.DB,A.COMMAND,A.TIME,A.STATE,LEFT(A.INFO,150) AS statement FROM information_schema.PROCESSLIST A INNER JOIN performance_schema.threads B on A.ID=B.PROCESSLIST_ID where A.id !=connection_id();

processlist_id 为6（ os中 mysqld的线程id为28252）正在等待MDL 元数据锁，通过gdb堆栈跟踪发现，该连接正在执行的内部函数为 pthread_cond_timedwait

注意

MySQL 几种调式分析利器-LMLPHP

strace

查看系统调用
strace -cp $mysqld_pid

查看执行语句
mysqld_pid=4522
strace -f -F -ff -o mysqld-strace -s 1024 -p $mysqld_pid
find ./ -name "mysqld-strace" -type f -print |xargs grep -n "SELECT.FROM"

查看读写的文件
strace -o /tmp/strace_output.txt -T -tt -f -e trace=read,open -p “mysqld_pid”

perf

mysqld内部函数整体消耗情况
perf top -p 4522
如

Samples: 66  of event 'cpu-clock', Event count (approx.): 6520074
Overhead  Shared Object       Symbol
  18.42%  mysqld              [.] sync_array_print_long_waits_low
  10.83%  [kernel]            [k] _raw_spin_unlock_irqrestore
   7.88%  libaio.so.1.0.1     [.] 0x0000000000000645
   7.30%  [kernel]            [k] finish_task_switch
   6.84%  [kernel]            [k] system_call_after_swapgs
   6.22%  [kernel]            [k] aio_read_events
   6.02%  libc-2.17.so        [.] __memset_sse2

记录所有内部函数调用
该命令会输出记录到perf.data中
perf record -p 4522

解析并查看perf.data内容
perf script -i perf.data>perf.log && less perf.log

参考

MySQL所有操作hang住问题的故障排查
 linux strace追踪mysql执行语句（mysqld --debug)
linux 调试利器gdb, strace, pstack, pstree, lsof
Debugging a MySQL Server
What to Do If MySQL Keeps Crashing

lblog_s5ssp1ty