DELETE performance problem

[Bradford Folkens]

Hope someone can give some insight into this... Maybe I'm missing something obvious:

Trying to delete ~20000000 rows from a table stored with InnoDB takes a long (long) time (approx. 12-15 hours)
Total rows in the table: 35000000

Note that on the 'SHOW INNODB STATUS' output below, the 'Number of rows ... deleted' increments by 1 every 2-3 seconds, while the reads/s is

high...

I've also tried DELETE QUICK - but it still takes a long time...

I'm using Using MySQL 4.0.22-standard-log

Other stuff below:


Machine:
- 2 Processor Intel Xeon 2.4GHz - (effective 4 processor with HT)
- 4G Ram
- SCSI Raid 10 array
- Kernel 2.6.9-1.11_FC2smp


(INNODB STATUS - another snap below)
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread process no. 22053, id 28680, state: sleeping
Number of rows inserted 3291798, updated 6709, deleted 120807, read 1915639050
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1962000.00 reads/s



SHOW CREATE TABLE tblKeyword;
CREATE TABLE `tblKeyword` (
`KeywordID` int(10) unsigned NOT NULL default '0',
`DocID` int(10) unsigned NOT NULL default '0',
`Offset` int(10) unsigned NOT NULL default '0',
`ContextID` int(10) unsigned NOT NULL default '0',
`TypeID` int(2) unsigned NOT NULL default '1',
UNIQUE KEY `tblKeyword_U1` (`ContextID`,`KeywordID`,`DocID`,`Offset`,`TypeID`)
) TYPE=InnoDB





(COMPLETE LISTING OF INNODB STATUS)
*************************** 1. row ***************************
Status:
=====================================
050129 19:28:37 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 0 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1337481, signal count 1325934
Mutex spin waits 86845602, rounds 354726092, OS waits 284367
RW-shared spins 765427, OS waits 214537; RW-excl spins 2342590, OS waits 479588
------------------------
LATEST DETECTED DEADLOCK
------------------------
050129 18:52:06
*** (1) TRANSACTION:
TRANSACTION 0 541304062, ACTIVE 1 sec, process no 22575, OS thread id 110620 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 2
MySQL thread id 7062, query id 6228903 12-215-42-20.client.mchsi.com 12.215.42.20 someuser updating
DELETE FROM tblSearchCache WHERE KeywordID=611083 AND domain='low-carb-recipes.ws'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 174573 n bits 88 index `PRIMARY` of table `someschema/tblSearchCache` trx id 0 541304062 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

