Quote Originally Posted by pi0tr3k View Post


I'm worried about OPTIMIZE TABLE query, as I'm sure my mysql server will die.
I have tables with 4MILL records, ~12GB ..

How can I optimize such a big table, without any problems.?
Increase the size of your innodb buffer pool. You've got quite a bit of free RAM and you could possibly up that size by another 1GB or so. Optimize your tables after that.

Use the nice command as Whoo suggested (or renice the pid after), and take your site down while the optimize is running. Depending on your server and drive, it may take some time.

I suggested taking the site offline because you don't want queries hitting your database all the while you're optimizing the tables which would just build up a queue because they'll wait for locked tables to open before they can actually complete; you'll end up building a backlog of hundreds of queries that will kill the MySQL server.