MySQL thing series: 3:innodb_flush_log_at_trx_commit Small Experiment

1: Creating Tables and stored procedures

mysql> CREATE DATABASE Trx; Query OK, 1 row affected (0.02 sec) mysql> use trxdatabase changedmysql> CREATE TABLE test_trx (    ID int (5), C1/>-> uname char (()),    engine =innodb; Query OK, 0 rows affected (0.01 sec) mysql> DROP PROCEDURE IF EXISTS Pro_trx $ $Query OK, 0 rows Affected (0.00 sec) MySQL > DELIMITER $ $mysql > DROP PROCEDURE IF EXISTS Pro_trx $ $Query OK, 0 rows affected, 1 Warning (0.00 sec) Mysql> CRE ATE PROCEDURE Pro_trx (count int UNSIGNED),    BEGIN,    DECLARE fir int UNSIGNED DEFAULT 1;    -DECLARE sec CHAR (() DEFAULT REPEAT (' id ');    -While Fir <= count----do-,    INSERT into Test_trx SELECT Null,fir;    COMMIT;    SET fir = fir+1;    and END while;    -END;    -$ $Query OK, 0 rows Affected (0.00 sec) mysql> DELIMITER;

　　

2:

mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +--------------------------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 1     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Set to 0

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 0     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx (100000); Query OK, 0 rows affected (10.20 sec)

　　

Set to 1:

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 1     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx (100000); Query OK, 0 rows affected (4 min 45.12 sec)

Set to 2:

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 2; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 2     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx (100000); Query OK, 0 rows affected (13.80 sec)

　　

Reset to 0: under observation

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 0     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx (100000); Query OK, 0 rows affected (10.25 sec)

　　

Innodb_flush_log_at_trx_commit is set to a different value, the performance difference is obvious.

You can improve performance by setting it to 0 or both. However, this setting loses ACID properties. For the above stored procedures, in order to improve the performance of the submission of things, should be 100,000 rows of records inserted into the table after the commit operation. Instead of every insert, submit one at a time. The advantage of this is that things can be rolled back to their original state.

2: Rewrite the stored procedure

Mysql> DELIMITER $ $mysql > DROP PROCEDURE IF EXISTS pro_trx_one_commit$ $Query OK, 0 rows affected, 1 warning (0.00 SE C

　　

Mysql> CREATE PROCEDURE pro_trx_one_commit (count int UNSIGNED),    BEGIN,    DECLARE fir int UNSIGNED DEFA ULT 1;    -DECLARE sec CHAR (() DEFAULT REPEAT (' id ');    , while Fir <=count    , do,    INSERT into Test_trx SELECT Null,fir;    SET fir = fir+1;    and END while;    COMMIT;    -END;    -$ $Query OK, 0 rows Affected (0.00 sec) mysql> DELIMITER;

　　

Innodb_flush_log_at_trx_commit is set to 0:

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 0     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx_one_commit (100000); Query OK, 0 rows affected (10.19 sec)

　　

Innodb_flush_log_at_trx_commit is set to 1:

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 1     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx_one_commit (100000); Query OK, 0 rows affected (5 min 27.33 sec)

　　

Innodb_flush_log_at_trx_commit is set to 2:

Mysql> call Pro_trx_one_commit (100000); Query OK, 0 rows affected (13.50 sec)

　　

3: Rewrite the stored procedure again

Mysql> DELIMITER $ $mysql > CREATE PROCEDURE pro_trx_no_commit (count int UNSIGNED),    BEGIN    DECLARE fir INT UNSIGNED DEFAULT 1;    -DECLARE sec CHAR (() DEFAULT REPEAT (' id ');    , while Fir <=count    , do,    INSERT into Test_trx SELECT Null,fir;    SET fir = fir+1;    and END while;    -END;    -$ $Query OK, 0 rows Affected (0.00 sec) mysql> DELIMITER;

　　

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 1; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%innodb_flush_log_at_trx_commit% '; +------------------ --------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 1     |+--------------------------------+-------+1 row in Set (0.00 sec)

　　

Mysql> call Pro_trx_no_commit (100000)  ; Query OK, 1 row affected (4 min 55.05 sec)

　　

mysql> SHOW VARIABLES like '%commit% '; +--------------------------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Autocommit                     | On    | | innodb_commit_concurrency      | 0     | | innodb_flush_log_at_trx_commit | 1     |+----------------------- ---------+-------+3 rows in Set (0.00 sec)

　　

mysql> SET GLOBAL autocommit = OFF; Query OK, 0 rows Affected (0.00 sec) mysql> SHOW VARIABLES like '%commit% '; +--------------------------------+-------+| Variable_name                  | Value |+--------------------------------+-------+| Autocommit                     | On    | | innodb_commit_concurrency      | 0     | | innodb_flush_log_at_trx_commit | 1     |+----------------------- ---------+-------+3 rows in Set (0.00 sec) Open another connection mysql> SHOW VARIABLES like '%commit% '; +---------------------------- ----+-------+| Variable_name                  | Value |+--------------------------------+-------+| Autocommit                     | OFF   | | innodb_commit_concurrency      | 0     | | innodb_flush_log_at_trx_commit | 1     |+-------------------- ------------+-------+3 rows in Set (0.00 sec)

　　

Turn off autocommit and recall the stored procedure to see the performance:

Mysql> call Pro_trx_no_commit (100000); Query OK, 1 row affected (2.66 sec)

　　

mysql> use trxdatabase changedmysql> call Pro_trx_no_commit (100000); Query OK, 1 row affected (2.66 sec) mysql> call Pro_trx_one_commit (100000); Query OK, 0 rows affected (2.48 sec) mysql> call Pro_trx (100000); Query OK, 0 rows affected (4 min 47.90 sec)

　　

MySQL thing series: 3:innodb_flush_log_at_trx_commit Small Experiment