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.
Your friendly neighborhood SA,
Paul DuBois <paul@stripped>
11/03/2005 12:11 AM
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 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