innodb_file_per_table - Converting to Per Table Data for InnoDB

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Issue with shared InnoDB /var/lib/mysql/ibdata1 storage
InnoDB tables currently store data and indexes into a shared tablespace (/var/lib/mysql/ibdata1). Due to the shared tablespace, data corruption for one InnoDB table can result in MySQL failing to start up on the entire machine. Repairing InnoDB corruption can be extremely difficult to perform and can result in data loss for tables that were not corrupted originally during that repair process.

Since MySQL 5.5 will be using InnoDB as the default storage engine, it is important to consider the consequences of continuing to utilize the shared tablespace in /var/lib/mysql/ibdata1

Changing to per-table tablespace with innodb_file_per_table

As an option to resolve the issue, MySQL has a configuration variable called innodb_file per_table. To use this variable, the following could be placed into /etc/my.cnf to convert InnoDB to a per table file for each InnoDB engine table:

Code:
innodb_file_per_table=1
After adding the line, MySQL would need to be restarted on the machine.

The result for using that line in /etc/my.cnf would cause any databases after the line is added to create .idb files in /var/lib/mysql/database/ location. Please note that the shared tablespace will still hold internal data dictionary and undo logs.

Converting old InnoDB tables

Any old databases with InnoDB tables set to previously share the tablespace in ibdata1 will still be using that file, so those old databases would need to be switched to the new system. The following command in MySQL CLI would create a list of InnoDB engine tables and a command to run for each to convert them to the new innodb_file_per_table system:

Code:
select concat('alter table ',TABLE_SCHEMA ,'.',table_name,' ENGINE=InnoDB;') FROM INFORMATION_SCHEMA.tables where table_type='BASE TABLE' and engine = 'InnoDB';
An example for Roundcube on my test machine shows the following return upon running the prior command:

Code:
alter table roundcube.cache ENGINE=InnoDB;
alter table roundcube.contacts ENGINE=InnoDB;
alter table roundcube.identities ENGINE=InnoDB;
alter table roundcube.messages ENGINE=InnoDB;
alter table roundcube.session ENGINE=InnoDB;
alter table roundcube.users ENGINE=InnoDB;
You would then simply need to issue the commands noted by MySQL CLI to then covert each table to the new innodb_file_per_table format.

Please note that these commands would only need to be run in MySQL command line for the conversion, which can be reached in root SSH by typing mysql at the bash prompt.

Possible Issues for Converting Old InnoDB Tables

1. Possible system load might occur during the conversion
2. Possible issues with drive space filling up for the conversion
 

brianoz

Well-Known Member
Mar 13, 2004
1,146
7
168
Melbourne, Australia
cPanel Access Level
Root Administrator
Tristan,

Reading elsewhere implies that, although this would convert the data in ibdata1 into individual files, one per table, it would not remove reliance on the existing ibdata1 file.

That is, despite this breaking out the tables into individual files, you still would not be able to remove or truncate your (probably now inconveniently massive) ibdata1 file.

Can you confirm or deny this rumour?

It would probably be nice if cpanel provided a /scripts command to fix this issue - it's bordering on being a bug that cpanel allowed this in the first place as one massive ibdata1 file just doesn't work in the shared hosting context!

Cheers and thanks ...
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hi brianoz,

My guide already touches on those points. You are correct that the ibdata1 file cannot be removed, since:

Please note that the shared tablespace will still hold internal data dictionary and undo logs.
Additionally, as I noted:

Any old databases with InnoDB tables set to previously share the tablespace in ibdata1 will still be using that file, so those old databases would need to be switched to the new system.
Once you switch them, I'm not certain if the ibdata1 file still contains that data or not, but it shouldn't, since it wouldn't make sense for it to contain data moved to .ibd files. You will never get rid of the ibdata1 file when it has the internal data dictionary and undo logs, though.

Also, there's a feature request in for adding innodb_file_per_table as the cPanel default setting for /etc/my.cnf on cPanel installation. I made that feature request in our bug tracking system several months ago and it is under considerations.
 

VeZoZ

Well-Known Member
Dec 14, 2002
245
0
166
cPanel Access Level
DataCenter Provider
Once you switch them, I'm not certain if the ibdata1 file still contains that data or not, but it shouldn't, since it wouldn't make sense for it to contain data moved to .ibd files. You will never get rid of the ibdata1 file when it has the internal data dictionary and undo logs, though.
I do not believe the ibdata1 file ever shrinks in size. You could have a 2gb file and remove 1gb of innodb data and it will not shrink in size. It will use that space for future data that is added.

