MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验

2022-10-08 12:32:04

1：创建表和存储过程

mysql> create database trx;

Query OK, 1 row affected (0.02 sec)

mysql> USE trx

Database changed

mysql> CREATE TABLE test_trx(

    -> id int(5),

    -> uname char(30))

    -> 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> CREATE PROCEDURE pro_trx(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <= count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> COMMIT;

    -> SET fir = fir+1;

    -> END WHILE;

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

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)

设置为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)

设置为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)

设置为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)

重新设置为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.25 sec)

innodb_flush_log_at_trx_commit设置为不同的值，性能差别很明显。

可以通过设置为0或者2来提高事物提高性能。但是这种设置丧失了ACID特性。而针对上述存储过程，为了提高事物的提交性能，应该在将10万行记录插入表后进行一次的COMMIT操作。而不是每插入一条就提交一次。这样做的优点是事物可以回滚到最初的状态。

2：改写一下存储过程

mysql> DELIMITER $$

mysql> DROP PROCEDURE IF EXISTS pro_trx_one_commit$$

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE pro_trx_one_commit(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <=count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> SET fir = fir+1;

    -> END WHILE;

    -> COMMIT;

    -> END;

    -> $$

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

innodb_flush_log_at_trx_commit 设置为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 设置为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 设置为2：

mysql> CALL pro_trx_one_commit(100000);

Query OK, 0 rows affected (13.50 sec)

3:再次改写存储过程

mysql> DELIMITER $$

mysql> CREATE PROCEDURE pro_trx_no_commit(count int UNSIGNED)

    -> BEGIN

    -> DECLARE fir INT UNSIGNED DEFAULT 1;

    -> DECLARE sec CHAR(50) DEFAULT REPEAT('id',50);

    -> WHILE fir <=count

    -> DO

    -> INSERT INTO test_trx SELECT null,fir;

    -> SET fir = fir+1;

    -> 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)

另外开一个连接

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)

把自动提交关掉之后重新调用存储过程查看性能：

mysql> CALL pro_trx_no_commit(100000);

Query OK, 1 row affected (2.66 sec)

mysql> USE trx

Database changed

mysql> 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)

码农公寓

相关文章