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  
Paul,

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,
phiLLip



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

To
phillip@stripped, mysql@stripped
cc

Subject
Re: optimize table on live database






At 14:08 -0500 11/2/05, phillip@stripped wrote:
>Hello,
>
>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 
I'm
>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 
not
>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
>original
>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 AB, www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
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