Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
+----+-------------+-----------+----------+---------+------+----------+--------------------------------------------------------+
| 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)
share|improve this question
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? –  ypercube Sep 3 '13 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. –  ypercube Sep 3 '13 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? –  ypercube Sep 3 '13 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/… –  Raymond Nijland Sep 3 '13 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 '13 at 23:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.