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.
source
share