krishna chandra prajapati wrote:
Hi Sebastain,
The data size is around 10 GB. Three months data will be around 600 to 700
MB. If i have deleted the data then how i will reclaim the free space as
innodb storage engine is being used. Data deletion will take a lot of time.
In addition to this i have to perform other task also.
My task is to keep 3 monts data on the production server. archive the whole
data and move it other computer. Reclaim the free space also.
You mentioned many of the issues I wrote about here:
http://www.xaprb.com/blog/2006/05/02/how-to-write-efficient-archiving-and-purging-jobs-in-sql/
http://www.xaprb.com/blog/2007/06/13/archive-strategies-for-oltp-servers-part-1/
http://www.xaprb.com/blog/2007/06/14/archive-strategies-for-oltp-servers-part-2/
http://www.xaprb.com/blog/2007/06/15/archive-strategies-for-oltp-servers-part-3/
Finally, MySQL Archiver is a MySQL implementation of what I've learned
about archiving data over the last several years. You might want to
read the articles first to understand how it works. You can use it to
efficiently move the data from one server to another without impacting
the production server very much at all. It also has a plugin mechanism
for "performing other tasks also" before, during, or after archiving.
http://mysqltoolkit.sourceforge.net/
As far as reclaiming InnoDB free space, this is an issue I've had to
deal with as well. I suggest you archive the data first and don't worry
about the 10GB yet. When you get your data as small as desired, you
need to dump your database, resize your InnoDB tablespace and reload.
You may want to do this on another machine for minimal downtime --
replicate to another machine, resize, then swap it with your current master.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
