Re: deleting big tables
this is my process list ++--+---+--+-++-+--+ | Id | User | Host | db | Command | Time | State | Info | ++--+---+--+-++-+--+ | 37 | pau | localhost | UTR | Killed | 260012 | query end | delete from ensemblmotive| | 58 | pau | localhost | UTR | Query | 81396 | Waiting for table metadata lock | drop index iutr on ensemblmotive | | 59 | pau | localhost | UTR | Query | 45331 | Waiting for table metadata lock | drop table ensemblmotive | | 66 | pau | localhost | UTR | Query | 0 | NULL| show processlist | ++--+---+--+-++-+--+ process with id 37 have been there for a long time, i tried to kill it and drop the table. what can i do? Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ 2015-05-17 7:23 GMT+02:00 Adarsh Sharma eddy.ada...@gmail.com: Hi Pau, Ideally drop table should not take that much time , you have to check if your command is executing or it is in waiting stage. May be you are not able to get lock on that table. Cheers, Adarsh Sharma On Sat, 16 May 2015 at 23:34 Pau Marc Muñoz Torres paum...@gmail.com wrote: Hello every body i have a big table in my sql server and i want to delete it, it also have some indexes. I tried to drop table and delete commands but i eventually get a time out. Wath can i do with it, does it exist any method to delete tables quicly? i know that drop and delete are not equivalent but i want to get rid of all information inside thanks Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/
Re: deleting big tables
i solved the problem by rebooting my computer. i just drop the table in seconds thanks Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ 2015-05-17 12:00 GMT+02:00 Pau Marc Muñoz Torres paum...@gmail.com: this is the innodb output, i tried to kill the process using kil, kill query and kill connection but doesn't worked. what can i do? thanks 150517 11:50:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 3 seconds - BACKGROUND THREAD - srv_master_thread loops: 140779 1_second, 121940 sleeps, 13482 10_second, 11383 background, 7600 flush srv_master_thread log flush and writes: 154479 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 2091707, signal count 9920537 Mutex spin waits 26944439, rounds 111751039, OS waits 966302 RW-shared spins 5087632, rounds 68696066, OS waits 929958 RW-excl spins 2980761, rounds 27893952, OS waits 158867 Spin rounds per wait: 4.15 mutex, 13.50 RW-shared, 9.36 RW-excl TRANSACTIONS Trx id counter 154B1E14 Purge done for trx's n:o 154B1E0A undo n:o 0 History list length 1136 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 67, OS thread handle 0x7f11bc426700, query id 244 localhost pau SHOW ENGINE INNODB STATUS ---TRANSACTION 154B1E00, ACTIVE 265942 sec rollback mysql tables in use 1, locked 1 ROLLING BACK 297751 lock struct(s), heap size 35387832, 74438247 row lock(s), undo log entries 66688203 MySQL thread id 37, OS thread handle 0x7f11bc4b9700, query id 110 localhost pau query end delete from ensemblmotive FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 1 13648332 OS file reads, 34442363 OS file writes, 1064506 OS fsyncs 84.73 reads/s, 16384 avg bytes/read, 49.74 writes/s, 1.75 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 6150, seg size 6152, 5407097 merges merged operations: insert 0, delete mark 206521397, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 101 buffer(s) 370.88 hash searches/s, 150.28 non-hash searches/s --- LOG --- Log sequence number 117269257408 Log flushed up to 117269225038 Last checkpoint at 117268694768 1 pending log writes, 0 pending chkp writes 319455 log i/o's done, 0.75 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 130206 Buffer pool size 8191 Free buffers 0 Database pages 5931 Old database pages 2170 Modified db pages 4679 Pending reads 0 Pending writes: LRU 120, flush list 0, single page 0 Pages made young 22819462, not young 0 89.73 youngs/s, 0.00 non-youngs/s Pages read 13648346, created 340720, written 33498386 84.73 reads/s, 0.00 creates/s, 47.49 writes/s Buffer pool hit rate 929 / 1000, young-making rate 76 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5931, unzip_LRU len: 0 I/O sum[8119]:cur[172], unzip sum[0]:cur[0] -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1173, id 139714143528704, state: flushing log Number of rows inserted 0, updated 0, deleted 74140498, read 74808849 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ 2015-05-17 10:31 GMT+02:00 Pothanaboyina Trimurthy skd.trimur...@gmail.com: Hi Pou, Before killing those connections first check for the undo log entries from the engine innodb status. If there are too many undo log
Re: deleting big tables
this is the innodb output, i tried to kill the process using kil, kill query and kill connection but doesn't worked. what can i do? thanks 150517 11:50:46 INNODB MONITOR OUTPUT = Per second averages calculated from the last 3 seconds - BACKGROUND THREAD - srv_master_thread loops: 140779 1_second, 121940 sleeps, 13482 10_second, 11383 background, 7600 flush srv_master_thread log flush and writes: 154479 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 2091707, signal count 9920537 Mutex spin waits 26944439, rounds 111751039, OS waits 966302 RW-shared spins 5087632, rounds 68696066, OS waits 929958 RW-excl spins 2980761, rounds 27893952, OS waits 158867 Spin rounds per wait: 4.15 mutex, 13.50 RW-shared, 9.36 RW-excl TRANSACTIONS Trx id counter 154B1E14 Purge done for trx's n:o 154B1E0A undo n:o 0 History list length 1136 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 67, OS thread handle 0x7f11bc426700, query id 244 localhost pau SHOW ENGINE INNODB STATUS ---TRANSACTION 154B1E00, ACTIVE 265942 sec rollback mysql tables in use 1, locked 1 ROLLING BACK 297751 lock struct(s), heap size 35387832, 74438247 row lock(s), undo log entries 66688203 MySQL thread id 37, OS thread handle 0x7f11bc4b9700, query id 110 localhost pau query end delete from ensemblmotive FILE I/O I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 1 13648332 OS file reads, 34442363 OS file writes, 1064506 OS fsyncs 84.73 reads/s, 16384 avg bytes/read, 49.74 writes/s, 1.75 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 6150, seg size 6152, 5407097 merges merged operations: insert 0, delete mark 206521397, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276671, node heap has 101 buffer(s) 370.88 hash searches/s, 150.28 non-hash searches/s --- LOG --- Log sequence number 117269257408 Log flushed up to 117269225038 Last checkpoint at 117268694768 1 pending log writes, 0 pending chkp writes 319455 log i/o's done, 0.75 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 130206 Buffer pool size 8191 Free buffers 0 Database pages 5931 Old database pages 2170 Modified db pages 4679 Pending reads 0 Pending writes: LRU 120, flush list 0, single page 0 Pages made young 22819462, not young 0 89.73 youngs/s, 0.00 non-youngs/s Pages read 13648346, created 340720, written 33498386 84.73 reads/s, 0.00 creates/s, 47.49 writes/s Buffer pool hit rate 929 / 1000, young-making rate 76 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 5931, unzip_LRU len: 0 I/O sum[8119]:cur[172], unzip sum[0]:cur[0] -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 1173, id 139714143528704, state: flushing log Number of rows inserted 0, updated 0, deleted 74140498, read 74808849 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/ 2015-05-17 10:31 GMT+02:00 Pothanaboyina Trimurthy skd.trimur...@gmail.com : Hi Pou, Before killing those connections first check for the undo log entries from the engine innodb status. If there are too many undo log entries it will take some time to clean up those entries. If you force fully kill those connections there are more chances to crash the DB instance. On 17 May 2015 1:54 pm, Adarsh Sharma eddy.ada...@gmail.com wrote: Hi Pou, This is the reason why your drop commands taking too much time because they are in waiting state.Even it is quite surprising to me the purpose of the delete command. I would say
deleting big tables
Hello every body i have a big table in my sql server and i want to delete it, it also have some indexes. I tried to drop table and delete commands but i eventually get a time out. Wath can i do with it, does it exist any method to delete tables quicly? i know that drop and delete are not equivalent but i want to get rid of all information inside thanks Pau Marc Muñoz Torres skype: pau_marc http://www.linkedin.com/in/paumarc http://www.researchgate.net/profile/Pau_Marc_Torres3/info/
