CODEWITHSUNDEEP
mysqlperformanceinsertparallel-processing
user1459544
user1459544

Reputation: 117

Faster way to concurrently insert data into MySQL

I have a parallel process with about 64 children that each need to insert data into a landing table. I am currently using a MySQL MyISAM engine, and I disable keys before and after inserts.

However, this seems to be a huge bottleneck in my process. I believe MySQL is table locking for each insert and so processes are constantly waiting to write.

The inserts are independent and there is no danger of conflicting inserts. This also does not need transactions or anything of that nature.

Is there a different engine, or ways to improve the insert/write performance of MySQL?

I have thought about instantiating a table for each process, but this would make the code more complex, and that is not really my style....

Any suggestions would be greatly appreciated.

Thanks!

Upvotes: 6

Views: 5669

Answers (2)

snurre
snurre

Reputation: 3105

MyISAM does indeed lock the tables when inserting, updating or deleting. InnoDB allows transaction and row-based locks.

You can also look into LOAD DATA INFILE which is faster for bulk inserts.

Upvotes: 2

eggyal
eggyal

Reputation: 125865

As documented under INSERT DELAYED Syntax:

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete.

[ deletia ]

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

Upvotes: 4

Related Questions