Gheek.net

April 15, 2011

How to properly setup Innodb in MySQL to shrink when optimized.

Filed under: mysql — lancevermilion @ 4:22 pm

Taken from: http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine

There is a more complete answer with regard to InnoDB

Keep in mind the busiest file in the InnoDB infrastructure

/var/lib/mysql/ibdata1

This file normally houses four types of information

1) Table Data

2) Table Indexes

3) MVCC (Multiversioning Concurrency Control) Data

4) Table Metadata

Many people create multiple ibdata files hoping for better diskspace management and performance. SORRY, NOT HAPPENING !!!

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored ibdata1 does two things: 1) Makes the table’s data and indexes contiguous inside ibdata1 2) It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segragate Table Data and Table Indexes from ibdata1 and manage them independently. To shrink ibdata1 once and for all you must do the following

1) MySQLDump all databases into a SQL text file (call it SQLData.sql)

2) Drop all databases (except mysql schema)

3) Shutdown mysql

4) Add the following lines to /etc/my.cnf

innodb_file_per_table

innodb_flush_method=O_DIRECT

innodb_log_file_size=1G

innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

7) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header) mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many time in my career as a MYSQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

http://www.linkedin.com/in/rolandoedwards

www . logicworks . net

About these ads

1 Comment »

  1. Thank you Lance, it worked great for me! And was it’s on the first page of google I think it helped out many more!

    Comment by nikecow — February 5, 2013 @ 8:46 am


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

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: