How to improve MySQL INSERT and UPDATE performance?

Question

Performance of INSERT and UPDATE statements in our database seems to be degrading and causing poor performance in our web app.

Tables are InnoDB and the application uses transactions. Are there any easy tweaks that I can make to speed things up?

I think we might be seeing some locking issues, how can I find out?

Answer 1

If you are not using indexes, they can help improve performance of update queries.

Answer 2

• You could change the settings to speed InnoDB inserts up.
• And even more ways to speed up InnoDB
• ...and one more optimization article

Answer 3

I would try setting your tables to delay index updates.

ALTER TABLE {name} delay_key_write='1'

Answer 4

I would not look at locking/blocking unless the number of concurrent users have been increasing over time.

If the performance gradually degraded over time I would look at the query plans with the EXPLAIN statement.

It would be helpful to have the results of these from the development or initial production environment, for comparison purposes.

Dropping or adding an index may be needed, or some other maintenance action specified in other posts.

Answer 5

INSERT and UPDATE get progressively slower when the number of rows increases on a table with an index. Innodb tables are even slower than MyISAM tables for inserts and the delayed key write option is not available.

The most effective way to speed things up would be to save the data first into a flat file and then do LOAD DATA , this is about 20x faster.

The second option would be create a temporary in memory table, load the data into it and then do a INSERT INTO SELECT in batches. That is once you have about 100 rows in your temp table, load them into the permanent one.

Additionally you can get an small improvement in speed by moving the Index file into a separate physical hard drive from the one where the data file is stored. Also try to move any bin logs into a different device. Same applies for the temporary file location.