Mysql – Moving to innodb_file_per_table

innodbMySQL

I have just noticed (unfortunately while trying to restore from backup), that on my production server there is a 35GB ibdata1 file!

As I understand I can reduce its size by moving to innodb_file_per_table.
However following the answers I have seen here (link below), you have to dump and drop all databases before you can start the conversion.

How long would it take to do this convert?

Is it possible to stagger the convert and do it one database at a time?

Thanks.

What is the best way to reduce the size of ibdata in mysql?

Best Answer

You already linked rolando's post on how to do the migration. You can change the setting online to make use of per table files, but this will not fix your huge ibdata file size, even though single files are used afterwards.

To discern the time needed, try doing it on another installation you recreate from your backups. That way you will get reasonable numbers (as long as the systems have similar specs and hardware being used) how long this process will take, also you can try it out before laying hands onto the production system.

If you have hard uptime requirements another way to do this online might be through replication. To create a slave, you have create an initial mysqldump with special parameters, but this shouldn't be a problem since I guess you create your backups via dumps anyway?

When setting up the replication, you can let the slave run with innodb_file_per_table even though the master does not. Once all things are set, put the masters' ip onto the slave.

If all this is not possible due to the lack of hardware, then your system with the harsh uptime requirements was maybe sold too cheaply and you now have the ungrateful job to make something work with risks that should have been adressed in the initial planning by getting an adequate test setup besides your production setup.

For getting your mysqldumps to speed up, try piping them directly into parallel bzip, to get the cores to do more work and save time by reducing the needed disk accesses. Rougly like this:

# DUMP
mysqldump ... | pbzip2 -c > MYSQLDUMP.sql
# REPLAY
pbzip2 -dkc MYSQLDUMP.sql | mysql (...)

Related Solutions

Mysql – Restore the data from Snapshot Backup when the data spans over multiple directories

To begin with, storing ibdata on a separate volume from the datadir is a bad idea because of

MetaData Issues : I wrote about this as well
Performance Issues : I wrote about that back in August 2011
- MySQL InnoDB data file directly on a disk slice (fixed-size)?

Getting back to your problem, the reason LVM for ibdata1 is not good has to do with the metadata in ibdata1 at any given point-in-time. If you were to LVM Snapshot ibdata1, then create new tables, and drop some tables, somne of the corresponding .frm files within datadir would not match. The time needed to coalesce that large an ibdata would be astronomical (unless 21 hours is acceptable to you). I tried using rsync as opposed to LVM and I fared no better.

The safest and most sensible way to clear the space with innodb_file_per_table disabled is to do the following on the Slave

Step 01) mysqldump all databases
Step 02) shutdown mysql
Step 03) delete ibdata1
Step 04) startup mysql
Step 05) reload the mysqldump

Without this method, ibdata1 can never be shrunk otherwise. Even in my post in StackOverflow on InnoDB Cleanup (using innodb_file_per_table), you have to do those same 5 steps.

MySQL – Reducing ibdata File Size for MyISAM Tables

I already answered this in StackOverflow : https://stackoverflow.com/a/11636341/491757

If you want to defrag all your MyISAM tables, here is a shell script to do so...

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') "
SQL="${SQL} FROM information_schema.tables "
SQL="${SQL} WHERE engine='MyISAM' AND table_schema NOT IN "
SQL="${SQL} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQL}" > GlobalMyISAMOptmizeTable.sql
less GlobalMyISAMOptmizeTable.sql

Once you trust the script visually, just run it

mysql ${MYSQL_CONN} < GlobalMyISAMOptmizeTable.sql

Give it a Try !!!

Best Answer

Related Solutions

Mysql – Restore the data from Snapshot Backup when the data spans over multiple directories

MySQL – Reducing ibdata File Size for MyISAM Tables

Related Question