ITPub博客

首页 > 数据库 > MySQL > Windows下关闭MySQL的自动提交(autocommit)功能

Windows下关闭MySQL的自动提交(autocommit)功能

原创 MySQL 作者:Hoegh 时间:2016-11-01 10:42:35 0 删除 编辑
随着MySQL的应用日益广泛,支持事务特性的InnoDB已经成为MySQL的默认存储引擎。和很多关系数据库不同的是,在InnoDB存储引擎中,事务默认是自动提交的,也就是说每条DML语句都会触发commit操作。这一自动提交(autocommit)特性在很多场景下对于性能还是有一定影响的。

最近我们尝试将一个Oracle数据库的数据迁移到MySQL数据库,发现导入时间很长。举个例子来说,其中有一个表包含四千多条数据,插入时间竟然超过了100秒。每插入一条数据,数据库就会自动提交一次,也就是说单这一个表MySQL会commit超过4000次,如果我们关闭自动提交功能,通过程序来控制,只要一次commit就可以了。

那么,如何关闭MySQL的autocommit特性呢?
通常有两种方法:
  • 一种是通过set命令修改会话级别或者数据库级别的参数,但是数据库重启后参数会恢复默认值;
  • 第二种方法是修改mysql的配置文件my.ini,一劳永逸。

1.测试环境 Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16 

我是在Windows Server 2008 r2环境下进行测试。

点击(此处)折叠或打开

  1. mysql> status
  2. --------------
  3. mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86_64)

  4. Connection id: 2
  5. Current database:
  6. Current user: root@localhost
  7. SSL: Not in use
  8. Using delimiter: ;
  9. Server version: 5.7.16 MySQL Community Server (GPL)
  10. Protocol version: 10
  11. Connection: localhost via TCP/IP
  12. Server characterset: latin1
  13. Db characterset: latin1
  14. Client characterset: utf8
  15. Conn. characterset: utf8
  16. TCP port: 3306
  17. Uptime: 9 min 5 sec

  18. Threads: 1 Questions: 7 Slow queries: 0 Opens: 106 Flush tables: 1 Open tables: 99 Queries per second avg: 0.012
  19. --------------

  20. mysql>

2.通过set来关闭autocommit,重启后恢复默认值

首先,我们通过set命令来修改autocommit参数。

点击(此处)折叠或打开

  1. mysql>
  2. mysql> show global variables like '%commit%';
  3. +-----------------------------------------+-------+
  4. | Variable_name | Value |
  5. +-----------------------------------------+-------+
  6. | autocommit | ON |
  7. | binlog_group_commit_sync_delay | 0 |
  8. | binlog_group_commit_sync_no_delay_count | 0 |
  9. | binlog_order_commits | ON |
  10. | innodb_api_bk_commit_interval | 5 |
  11. | innodb_commit_concurrency | 0 |
  12. | innodb_flush_log_at_trx_commit | 1 |
  13. | slave_preserve_commit_order | OFF |
  14. +-----------------------------------------+-------+
  15. 8 rows in set, 1 warning (0.00 sec)

  16. mysql> set autocommit=0;
  17. Query OK, 0 rows affected (0.00 sec)
  18. mysql>
  19. mysql> set global autocommit=0;
  20. Query OK, 0 rows affected (0.00 sec)

  21. mysql> show global variables like '%commit%';
  22. +-----------------------------------------+-------+
  23. | Variable_name | Value |
  24. +-----------------------------------------+-------+
  25. | autocommit | OFF |
  26. | binlog_group_commit_sync_delay | 0 |
  27. | binlog_group_commit_sync_no_delay_count | 0 |
  28. | binlog_order_commits | ON |
  29. | innodb_api_bk_commit_interval | 5 |
  30. | innodb_commit_concurrency | 0 |
  31. | innodb_flush_log_at_trx_commit | 1 |
  32. | slave_preserve_commit_order | OFF |
  33. +-----------------------------------------+-------+
  34. 8 rows in set, 1 warning (0.02 sec)

  35. mysql>


接下来,我们重启数据库,发现autocommit参数已经恢复默认值。

