List:General Discussion« Previous MessageNext Message »
From:phillip Date:November 3 2005 2:41pm
Subject:Re: optimize table on live database
View as plain text  

Thanks for your reply! I have tried using optimize while the table was 
"live". However, in the two instances that I have attempted, it generated 
an error, and the table could no longer be read. I had to delete the 
database and rebuild the table. Can table size be an issue here? I don't 
remember the particular tablesize  at that time, but it could easily get 
to be around 1 gig or more.

Any suggestions?

Your friendly neighborhood SA,

Paul DuBois <paul@stripped> 
11/03/2005 12:11 AM

phillip@stripped, mysql@stripped

Re: optimize table on live database

At 14:08 -0500 11/2/05, phillip@stripped wrote:
>I've been looking for information related to best practice on how to
>OPTIMIZE TABLE <table name> with out taking the database offline. I
>understand that it is not good to run an optimize while the database is
>being used. So what is a good way of handling this?
>In my particular application, there are constant inserts going on, and 
>doing a delete on older records, and then optimize the table. It's
>important to not lose any of those inserts, however I realize that may 
>be possible, so it is acceptable to loose some.
>The only methods I've thought of are:
>1. revoke the user's insert permission, then optimize the table, and then
>grant the permission's back.
>2. Rename the table, then optimize the table, and rename it back to the
>3. stop mysql, use myisamchk to optimize the table, then start mysql
>Is there a better way?

You don't have to do any of that.  OPTIMIZE TABLE will block other
clients from modifying the table while it runs.

Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA

MySQL General Mailing List
For list archives:
To unsubscribe:

optimize table on live databasephillip2 Nov
Re: optimize table on live databasePaul DuBois3 Nov
  • Re: optimize table on live databasephillip3 Nov
Re: optimize table on live databasePaul DuBois3 Nov