INNODB,INSERT进入慢查询的问题
时间:2011-08-11
来源:互联网
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.56-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> select count(*) from smw_ids;
+----------+
| count(*) |
+----------+
| 1494191 |
+----------+
1 row in set (2.05 sec)
engine=innodb
慢查询如下:
# Time: 110811 1:00:24
# User@Host: dev[dev] @ [192.168.1.1]
# Query_time: 12.190885 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 0
SET timestamp=1313042424;
INSERT /* SMW::makeSMWPageID Lucia Galarza */ INTO `smw_ids` (smw_id,smw_title,smw_namespace,smw_iw,smw_sortkey) VALUES (NULL,'global_coordinates','1196','','GLOBAL COORDINATES global coordinates');
表对smw_namespace 做了分区,分区个数有60几个。
mysql> show index from smw_ids;
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids | 0 | PRIMARY | 1 | smw_id | A | 1503932 | NULL | NULL | | BTREE | |
| smw_ids | 0 | PRIMARY | 2 | smw_namespace | A | 1503932 | NULL | NULL | | BTREE | |
| smw_ids | 1 | smw_title | 1 | smw_title | A | 1503932 | 50 | NULL | | BTREE | |
| smw_ids | 1 | idx_smw_sortkey | 1 | smw_sortkey | A | 1503932 | NULL | NULL | | BTREE | |
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.06 sec)
索引也很少,都是小索引。
INNODB下的插入是不会阻塞的,不锁表的。为什么写入花了13秒这么长啊?
不是第一次出现了,经常有,有的花2S,有的3,4S
各位帮我看看啥原因
+----------------------+
| version() |
+----------------------+
| 5.1.56-community-log |
+----------------------+
1 row in set (0.00 sec)
mysql> select count(*) from smw_ids;
+----------+
| count(*) |
+----------+
| 1494191 |
+----------+
1 row in set (2.05 sec)
engine=innodb
慢查询如下:
# Time: 110811 1:00:24
# User@Host: dev[dev] @ [192.168.1.1]
# Query_time: 12.190885 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 0
SET timestamp=1313042424;
INSERT /* SMW::makeSMWPageID Lucia Galarza */ INTO `smw_ids` (smw_id,smw_title,smw_namespace,smw_iw,smw_sortkey) VALUES (NULL,'global_coordinates','1196','','GLOBAL COORDINATES global coordinates');
表对smw_namespace 做了分区,分区个数有60几个。
mysql> show index from smw_ids;
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
| smw_ids | 0 | PRIMARY | 1 | smw_id | A | 1503932 | NULL | NULL | | BTREE | |
| smw_ids | 0 | PRIMARY | 2 | smw_namespace | A | 1503932 | NULL | NULL | | BTREE | |
| smw_ids | 1 | smw_title | 1 | smw_title | A | 1503932 | 50 | NULL | | BTREE | |
| smw_ids | 1 | idx_smw_sortkey | 1 | smw_sortkey | A | 1503932 | NULL | NULL | | BTREE | |
+---------+------------+-----------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
4 rows in set (0.06 sec)
索引也很少,都是小索引。
INNODB下的插入是不会阻塞的,不锁表的。为什么写入花了13秒这么长啊?
不是第一次出现了,经常有,有的花2S,有的3,4S
各位帮我看看啥原因
作者: zuoxingyu 发布时间: 2011-08-11
检查了BINLOG日志,在这个插入的前后,都没有对SMW_IDS表的UPDATE,DELETE操作。
作者: zuoxingyu 发布时间: 2011-08-11
正常插入在0.05S以内。
贴出BINLOG日志和SLOWLOG
SQL code
慢查询:
SQL code
插入写BINLOG时间是1:00:11,写入到SLOWLOG时间是1:00:24,Query_time: 12.190885
贴出BINLOG日志和SLOWLOG
SQL code
# at 1372838 #110811 1:00:11 server id 21528 end_log_pos 1372866 Intvar SET INSERT_ID=1547478/*!*/; # at 1372866 #110811 1:00:11 server id 21528 end_log_pos 1373128 Query thread_id=5181829 exec_time=13 error_code=0 SET TIMESTAMP=1313042411/*!*/; INSERT /* SMW::makeSMWPageID Lucia Galarza */ INTO `smw_ids` (smw_id,smw_title,smw_namespace,smw_iw,smw_sortkey) VALUES (NULL,'global_coordinates','1196','','GLOBAL COORDINATES global coordinates') /*!*/;
慢查询:
SQL code
# Time: 110811 1:00:24 # User@Host: dev[dev] @ [192.168.1.1] # Query_time: 12.190885 Lock_time: 0.000089 Rows_sent: 0 Rows_examined: 0 SET timestamp=1313042424; INSERT /* SMW::makeSMWPageID Lucia Galarza */ INTO `smw_ids` (smw_id,smw_title,smw_namespace,smw_iw,smw_sortkey) VALUES (NULL,'global_coordinates','1196','','GLOBAL COORDINATES global coordinates');
插入写BINLOG时间是1:00:11,写入到SLOWLOG时间是1:00:24,Query_time: 12.190885
作者: zuoxingyu 发布时间: 2011-08-11
是否一点钟的时候在做数据库备份?
作者: nicenight 发布时间: 2011-08-11
没有做备份。
作者: zuoxingyu 发布时间: 2011-08-11
相关阅读 更多
热门阅读
-
office 2019专业增强版最新2021版激活秘钥/序列号/激活码推荐 附激活工具
阅读:74
-
如何安装mysql8.0
阅读:31
-
Word快速设置标题样式步骤详解
阅读:28
-
20+道必知必会的Vue面试题(附答案解析)
阅读:37
-
HTML如何制作表单
阅读:22
-
百词斩可以改天数吗?当然可以,4个步骤轻松修改天数!
阅读:31
-
ET文件格式和XLS格式文件之间如何转化?
阅读:24
-
react和vue的区别及优缺点是什么
阅读:121
-
支付宝人脸识别如何关闭?
阅读:21
-
腾讯微云怎么修改照片或视频备份路径?
阅读:28