DROP TABLE TOOK 39MIN

--00504502b08c5e1b690477ea7cc3
Content-Type: text/plain; charset=ISO-8859-1

Hi Experts,

I have a crm(customer resource management) table which contains 654 million
records. Dropping table took 39min. In addition to this other queries become
very slow and they are not associated with bkp_mtlog any way. why?

mysql> show table status like 'bkp_mtlog';
+-----------+--------+---------+------------+-----------+--- -------------+--------------+-----------------+------------- -+-------------+----------------+-------------+------------- +------------+-------------------+----------+--------------- -+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment
| Create_time | Update_time | Check_time | Collation | Checksum |
Create_options | Comment |
+-----------+--------+---------+------------+-----------+--- -------------+--------------+-----------------+------------- -+-------------+----------------+-------------+------------- +------------+-------------------+----------+--------------- -+---------+
| bkp_mtlog | InnoDB | 10 | Compact | 654135647 | 289 |
189507928064 | 0 | 549887164416 | 58322845696 | NULL
| NULL | NULL | NULL | latin1_swedish_ci | NULL |
partitioned | |
+-----------+--------+---------+------------+-----------+--- -------------+--------------+-----------------+------------- -+-------------+----------------+-------------+------------- +------------+-------------------+----------+--------------- -+---------+
1 row in set (2 min 11.29 sec)

mysql> drop table bkp_mtlog;
Query OK, 0 rows affected (39 min 7.39 sec)

Thanks,
Krishna

--00504502b08c5e1b690477ea7cc3--

Re: DROP TABLE TOOK 39MIN

--0016e6d78553574ccd0477ec2af8
Content-Type: text/plain; charset=ISO-8859-1

Presumably because you are removing 189 gigabyte of data and 549 gigabyte of
indexes, all of which need to be marked as deleted in your innodb file. I/O
is rather expensive :-)

On MyISAM this would have been close to instantaneous (as you probably
expected), because the datafile is used only for that table, so all that's
needed is three filesystem delete operations.

--0016e6d78553574ccd0477ec2af8--

Re: DROP TABLE TOOK 39MIN

Under InnoDb, you could use file-per-table which would have
significantly reduced the inter-dependencies.. given the large data
size and heavy I/O you report, it might be a wise way to go.

- michael dykman


On Mon, Nov 9, 2009 at 3:41 AM, Johan De Meersman wrote:
> Presumably because you are removing 189 gigabyte of data and 549 gigabyte of
> indexes, all of which need to be marked as deleted in your innodb file. I/O
> is rather expensive :-)
>
> On MyISAM this would have been close to instantaneous (as you probably
> expected), because the datafile is used only for that table, so all that's
> needed is three filesystem delete operations.
>



--
- michael dykman
- mdykman@gmail.com

"May you live every day of your life."
Jonathan Swift

Larry's First Law of Language Redesign: Everyone wants the colon.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: DROP TABLE TOOK 39MIN

--00504502b357e263940477f46e9d
Content-Type: text/plain; charset=ISO-8859-1

Hi Michael,

Already using innodb_file_per_table.

Krishna

On Mon, Nov 9, 2009 at 9:39 PM, Michael Dykman wrote:

> Under InnoDb, you could use file-per-table which would have
> significantly reduced the inter-dependencies.. given the large data
> size and heavy I/O you report, it might be a wise way to go.
>
> - michael dykman
>
>
> On Mon, Nov 9, 2009 at 3:41 AM, Johan De Meersman
> wrote:
> > Presumably because you are removing 189 gigabyte of data and 549 gigabyte
> of
> > indexes, all of which need to be marked as deleted in your innodb file.
> I/O
> > is rather expensive :-)
> >
> > On MyISAM this would have been close to instantaneous (as you probably
> > expected), because the datafile is used only for that table, so all
> that's
> > needed is three filesystem delete operations.
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
> Larry's First Law of Language Redesign: Everyone wants the colon.
>

--00504502b357e263940477f46e9d--

RE: DROP TABLE TOOK 39MIN

But if you restart your mysql and then drop the table, It will take only
2 min to drop the table.

Thanks,
Dilipkumar



-----Original Message-----
From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]=20
Sent: Tuesday, November 10, 2009 12:04 AM
To: Michael Dykman
Cc: MySQL
Subject: Re: DROP TABLE TOOK 39MIN

Hi Michael,

Already using innodb_file_per_table.

Krishna

On Mon, Nov 9, 2009 at 9:39 PM, Michael Dykman
wrote:

> Under InnoDb, you could use file-per-table which would have
> significantly reduced the inter-dependencies.. given the large data
> size and heavy I/O you report, it might be a wise way to go.
>
> - michael dykman
>
>
> On Mon, Nov 9, 2009 at 3:41 AM, Johan De Meersman
> wrote:
> > Presumably because you are removing 189 gigabyte of data and 549
gigabyte
> of
> > indexes, all of which need to be marked as deleted in your innodb
file.
> I/O
> > is rather expensive :-)
> >
> > On MyISAM this would have been close to instantaneous (as you
probably
> > expected), because the datafile is used only for that table, so all
> that's
> > needed is three filesystem delete operations.
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> "May you live every day of your life."
> Jonathan Swift
>
> Larry's First Law of Language Redesign: Everyone wants the colon.
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: DROP TABLE TOOK 39MIN

--0016e6d77fa035bdb70478066f42
Content-Type: text/plain; charset=ISO-8859-1

Restarting isn't an option in most production environments, but I wonder why
you say that it'd take far less time after a restart ?

On Tue, Nov 10, 2009 at 4:47 PM, Parikh, Dilip Kumar <
dilipkumar.parikh@eds.com> wrote:

> But if you restart your mysql and then drop the table, It will take only
> 2 min to drop the table.
>
> Thanks,
> Dilipkumar
>
>
>
> -----Original Message-----
> From: Krishna Chandra Prajapati [mailto:prajapatikc@gmail.com]
> Sent: Tuesday, November 10, 2009 12:04 AM
> To: Michael Dykman
> Cc: MySQL
> Subject: Re: DROP TABLE TOOK 39MIN
>
> Hi Michael,
>
> Already using innodb_file_per_table.
>
> Krishna
>
> On Mon, Nov 9, 2009 at 9:39 PM, Michael Dykman
> wrote:
>
> > Under InnoDb, you could use file-per-table which would have
> > significantly reduced the inter-dependencies.. given the large data
> > size and heavy I/O you report, it might be a wise way to go.
> >
> > - michael dykman
> >
> >
> > On Mon, Nov 9, 2009 at 3:41 AM, Johan De Meersman
> > wrote:
> > > Presumably because you are removing 189 gigabyte of data and 549
> gigabyte
> > of
> > > indexes, all of which need to be marked as deleted in your innodb
> file.
> > I/O
> > > is rather expensive :-)
> > >
> > > On MyISAM this would have been close to instantaneous (as you
> probably
> > > expected), because the datafile is used only for that table, so all
> > that's
> > > needed is three filesystem delete operations.
> > >
> >
> >
> >
> > --
> > - michael dykman
> > - mdykman@gmail.com
> >
> > "May you live every day of your life."
> > Jonathan Swift
> >
> > Larry's First Law of Language Redesign: Everyone wants the colon.
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>

--0016e6d77fa035bdb70478066f42--