Web lists-archives.com

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