首页 / MYSQL / MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7381字，纯文字阅读大概需要11分钟。

内容图文

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 ;

　　

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)

　　

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

　　

 

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

标签：

本文系统来源：http://www.cnblogs.com/xiaoit/p/4554048.html

内容总结

以上是互联网集市为您收集整理的MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验全部内容，希望文章能够帮你解决MySQL事物系列：3：innodb_flush_log_at_trx_commit小实验所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/448810.html

来源：【匿名】