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 <[EMAIL PROTECTED]> 11/03/2005 12:11 AM To [EMAIL PROTECTED], mysql@lists.mysql.com cc Subject Re: optimize table on live database At 14:08 -0500 11/2/05, [EMAIL PROTECTED] 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/[EMAIL PROTECTED]
