create table t1(id int);
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| autocommit | ON |
| innodb_commit_concurrency | 0 |
| innodb_flush_log_at_trx_commit | 0 |
+--------------------------------+-------+
3 rows in set (0.00 sec)
mysql> set profiling =1;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1 values(1011);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values(1011);
^[[A
Query OK, 1 row affected (16.54 sec)
mysql> insert into t1 values(1011);
Query OK, 1 row affected (5.06 sec)
mysql> show profiles;
+----------+-------------+-----------------------------+
| Query_ID | Duration | Query |
+----------+-------------+-----------------------------+
| 22 | 0.00021000 | delete from t1 |
| 23 | 0.00015300 | insert into t1 values(1011) |
| 24 | 0.00020500 | insert into t1 values(1011) |
| 25 | 0.00016400 | insert into t1 values(1011) |
| 26 | 0.00014800 | insert into t1 values(1011) |
| 27 | 0.00013600 | insert into t1 values(1011) |
| 28 | 0.00014200 | insert into t1 values(1011) |
| 29 | 0.00018600 | insert into t1 values(1011) |
| 30 | 0.00010500 | insert into t1 values(1011) |
| 31 | 0.00013800 | insert into t1 values(1011) |
| 32 | 0.00015900 | insert into t1 values(1011) |
| 33 | 0.00021900 | delete from t1 |
| 34 | 0.00015800 | insert into t1 values(1011) |
| 35 | 16.53985000 | insert into t1 values(1011) |
| 36 | 5.05954200 | insert into t1 values(1011) |
+----------+-------------+-----------------------------+
15 rows in set (0.00 sec)
mysql> show profile for query 35;
+------------------------------+-----------+
| Status | Duration |
+------------------------------+-----------+
| starting | 0.000025 |
| checking permissions | 0.000007 |
| Opening tables | 0.000015 |
| System lock | 0.000006 |
| init | 0.000008 |
| update | 16.539692 |
| Waiting for query cache lock | 0.000014 |
| update | 0.000009 |
| end | 0.000005 |
| query end | 0.000023 |
| closing tables | 0.000009 |
| freeing items | 0.000023 |
| logging slow query | 0.000004 |
| logging slow query | 0.000007 |
| cleaning up | 0.000003 |
+------------------------------+-----------+
15 rows in set (0.01 sec)
-----------------------------------
mysql> set global innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)
mysql> show profile for query 41;
+------------------------------+----------+
| Status | Duration |
+------------------------------+----------+
| starting | 0.000043 |
| checking permissions | 0.000009 |
| Opening tables | 0.000025 |
| System lock | 0.000010 |
| init | 0.000015 |
| update | 0.000042 |
| Waiting for query cache lock | 0.000007 |
| update | 0.000013 |
| end | 0.000005 |
| query end | 5.806777 |
| closing tables | 0.000024 |
| freeing items | 0.000027 |
| logging slow query | 0.000006 |
| cleaning up | 0.000006 |
+------------------------------+----------+
14 rows in set (0.00 sec)
要不就是 update、要不就是query end,结果insert效率低。