1

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?

  • Not possible for one database at a time... mysqldump is a simplest way... dump everything and start a fresh with file-per-table ON. What is your total data size and system configuration!! I guess that's required for an estimate. time-to-convert = time-to-dump+time-to-restore+misc an alternate way is: convert everything to myisam -> stop mysql -> move ibdata1 file -> start mysql -> convert everything to innodb. This will allow you to do one database at a time but you know myisam caveats... – mysql_user Nov 25 '15 at 12:44
  • Thanks for the reply, you have given me a rough idea of what is involved. Looks like the cumulative size of the databases is ~19GB. Which configuration options should I be looking at? the Innodb specific ones? – pwnyrainbow Nov 25 '15 at 14:41
  • What's your system config (ram/cpu) ? innodb bufferpool? is this dedicated mysql server? You can perform dump load tests and do the estimates!! – mysql_user Nov 25 '15 at 16:22
  • Plan on a few hours. It's really a question of disk speed. And you need to read and write the data at least twice. – Rick James Dec 14 '15 at 1:22
  • The cumulative size of the Data and indexes is 19GB? So, upwards of 16GB is "free"? How much "free" is shown in SHOW TABLE STATUS? If it is closer to 36G for data+index, then you won't save any disk space by file_per_table. – Rick James Dec 14 '15 at 1:24
0

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 (...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.