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
python myanalyzer.py -i xxx -P xx mysql looks good!
显示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):
