Re: deleting big tables
The problem was here: ---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 Your first query was trying to delete rows from the table and probably you killed it. The rollback process has started and then, you weren't able to acquire a lock on that table until the end of rollback process. As you rebooted the server, all the locks were released and ... luck you not having your data corrupted. -- *Wagner Bianchi* 2015-05-17 13:06 GMT-03:00 Suresh Kuna : > Your drop index table needs the table rebuild and locked the drop table. In > this case, you have to drop table rather than drop indexes or deleting > data. > > On Sun, May 17, 2015 at 7:11 AM, Pau Marc Muñoz Torres > wrote: > > > 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 : > > > > > 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 1302
Re: deleting big tables
Your drop index table needs the table rebuild and locked the drop table. In this case, you have to drop table rather than drop indexes or deleting data. On Sun, May 17, 2015 at 7:11 AM, Pau Marc Muñoz Torres wrote: > 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 : > > > 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, delete
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 : > 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 conne
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 : > 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" 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 ,kill all pids ( 37,5
To:, CC: & mailing lists (was: Re: deleting big tables)
On Sun, May 17, 2015 at 02:01:57PM +0530, Pothanaboyina Trimurthy wrote: Guys, can I implore you to post to a mailing list using its address in To: field and not CC:ing it? You are constantly breaking out of my filters. -- vag·a·bond adjective \ˈva-gə-ˌbänd\ a : of, relating to, or characteristic of a wanderer b : leading an unsettled, irresponsible, or disreputable life -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: deleting big tables
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" 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 ,kill all pids ( 37,58,59,66 ) and just drop > the table ( it will delete everything ). Please take a backup if needed. > > mysql > drop table ensemblmotive ; > > Thanks, > Adarsh > > > > > On Sun, 17 May 2015 at 13:44 Pau Marc Muñoz Torres > wrote: > > > 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 : > > > >> 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 > >> 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
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 ,kill all pids ( 37,58,59,66 ) and just drop the table ( it will delete everything ). Please take a backup if needed. mysql > drop table ensemblmotive ; Thanks, Adarsh On Sun, 17 May 2015 at 13:44 Pau Marc Muñoz Torres wrote: > 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 : > >> 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 >> 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
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 : > 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 > 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
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 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
Hi Pau, Would you please paste the timeout error ? If you want to get rid of a table then the recommendation is to drop the table in non-peak hours. Thanks Suresh Kuna On Sat, May 16, 2015 at 2:00 PM, Pau Marc Muñoz Torres 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/ > -- -- -- Thanks Suresh Kuna
