CODEWITHSUNDEEP
mysqlinnodbamazon-rds
chaimp
chaimp

Reputation: 17907

How to improve InnoDB's SELECT performance while INSERTing

We recently switched our tables to use InnoDB (from MyISAM) specifically so we could take advantage of the ability to make updates to our database while still allowing SELECT queries to occur (i.e. by not locking the entire table for each INSERT)

We have a cycle that runs weekly and INSERTS approximately 100 million rows using "INSERT INTO ... ON DUPLICATE KEY UPDATE ..."

We are fairly pleased with the current update performance of around 2000 insert/updates per second.

However, while this process is running, we have observed that regular queries take very long.

For example, this took about 5 minutes to execute:

SELECT itemid FROM items WHERE itemid = 950768

(When the INSERTs are not happening, the above query takes several milliseconds.)

Is there any way to force SELECT queries to take a higher priority? Otherwise, are there any parameters that I could change in the MySQL configuration that would improve the performance?

We would ideally perform these updates when traffic is low, but anything more than a couple seconds per SELECT query would seem to defeat the purpose of being able to simultaneously update and read from the database. I am looking for any suggestions.

We are using Amazon's RDS as our MySQL server.

Thanks!

Upvotes: 1

Views: 838

Answers (1)

spaaarky21
spaaarky21

Reputation: 6878

I imagine you have already solved this nearly a year later :) but I thought I would chime in. According to MySQL's documentation on internal locking (as opposed to explicit, user-initiated locking):

Table updates are given higher priority than table retrievals. Therefore, when a lock is released, the lock is made available to the requests in the write lock queue and then to the requests in the read lock queue. This ensures that updates to a table are not “starved” even if there is heavy SELECT activity for the table. However, if you have many updates for a table, SELECT statements wait until there are no more updates.

So it sounds like your SELECT is getting queued up until your inserts/updates finish (or at least there's a pause.) Information on altering that priority can be found on MySQL's Table Locking Issues page.

Upvotes: 2

Related Questions