*** (2) TRANSACTION:
TRANSACTION 0 541304061, ACTIVE 4 sec, process no 22596, OS thread id 135202 inserting, thread declared inside InnoDB 441
mysql tables in use 2, locked 2
122 lock struct(s), heap size 11584, undo log entries 282
MySQL thread id 7060, query id 6228875 12-215-42-20.client.mchsi.com 12.215.42.20 someuser Sending data
REPLACE INTO tblSearchCache (KeywordID, ProximityScore, Rank, KeywordScore, TitleScore, Total, URL, Timestamp, Content, Title, BooleanScore,

Domain, SimilarID, OriginalID) SELECT 611082 AS KeywordID, 70*(((IF(LOCATE(' portuguese ',LCASE(tblEntry.Content))>0,IF(LOCATE(' eggpplant

',LCASE(tblEntry.Con
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 174573 n bits 88 index `PRIMARY` of table `someschema/tblSearchCache` trx id 0 541304061 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 174573 n bits 88 index `PRIMARY` of table `someschema/tblSearchCache` trx id 0 541304061 lock_mode X insert

intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; 1-byte offs TRUE; info bits 0
0: len 9; hex 73757072656d756d00; asc supremum ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 541592878
Purge done for trx's n:o < 0 541592874 undo n:o < 0 0
Total number of lock structs in row lock hash table 211885
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 22197, OS thread id 81941
MySQL thread id 41377, query id 6833094 localhost someuser
show innodb status
---TRANSACTION 0 0, not started, process no 22579, OS thread id 127008
MySQL thread id 38270, query id 6230933 12-215-42-19.client.mchsi.com 12.215.42.19 someuser
---TRANSACTION 0 541592327, not started, process no 22559, OS thread id 106523
MySQL thread id 37740, query id 6824213 12-215-42-20.client.mchsi.com 12.215.42.20 remotecs
---TRANSACTION 0 0, not started, process no 22580, OS thread id 131105
mysql tables in use 1, locked 1
MySQL thread id 33403, query id 5589255 12-215-42-19.client.mchsi.com 12.215.42.19 someuser Table lock
ALTER TABLE `someschema`.`tblKeyword` DROP INDEX `tblKeyword_I1`
---TRANSACTION 0 541592823, not started, process no 22575, OS thread id 110620
MySQL thread id 7062, query id 6832777 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541592820, not started, process no 22596, OS thread id 135202
MySQL thread id 7060, query id 6832749 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541303179, not started, process no 22181, OS thread id 77844
MySQL thread id 6805, query id 6216130 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541592827, not started, process no 22489, OS thread id 86038
MySQL thread id 6732, query id 6832795 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541303145, not started, process no 22060, OS thread id 53262
MySQL thread id 6730, query id 6216126 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 0, not started, process no 22063, OS thread id 65553
MySQL thread id 1873, query id 6833093 12-215-42-19.client.mchsi.com 12.215.42.19 someuser cleaning up
SHOW INNODB STATUS
---TRANSACTION 0 0, not started, process no 22148, OS thread id 73747
MySQL thread id 1870, query id 6818730 12-215-42-19.client.mchsi.com 12.215.42.19 someuser
---TRANSACTION 0 541302601, not started, process no 22061, OS thread id 57359
MySQL thread id 311, query id 6208678 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541302611, not started, process no 22059, OS thread id 49165
MySQL thread id 300, query id 6208766 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541303256, not started, process no 22062, OS thread id 61456
MySQL thread id 116, query id 6216115 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541592876, not started, process no 22064, OS thread id 69650
MySQL thread id 9, query id 6833084 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 541592154, not started, process no 22056, OS thread id 36874
MySQL thread id 1, query id 6822213 12-215-42-20.client.mchsi.com 12.215.42.20 someuser
---TRANSACTION 0 538402910, ACTIVE 38478 sec, process no 22600, OS thread id 139299 fetching rows, thread declared inside InnoDB 413
mysql tables in use 2, locked 2
211887 lock struct(s), heap size 20229440, undo log entries 46
MySQL thread id 3443, query id 42269 12-215-42-20.client.mchsi.com 12.215.42.20 someuser Sending data
DELETE QUICK tblKeyword, tblKeywordRef FROM tblKeyword k, tblKeywordRef kr WHERE k.ContextID = 2 AND kr.ContextID = 2
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
2354086 OS file reads, 3212056 OS file writes, 3193049 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7,
473 inserts, 473 merged recs, 446 merges
Hash table size 5810587, used cells 5340062, node heap has 14296 buffer(s)
1805000.00 hash searches/s, 144000.00 non-hash searches/s
---
LOG
---
Log sequence number 88 1476730304
Log flushed up to 88 1476730304
Last checkpoint at 88 1476729888
0 pending log writes, 0 pending chkp writes
3179189 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1585075606; in additional pool allocated 2344320
Buffer pool size 89600
Free buffers 0
Database pages 74070
Modified db pages 4
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10161192, created 30949, written 71391
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread process no. 22053, id 28680, state: sleeping
Number of rows inserted 3294948, updated 6779, deleted 123836, read 1994881244
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 2027000.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.11 sec)





*************************************
vmstat output
*************************************
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 1 0 3200 5412 2058468 0 0 1 4 6 5 4 1 93 3
0 1 0 3532 5456 2058164 0 0 2744 1740 1312 483 11 1 69 19
0 1 0 3968 5448 2057652 0 0 2956 20 1187 339 9 1 74 16
0 1 0 3520 5440 2058440 0 0 2128 0 1167 341 11 1 74 15
0 1 0 4608 5440 2057400 0 0 1992 0 1143 289 10 0 74 15
1 0 0 4608 5436 2057144 0 0 2708 36 1220 346 10 1 74 15
0 1 0 3840 5432 2057928 0 0 1920 2600 1182 261 9 0 74 16
0 1 0 2912 5444 2058696 0 0 2416 396 1197 390 10 0 74 16
1 0 0 3216 5432 2058448 0 0 2560 0 1184 347 10 1 74 16
0 1 0 4736 5344 2056976 0 0 1872 0 1150 301 9 1 74 16
1 0 0 3840 5360 2058000 0 0 2180 0 1174 311 11 1 74 14
0 1 0 4348 5360 2057480 0 0 1120 0 1091 185 6 0 74 19
1 0 0 4736 5364 2056696 0 0 4360 2616 1318 417 12 1 73 14
1 0 0 3960 5396 2057704 0 0 2428 848 1217 394 10 1 73 15