FAQ: Why are my INSERT statements slow on MySQL?

Aiven Community
1

To figure out what could be affecting inserts, there are a few relevant questions that you should consider when investigating why an INSERT operation might be slow. Some of the relevant questions include:

  1. Do you have an unusually high number of indexes on the table?
  • Having a large number of indexes on a table can significantly slow down insert operations. This is due to the fact that for each INSERT, the index needs to be updated thereby increasing the overall overhead of the INSERT operation.
  1. Is the server under heavy load when the INSERT is being run?
  • When the server has high CPU, memory, or disk usage, INSERT operations could be delayed due to resource contention. Inserting data under heavy loads can increase wait times and slower performance.
  1. Are there any triggers on the table where the inserts are being carried out?
  • Triggers can add additional processing overhead to each INSERT operation. If you have triggers set up on the table, they will execute whenever an insert occurs, potentially causing delays.
  1. Are you measuring the time for the insertion only or is the network latency also included?
  • When measuring the time for insertions, it’s important to consider whether the network latency is included. If the measurement consists of network latency, especially in remote setups, it can affect the perceived speed of the INSERT operation.
  1. What is the storage engine being used?
  • The storage engine being used can impact INSERT performance. InnoDB is the default and recommended engine for most use cases due to its support for transactions and better crash recovery mechanisms. MyISAM, while faster for inserts in some scenarios, lacks these features and might not be as performant overall.
  1. What is the size of the DB? What is the size of the table?
  • The size of the database and the specific table being inserted into can affect INSERT performance. Larger tables require more disk I/O and can result in slower inserts. Additionally, performance can suffer if the table is fragmented or has a lot of data.
7 Likes
2

Super late to the party here, but THANK YOU so much for sharing your insights, @ENhara!

I also wanted to point out that these days, Aiven has a product called EverSQL that does these sort of performance checks in an automated fashion!