inserting into a table with 3 million rows is slow

Question

I have the following MySQL query:

INSERT INTO shopious_instagram_item_viewer (created, viewer_id, item_id) VALUES ('2014-11-11 21:26:53', 210054, 714755)\G

Here's how the table is structured:

I am wondering why a simple insert like this would take me on average 37 seconds to complete based on the analysis of percona toolkit.

# Query 2: 0.00 QPS, 0.09x concurrency, ID 0x32289FAFEDA31EE5 at byte 271077009
# This item is included in the report because it matches --limit.
# Scores: V/M = 4.60
# Time range: 2014-11-07 07:12:26 to 2014-11-13 06:18:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    2459
# Exec time     16  48495s      1s     52s     20s     37s     10s     19s
# Lock time      0   256ms    21us   122ms   104us    54us     3ms    38us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size     0 287.03k     115     120  119.53  118.34    0.72  118.34
# String:
# Databases    ShopiousDirectory
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  #############
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `ShopiousDirectory` LIKE 'shopious_instagram_item_viewer'\G
#    SHOW CREATE TABLE `ShopiousDirectory`.`shopious_instagram_item_viewer`\G
INSERT INTO shopious_instagram_item_viewer (created, viewer_id, item_id) VALUES ('2014-11-11 21:26:53', 210054, 714755)\G

I am planning to add indexes, but don't think that helps a lot on insertion.

Answer 1

You could disable FK checks before the insert and re-enable them after:

SET foreign_key_checks=0;
...SQL QUERY...
SET foreign_key_checks=1;

Answer 2

There's nothing strange about this table you're INSERTing to. It shouldn't take as long as it is to perform your inserts.

There's a lot of information missing from your question, though.

1. What access method are you using?
2. Are you inserting lots of rows and getting a 20sec average insert time, or or are you just inserting a row now and again? You did about 400 of these inserts a day. Were they done all at once each day, in big bunches, or one or two at a time?
3. What sort of read traffic are other parts of your application performing on this table? Are you running lots of queries that have to read tons of rows (aggregates, etc) or are you occasionally reading a row or two here and there?

The stats from Percona -- with such a large difference between the 95th percentile time and the worst-case time -- make it look to me like you do have contention between your inserts and other traffic on the table, and that your other traffic on the table is constant and heavy.

Try doing your insert operations when your application is quiet. If that improves your INSERT performance, you know the bad performance was contention between reads and writes.

Are you using InnoDB? If so, try wrapping bunches of INSERT statements into a single transaction. That is, do ...

BEGIN;
INSERT INTO table (col, col, col) VALUES (val, val, val);
INSERT INTO table (col, col, col) VALUES (val, val, val);
... between ten and 100 of these ....
INSERT INTO table (col, col, col) VALUES (val, val, val);
INSERT INTO table (col, col, col) VALUES (val, val, val);
COMMIT;

That batches up the table changes and makes things faster. It won't help if you only have occasional isolated INSERT operations though.

Are you using MyISAM? If so, forget about the transactions. But, try

INSERT DELAYED INTO table (col, col, col) VALUES (val, val, val);

This will queue up the insert inside the data base server; it's a send-and-forget scheme for doing inserts. The insert operation gets queued up until competing SELECT and other operations are done.

And, in the final analysis, a 20-sec average insert time for 400 rows a day may not be disastrous for your application, even though it is definitely slow.