Hi Krishna, Drop partition should be very quick - much faster than doing a DELETE on the same amount of data. Internally, it will be the same as doing a drop table for that partition.
Regards, Gavin Towey From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com] Sent: Thursday, November 19, 2009 1:15 AM To: Gavin Towey Cc: MySQL Subject: Re: DELETE DATA FROM TABLE Hi Gavin, I am using innodb with file-per-table. I agree with you dropping a partition will reclaim disk space. alter table <table name> drop partition <partition name> But, my concern is "alter table <table name> drop partition <partition name>" on very big table would might take a lot of time. (Although, I haven't tested) Thanks for the immediate response. Thanks & Regard, Krishna Ch. Prajapati On Thu, Nov 19, 2009 at 2:22 PM, Gavin Towey <gto...@ffn.com<mailto:gto...@ffn.com>> wrote: Assuming you're using either myisam tables, or innodb with file-per-table option turned on, then dropping a whole partition at a time will allow you to reclaim disk space. If you're using innodb with a single tablespace currently, then unfortunately, you would have to export all your data, shutdown mysql, change you're my.cnf & delete the tablespace & ib_log files, then restart and re-import all your data. If you need to do this, you should probably seek a bit more information about from this list or other sources. Regards, Gavin Towey -----Original Message----- From: Krishna Chandra Prajapati [mailto:prajapat...@gmail.com<mailto:prajapat...@gmail.com>] Sent: Thursday, November 19, 2009 12:13 AM To: MySQL Subject: DELETE DATA FROM TABLE Hi Experts, I have a crm table where 12 millions records inserted/day. We are running report queries on this table and using partitioning features for faster results. we have to maintain 45 days data means 540million records. As per my calculation 540 records will use 1.8 TB of disk space. Total disk space available is 2.3TB. Deleting data doesn't free up the disk space. So, I was thinking of rotating the table. But doesn't have enough disk space. Any Idea, how this task can be performed. Any idea or suggestion is highly appreciated. Thanks & Regards, Krishna Ch. Prajapati The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. ________________________________ The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
