Mysql Slow Insert

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 |                |
+-----------+-----------+------+-----+-------------------+----------------+

With these keys:

+--------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| 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      |         |
+--开发者_运维知识库------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

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?

BTW: Each day there're ~80 slow INSERTS and 40 slow UPDATES like this.

---

There are 277259 rows and only some inserts are slow (rare)

Whenever a B-Tree page is full, it needs to be split which takes some time. Insert performance is also slower the more indexes you have, since each insert updates all indexes. 9000 has already stated correctly that your (timestamp,staff) index covers the (timestamp) index in 95% of cases, there are very rare cases when a single-column (timestamp) index will be required for better performance.

There are also some periodic background tasks that can occasionally slow down an insert or two over the course of a day.

Additionally, another reason for delays is simply database activity. If you have transactions that are locking pages that the insert needs to update (or page-split), the insert has to wait until the write locks are acquiesced. These other activity do not even need to actually start a transaction, and they don't even have to be read-read contention; you can also have write-write contention or a queue built up from heavy activity.

And the last possible reason - your database server is out of resources, be it memory or CPU or network i/o. There is only so much a server can do, so it will have to wait until it has enough resources.

---

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.

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.

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.

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).

Further reading:

• 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

---

If you're inserting into a table in large dense bursts, it may need to take some time for housekeeping, e.g. to allocate more space for the table and indexes.

If don't want your app to wait, try using INSERT DELAYED though it does have its downsides.

---

If you are running in a cluster enviroment, auto-increment columns may slow inserts. Try tweaking ndb_autoincrement_prefetch_sz (see http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-system-variables.html#sysvar_ndb_autoincrement_prefetch_sz)

---

If you happen to be back-level on your MySQL installation, we noticed a lot of that sort of slowness when using version 4.1.

---

As my experience InnoDB performance is lower than MyISAM.
Have you try using MyISAM instead?
Or maybe you need to tweak your InnoDB configuration: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html
Hope that help.