I have a server that receives data from thousands of locations all over the world. Periodically, that server connects to my DB server and inserts records, one row at a time in rapid succession. There can be up to 6 of these processes (perl) running simultaneously and each may have over 50,000 insert statements, and subsequently, the tables are getting locked.

What I am trying to figure out is what causes locking? Am I better off creating a multi-insert with, say, 100 rows at a time and doing them end to end? What do I use for guidelines?

The DB server has 100GB RAM and 12 processors. It is very lightly used but when these inserts come in, everyone freezes up for a couple minutes which disrupts peopel running reports, etc.

Thanks for any advice. I know I need to stagger the inserts, I am just asking what is a recommended way to do this.

  • 3
    with InnoDB it should be better because MyISAM is table level locks while InnoDB is row level. InnoDB still has to grab AUTO_INC lock if you have an auto-incremented field, but it's more lightweight than a table lock. So try InnoDB first and then you might consider how to optimize it further
    – akuzminsky
    Oct 7 '15 at 22:52
  • I cannot simply convert to innodb. There are tables with nearly a billion records. Oct 7 '15 at 22:56
  • 1

You cannot get rid of all locking. However, there are ways to speed up what you are doing:

  • Use multi-row INSERTs. 100 rows at a time will run 10 times as fast.
  • As mentioned, switch to InnoDB using pt-online-schema-change. Then put multiple INSERTs into a transaction (BEGIN ... COMMIT).
  • The 6 threads could write to 6 different tables (such as CREATE TEMPORARY TABLE), then INSERT INTO real_table SELECT * FROM tmp_table. Even on MyISAM this will run significantly faster because of 6 locks instead of 6*50K. Read about concurrent_insert to see how to avoid 50K table locks into the tmp tables.

Please show us the SHOW CREATE TABLE. There could be significant other issues, especially with indexes.

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .