MySQL中遇到问题，我们首先会去看show processlist,如果你的链接数量比较多，阻塞比较多的情况下，一般不太好查源头，我们还要手工执行一些sql ,才能定位问题，针对一些由于配置导致的MYSQL响应慢问题，也是需要进行一些排查。
myanalyzer.py 这个工具能自动分析线程阻塞的原因，并给出建议，能非常方便的快速定位问题，针对对mysql不熟悉的人，也能使用这个工具进行故障处理,排查mysql的当前运行状态。另外我们在使用show processlist的情况下，如果sql过长，看不到完整的sql,使用这个工具能查看完整的sql信息。
python myanalyzer.py --help
usage: myanalyzer.py -t 5 -i 192.168.0.0.1
optional arguments:
-h, --help show this help message and exit
-a ACTION, --action ACTION
show or check.
-n TOPN, --topn TOPN show topn long query or big transaction record.
-t TIME, --time TIME check thread which time greater than t.
-i IP, --ip IP server ip.
-P PORT, --port PORT server port.
-c CONFIG, --config CONFIG
read MySQL configuration from. (default: '~/.my.cnf'
-s SECTION, --section SECTION
read MySQL configuration from this section. (default: '[client]')
示例输出
python myanalyzer.py -i xxx -P xxx
There are no long query,but there are long uncommitted transaction
trx_id trx_state trx_started trx_requested_lock_id trx_mysql_thread_id trx_query trx_tables_in_use trx_tables_locked trx_isolation_level user host db info
--------------- --------- ------------------- --------------------- ------------------- --------- ----------------- ----------------- ------------------- --------------------------- ------------------- ------------------------- ------
421553576313088 RUNNING 2021-12-22 09:58:18 <null> 430824 None 0 0 READ COMMITTED xx xx:55057 xxb <null>
421553576049136 RUNNING 2021-12-23 10:41:25 <null> 449170 None 0 0 READ COMMITTED xxx xx:50408 xxx <null>
377122286 RUNNING 2021-12-23 09:00:32 <null> 448342 None 0 1 READ COMMITTED xxx xx:62272 xxx <null>
You're about to kill long transaction.
Do you want to proceed? (y/n): n
kill nothing
显示show processlist
$ python myanalyzer.py -c /Users/xiaoyu.bai/sandboxes/rsandbox_percona-server-5_7_21/master/my.sandbox.cnf -a show
ID USER HOST DB COMMAND TIME STATE INFO ROWS_SENT ROWS_EXAMINED
-- -------- --------- ------ ------- ---- ---------- ---------------------------------------------- --------- -------------
2 msandbox localhost test Query 6 User sleep 'select sleep(100) from user limit 1' 0 0
3 msandbox localhost <null> Query 0 executing 'select * from information_schema.processlist' 0 0
显示阻塞
$ python myanalyzer.py -c /Users/xiaoyu.bai/sandboxes/rsandbox_percona-server-5_7_21/master/my.sandbox.cnf -t 1
id :4 alter table `user` add column sex9 int is waiting for table metadata lock. waiting 31 seconds
cause by uncommited transaction or long query,kill long transactions or long query
trx_id trx_state trx_started trx_requested_lock_id trx_mysql_thread_id trx_query trx_tables_in_use trx_tables_locked trx_isolation_level user host db info
--------------- --------- ------------------- --------------------- ------------------- ------------------------------------- ----------------- ----------------- ------------------- -------- --------- ---- -----------------------------------
421785610750400 RUNNING 2021-12-23 17:06:56 <null> 59 'select sleep(100) from user limit 1' 1 0 SERIALIZABLE msandbox localhost test select sleep(100) from user limit 1
You're about to kill long transaction.
Do you want to proceed? (y/n): y
done
ID USER HOST DB COMMAND TIME STATE INFO ROWS_SENT ROWS_EXAMINED
-- -------- --------- ---- ------- ---- ---------- ------------------------------------- --------- -------------
59 msandbox localhost test Query 33 User sleep 'select sleep(100) from user limit 1' 0 0
You're about to kill long query select.
Do you want to proceed? (y/n): y
thread already been killed.
done
显示锁等待
$ python myanalyzer.py -c /Users/xiaoyu.bai/sandboxes/rsandbox_percona-server-5_7_21/master/my.sandbox.cnf -t 1
long sql is executing! id: 70 user: msandbox host: localhost sql is: update user set sex9=1
DML is blocked, waiting info is ::
waiting_thread waiting_query waiting_rows_modified waiting_age waiting_wait_secs waiting_user waiting_host waiting_db blocking_thread blocking_query blocking_rows_modified blocking_age blocking_wait_secs blocking_user blocking_host blocking_db
-------------- ------------------------------- --------------------- ----------- ----------------- ------------ ------------ ---------- --------------- ------------------------ ---------------------- ------------ ------------------ ------------- ------------- -----------
4 "update user set username='aa'" 0 4 4 msandbox localhost test 70 'update user set sex9=1' 497191 12 <null> msandbox localhost test
trx_id trx_state trx_started trx_requested_lock_id trx_mysql_thread_id trx_query trx_tables_in_use trx_tables_locked trx_isolation_level user host db info
------- --------- ------------------- --------------------- ------------------- ------------------------ ----------------- ----------------- ------------------- -------- --------- ---- ----------------------
1347463 RUNNING 2021-12-23 17:11:53 <null> 70 'update user set sex9=1' 1 1 SERIALIZABLE msandbox localhost test update user set sex9=1
You're about to kill long transaction.
Do you want to proceed? (y/n):
