MySQL – How to Insert Tens of Thousands of Rows Without Locking

lockingmyisamMySQL

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.

Best Answer

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.

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

First, each time you UPDATE the status column, you are having to update the index as well (source). Evaluate your indexing to see if you really need the index on the status column. My guess is no, since it has an extremely low cardinality and MySQL probably won't use it anyway.

If you ignore me and think you do need it, follow the advice in the article to drop the index before your loop and re-add it after you're done.

Here are some other things you might do if that doesn't help:

You are taking all the columns from the data but only using number. Don't do a SELECT *, but instead a SELECT number. That won't help your writes, but it is a good performance practice. Only select the columns you're using.
Your number index isn't getting used at all. This means it is not unique enough to be useful for updating. (Slight tangent: how many rows does a single UPDATE affect?) I would drop it, or at least add it to process index.
It looks like process is unique enough for MySQL to whittle the amount of rows down to 16k, instead of 1 million. In light of this, I would add AND process=x to your update statement (I'm assuming you know process from the original SELECT statement):
```
-- FAILED--
UPDATE data SET status = 2, error='$error' WHERE process=X AND number = $data['number']

-- SUCCESS --
UPDATE data SET status = 1 WHERE process=X AND number = $data['number']
```

A hint about unnecessary indexes in InnoDB. InnoDB is using a hidden 'primary key' (since you don't have one defined) and is using that when it writes the indexes. So for each Index you're using, you add the size of the index + the size of the hidden primary key to the data file. If you're not using the index (or MySQL can't use it), you are wasting space and adding overhead each time you insert a new number (same for status, as discussed earlier)

Sql-server – INSERT statements in a transaction and locking a range of rows

If you want to prevent messing up the sequence, you can lock the tables involved when you are doing the inserts. You will have to assess whether the duration of this locking is acceptable or not.

The command would be... Insert into table WITH (TABLOCK) (values).....

The scope of the transaction described in your question would not prevent external inserts from happening. Hence my suggestion of using table locks.

Best Answer

Related Solutions

Mysql – Improve `Update` performance (rows locking issue)

Sql-server – INSERT statements in a transaction and locking a range of rows

Related Question