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
