List:General Discussion« Previous MessageNext Message »
From:Theodore Petkantchin Date:September 17 2008 12:56am
Subject:RE: optimize table
View as plain text  
Hi Ananda,

1. Long term solution: When you upgrade to MySQL 5.1 partition the table
e.g. by time. This approach was used for 10s of years in the other DBs.

http://dev.mysql.com/tech-resources/articles/performance-partitioning.ht
ml

http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html


2. Short term: There is no point to defragment the table. On the file
system it will be fragmented any way. Just defragment the indexes, which
is faster. May be once a week will be OK.

REPAIR TABLE your_table archive QUICK;


Regards
Theo 


-----Original Message-----
From: Ananda Kumar [mailto:anandkl@stripped] 
Sent: Monday, 15 September 2008 9:45 PM
To: mysql
Subject: optimize table

I have a table which adds on a daily basis around 3 million records.
We have a automated stored proc, which deletes 1 million reocrds.
Now the optimize table command takes loot of time for me to defragment
the
table.
Is there a any way i can improve the speed of optimize table command.

My mysql version is 5.0.41 and have 16GB RM and 8 CPU's

regards
anandkl
Thread
optimize tableAnanda Kumar15 Sep
  • RE: optimize tableTheodore Petkantchin17 Sep
Re: optimize tableAnanda Kumar16 Sep
  • Re: optimize tablechandru16 Sep