I received a monitoring text message saying that the threshold value was updated and inserted, So I log on to mysql-monitor to view the alarm. In this case, I log on to the mysql server and use the binlog analysis before and after, which table is updated?
I received a monitoring text message saying that the threshold value was updated and inserted, So I log on to mysql-monitor to view the alarm. In this case, I log on to the mysql server and use the binlog analysis before and after, which table is updated?
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. This is true. In US space,
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, website space, please be sure to keep this source
, Hong Kong VM