2

When I delete 5000 rows from a table .It is costs more than 2 seconds but if I change delete to select it is just costs a few milliseconds.

How can I improve the performance?

Here is the details:

There are 2 million rows. And the meeting_number have index.

    mysql> show table status like 'zm_meeting_ext'\G;
    *************************** 1. row ***************************
               Name: zm_meeting_ext
             Engine: InnoDB
            Version: 10
         Row_format: Compact
               Rows: 2018854
     Avg_row_length: 318
        Data_length: 643842048
    Max_data_length: 0
       Index_length: 1225310208
          Data_free: 59768832
     Auto_increment: NULL
        Create_time: 2015-08-29 05:48:54
        Update_time: NULL
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options: 
            Comment: 
    1 row in set (0.24 sec)
mysql> show index  from zm_meeting_ext\G;
*************************** 1. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: zm_meeting_ext
   Non_unique: 0
     Key_name: meeting_ext_key
 Seq_in_index: 1
  Column_name: meeting_number
    Collation: A
  Cardinality: 2018856
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 

delete Statement:

DELETE  FROM zm_meeting_ext 
        WHERE   meeting_number IN
                 (  
                    888234641
                 , 
                    713842289
                 , 
                    587345701
                 , 
                    543533869
                 , 
                    885497679
                 , 
                    805900814
                 , 
                    804899468
                 , 
                    115617318
                 , 
                    582279870
                .......
                5000 numbers
                )

So how can I improve the performance and what is the principle of the delete operation?

2

When You SELECT data - You are work with InnoDB memory cache, You not write to disk, You are not log operations.

It is normal when same select thousands time faster then DELETE.

When You run DELETE command, MySQL must:

  • do the same as SELECT - found rows
  • log operation (write into redo-log files)
  • mark rows as deleted
  • lock deleted rows - wait if any other operations use them
  • update indexes

2 sec it is not significant huge time, but if it make collisions for other operations - You can to split operation for chunks

  • yes, it is more correct form - still additional work – a_vlad Nov 23 '16 at 11:14
  • As you said,when does InnoDB delete the rows in disk? – zane Nov 24 '16 at 1:51
  • @zane - select - could work with disk, could not. DELETE/UPDATE/INSERT - always, even if all operations on rows which in memory cache - redo log files + row level locks would be. Faster or slower - depends from size of transaction, from buffers and etc - but this operation will be always and they slower than read. – a_vlad Nov 24 '16 at 2:37
  • All of the DELETE/UPDATE/INSERT operations will log into redo-log files. – zane Nov 24 '16 at 3:05
  • exactly, and this operation mean - write, plus not forget about locks. Select (from Your question) - not write nothing (in best case, in worst temp table on disk but this query also will be longer) – a_vlad Nov 24 '16 at 9:54
0

From Rick James Blog:

InnoDB has to write the undo information to its transaction logs; this significantly increases the I/O required.

I think if you chunk delete to multiply delete, Speed will be better.

0

Take a look at the Percona pt-archiver tool ( https://www.percona.com/doc/percona-toolkit/2.1/pt-archiver.html ) which will nibble away at the data in small chunks to achieve the desired effect.

This has the benefit of not locking parts of the table for extended periods of time whilst a large delete is in progress, which will benefit the rest of the application performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.