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:prajapatikc@stripped]
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
<gtowey@stripped<mailto:gtowey@stripped>> 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:prajapatikc@stripped<mailto:prajapatikc@stripped>]
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.
