Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've come across a symptom in MySQL which got me baffled. First, the time it takes to INSERT a certain amount of records changes non-linearily with the amount of records. Second, the time also depends on the size of table, even when it has no indexes.

First, the tables (both MyISAM)

mysql> desc articles;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| id             | int(11)      | NO   | PRI | NULL    | auto_increment |
| uuid           | varchar(250) | NO   | UNI | NULL    |                |
...snip unrelated columns...
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)

mysql> desc articles_groups;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| article_id   | int(11)      | NO   | MUL | NULL    |                |
| group_id     | int(11)      | NO   | MUL | NULL    |                |
| seq_num      | bigint(20)   | YES  |     | NULL    |                |
| article_uuid | varchar(250) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

Both tables contain about 15 million records with little fragmentation; I need to combine some data from the tables, so I create a temporary table:

mysql> create temporary table aga (aid int, agid int);

Nothing fancy; no indexes or primary keys.

Then I insert data int aga by combining data from the other tables:

mysql> insert into aga (aid,agid) select articles.id,articles_groups.id FROM
  articles,articles_groups where articles.uuid=articles_groups.article_uuid AND 
  articles_groups.id >= 0 AND articles_groups.id < 10000;
Query OK, 9999 rows affected (0.50 sec)

I've limited the number of records because a straight insert takes way too long. Now comes the interesting part. Look at the following table on how long the inserts take, depending on the number of records:

 articles_groups.id        Time (seconds)  
 -------------------------+--------------
 0 - 10K                  | 0.50
 10K - 20K                | 0.47
 20K - 30K                | 0.47
 30K - 50K                | 0.92
 50K - 100K               | 2.28
 100K - 200K              | 35.87 (!)
 200K - 300K              | 33.87
 300K - 500K              | 96.76
 500K - 510K              | 8.81 (eh?)
 510K - 520K              | 5.89 
 1000K - 1010K            | 4.75
 2000K - 2010K            | 10.14
 2010K - 2020K            | 9.02

Obviously it takes longer to insert more records, but there's a big jump when going from 50K inserts to 100K inserts. And 200K inserts take triple the time, not double. I could understand this if the aga table has indexes, but it doesn't.

After deleting the records from aga and performing the last insert again, it just takes 0.68 seconds, so clearly there is a relation to the size of the table and not the articles_groups.id (which is what I thought at first).

This is on a Linux machine with MySQL 5.1.46; quad core, 8 GB RAM; MySQL uses about 1.3 GB RAM.

Now as to my questions:

  • Appearantly there is a 'wall' between 50K and 100K inserts for this particular query. Is there an easy way to find the optimal number of inserts, other than trial and error?

  • If I would split the query into 10K batches as I have done above and run them in sequence, would that be faster than 1 query with no limits?

  • Why does the time depend on the table size, even without indexes?

share|improve this question
    
Your analysis doesn't mean a great deal. Much of the difference will be due to caching "After deleting the records from aga and performing the last insert again, it just takes 0.68 seconds" -- so cached in memory!, plus write bottleneck and/or writeback cache saturation on the disk. Is is a physical machine or a VM? –  Phil Oct 30 '13 at 0:43
    
Physical machine. Yes, of course smaller batches will be cached on disk. But a jump from 2.28 to 35 seconds is not just from writing to disk. For what it's worth, the database file for aga on disk after 800K inserts is 6.2 megabytes. Hardly a harddisk bottleneck.... –  JvO Oct 30 '13 at 0:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.