+ -
当前位置:首页 → 问答吧 → INNODB,INSERT进入慢查询的问题

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

各位帮我看看啥原因

作者: zuoxingyu   发布时间: 2011-08-11

检查了BINLOG日志,在这个插入的前后,都没有对SMW_IDS表的UPDATE,DELETE操作。

作者: zuoxingyu   发布时间: 2011-08-11

正常插入在0.05S以内。
贴出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