CODEWITHSUNDEEP
mysqldatabasedatabase-administration
Rams
Rams

Reputation: 2189

Optimize MySql 5.7 table without downtime

I have a table in MySql 5.7 server. We store around 1M rows every day. After each row insertion, we do some processing on that row after that we delete that row.

Table disk space is rapidly increasing because of this huge number of write operations. So I want to do OPTIMIZE the Table.

So if I apply OPTIMIZE Table command on production table does it give any downtime.

What is the best way to do OPTIMIZE table?

MySql Server details

Server version: 5.7

Engine: InnoDB

Hosting: Google Cloud Sql

Thanks

Upvotes: 2

Views: 6211

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562398

Yes, you're right, OPTIMIZE TABLE will perform the work in place as of MySQL 5.6.17 or later. We find this note in the documentation on which operations support online DDL here: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

You can experiment to test this (I did after your comment):

  1. Create a table and fill it with a few hundred thousand rows of random data.
  2. Run OPTIMIZE TABLE, because a table of that size will take 20-30 seconds or more.
  3. While that is running, open a second window and try SELECT, INSERT, UPDATE, or DELETE against the table. You will find it does not block DML.

If you do have an ALTER TABLE that cannot work as online DDL, there's a good free tool pt-online-schema-change that help to avoid locking behavior even for table-restructure operations. This tool does NOT lock the table, but it creates triggers so any changes are captured and applied to the new copy table while it's in progress of copying all the rows.

Upvotes: 1

Related Questions