1
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| Id | User        | Host      | db       | Command | Time | State    | Info                                                   |
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| 39 | tigase_user | localhost | tigasedb | Query   | 5406 | updating | delete from tig_pairs where uid >= 2 and uid <= 700000 |
| 40 | tigase_user | localhost | tigasedb | Query   |    0 | NULL     | show processlist                                       |
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+

It just stays in the 'updating' state for most of the 5406 seconds. Mysql version Server version: 5.1.69-0ubuntu0.11.10.1-log (Ubuntu)

thanks

 mysql> select count(*) from tig_pairs;
+----------+
| count(*) |
+----------+
|  6275968 |
+----------+
1 row in set (2.84 sec)

mysql> show create table tig_pairs;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                    |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tig_pairs | CREATE TABLE `tig_pairs` (
  `nid` int(10) unsigned NOT NULL,
  `uid` int(10) unsigned NOT NULL,
  `pkey` varchar(255) NOT NULL,
  `pval` mediumtext,
  KEY `uid` (`uid`),
  KEY `pkey` (`pkey`),
  KEY `nid_pkey` (`nid`,`pkey`),
  CONSTRAINT `tig_pairs_constr_1` FOREIGN KEY (`uid`) REFERENCES `tig_users` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5
  • 2
    Please add the table definition (output of SHOW CREATE TABLE tig_pairs;) so we know engine used and indexes. And how big is the table, how many rows? Sep 3, 2013 at 6:34
  • Thank you for adding the info. I suspect the text column and that the query deletes a large part of the table play a role in the huge delay but it may be something else. One and a half hour for a delete, even if it is deleting 7M rows sounds too long. Sep 3, 2013 at 13:50
  • 1
    Have you tried to time the operation when deleting in chunks (of say, 700 statements with 1000 different uid each) instead of one big DELETE? Sep 3, 2013 at 14:12
  • Check your /tmp/ dir if there is an MYD / MYI file there then your query is running as an disk based table what is very slow. if not delete an smaller range. This could also be an reason (article that i wrote here) dba.stackexchange.com/questions/48072/… Sep 3, 2013 at 19:19
  • If you delete way more than half of the data it could be ore efficient to copy data you want to keep to a new table and then TRUNCATE the table. TRUNCATE simply drops everything, DELETE goes row by row, while keeping MVCC guarantees
    – johannes
    Sep 3, 2013 at 23:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.