Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb

Moving the undo log files outside seemed logical but this procedure seems rather complicated:

http://dev.mysql.com/doc/refman/5.6/en/tablespace-splitting-undo.html

Does anyone have hands-on experience accomplishing this on a live server?

This line makes it sound like it is impossible once mysql is already installed and running:

this feature can only be enabled when initializing a MySQL instance

So too late now?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

While @ohlin's answer has a decent workaround, it still requires setting up an entire MySQL instance with the undo logs configured the way you want. Then, you have to failover the application to the new MySQL Instance. That still introduces a few minutes of logical downtime from the perspective of the application because of changing the IP addresses in the code or changing DNS.

Your Original Question

Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

The answer is definitely NO. Why ? Given the following diagram

InnoDB Architecture

Look at the following options for the location of the undo logs

OPTION #1 : Undo Logs Inside System Tablespace

By default, the undo logs reside in the system tablespace file known as ibdata1. Undo logs has been the cause of the greatest amount of uncontrolled growth in the face of many transactions (See my post How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?)

To relocate ibdata1, you must configure innodb_data_home_dir and innodb_data_file_path. They are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs buried inside ibdata1 cannot be moved for a live MySQL instance.

OPTION #2 : Undo Logs Outside System Tablespace

There are options to configure the undo logs on different disks:

  • innodb_undo_logs : Although you can increase or decrease how many rollback segments are used within a transaction, the number of rollback segments physically present in the system never decreases. Thus you might start with a low value for this parameter and gradually increase it, to avoid allocating rollback segments that are not needed later (MySQL Documentation)
  • innodb_undo_tablespaces
  • innodb_undo_directory

Both innodb_undo_tablespaces and innodb_undo_directory are global variables, but they are not dynamic. Since a mysql restart would be required to change these settings, the undo logs outside ibdata1 cannot be moved for a live MySQL instance.

EPILOGUE

When I read this question, it reminded me of a blog from a FaceBook DB Engineer that configured InnoDB on different disks.

I referred to his blog in my post How do I determine how much data is being written per day through insert, update and delete operations?

Even with this idea, it requires configuring the undo logs in a different disk location from the very beginning. Doing this after mysql is running is simply not possible.

share|improve this answer

@RolandoMySQLDBA has a good article on how to Clean a MySQL INNODB storage engine. Basically, what he says is that it's not that easy on a live server.

However, as I was reading about innodb-undo-tablespace I noticed that they mentioned the option of setting up a new server with the desired settings and then link it to the live server as a slave.

(7) Deploy a new production instance using the ideal settings for these options. Set it up as a slave server in a replication configuration, or transfer data from an earlier production instance.

The replication will then make sure your new server has all the data. You should be able to set it all up this way and then stop your system for a few minutes while you change so the slave (with the desired settings) becomes the master. This can be a temporary solution and you'll be given opportunity to make needed changes to the first server's settings.

I haven't tried this myself. I usually go with the old school method of dumping the database and then installing it with the new correct settings. My suggestion above is a bit tricky but if you have no other option it might be worth trying.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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