Shulou(Shulou.com)05/31 Report--

The main content of this article is to explain "what is the reason for Innodb in MySQL about the increase of 2 per DML in Handler_commit". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the reason why Innodb in MySQL increases DML by 2 each time in Handler_commit?"

Let me ask you a question. I insert one statement at a time, query show global status like 'Handler_commit'; and find that each increment is 2. Shouldn't it be 1? The simplest insert into table a values (1); first, problem presentation

The statement is as follows:

Mysql > flush status;Query OK, 0 rows affected (0.10 sec) mysql > set sql_log_bin=1;Query OK, 0 rows affected (0.01 sec) mysql > insert into testm values; Query OK, 1 row affected (0.15 sec) mysql > show status like'% commit%' +-+-+ | Variable_name | Value | +-+-+ | Com_commit | 0 | Com_xa_commit | 0 | Handler_commit | 2 | +-+-+ 3 rows in set (0.01sec)

Ask why Handler_commit is 2 instead of 1.

Second, cause analysis

In fact, the reason for this problem can be seen only by looking at the way the Handler_commit index has increased. In fact, this indicator appears in the ha_commit_low function as follows:

For (; ha_info; ha_info= ha_info_next) {int err; handlerton * ht= ha_info- > ht (); if ((err= ht- > commit (ht, thd, all)) {my_error (ER_ERROR_DURING_COMMIT, MYF (0), err); error=1;} DBUG_ASSERT (! thd- > status_var_aggregated); thd- > status_var.ha_commit_count++ / / add ha_info_next= ha_info- > next (); if (restore_backup_ha_data) reattach_engine_ha_data_to_thd (thd, ht); ha_info- > reset (); / * keep it conveniently zero-filled * /}

You can clearly see that ha_commit_count is actually the number of times ht- > commit is called, and since there are multiple Handler, it needs to be called multiple times. For the structure that turns on binlog+innodb, you need to do the following:

Commit of binlog

Commit of Innodb

As you'll see later, the commit of the actual binlog does nothing, but this is a protocol.

So if we close binlog, we can find that the Handler_commit is 1 as follows:

Mysql > set sql_log_bin=0;Query OK, 0 rows affected (0.00 sec) mysql > insert into testm values; Query OK, 1 row affected (0.10 sec) mysql > show status like'% commit%' +-+-+ | Variable_name | Value | +-+-+ | Com_commit | 0 | Com_xa_commit | 0 | Handler_commit | 1 | +-+-+ 3 rows in set (0.01sec) Binlog commit stack frame # 0 binlog_commit (hton=0x3485e30 Thd=0x7fff2c014430, all=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:1833#1 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c014430, all=false, run_after_commit=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#2 0x000000000185772b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2e01c80, thd=0x7fff2c014430 First=0x7fff2c014430) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#3 0x0000000001858f5d in MYSQL_BIN_LOG::ordered_commit (this=0x2e01c80, thd=0x7fff2c014430, all=false, skip_commit=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#4 0x000000000185700c in MYSQL_BIN_LOG::commit (this=0x2e01c80, thd=0x7fff2c014430 All=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#5 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c014430, all=false, ignore_global_read_lock=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818

But in fact, binlog_commit didn't do anything, because before that, he had already done what he needed to do, such as flush, sync and so on.

Static int binlog_commit (handlerton * hton, THD * thd, bool all) {DBUG_ENTER ("binlog_commit"); / * Nothing to do (any more) on commit. * / DBUG_RETURN (0) Innodb commit interface # 0 innobase_commit (hton=0x2e9edd0, thd=0x7fff2c014430, commit_trx=false) at / root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/handler/ha_innodb.cc:4652#1 0x0000000000f64104 in ha_commit_low (thd=0x7fff2c014430, all=false, run_after_commit=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1923#2 0x000000000185772b in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x2e01c80, thd=0x7fff2c014430) First=0x7fff2c014430) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8647#3 0x0000000001858f5d in MYSQL_BIN_LOG::ordered_commit (this=0x2e01c80, thd=0x7fff2c014430, all=false, skip_commit=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:9318#4 0x000000000185700c in MYSQL_BIN_LOG::commit (this=0x2e01c80, thd=0x7fff2c014430 All=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/binlog.cc:8440#5 0x0000000000f63df8 in ha_commit_trans (thd=0x7fff2c014430, all=false, ignore_global_read_lock=false) at / root/mysql5.7.14/percona-server-5.7.14-7/sql/handler.cc:1818

In fact, innodb comit is what needs to be done, and there are some things to do here, such as the change of the state of things and the release of resources.

Finally, select will also increase Handler_commit to 1.

At this point, I believe you have a deeper understanding of "what is the reason why Innodb in MySQL increases 2 per DML in Handler_commit". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.