Eric, -----Original Message----- From: Eric Mayers <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Date: Friday, February 15, 2002 1:10 AM Subject: RE: Does delete from .. where (condition) use an index?
>Heikki, > >I thought I'd bring this off the list to reduce list traffic (if you >think its useful please feel free to respond on the list). I only used I respond to the mailing list because I think this is useful info for other users. >the "USE INDEX" syntax in the "EXPLAIN SELECT ..." statement, not in the >delete. It gives me a syntax error if I include USE INDEX in a DELETE >statement... > >"DELETE FROM SYSLOG USE INDEX(ds_index) WHERE datestamp < 37827382;" Is >this the wrong syntax? Can I tell it to use the index? Sorry, no. There is no such syntax for DELETE FROM. ... >I'm working on getting more memory in the box so I can increase the size >of the buffer pool considerably. I've also put a limit on the delete >statement and put it in a loop checking affected rows and that seems to >have helped signifigantly A great idea, which did not come to my mind: using the LIMIT clause in DELETE FROM you can easily control the size of deletions! >Again, thanks for your help! > >Best Regards, >Eric Mayers >Software Engineer I >Captus Networks Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com >> -----Original Message----- >> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] >> Sent: Thursday, February 14, 2002 1:23 PM >> To: Eric Mayers; [EMAIL PROTECTED] >> Subject: Re: Does delete from .. where (condition) use an index? >> >> >> 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
