Mysql – INSERT take too much time

insertmyisamMySQLoptimization

I have in my MySQL DB a few tables
Most of the time, I'm only doing INSERT in my db. I have up to 4 connections to the DB, each one inserting into a different table. There cannot be two connections inserting data in the same table at the same time.

In one of my table, I'm doing requests like : INSERT INTO … VALUES <1000 tuples> ON DUPLICATE KEY UPDATE < do some basic operation on columns which are not in the PK> .
This table does not have any index, it only has on PK on 2 columns. The row format is fixed (only fixed size column). The table is using MyISAM, and has about 3 million rows (The total size of the table is about 400Mb, and the PK takes about 40Mb.)

Most of the time, the UPDATE part of the query is applied, since we may have a lot of lines already existing in the DB in our queries.

My problem is that doing an insert for 10000 rows take about 10s, which is a real problem for my application.

I tried to OPTIMIZE the table but it didn't change anything.

Here are the config parameters I changed:

  • key_buffer_size = 2G

  • max_allowed_packet = 64M

  • table_open_cache = 256

  • Binary logs are disabled (it's not

I put this big key_buffer_size since other tables might be using it too.

So, do you guys have any suggestion about how to fix that problem ?

If you need more infos, plz tell me 🙂

Thanks !

Edit: I'm using JDBC to run my queries, is there something specific to my case to enable in jdbc parameters ?

Best Answer

You should increase your bulk_insert_buffer_size to 512M because it accommodates bulk loading of MyISAM tables. According to the MySQL Documentation on bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

In MySQL 5.6, the max value for bulk_insert_buffer_size is 4G.

If your MyISAM table has TEXT/BLOB data, I would also increase the max_allowed_packet. What is a MySQL Packet used for ? See my SuperUser Post What does the MySQL “max_allowed_packet” setting actually control?

UPDATE 2014-02-15 19:49 EST

Your last comment was

The table has only fixed size data (char[60], int, ...) One line takes 126b. About the bulk_insert_buffer_size, since I'm using INSERT ... ON DUPLICATE ..., would it help ? Also, why did you choose 512M, and not a slower value, similar to the max_allowed_packet ? (or maybe 128M)

My choice of 512M was arbitrary. You can set it to whatever you are comfortable with. Just don't leave it at the default value of 8M.

Since you gave the row size, let's to the math.

10,000 rows X 126 bytes/row = 1,260,000 = 1.2 MB

OK, bulk insert buffer may not be an issue.

OBSERVATION

I don't think MySQL likes INSERT IGNORE combined with ON DUPLICATE KEY update. Why ?

  • INSERT IGNORE says INSERT but reject the incoming row if the PRIMARY KEY already exists.
  • ON DUPLICATE KEY says INSERT but perform some UPDATE on specific columns if the PRIMARY KEY already exists.
  • Logically, this does not make sense to use both of them. Which one do you want?
    • IGNORE duplicates
    • UPDATE columns on duplicates

The INSERT should either be

INSERT IGNORE

or

INSERT ... ON DUPLICATE KEY

Since you have a meaningful ON DUPLICATE KEY, ditch the word IGNORE.

You could also change INSERT ... ON DUPLICATE KEY into REPLACE INTO if you are replacing all non PRIMARY KEY columns.

Related Question