Menu
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!
371
To optimize insert speed, combine many small operations into a single large operation. Ideally, you make a single connection, send the data for many new rows at once, and delay all index updates and consistency checking until the very end.
To improve insert performance you should use batch inserts. insert into table my_table(col1, col2) VALUES (val1_1, val2_1), (val1_2, val2_2); Storing records to a file and using load data infile yields even better results (best in my case), but it requires more effort.
You can use the following methods to speed up inserts: If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.
As documented under INSERT DELAYED Syntax:
The
DELAYEDoption for theINSERTstatement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for theINSERTto complete.
[ deletia ]Another major benefit of using
INSERT DELAYEDis that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
