Cause of a sudden high transaction volume in one diagnosis

I received a monitoring text message saying that the threshold value was updated and inserted, So I logged on to mysql-monitor to view the alarm,

650) this. width = 650; "border =" 0 "src =" http://www.bkjia.com/uploads/allimg/131229/19412211N-0.jpg "alt =" "/>

Log on to the mysql server and analyze the binlog before and after to check which table is updated greatly.

[Root @ XXX-02 logs] # mysqlbinlog -- no-defaults -- base64-output = decode-rows-v mysql-bin.053373 | more
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#120802 16:36:47 server id 4713306 end_log_pos 106 Start: binlog v 4, server v 5.1.43sp1-br38368-enterprise-gpl-pro-log c
Reated 120802 16:36:47

Use this command to analyze the tables with frequent write operations.

[Root @ XXX-02 logs] # mysqlbinlog -- no-defaults -- base64-output = decode-rows-v mysql-bin.053373 | awk '/###/ {if ($0 ~ /UPDATE | INSERT | DELETE/) count [$2 "" $ NF] ++} END {for (I in count) print I, "\ t ", count [I]} '| column-t | sort-k3nr | more
Update db. Dynamic 133971
Update db. User 54834
Update db. Quota 24938
Update db. OrderHistory 24482
Update db. BOSSOperation 19767
Update db. SmsCount 18235
Update db. Buddy 10919
Insert db. Buddy_Log 10024

========================================================== ==================================

View the binlog logs after.

[Root @ XXX-02 logs] # mysqlbinlog -- no-defaults -- base64-output = decode-rows-v mysql-bin.053375 | more
/*! 40019 SET @ session. max_insert_delayed_threads = 0 */;
/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */;
DELIMITER /*! */;
# At 4
#120802 17:10:54 server id 4713306 end_log_pos 106 Start: binlog v 4, server v 5.1.43sp1-br38368-enterprise-gpl-pro-log c
Reated 120802 17:10:54
# At 106

Use this command to analyze the tables with frequent write operations.

[Root @ XXX-02 logs] # mysqlbinlog -- no-defaults -- base64-output = decode-rows-v mysql-bin.053375 | awk '/###/ {if ($0 ~ /UPDATE | INSERT | DELETE/) count [$2 "" $ NF] ++} END {for (I in count) print I, "\ t ", count [I]} '| column-t | sort-k3nr
Insert db. Buddy_Log 194160
Insert db. Buddy 192587
Update db. Dynamic 62767
Update db. User 30103
Update db. OrderHistory 12507
Update db. Quota 12318
Update db. BOSSOperation 9892

 

This intuitively shows which tables are updated more often, and then finds development to confirm whether the problem is caused by business growth.

 

 

 

This article is from the "hechun's technical column" blog, please be sure to keep this source http://hcymysql.blog.51cto.com/5223301/951613