The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

innodb_file_per_table - Converting to Per Table Data for InnoDB

Discussion in 'E-mail Discussions' started by cPanelTristan, Oct 7, 2010.

  1. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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
     
  2. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    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 ...
     
  3. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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:

    Additionally, as I noted:

    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.
     
  4. VeZoZ

    VeZoZ Well-Known Member

    Joined:
    Dec 14, 2002
    Messages:
    248
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    DataCenter Provider
    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.
     
  5. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    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
     
  6. nocbr.com

    nocbr.com Member

    Joined:
    May 1, 2006
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Any news about this feature request ?
     
  7. brianoz

    brianoz Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,146
    Likes Received:
    6
    Trophy Points:
    38
    Location:
    Melbourne, Australia
    cPanel Access Level:
    Root Administrator
    Sorry folks - my link above is total nonsense, it should have been a third party link, which I now (months later) can't remember ...
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    There is no current estimate or update on the feature request, which is internal case #41802.
     
  9. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    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.
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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.
     
  11. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    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.
     
  12. activa

    activa Well-Known Member

    Joined:
    May 23, 2006
    Messages:
    204
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Morocco
    cPanel Access Level:
    Root Administrator
    How we can check if the table are in ibdata1 file or in a separated file ?
     
  13. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    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
     
    #13 cPanelTristan, Mar 26, 2012
    Last edited: Sep 22, 2015
  14. acenetryan

    acenetryan Well-Known Member
    PartnerNOC

    Joined:
    Aug 21, 2005
    Messages:
    197
    Likes Received:
    1
    Trophy Points:
    18

Share This Page