Web lists-archives.com

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.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/
>> >>>
>> >>
>> >
>>
>