Slow SQL Queries After a Large Amount of Data Is Deleted from a Large Table

Scenario

After multiple wide columns of data (records about 1 GB long) are deleted at the same time, performing an INSERT, DELETE, UPDATE, or SELECT operation on the same table again takes a long time. After about 20 minutes, the problem is resolved.

Problem Reproduction

1. Suppose, for example, that the value of max_allowed_packet is 1073741824.
2. Create a table.

   CREATE TABLE IF NOT EXISTS zstest1 
   ( 
   id int  PRIMARY KEY not null, 
   c_longtext LONGTEXT 
   ); 

3. Insert data to the table.

   insert into zstest1 values(1, repeat('a', 1073741800)); 
   insert into zstest1 values(2, repeat('a', 1073741800)); 
   insert into zstest1 values(3, repeat('a', 1073741800)); 
   insert into zstest1 values(4, repeat('a', 1073741800)); 
   insert into zstest1 values(5, repeat('a', 1073741800)); 
   insert into zstest1 values(6, repeat('a', 1073741800)); 
   insert into zstest1 values(7, repeat('a', 1073741800)); 
   insert into zstest1 values(8, repeat('a', 1073741800)); 
   insert into zstest1 values(9, repeat('a', 1073741800)); 
   insert into zstest1 values(10, repeat('a', 1073741800)); 

4. Delete data from the table.

   delete from zstest1; 

5. Execute a query.

   select id from zstest1;    //The execution is slow.

Possible Causes

After the DELETE operation is performed, the background purge thread clears all records marked with a delete mark. Due to the large amount of data to be deleted, the purge thread obtains the SX lock of the index root node where the page is located when traversing and releasing the page. As a result, the SELECT statement cannot obtain the RW lock of the root page and keeps waiting.

Solution

• This phenomenon is normal. After the purge operation is complete, the issue will resolve itself.
• Scale up the instance specifications to improve the purge efficiency.
• Do not delete a large amount of data at the same time. To delete all data from a table, use the truncate table statement.