mysql 调试分析利器_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();

+---------------------+----------------+-------+-----------+------+---------+------+---------------------------------+----------------------------------------+

| mysqld_os_thread_id | processlist_id | user | host | db | command | time | state | statement |

+---------------------+----------------+-------+-----------+------+---------+------+---------------------------------+----------------------------------------+

| 27015 | 4 | admin | localhost | null | sleep | 2597 | | null |

| 28252 | 6 | admin | localhost | test | query | 1166 | waiting for table metadata lock | alter table test.test_1 drop index idx |

+---------------------+----------------+-------+-----------+------+---------+------+---------------------------------+----------------------------------------+

(gdb) thread 4

[switching to thread 4 (thread 0x7f8d107f8700 (lwp 28252))]

#0 0x00007f8d3b147d12 in pthread_cond_timedwait@@glibc_2.3.2 () from /lib64/libpthread.so.0

bt

........

#4 mdl_wait::timed_wait (this=0x7f8ce4000958, owner=0x7f8ce40008c0, abs_timeout=0x7f8d107f37e0, set_status_on_timeout=false,

wait_state_name=) at /export/home/pb2/build/sb_0-27500212-1520171728.22/mysql-5.7.22/sql/mdl.cc:1861

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

注意

pstack 和gdb都可以获取堆栈信息，区别是gdb是交互式的(需要手动退出)，pstack是快照形式(执行3s自动退出)

对mysqld使用gdb后，已经建立的连接无法再执行语句，新连接无法建立

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

参考

希望与广大网友互动？？

点此进行留言吧！