Gradually analyze the reasons why MySQL slave database com_insert does not change

We all know that com_insert and other com_xxx parameters can be used to monitor the access volume of database instances, which is what we often call QPS. Based on the MySQL replication principle, all operations performed by the master database are replayed from the slave database to ensure data consistency. Therefore, the com_insert of the master database and the com_insert of the slave database should be equal in theory.
As shown below, the second column represents the master database, and the third column represents the slave database:

Copy codeThe Code is as follows:
Com_select 22, 1138
Com_update 36 37
Com _ insert 133 135
Com_delete 0 0
Qcache_hits 0 0
Com_replace 0 0
Connections 13 24

But let's look at another business:

Copy codeThe Code is as follows:
Com_select 0 95
Com_update 0 0
Com_insert 92 0
Com_delete 20 0
Qcache_hits 0 6
Com_replace 0 0
Connections 0 6

We can clearly see that the master database has 92 writes but 0 writes from the database. Why?

The only difference between the two businesses is that the binlog_format settings are different.

Copy codeThe Code is as follows:
First Business
Show global variables like '% binlog_format % ';
+ --------------- + ----------- +
| Variable_name | Value |
+ --------------- + ----------- +
| Binlog_format | STATEMENT |
+ --------------- + ----------- +

Second business
Show global variables like '% binlog_format % ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Binlog_format | ROW |
+ --------------- + ------- +

Let's take a look at the definition of the official com_xxx document:

The Com_xxx statement counter variables indicate the number of times each xxx statement has been executed. There is one status variable for each type of statement. For example, Com_delete and Com_update count DELETE and UPDATE statements, respectively. Com_delete_multi and Com_update_multi are similar but apply to DELETE and UPDATE statements that use multiple-table syntax.

From the above documents, we can only see how com_xxx works, but it does not explain why com_insert does not change after RBR is used.

Next, let's take a look at the following documents.

You cannot examine the logs to see what statements were executed, nor can you see on the slave what statements were received from the master and executed.However, you can see what data was changed using mysqlbinlog with the options --base64-output=DECODE-ROWS and --verbose.

The reason for combining the two paragraphs is as follows:

1. The statement is received on the master database. Therefore, com_insert meets the trigger conditions and will increase as the business grows.

2. The slave database obtains the binlog of the master database and replays the updated data. However, the log format of the master database is row format. As a result, the statement is not recorded in the binlog, it is a data change record.

3. Although the slave database can still update records, the data changes cannot be parsed because of one statement or multiple statment statements, therefore, the statment counter of com_insert is not updated.

Basically, inferences are in line with the actual situation, but there is no code to prove, it is a pity.

In addition, if we cannot monitor the write status of the slave database through com_insert, what status should we monitor?

We recommend that you monitor the write status of instances in row format by monitoring innodb_rows_inserted.

Copy codeThe Code is as follows:
Show global status like 'innodb _ rows_inserted ';
+ ---------------------- + ------------ +
| Variable_name | Value |
+ ---------------------- + ------------ +
| Innodb_rows_inserted | 2666049650 |
+ ---------------------- + ------------ +

Appendix: (link to the official documents of the two documents)

Http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Com_xxx

Http://dev.mysql.com/doc/refman/5.5/en/replication-sbr-rbr.html