Hi All,
I am using INNODB.I have a delete quetry something like this :
delete from modnaptrrecord_zone where
modnaptrrecord_zone.modnaptrrecord_id in (593536 ,593537 ,593538 ,593539 ,593540
........) and modnaptrrecord_zone.modnaptrrecord_id not in (593536
........................)
This is taking a very long time
to execute..somewhere around 15mins. The values in the "id in"
and "not in" of the where clause are around 10000. I am sending the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking such a long time ? And how to optimize this query ?
and "not in" of the where clause are around 10000. I am sending the "SHOW INNODB STATUS also. Can somebody let me know, why this is taking such a long time ? And how to optimize this query ?
Thanks
Prasad
The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com |
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- [EMAIL PROTECTED]> /usr/local/mysql/bin/mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 to server version: 4.1.7-standard
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use ipworks
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show innodb status
-> ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
060621 0:22:26 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 14 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 63030, signal count 57362
Mutex spin waits 77424, rounds 805746, OS waits 26061
RW-shared spins 25589, OS waits 12138; RW-excl spins 24700, OS waits 20788
------------
TRANSACTIONS
------------
Trx id counter 0 202934
Purge done for trx's n:o < 0 202934 undo n:o < 0 0
Total number of lock structs in row lock hash table 483
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 36
MySQL thread id 27, query id 2554040 localhost root
show innodb status
---TRANSACTION 0 202928, not started, OS thread id 32
MySQL thread id 23, query id 2553772 localhost 127.0.0.1 nobody
---TRANSACTION 0 202918, not started, OS thread id 30
MySQL thread id 21, query id 2553762 localhost 127.0.0.1 nobody
---TRANSACTION 0 202924, not started, OS thread id 29
MySQL thread id 20, query id 2553768 localhost 127.0.0.1 nobody
---TRANSACTION 0 202920, not started, OS thread id 28
MySQL thread id 19, query id 2553764 localhost 127.0.0.1 nobody
---TRANSACTION 0 202914, not started, OS thread id 26
MySQL thread id 17, query id 2553758 localhost 127.0.0.1 nobody
---TRANSACTION 0 202912, not started, OS thread id 25
MySQL thread id 16, query id 2553756 localhost 127.0.0.1 nobody
---TRANSACTION 0 202930, not started, OS thread id 24
MySQL thread id 15, query id 2553774 localhost 127.0.0.1 nobody
---TRANSACTION 0 202926, not started, OS thread id 23
MySQL thread id 14, query id 2553770 localhost 127.0.0.1 nobody
---TRANSACTION 0 202932, not started, OS thread id 22
MySQL thread id 13, query id 2554039 localhost 127.0.0.1 nobody
---TRANSACTION 0 202922, not started, OS thread id 17
MySQL thread id 8, query id 2553766 localhost 127.0.0.1 nobody
---TRANSACTION 0 202916, not started, OS thread id 16
MySQL thread id 7, query id 2553760 localhost 127.0.0.1 nobody
---TRANSACTION 0 202358, ACTIVE 8800 sec, OS thread id 27 fetching rows, thread
declared inside InnoDB 34
mysql tables in use 1, locked 1
485 lock struct(s), heap size 63472, undo log entries 122610
MySQL thread id 18, query id 2553267 localhost 127.0.0.1 nobody updating
delete from modnaptrrecord_zone where modnaptrrecord_zone.modnaptrrecord_id in
(106393 ,106395 ,106396 ,106397 ,106398 ,106399 ,106400 ,106401 ,106402 ,106403
,106404 ,106405 ,106406 ,106407 ,106408 ,106409 ,106410 ,106411 ,106412 ,106413
,106414 ,106415 ,106416 ,106417 ,106418 ,106419 ,106420 ,1064
--------
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
0 OS file reads, 155686 OS file writes, 139613 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.43 writes/s, 0.29 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 2310107, used cells 487064, node heap has 808 buffer(s)
0.07 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 125464410
Log flushed up to 0 125464410
Last checkpoint at 0 125464410
0 pending log writes, 0 pending chkp writes
113542 log i/o's done, 0.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1179586242; in additional pool allocated 13436672
Buffer pool size 64000
Free buffers 57002
Database pages 6187
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 0, created 6187, written 39212
0.00 reads/s, 0.00 creates/s, 0.21 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
Main thread id 8, state: waiting for server activity
Number of rows inserted 601360, updated 200778, deleted 140925, read 6880299
0.00 inserts/s, 0.07 updates/s, 0.00 deletes/s, 20.36 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.10 sec)
mysql>
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
