Eric, thank you for the printouts. Looks like MySQL refused to obey the USE INDEX clause! Did you run the EXPLAIN on a very small table? Otherwise I do not understand why it reports only 4700 rows.
You have a very long-running mass delete below. It has row locks on 13000 pages = 200 MB. It has delete marked (= deleted) 650 000 rows (= number of undo log entries). There are quite a lot of disk reads and writes per second: 23 + 28. The load is probably disk-bound. You have a very small buffer pool, only 1024 pages = 16 MB. I assume this is a stress test for a very small buffer pool. There are no dangling open transactions: only the mass delete and a single insert exist. In short, everything looks ok in the monitor output. I think it is worth to try 3.23.48 because the optimizer is tuned there. I tested deleting 15 000 rows from a 150 000 row table, and .48 chose to use the right index. Make sure the delete batches are small enough so that the optimizer picks the right index. EXPLAIN SELECT is the way to study optimizer choices. Note that because of the insert buffer, InnoDB can make inserts with less disk i/o than deletes. In the monitor output you see 9 000 000 insert buffer records were merged in 700 000 merges: on the average 13 records were inserted at a time. Deleting these records will use more disk i/o because there is no similar optimization in deletes. Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB -----Original Message----- From: Eric Mayers <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]>; [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Thursday, February 14, 2002 7:28 PM Subject: RE: Does delete from .. where (condition) use an index? >Heikki, > >Here is what you asked for: > >mysql> EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE datestamp ><= 20020213185230; >+--------+------+---------------+------+---------+------+------+-------- >----+ >| table | type | possible_keys | key | key_len | ref | rows | Extra >| >+--------+------+---------------+------+---------+------+------+-------- >----+ >| Syslog | ALL | ds_index | NULL | NULL | NULL | 4719 | where >used | >+--------+------+---------------+------+---------+------+------+-------- >----+ >1 row in set (0.00 sec) > >And here is a single InnoDB Monitor output: > >===================================== >020214 9:07:25 INNODB MONITOR OUTPUT >===================================== >---------- >SEMAPHORES >---------- >OS WAIT ARRAY INFO: reservation count 85376, signal count 83573 >Mutex spin waits 107640, rounds 1033422, OS waits 33331 >RW-shared spins 34894, OS waits 17557; RW-excl spins 34957, OS waits >34400 >------------ >TRANSACTIONS >------------ >Trx id counter 0 10816708 >Purge done for trx's n:o < 0 475530 undo n:o < 0 0 >Total number of lock structs in row lock hash table 13009 >---TRANSACTION 0 10816707, OS thread id 876555, not started, runs or >sleeps >MySQL thread id 205, query id 12109132 localhost root >INSERT INTO logs.Syslog set caid='630', datestamp=NULL, >message='4187481481 1481418748 418748 418748 >---TRANSACTION 0 5633944, OS thread id 1445901 updating or deleting, >active, runs or sleeps, has 13010 lock struct(s), undo log entries >650408 >MySQL thread id 344, query id 6056015 localhost root updating >DELETE FROM logs.Syslog where (datestamp <= (20020213190141 + (3600))) >-------- >FILE I/O >-------- >I/O thread 0 state: waiting for i/o request >I/O thread 1 state: waiting for i/o request >I/O thread 2 state: waiting for i/o request >I/O thread 3 state: waiting for i/o request >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: 1 >1164459 OS file reads, 1266570 OS file writes, 100823 OS fsyncs >23.25 reads/s, 27.88 writes/s, 2.06 fsyncs/s >------------- >INSERT BUFFER >------------- >Ibuf for space 0: size 518, free list len 272, seg size 791, >9152117 inserts, 8984010 merged recs, 675487 merges >--- >LOG >--- >Log sequence number 0 4190770295 >Log flushed up to 0 4190657162 >Last checkpoint at 0 4178363488 >1 pending log writes, 0 pending chkp writes >52481 log i/o's done, 0.88 log i/o's/second >---------------------- >BUFFER POOL AND MEMORY >---------------------- >Total memory allocated 33029216; in additional pool allocated 269312 >Free list length 121 >LRU list length 856 >Flush list length 805 >Buffer pool size 1024 >Pending reads 0 >Pending writes: LRU 0, flush list 0, single page 0 >Pages read 1256311, created 255856, written 1602244 >24.31 reads/s, 3.88 creates/s, 36.00 writes/s >Buffer pool hit rate 994 / 1000 >-------------- >ROW OPERATIONS >-------------- >1 queries inside InnoDB; main thread: flushing log >Number of rows inserted 10806106, updated 0, deleted 806508, read >6850315 >181.44 inserts/s, 0.00 updates/s, 11.69 deletes/s, 11.69 reads/s >---------------------------- >END OF INNODB MONITOR OUTPUT >============================ > > >Eric > > >> -----Original Message----- >> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] >> Sent: Wednesday, February 13, 2002 5:59 PM >> To: Eric Mayers; [EMAIL PROTECTED] >> Subject: Re: Does delete from .. where (condition) use an index? >> >> >> Eric, >> >> print what >> >> EXPLAIN SELECT * FROM Syslog USE INDEX(ds_index) WHERE ... >> >> says. >> >> Please also show what the InnoDB monitor prints. >> >> Regards, >> >> Heikki >> >> >> -----Original Message----- >> From: Eric Mayers <[EMAIL PROTECTED]> >> To: Heikki Tuuri <[EMAIL PROTECTED]>; [EMAIL PROTECTED] >> <[EMAIL PROTECTED]> >> Date: Thursday, February 14, 2002 3:44 AM >> Subject: RE: Does delete from .. where (condition) use an index? >> >> >> >Heikki, >> > >> >mysql> EXPLAIN SELECT * FROM Syslog WHERE datestamp <= 2002021310712; >> >+--------+------+---------------+------+---------+------+---- >> -----+----- >> >-------+ >> >| table | type | possible_keys | key | key_len | ref | rows | >> >Extra | >> >+--------+------+---------------+------+---------+------+---- >> -----+----- >> >-------+ >> >| Syslog | ALL | ds_index | NULL | NULL | NULL | 2204932 | >> >where used | >> >+--------+------+---------------+------+---------+------+---- >> -----+----- >> >-------+ >> >1 row in set (0.00 sec) >> > >> >How can I force it to use the index? >> > >> >(the indexes look like: >> > >> >mysql> show index from Syslog; >> >+--------+------------+----------+--------------+------------ >> -+--------- >> >--+-------------+----------+--------+---------+ >> >| Table | Non_unique | Key_name | Seq_in_index | Column_name | >> >Collation | Cardinality | Sub_part | Packed | Comment | >> >+--------+------------+----------+--------------+------------ >> -+--------- >> >--+-------------+----------+--------+---------+ >> >| Syslog | 1 | ds_index | 1 | datestamp | A >> >| 5893 | NULL | NULL | | >> >| Syslog | 1 | ca_index | 1 | caid | A >> >| 9730 | NULL | NULL | | >> >+--------+------------+----------+--------------+------------ >> -+--------- >> >--+-------------+----------+--------+---------+ >> >2 rows in set (6.90 sec) >> > >> >) >> > >> >Thanks, >> >Eric >> > >> > >> >> -----Original Message----- >> >> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] >> >> Sent: Wednesday, February 13, 2002 5:34 PM >> >> To: [EMAIL PROTECTED] >> >> Subject: Re: Does delete from .. where (condition) use an index? >> >> >> >> >> >> Eric, >> >> >> >> MySQL is very pessimistic about key accesses: it assumes many >> >> of them cause >> >> a random disk read. >> >> >> >> Please print >> >> >> >> EXPLAIN SELECT * FROM Syslog WHERE datestamp < ... >> >> >> >> to see how it accesses the table. >> >> >> >> I have tuned the optimization in 3.23.48 so that it would >> favor index >> >> searches more often. Please try also with 3.23.48. >> >> >> >> Regards, >> >> >> >> Heikki >> >> Innobase Oy >> >> >> >> "Eric Mayers" wrote in message ... >> >> >I haven't been able to find details about how/if MySQL (InnoDB) >> >> >optimizes deletes. Does it use indexes? Can I force it to use an >> >> >index? >> >> > >> >> >My table is defined as: >> >> > >> >> >CREATE TABLE Syslog( >> >> > id int(11) not null, >> >> > datestamp timestamp(14), >> >> > message char(255), >> >> > KEY ds_index(datestamp), >> >> > KEY id_index(id) >> >> >) type=InnoDB; >> >> > >> >> > >> >> >There are about 15 mil rows, and I'm just trying to delete a small >> >> >portion of them (0.05% perhaps). My delete looks like: >> >> > >> >> >DELETE FROM Syslog WHERE datestamp <= 2002021310712; >> >> > >> >> >(the magic number there comes from "SELECT (min(datestamp) + >> >> 30) AS min >> >> >FROM Syslog" in a previous query). >> >> > >> >> >.. and its taking a very long time.. watching the innodb >> monitor I'm >> >> >seeing a large number of reads/s (29000) and a small number >> >> of deletes/s >> >> >(10) which makes me believe its not using the index.. >> >> > >> >> >Any way to improve this? >> >> > >> >> >Eric Mayers >> >> >Software Engineer I >> >> >> >> >> >> >> >> >> >> >> --------------------------------------------------------------------- >> >> Before posting, please check: >> >> http://www.mysql.com/manual.php (the manual) >> >> http://lists.mysql.com/ (the list archive) >> >> >> >> To request this thread, e-mail <[EMAIL PROTECTED]> >> >> To unsubscribe, e-mail >> >> <[EMAIL PROTECTED]> >> >> Trouble unsubscribing? Try: >http://lists.mysql.com/php/unsubscribe.php >>> >>> > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
