Mysql慢速插入 [英] Mysql Slow Insert
问题描述
我有以下InnoDB表:
I have the following InnoDB table:
+-----------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| doc_id | char(32) | NO | | NULL | |
| staff | char(18) | NO | | NULL | |
| timestamp | timestamp | NO | MUL | CURRENT_TIMESTAMP | |
+-----------+-----------+------+-----+-------------------+----------------+
使用这些键:
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| staff_online | 0 | PRIMARY | 1 | id | A | 277350 | NULL | NULL | | BTREE | |
| staff_online | 1 | timestamp | 1 | timestamp | A | 277350 | NULL | NULL | | BTREE | |
| staff_online | 1 | staff_timestamp | 1 | timestamp | A | 277350 | NULL | NULL | | BTREE | |
| staff_online | 1 | staff_timestamp | 2 | staff | A | 277350 | NULL | NULL | | BTREE | |
+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
我只是注意到in mysql-slow.log有时我在此表上执行INSERT查询，耗时超过1秒
I just noticed that in mysql-slow.log I sometimes have an INSERT query on this table which takes more than 1 second
INSERT INTO `staff_online` (`doc_id`, `staff`, `timestamp`) VALUES ('150b60a0ab8c5888bdbbb80bd8b7f8a2', 'asia', '2011-01-29 16:52:54')
我真的很困惑为什么要花这么长时间.我如何加快速度?
I'm really puzzled why it takes so long. How can I speed it up?
顺便说一句:这样每天大约有80个缓慢的插入和40个缓慢的更新.
BTW: Each day there're ~80 slow INSERTS and 40 slow UPDATES like this.
推荐答案
有时候，查询本身并不是导致速度下降的原因-由于事务隔离和锁定，对表进行操作的另一个查询很容易导致插入速度下降.您的慢查询可能只是在等待其他事务完成.在繁忙的表上，或者服务器正在执行长/复杂事务时，这是很常见的.
Sometimes it is not the query itself which causes a slowdown - another query operating on the table can easily cause inserts to slow down due to transactional isolation and locking. Your slow queries might simply have been waiting for another transaction(s) to complete. This is fairly common on a busy table, or if your server is executing long/complex transactions.
另一个重要因素将是数据库的整体性能:my.cnf文件的调优方式，服务器本身的调优方式，服务器上还运行着什么以及服务器上正在运行的硬件.
Another significant factor will be the overall performance of your database: how your my.cnf file is tuned, how the server itself is tuned, what else the server has running on it, and of course, what hardware the server is running.
Linux工具mytop和查询SHOW ENGINE INNODB STATUS\G有助于查看可能的故障点.通用的linux性能工具还可以显示磁盘的繁忙程度，等等.
The linux tool mytop and the query SHOW ENGINE INNODB STATUS\G can be helpful to see possible trouble spots. General linux performance tools can also show how busy your disks are, etc.
鉴于此表的性质，您是否考虑过另一种跟踪谁在线的方法?在MySQL中，过去我曾将MEMORY表用于此类目的. NoSQL数据存储区也可能适合此类信息. Redis可以将其存储为排序成功的集(得分==时间戳).
Given the nature of this table, have you considered an alternative way to keep track of who is online? In MySQL, I have used a MEMORY table for such purposes in the past. A NoSQL data store might also be good for this type of information. Redis could store this as a sorted set with much success (score == timestamp).
进一步阅读:
- http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
- http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
- http://redis.io/commands#sorted_set
- http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html
- http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html
- http://redis.io/commands#sorted_set
这篇关于Mysql慢速插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
