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:prajapat...@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 mdyk...@gmail.com 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 vegiv...@tuxera.be 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 - mdyk...@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=arch...@jab.org
Re: DROP TABLE TOOK 39MIN
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.par...@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:prajapat...@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 mdyk...@gmail.com 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 vegiv...@tuxera.be 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 - mdyk...@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=vegiv...@tuxera.be
Re: DROP TABLE TOOK 39MIN
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.
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 vegiv...@tuxera.be 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 - mdyk...@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=arch...@jab.org
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 mdyk...@gmail.com 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 vegiv...@tuxera.be 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 - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon.
DROP TABLE TOOK 39MIN
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
