mysql pstack进程号,使用strace跟踪mysql的运行

Strace简介

官网：https://strace.io/

strace是个功能强大的Linux调试分析诊断工具，可用于跟踪程序执行时进程系统调用(system call)和所接收的信号，尤其是针对源码不可读或源码无法再编译的程序。

使用strace的正确姿势：

• strace -T -tt -o /tmp/strace.log CMD

• strace -T -tt CMD 2>&1 |tee /tmp/strace.log

• strace -T -tt -s 100 -o /tmp/strace.log CMD

• strace -T -tt -s 100 -ff -o /tmp/strace.log CMD #加ff强制strace主线程，如果跟踪mysql，则将CMD替换为/usr/local/mysqld

• strace -T -tt -s 100 -e trace=XXXX -o /tmp/strace.log CMD

root@:mysql_dev:~$strace -c ls > /dev/null

% time seconds usecs/call calls errors syscall

------ ----------- ----------- --------- --------- ----------------

100.00 0.000024 2 11 open

0.00 0.000000 0 10 read

0.00 0.000000 0 1 write

0.00 0.000000 0 14 close

0.00 0.000000 0 12 fstat

0.00 0.000000 0 28 mmap

0.00 0.000000 0 16 mprotect

0.00 0.000000 0 3 munmap

0.00 0.000000 0 3 brk

0.00 0.000000 0 2 rt_sigaction

0.00 0.000000 0 1 rt_sigprocmask

0.00 0.000000 0 3 3 ioctl

0.00 0.000000 0 1 1 access

0.00 0.000000 0 1 execve

0.00 0.000000 0 1 fcntl

0.00 0.000000 0 2 getdents

0.00 0.000000 0 1 getrlimit

0.00 0.000000 0 1 statfs

0.00 0.000000 0 1 arch_prctl

0.00 0.000000 0 2 1 futex

0.00 0.000000 0 1 set_tid_address

0.00 0.000000 0 1 openat

0.00 0.000000 0 1 set_robust_list

------ ----------- ----------- --------- --------- ----------------

100.00 0.000024 117 5 total

跟踪MySQL启动时，依次读取my.cnf的顺序

首先查看mysqld命令行帮助信息，可以看到默认查找My.cnf的顺序：

[root@mysql-118 ~]# /opt/my4406/bin/mysqld --verbose --help|more

... ...

Default options are read from the following files in the given order:

/etc/my.cnf /etc/mysql/my.cnf /usr/local/Percona-Server-5.7.24-27-Linux.x86_64.ssl101/etc/my.cnf ~/.my.cnf

使用strace跟踪验证：strace /opt/my4406/bin/mysqld 2>&1|tee strace_1.log

[root@mysql-119 ~]# more strace_1.log|grep "my.cnf"

stat("/etc/my.cnf", 0x7ffcd22ba4e0) = -1 ENOENT (No such file or directory)

stat("/etc/mysql/my.cnf", 0x7ffcd22ba4e0) = -1 ENOENT (No such file or directory)

stat("/usr/local/Percona-Server-5.7.24-27-Linux.x86_64.ssl101/etc/my.cnf", 0x7ffcd22ba4e0) = -1 ENOENT (No such file or directory)

stat("/root/.my.cnf", 0x7ffcd22ba4e0) = -1 ENOENT (No such file or directory)

由于mysql8.0支持参数修改持久化，每次参数修改会保存到mysqld-auto.cnf，重启mysqld时最后都会读取该配置文件里面的内容覆盖掉前面的配置。所以starce可以看到mysql8多查找了一个配置文件：

[root@mysql-118 ~]# cat strace_mysql8.log|grep ".cnf"

stat("/etc/my.cnf", 0x7ffc58c1eaf0) = -1 ENOENT (No such file or directory)

stat("/etc/mysql/my.cnf", 0x7ffc58c1eaf0) = -1 ENOENT (No such file or directory)

stat("/usr/local/mysql/etc/my.cnf", 0x7ffc58c1eaf0) = -1 ENOENT (No such file or directory)

stat("/root/.my.cnf", 0x7ffc58c1eaf0) = -1 ENOENT (No such file or directory)

stat("data/mysqld-auto.cnf", 0x7ffc58c22620) = -1 ENOENT (No such file or directory)

为strace输出添加时间戳

[root@mysql-119 ~]# strace -T -tt -s 100 -o /tmp/strace_2.log /opt/my4406/bin/mysqld

08:02:42.959913 stat("/etc/my.cnf", 0x7ffdd3d6a1e0) = -1 ENOENT (No such file or directory) <0.000011>

08:02:42.960008 stat("/etc/mysql/my.cnf", 0x7ffdd3d6a1e0) = -1 ENOENT (No such file or directory) <0.000011>

