Mani's Blog

September 6, 2011

MySQL: Defragmenting a Table

Filed under: MySQL — mani @ 11:28 pm

We have many sharded databases in MySQL.  The nature of the application updates, deletes records.  Realized that we may have lot of fragmentation on tables and wanted to remove it.  This is has been done on Master Slave environment.  We were able to reclaim good amount of storage from bigger fragmented tables.  It is performed by NULL alter table operation.  BE AWARE that this process would rebuild the objects, so cannot have DML running on the table during defragmentation.

First do it on Slave, then failover the service to slave  and then do it on Master.

— Set timeout

set wait_timeout = 28800;
set interactive_timeout = 28800;

select @@interactive_timeout;
select @@wait_timeout;
— stop the slave

stop slave;
show slave status\G

set sql_log_bin = 0;

ALTER TABLE _topic ENGINE=INNODB ;
Query OK, 882094 rows affected (11 min 48.53 sec)
Records: 882094  Duplicates: 0  Warnings: 0

Once you are done.  Start slave to catchup.

Failover the service to slave and do the same on Master.

We are able to reclaim 50GB storage per cluster(including master and slave).   Anyway it depends on environment and its characteristics.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: