The query is running already Query 4493 Updating update dbmail_messages set status=3 where deleted_flag=1 and status!=3 This is one thing, but independent of the query what does mysql altering tables for a very long time without disk-io/cpu-usage? +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | dbmail_messages | ALL | status_index | NULL | NULL | NULL | 90221 | Using where | +----+-------------+-----------------+------+---------------+------+---------+------+-------+-------------+ 1 row in set (0.00 sec) ===================================== 090803 12:59:40 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 54 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 74963, signal count 74130 Mutex spin waits 0, rounds 412800, OS waits 13567 RW-shared spins 117210, OS waits 57229; RW-excl spins 4120, OS waits 2848 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 090802 23:13:41 Transaction: TRANSACTION 0 10723189, ACTIVE 0 sec, process no 19997, OS thread id 140497762027856 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1216, 2 row lock(s), undo log entries 1 MySQL thread id 996, query id 1221574 localhost root update insert into dbmail_acl (user_id, mailbox_id, lookup_flag, read_flag, seen_flag, write_flag, insert_flag, post_flag, create_flag, delete_flag, administer_flag) values (4, 1948, 1, 1, 0, 1, 1, 1, 1, 1, 1) Foreign key constraint fails for table `dbmail`.`dbmail_acl`: , CONSTRAINT `dbmail_acl_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index `mailbox_id_index` tuple: DATA TUPLE: 2 fields; 0: len 8; hex 000000000000079c; asc ;; 1: len 8; hex 0000000000000004; asc ;; But in parent table `dbmail`.`dbmail_mailboxes`, in index `PRIMARY`, the closest match we can find is record: PHYSICAL RECORD: n_fields 14; compact format; info bits 0 0: len 8; hex 000000000000079d; asc ;; 1: len 6; hex 0000009afb8c; asc ;; 2: len 7; hex 8000003e5a2d3a; asc >Z-:;; 3: len 8; hex 000000000000009b; asc ;; 4: len 4; hex 4a756e6b; asc Junk;; 5: len 1; hex 01; asc ;; 6: len 1; hex 01; asc ;; 7: len 1; hex 01; asc ;; 8: len 1; hex 01; asc ;; 9: len 1; hex 01; asc ;; 10: len 1; hex 01; asc ;; 11: len 1; hex 00; asc ;; 12: len 1; hex 00; asc ;; 13: len 1; hex 02; asc ;; ------------ TRANSACTIONS ------------ Trx id counter 0 11298318 Purge done for trx's n:o < 0 11297421 undo n:o < 0 0 History list length 112 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 11298234, not started, process no 19997, OS thread id 140497757235536 MySQL thread id 1786, query id 2827769 localhost root SHOW INNODB STATUS ---TRANSACTION 0 11152179, not started, process no 19997, OS thread id 140497761495376 MySQL thread id 1168, query id 2496083 localhost dbmail ---TRANSACTION 0 11152170, not started, process no 19997, OS thread id 140497768683856 MySQL thread id 1634, query id 2496059 localhost dbmail ---TRANSACTION 0 11147923, not started, process no 19997, OS thread id 140497762027856 MySQL thread id 1631, query id 2489158 localhost dbmail ---TRANSACTION 0 11151147, not started, process no 19997, OS thread id 140497759365456 MySQL thread id 1633, query id 2494699 localhost dbmail ---TRANSACTION 0 11146327, not started, process no 19997, OS thread id 140497769216336 MySQL thread id 1632, query id 2485972 localhost dbmail ---TRANSACTION 0 11294758, not started, process no 19997, OS thread id 140497761229136 MySQL thread id 1354, query id 2808109 localhost dbmail ---TRANSACTION 0 11291496, not started, process no 19997, OS thread id 140497758300496 MySQL thread id 1350, query id 2799852 localhost dbmail ---TRANSACTION 0 11286582, not started, process no 19997, OS thread id 140497757501776 MySQL thread id 1349, query id 2789773 localhost dbmail ---TRANSACTION 0 11109450, not started, process no 19997, OS thread id 140497758566736 MySQL thread id 1327, query id 2261471 localhost dbmail ---TRANSACTION 0 11297116, not started, process no 19997, OS thread id 140497769482576 MySQL thread id 982, query id 2827704 localhost dbmail ---TRANSACTION 0 11286566, not started, process no 19997, OS thread id 140497760164176 MySQL thread id 826, query id 2789735 localhost dbmail ---TRANSACTION 0 11297095, not started, process no 19997, OS thread id 140497767618896 MySQL thread id 825, query id 2821280 localhost dbmail ---TRANSACTION 0 11296096, not started, process no 19997, OS thread id 140497762560336 MySQL thread id 824, query id 2812231 localhost dbmail ---TRANSACTION 0 11298266, not started, process no 19997, OS thread id 140497770015056 MySQL thread id 516, query id 2826829 localhost dbmail ---TRANSACTION 0 11298317, not started, process no 19997, OS thread id 140497760430416 MySQL thread id 389, query id 2827522 localhost dbmail ---TRANSACTION 0 11297114, not started, process no 19997, OS thread id 140497764690256 MySQL thread id 57, query id 2821324 localhost dbmail ---TRANSACTION 0 11297480, not started, process no 19997, OS thread id 140497765755216 MySQL thread id 55, query id 2823095 localhost dbmail ---TRANSACTION 0 11293523, not started, process no 19997, OS thread id 140497764424016 MySQL thread id 43, query id 2804680 localhost dbmail ---TRANSACTION 0 11296062, not started, process no 19997, OS thread id 140497763092816 MySQL thread id 42, query id 2812166 localhost dbmail ---TRANSACTION 0 11297076, not started, process no 19997, OS thread id 140497763625296 MySQL thread id 40, query id 2821236 localhost dbmail ---TRANSACTION 0 11286764, not started, process no 19997, OS thread id 140497763359056 MySQL thread id 41, query id 2790216 localhost dbmail ---TRANSACTION 0 11297484, not started, process no 19997, OS thread id 140497766021456 MySQL thread id 19, query id 2823096 localhost dbmail ---TRANSACTION 0 11286816, not started, process no 19997, OS thread id 140497763891536 MySQL thread id 39, query id 2790363 localhost dbmail ---TRANSACTION 0 11286601, not started, process no 19997, OS thread id 140497767086416 MySQL thread id 15, query id 2789817 localhost dbmail ---TRANSACTION 0 11297489, not started, process no 19997, OS thread id 140497766820176 MySQL thread id 16, query id 2823097 localhost dbmail ---TRANSACTION 0 11298294, not started, process no 19997, OS thread id 140497766287696 MySQL thread id 18, query id 2827161 localhost dbmail ---TRANSACTION 0 11298262, not started, process no 19997, OS thread id 140497766553936 MySQL thread id 17, query id 2826744 localhost dbmail ---TRANSACTION 0 11297291, ACTIVE 4537 sec, process no 19997, OS thread id 140497768950096 updating or deleting, thread declared inside InnoDB 382 mysql tables in use 1, locked 1 286 lock struct(s), heap size 30704, 52442 row lock(s), undo log entries 4072 MySQL thread id 1760, query id 2821906 arrakis.thelounge.net 10.0.0.6 dbmailpanel Updating update dbmail_messages set status=3 where deleted_flag=1 and status!=3 -------- 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 516604 OS file reads, 226022 OS file writes, 33619 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.52 writes/s, 0.30 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 18, seg size 20, 8673 inserts, 8673 merged recs, 4869 merges Hash table size 3187567, node heap has 542 buffer(s) 0.00 hash searches/s, 3.67 non-hash searches/s --- LOG --- Log sequence number 25 832259831 Log flushed up to 25 832257171 Last checkpoint at 25 832257171 0 pending log writes, 0 pending chkp writes 153531 log i/o's done, 0.13 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 322495514; in additional pool allocated 1048576 Dictionary memory allocated 221480 Buffer pool size 98304 Free buffers 0 Database pages 97761 Modified db pages 6 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1469200, created 46066, written 139928 0.00 reads/s, 0.00 creates/s, 0.35 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 19997, id 140497781303632, state: waiting for server activity Number of rows inserted 325232, updated 181143, deleted 79692, read 1604769063 0.00 inserts/s, 0.91 updates/s, 0.00 deletes/s, 0.91 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ "mysqltuner" says all right innodb_buffer_pool_size >= 16 GB which is recommended does not make really sense with 7.5 GB physical ram i would say :-) [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.36 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 114M (Tables: 21) [--] Data in InnoDB tables: 16G (Tables: 24) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 14 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 9h 21m 11s (2M q [23.552 qps], 1K conn, TX: 23B, RX: 950M) [--] Reads / Writes: 53% / 47% [--] Total buffers: 3.6G global + 2.6M per thread (500 max threads) [OK] Maximum possible memory usage: 4.9G (69% of installed RAM) [OK] Slow queries: 0% (33/2M) [OK] Highest usage of available connections: 11% (57/500) [OK] Key buffer size / total MyISAM indexes: 650.0M/579.7M [OK] Key buffer hit rate: 99.6% (12M cached / 44K reads) [OK] Query cache efficiency: 38.1% (519K cached / 1M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (2 temp sorts / 22K sorts) [OK] Temporary tables created on disk: 2% (262 on disk / 9K total) [OK] Thread cache hit rate: 96% (57 created / 1K connections) [OK] Table cache hit rate: 35% (79 open / 222 opened) [OK] Open file limit used: 1% (92/8K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) [!!] InnoDB data size / buffer pool: 16.8G/1.5G