DELETE DATA FROM TABLE

--005045017f2cae797a0478b4ec08
Content-Type: text/plain; charset=ISO-8859-1

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

--005045017f2cae797a0478b4ec08--

Re: DELETE DATA FROM TABLE

Krishna Chandra Prajapati wrote:
> 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.
>
The space is freed inside the table space but is not seen on disk. Use
"show table status" to show a tables "data_free" variable.
If you prompt want to free the space so you can see it on the file
system you can use "optimize table" command. But the operation can be
slow and the table will be locked.



--
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: DELETE DATA FROM TABLE

Assuming you're using either myisam tables, or innodb with file-per-table o=
ption turned on, then dropping a whole partition at a time will allow you t=
o reclaim disk space.

If you're using innodb with a single tablespace currently, then unfortunate=
ly, you would have to export all your data, shutdown mysql, change you're m=
y.cnf & delete the tablespace & ib_log files, then restart and re-import al=
l your data. If you need to do this, you should probably seek a bit more i=
nformation about from this list or other sources.

Regards,
Gavin Towey

-----Original Message-----
From: Krishna Chandra Prajapati [mailto:prajapatikc@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 rotatin=
g
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 c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.

--
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: DELETE DATA FROM TABLE

--000e0cd32f5467a0960478b5cce0
Content-Type: text/plain; charset=ISO-8859-1

Hi Gavin,

I am using innodb with file-per-table. I agree with you dropping a partition
will reclaim disk space.
alter table