Web lists-archives.com

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 <paumarc@xxxxxxxxx>:

> 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.trimurthy@xxxxxxxxx>:
>
>> 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.adarsh@xxxxxxxxx> 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 <paumarc@xxxxxxxxx>
>>> 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.adarsh@xxxxxxxxx>:
>>> >
>>> >> 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 <paumarc@xxxxxxxxx
>>> >
>>> >> 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/
>>> >>>
>>> >>
>>> >
>>>
>>
>