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 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 ,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 <paum...@gmail.com> >>> 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 <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/ >>> >>> >>> >> >>> > >>> >> >
