Shulou(Shulou.com)06/01 Report--

With the increasing application of MySQL, InnoDB, which supports transaction, has become the default storage engine of MySQL. Unlike many relational databases, in the InnoDB storage engine, transactions commit automatically by default, which means that each DML statement triggers a commit operation. This autocommit feature still has an impact on performance in many scenarios.

Recently we tried to migrate data from an Oracle database to a MySQL database and found that it took a long time to import. For example, one of the tables contains more than 4,000 pieces of data, and the insertion time is more than 100 seconds. Every time a piece of data is inserted, the database will automatically submit it, that is to say, the MySQL of this table alone will commit more than 4000 times. If we turn off the auto-commit function and control it through the program, we only need to commit once.

So, how do you turn off the autocommit feature of MySQL?

There are usually two ways:

One is to modify the session-level or database-level parameters through the set command, but the default values will be restored after the database restart; the second method is to modify the mysql configuration file my.ini once and for all.

1. Test environment Windows Server 2008 r2+MySQL Community Server (GPL) 5.7.16 I tested in the Windows Server 2008 R2 environment.

Click (here) to collapse or open

Mysql > status

-

Mysql Ver 14.14 Distrib 5.7.16, for Win64 (x86 / 64)

Connection id: 2

Current database:

Current user: root@localhost

SSL: Not in use

Using delimiter:

Server version: 5.7.16 MySQL Community Server (GPL)

Protocol version: 10

Connection: localhost via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. Characterset: utf8

TCP port: 3306

Uptime: 9 min 5 sec

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

-

Mysql >

two。 Turn off autocommit through set and restore the default values after rebooting. First of all, we modify the autocommit parameter through the set command.

Click (here) to collapse or open

Mysql >

Mysql > show global variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

| | binlog_group_commit_sync_delay | 0 | |

| | binlog_group_commit_sync_no_delay_count | 0 | |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

| | slave_preserve_commit_order | OFF |

+-+ +

8 rows in set, 1 warning (0.00 sec)

Mysql > set autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql >

Mysql > set global autocommit=0

Query OK, 0 rows affected (0.00 sec)

Mysql > show global variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

| | binlog_group_commit_sync_delay | 0 | |

| | binlog_group_commit_sync_no_delay_count | 0 | |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

| | slave_preserve_commit_order | OFF |

+-+ +

8 rows in set, 1 warning (0.02 sec)

Mysql >

Next, we restart the database and find that the autocommit parameter has been restored to its default value.

Click (here) to collapse or open

E:\ mysql-5.7.16-winx64\ bin > net stop mysql

The MySQL service is stopping.

The MySQL service stopped successfully.

E:\ mysql-5.7.16-winx64\ bin > net start mysql

The MySQL service is starting.

The MySQL service has started successfully.

E:\ mysql-5.7.16-winx64\ bin > mysql- u root-proot

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.7.16 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > mysql > show global variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | ON |

| | binlog_group_commit_sync_delay | 0 | |

| | binlog_group_commit_sync_no_delay_count | 0 | |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

| | slave_preserve_commit_order | OFF |

+-+ +

8 rows in set, 1 warning (0.00 sec)

Mysql > 3. Modify mysql's configuration file my.ini We find mysql's configuration file my.ini, and add a line to it to record "autocommit=0".

Click (here) to collapse or open

[mysql]

Default-character-set=utf8

[mysqld]

Max_connections=200

Default-storage-engine=INNODB

Basedir = E:\ mysql-5.7.16-winx64\ bin

Datadir = E:\ mysql-5.7.16-winx64\ data

Port = 3306

Autocommit=0

Then restart the database and verify that the autocommit parameter is OFF.

Click (here) to collapse or open

E:\ mysql-5.7.16-winx64\ bin > net stop mysql

The MySQL service is stopping.

The MySQL service stopped successfully.

E:\ mysql-5.7.16-winx64\ bin > net start mysql

The MySQL service is starting.

The MySQL service has started successfully.

E:\ mysql-5.7.16-winx64\ bin > mysql- u root-proot

Mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 2

Server version: 5.7.16 MySQL Community Server (GPL)

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

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

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

Mysql > show global variables like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | autocommit | OFF |

| | binlog_group_commit_sync_delay | 0 | |

| | binlog_group_commit_sync_no_delay_count | 0 | |

| | binlog_order_commits | ON |

| | innodb_api_bk_commit_interval | 5 | |

| | innodb_commit_concurrency | 0 | |

| | innodb_flush_log_at_trx_commit | 1 | |

| | slave_preserve_commit_order | OFF |

+-+ +

8 rows in set, 1 warning (0.01sec)

Mysql >

Mysql > We see that the autocommit parameter is OFF, and the goal is achieved.

4. The data insertion speed is ten times faster. Take the table mentioned at the beginning as an example. For more than 4000 pieces of data, the insertion time is 101505ms before turning off the autocommit parameter; after turning off the autocommit parameter, the insertion time is 8869ms, which increases the insertion speed by more than ten times.

But this time is actually quite a long time, continue to improve!

~ the end~

Hoegh

2016.11.01

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.