The only way to shrink it would be to dump all your innodb data using mysqldump. Drop all innodb tables currently on the system. Remove the ibdata1 file from the system. Add your innodb_file_per_table option to my.cnf. Restart mySQL and re-import all innodb tables.

If a system is already running without innodb_file_per_table and the ibdata file is already large on your system to not be switching. The reason is as data is removed this file will remain the same size. So you'll end up with wasted space over time as less of your tables use the ibdata1 file. If you convert all the tables you'll end up with a 2gb ibdata1 file or however large it was previously. I'm not sure if you even then can remove the ibdata1 file I bet you cannot without issues.

I would just use innodb_file_per_table moving forward on newer hardware or recently deployed where the amount of disk on the ibdata1 file is minimal so having an extra 200mb used or whatever it may be is not a big deal.

This is what we've been doing as our strategy. Eventually legacy hardware gets replaced which we'll do a cpanel account transfer to new hardware with innodb_file_per_table being used.

There is a bug about this in MySQL about the lack of purging. It's been a feature request for many years now. If they solve that our strategy might change but for now seems the best way to approach it.
 

brianoz

Well-Known Member
Mar 13, 2004
1,146
7
168
Melbourne, Australia
cPanel Access Level
Root Administrator
Tristan, sorry for missing you'd mentioned that the ibdata1 file doesn't shrink.

VezOz, sounds like a good policy (of using migration over time to shrink ibdata1), if you can wait.

We're looking at following the strategy in this link:

Building a WordPress site | White Dog Green Frog
 

nocbr.com

Member
May 1, 2006
22
0
151
Hi brianoz,
Also, there's a feature request in for adding innodb_file_per_table as the cPanel default setting for /etc/my.cnf on cPanel installation. I made that feature request in our bug tracking system several months ago and it is under considerations.
Any news about this feature request ?
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Any news about this feature request ?
There is no current estimate or update on the feature request, which is internal case #41802.
 

jols

Well-Known Member
Mar 13, 2004
1,107
3
168
Just a question so that it is perfectly clear. Here are the assumptions I am making from reading this thread. Please, someone at cPanel.net, please verify that my assumptions are correct:

Assumption #1 - Adding "innodb_file_per_table=1" to "/etc/my.cnf" will NOT cause any existing databases on the server to fail.

Assumption #2 - Adding "innodb_file_per_table=1" to "/etc/my.cnf" will have the immediate effect of halting the growth of the single ibdata1 file.

Assumption #3 - After adding "innodb_file_per_table=1" to "/etc/my.cnf" some of the databases on the server will still use the single ibdata1 file, and some will use their own smaller/independent ibdata1 files, thereby we have a hybrid system of sorts.


Thanks in advance for any clarification on these points.
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Hello jols,

Assumption #1 - I cannot guarantee anything in regards to data loss. You should take a full backup of your databases before making any changes to MySQL at all. Additionally, adding a variable to /etc/my.cnf doesn't cause MySQL to immediately change, so it would be upon /etc/my.cnf having the variable added and then restarting MySQL for the variable to take effect.

Assumption #2 - Yes, it will have the immediate effect of halting most the growth of the ibdata1 file after you've added the variable and restarted MySQL. The ibdata1 file will still contain (beyond the existing data for the prior InnoDB tables) the internal data dictionary and undo logs as I mentioned initially.

Assumption #3 - Yes, again this requires adding the variable and restarting MySQL. After you do that, then the previous data in ibdata1 will still be there and new InnoDB engine tables will now use newly created ibd files, so you would have a hybrid system. You can convert the older InnoDB engine tables using the steps I noted, though.
 

cPanelKenneth

cPanel Development
Staff member
Apr 7, 2006
4,607
79
458
cPanel Access Level
Root Administrator
Assumption #2 - Adding "innodb_file_per_table=1" to "/etc/my.cnf" will have the immediate effect of halting the growth of the single ibdata1 file.
Only for tables created after adding that entry and restarting MySQL. Existing tables will continue to use the central file for data storage until the conversion method Tristan outlined is performed. Until that step is performed the central file will continue to grow at an appreciable rate.
 

cPanelTristan

Quality Assurance Analyst
Staff member
Oct 2, 2010
7,607
40
248
somewhere over the rainbow
cPanel Access Level
Root Administrator
Have you added "innodb_file_per_table=1" to /etc/my.cnf file? If you haven't, then all InnoDB engine tables are in ibdata1 location. Otherwise, you can do a find for any files ending in .idb:

Code:
find /var/lib/mysql -name *.ibd
 
Last edited: