Reducing size of MySQL ibdata1 file

Reducing size of MySQL ibdata1 file

by John Wells -
Number of replies: 1

First, my database machine is a Win2k3 server with 3GB of RAM, running MySQL 5.  An export of our production database generates a .sql file of approximately 2.3GB.

 

I am in the process of upgrading this server to 2.0.  In so doing, I want to change some settings in my.ini so that the ibdata1 file doesn't grow so large.  Currently it is almost 6GB on a Windows server 2003 machine.

I am of two minds about this and have two procedures outlined.  I would appreciate any advice you may offer in this regard.

 

Option 1:

Step 0: Stop mysqld and backup the existing /data/ directory.  Restart mysqld.

Step 1: Backup the existing databases (information_schema, moodle, moodle2 (our test site) and mysql to individual .sql files.

Step 2: Drop the moodle and moodle2 databases.

Step 3: Stop mysqld.

Step 4: Delete the entire /data/ directory except the /data/mysql/ folder.

Step 5. Restart mysqld and re-create moodle, moodle2, and information_schema (if necessary) by importing the .sql backups.

Step 6. Repeat as needed periodically when the ibdata file grows too large.

 

Option 2: (Based on http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine)

Step 0: Stop mysqld and backup the /data/ directory.  Restart mysqld.

Step 1: Backup the existing databases (information_schema, moodle, moodle2 (our test site) and mysql to individual .sql files.

Step 2: Drop the moodle and moodle2 databases.

Step 3: Stop mysqld.

Step 4: Delete the entire /data/ directory except the /data/mysql/ folder.

Step 5: Per stackoverflow, add the following to my.ini:

innodb_file_per_table

innodb_flush_method=O_DIRECT

innodb_log_file_size=25M

innodb_buffer_pool_size=225M

Step 6: Restart mysqld, recreate moodle, and  moodle2 using the .sql backups.

 

Option 1 seems safer but still leaves me stuck with the possibility of an enourmous ibdata1 file.  Option 2 may well solve my problem, but I am totally unfamiliar with these settings changes....

 

Again, any advice in this matter is greatly appreciated.

 

Thanks for your time,

jw

Average of ratings: -
In reply to John Wells

Re: Reducing size of MySQL ibdata1 file

by Taylor Judd -

Hi jw,

The only setting there to my knowledge that specifically affects reclamation of disk space is innodb_file_per_table. This setting makes INNODB separate the single ibd file into multiple files per table. This means when an individual table is truncated there is an option to reclaim disk space. However since the data structures are stored separately there is some overall loss of performance and the total data footprint increases slightly.

We used this setting last year on moodle and it did allow us to reclaim some space. But as a policy we create a new database every year from scratch. Thus long-term data growth is handled separately. In my experience overall moodle does not prune much from the database so I'm not sure what reclamation you'd be able to do. Even when data is pruned while INNODB won't give the space back, it will reuse that space for new structures.

Just for review your other settings:

innodb_buffer_pool_size=225M

This is RAM used by the INNODB engine. Set this number as high as possible given system RAM.

 

innodb_flush_method=O_DIRECT

innodb_log_file_size=25M

Both of these settings effect how and how often data is flushed to disk. I'm not as knowledgeable of these settings but their main effect will be on your disk I/O not on final storage size.

Average of ratings: -