今天遇到个问题,刚刚启动的MySQL,运行的第一条语句是create database db;这条语句要运行10S,懵逼了,有趣的是每次都是整10S。
向大牛们请教说让使用show profile来观察开销。看官网介绍:
mysql> create database tpdd;
Query OK, 1 row affected (10.01 sec)
mysql> show profile;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000169 |
| checking permissions | 0.000536 |
| query end | 0.002839 |
| Waiting for semi-sync ACK from | 10.000552 |
| query end | 0.000057 |
| closing tables | 0.000032 |
| freeing items | 0.000069 |
| logging slow query | 0.000138 |
| cleaning up | 0.000046 |
+--------------------------------+-----------+
9 rows in set, 1 warning (0.02 sec)
发现是后台开启了半同步复制,查看设定的超时时间:
mysql> show global variables like 'rpl_semi_sync_%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
6 rows in set (0.00 sec)
果然,之后认为是异步复制不再等待ACK就正常了。
向大牛们请教说让使用show profile来观察开销。看官网介绍:
SHOW PROFILE [type
[,type
] ... ] [FOR QUERYn
] [LIMITrow_count
[OFFSEToffset
]]type
: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
开启:set profiling=1;
mysql> create database tpdd;
Query OK, 1 row affected (10.01 sec)
mysql> show profile;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| starting | 0.000169 |
| checking permissions | 0.000536 |
| query end | 0.002839 |
| Waiting for semi-sync ACK from | 10.000552 |
| query end | 0.000057 |
| closing tables | 0.000032 |
| freeing items | 0.000069 |
| logging slow query | 0.000138 |
| cleaning up | 0.000046 |
+--------------------------------+-----------+
9 rows in set, 1 warning (0.02 sec)
发现是后台开启了半同步复制,查看设定的超时时间:
mysql> show global variables like 'rpl_semi_sync_%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
+------------------------------------+-------+
6 rows in set (0.00 sec)
果然,之后认为是异步复制不再等待ACK就正常了。