点击(此处)折叠或打开

  1. E:\mysql-5.7.16-winx64\bin>net stop mysql
  2. MySQL 服务正在停止.
  3. MySQL 服务已成功停止。


  4. E:\mysql-5.7.16-winx64\bin>net start mysql
  5. MySQL 服务正在启动 .
  6. MySQL 服务已经启动成功。


  7. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. Welcome to the MySQL monitor. Commands end with ; or \g.
  10. Your MySQL connection id is 2
  11. Server version: 5.7.16 MySQL Community Server (GPL)

  12. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  13. Oracle is a registered trademark of Oracle Corporation and/or its
  14. affiliates. Other names may be trademarks of their respective
  15. owners.

  16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  17. mysql> 
  18. mysql> show global variables like '%commit%';
  19. +-----------------------------------------+-------+
  20. | Variable_name | Value |
  21. +-----------------------------------------+-------+
  22. | autocommit | ON |
  23. | binlog_group_commit_sync_delay | 0 |
  24. | binlog_group_commit_sync_no_delay_count | 0 |
  25. | binlog_order_commits | ON |
  26. | innodb_api_bk_commit_interval | 5 |
  27. | innodb_commit_concurrency | 0 |
  28. | innodb_flush_log_at_trx_commit | 1 |
  29. | slave_preserve_commit_order | OFF |
  30. +-----------------------------------------+-------+
  31. 8 rows in set, 1 warning (0.00 sec)

  32. mysql>

3.修改mysql的配置文件my.ini

我们找到mysql的配置文件my.ini,在里面添加一行记录“autocommit=0”。

点击(此处)折叠或打开

  1. [mysql]
  2. default-character-set=utf8
  3. [mysqld]
  4. max_connections=200
  5. default-storage-engine=INNODB
  6. basedir =E:\mysql-5.7.16-winx64\bin
  7. datadir =E:\mysql-5.7.16-winx64\data
  8. port = 3306
  9. autocommit=0

然后重新启动数据库,确认autocommit参数是否为OFF。

点击(此处)折叠或打开

  1. E:\mysql-5.7.16-winx64\bin>net stop mysql
  2. MySQL 服务正在停止.
  3. MySQL 服务已成功停止。


  4. E:\mysql-5.7.16-winx64\bin>net start mysql
  5. MySQL 服务正在启动 .
  6. MySQL 服务已经启动成功。


  7. E:\mysql-5.7.16-winx64\bin>mysql -u root -proot
  8. mysql: [Warning] Using a password on the command line interface can be insecure.
  9. Welcome to the MySQL monitor. Commands end with ; or \g.
  10. Your MySQL connection id is 2
  11. Server version: 5.7.16 MySQL Community Server (GPL)

  12. Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

  13. Oracle is a registered trademark of Oracle Corporation and/or its
  14. affiliates. Other names may be trademarks of their respective
  15. owners.

  16. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  17. mysql> show global variables like '%commit%';
  18. +-----------------------------------------+-------+
  19. | Variable_name | Value |
  20. +-----------------------------------------+-------+
  21. | autocommit | OFF |
  22. | binlog_group_commit_sync_delay | 0 |
  23. | binlog_group_commit_sync_no_delay_count | 0 |
  24. | binlog_order_commits | ON |
  25. | innodb_api_bk_commit_interval | 5 |
  26. | innodb_commit_concurrency | 0 |
  27. | innodb_flush_log_at_trx_commit | 1 |
  28. | slave_preserve_commit_order | OFF |
  29. +-----------------------------------------+-------+
  30. 8 rows in set, 1 warning (0.01 sec)

  31. mysql>
  32. mysql>
我们看到autocommit参数为OFF,目标达成。

4.数据插入速度提升十倍

以开头提到的那张表为例,4000多条数据,在关闭autocommit参数之前插入时间为101505ms;关闭autocommit参数之后插入时间为8869ms,插入速度提升了十倍还多。

但是这个时间其实还是蛮长的,继续提升!





~~~~~~~ the end~~~~~~~~~

hoegh
2016.11.01


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30162081/viewspace-2127395/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-03-13

  • 博文量
    132
  • 访问量
    2058304