How to improve MySQL INSERT and UPDATE performance?

Question

The performance of the INSERT and UPDATE statements in our database seems to be degrading and resulting in poor performance in our web application.

The tables are InnoDB and the application uses transactions. Are there any easy settings I can make to speed up the process?

I think we can observe some blocking problems, how can I find out?

Answer 1

• You can change the settings so that InnoDB speed is inserted.
• And even more ways to speed up InnoDB
• ... and another optimization article

Answer 2

INSERT and UPDATE are getting slower as the number of rows increases in the indexed table. Innodb tables are even slower than insert MyISAM tables, and the write delay option is not available.

The most effective way to speed things up is to first save the data to a flat file and then do LOAD DATA , which is about 20 times faster.

The second option is to create a temporary memory table, load data into it, and then make INSERT INTO SELECT in batches. This is when you have about 100 rows in your table pace, load them into a constant.

In addition, you can get a slight speed improvement by moving the index file to a separate physical hard drive from where the data file is stored. Also try moving any log logs to another device. The same applies to the temporary location of the file.

Answer 3

I would try setting your tables to delay index update.

 ALTER TABLE {name} delay_key_write='1' 

Answer 4

If you do not use indexes, they can help improve the performance of update requests.

Answer 5

I would not look at blocking / blocking if the number of simultaneous users increases over time.

If performance gradually worsened over time, I would look at query plans using the EXPLAIN statement.

It would be useful to obtain the results of these studies from a development or initial production environment for comparison purposes.

You may need to reset or add an index, or some other maintenance actions indicated in other messages.