Reducing size of MySQL ibdata1 file

Re: Reducing size of MySQL ibdata1 file

by Taylor Judd -
Number of replies: 0

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.