Slow delete multiple records in mysql

I noticed that on a test server, deleting multiline rows from a table takes a lot of time, for example, deleting 100 thousand rows takes about 80-90 seconds. I am using innodb mechanism in mysql. There are no DELETE triggers. The table now has about 23M rows. A time-consuming query looks something like this:

DELETE FROM contacts WHERE list_id = som_list_id
Index added

.

EXPLAIN SELECT *
FROM contacts
WHERE list_id =118

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  subscribers     ref     FK_list_id  FK_list_id  4   const   1  

Here are the profiling results:

    SET PROFILING = 1;
    DELETE FROM contacts WHERE list_id = 118;
    SHOW PROFILE FOR QUERY 1;


Status  Duration    CPU_user    CPU_system  Context_voluntary   Context_involuntary     Block_ops_in    Block_ops_out   Messages_sent   Messages_received   Page_faults_major   Page_faults_minor   Swaps   Source_function     Source_file     Source_line
starting    0.000031    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
checking permissions    0.000004    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    check_access    sql_parse.cc    4895
Opening tables  0.000031    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    open_tables     sql_base.cc     4860
System lock     0.000005    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_lock_tables   lock.cc     299
init    0.000079    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   79
updating    94.624119   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   277
end     0.000007    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   356
Waiting for query cache lock    0.000003    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    lock    sql_cache.cc    749
end     0.000006    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
query end   0.012509    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_execute_command   sql_parse.cc    4584
closing tables  0.000018    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_execute_command   sql_parse.cc    4636
freeing items   0.000038    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_parse     sql_parse.cc    5817
logging slow query  0.000006    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    log_slow_statement  sql_parse.cc    1581
logging slow query  0.000000    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    log_slow_statement  sql_parse.cc    1590
cleaning up     0.000003    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    dispatch_command    sql_parse.cc    1443

I tried to copy contacts to contact1, which does not have child tables that have: Profiling results are similar:

Status  Duration    CPU_user    CPU_system  Context_voluntary   Context_involuntary     Block_ops_in    Block_ops_out   Messages_sent   Messages_received   Page_faults_major   Page_faults_minor   Swaps   Source_function     Source_file     Source_line
starting    0.000028    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
checking permissions    0.000005    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    check_access    sql_parse.cc    4895
Opening tables  0.000035    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    open_tables     sql_base.cc     4860
System lock     0.000014    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_lock_tables   lock.cc     299
init    0.000792    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   79
updating    72.805936   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   277
end     0.000006    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_delete    sql_delete.cc   356
Waiting for query cache lock    0.000002    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    lock    sql_cache.cc    749
end     0.000006    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
query end   0.013662    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_execute_command   sql_parse.cc    4584
closing tables  0.000017    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_execute_command   sql_parse.cc    4636
freeing items   0.000044    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    mysql_parse     sql_parse.cc    5817
logging slow query  0.000003    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    log_slow_statement  sql_parse.cc    1581
logging slow query  0.000002    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    log_slow_statement  sql_parse.cc    1590
cleaning up     0.000003    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    dispatch_command    sql_parse.cc    1443

It seems like updating consumes most of the time.

Why does removal take so long? Does it take so much time because the server I / O system is slow? Thank.

+3
source share
1

MyISAM? ,

    delete quick from contacts...

    optimize table contacts

quick , . .

0

All Articles