08:02:42.960064 stat("/usr/local/Percona-Server-5.7.24-27-Linux.x86_64.ssl101/etc/my.cnf", 0x7ffdd3d6a1e0) = -1 ENOENT (No such file or directory) <0.000007>

08:02:42.960105 stat("/root/.my.cnf", 0x7ffdd3d6a1e0) = -1 ENOENT (No such file or directory) <0.000011>

MySQL默认启动的线程数

操作系统查看：ps -T pidof mysqld

无法看出每个线程具体是什么，不推荐使用。

利用pstack查看：pstack pidof mysql

不可直接在生产库执行，会导致mysql变的更慢，不推荐使用。

查询performance_schema.threads

mysql5.7以上的版本，推荐使用此方法。可以详细的看出每个线程是什么线程

[root@mysql-119 ~]# mysql --socket=/opt/my4406/run/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 509

Server version: 5.7.24-27-log Percona Server (GPL), Release 27, Revision bd42700

Copyright (c) 2009-2017 Percona LLC and/or its affiliates

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select thread_id, name from performance_schema.threads ;

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

| thread_id | name |

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

| 1 | thread/sql/main |

| 2 | thread/sql/thread_timer_notifier |

| 3 | thread/innodb/io_ibuf_thread |

| 4 | thread/innodb/io_log_thread |

| 5 | thread/innodb/io_read_thread |

| 6 | thread/innodb/io_read_thread |

| 7 | thread/innodb/io_read_thread |

| 8 | thread/innodb/io_read_thread |

| 9 | thread/innodb/io_read_thread |

| 10 | thread/innodb/io_read_thread |

| 11 | thread/innodb/io_read_thread |

| 12 | thread/innodb/io_read_thread |

| 13 | thread/innodb/io_write_thread |

| 14 | thread/innodb/io_write_thread |

| 15 | thread/innodb/io_write_thread |

| 16 | thread/innodb/io_write_thread |

| 17 | thread/innodb/io_write_thread |

| 18 | thread/innodb/io_write_thread |

| 19 | thread/innodb/io_write_thread |

| 20 | thread/innodb/io_write_thread |

| 21 | thread/innodb/page_cleaner_thread |

| 22 | thread/innodb/page_cleaner_thread |

| 23 | thread/innodb/page_cleaner_thread |

| 24 | thread/innodb/page_cleaner_thread |

| 25 | thread/innodb/buf_lru_manager_thread |

| 26 | thread/innodb/buf_lru_manager_thread |

| 27 | thread/innodb/buf_lru_manager_thread |

| 28 | thread/innodb/buf_lru_manager_thread |

| 29 | thread/innodb/srv_monitor_thread |

| 31 | thread/innodb/srv_lock_timeout_thread |

| 32 | thread/innodb/srv_error_monitor_thread |

| 33 | thread/innodb/srv_master_thread |

| 34 | thread/innodb/srv_purge_thread |

| 35 | thread/innodb/srv_worker_thread |

| 36 | thread/innodb/srv_worker_thread |

| 37 | thread/innodb/srv_worker_thread |

| 38 | thread/innodb/buf_dump_thread |

| 39 | thread/innodb/dict_stats_thread |

| 40 | thread/sql/slave_io |

| 41 | thread/sql/slave_sql |

| 42 | thread/sql/slave_worker |

| 43 | thread/sql/slave_worker |

| 44 | thread/sql/slave_worker |

| 45 | thread/sql/slave_worker |

| 46 | thread/sql/signal_handler |

| 47 | thread/sql/compress_gtid_table |

| 549 | thread/sql/one_connection |

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

47 rows in set (0.00 sec)

去除重复：

mysql> select name,count(*) as cnt from performance_schema.threads group by name order by cnt ;

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

| name | cnt |

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

| thread/innodb/buf_dump_thread | 1 |

| thread/sql/main | 1 |

| thread/sql/slave_io | 1 |

| thread/innodb/srv_lock_timeout_thread | 1 |

| thread/innodb/io_ibuf_thread | 1 |

| thread/innodb/srv_master_thread | 1 |

| thread/sql/compress_gtid_table | 1 |

| thread/innodb/dict_stats_thread | 1 |

| thread/innodb/srv_monitor_thread | 1 |

| thread/sql/thread_timer_notifier | 1 |

| thread/sql/slave_sql | 1 |

| thread/innodb/srv_error_monitor_thread | 1 |

| thread/innodb/io_log_thread | 1 |

| thread/sql/signal_handler | 1 |

| thread/innodb/srv_purge_thread | 1 |

| thread/sql/one_connection | 1 |

| thread/innodb/srv_worker_thread | 3 |

| thread/innodb/buf_lru_manager_thread | 4 |