1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Changing tables from MyISAM to InnoDB

Discussion in 'XenForo Questions and Support' started by Rigel Kentaurus, Apr 2, 2011.

  1. Rigel Kentaurus

    Rigel Kentaurus Well-Known Member

    I know I should probably use a ticket .. but trying with forums first :(

    I just notice this randomness in my file my.cnf
    Sigh ... and of course, after a "SHOW TABLE STATUS", all tables are myisam

    Before I start going through all my tables and "alter table engine=innodb" .. is there anything else I should know ? (indexes, other considerations, etc)

    Should I even bother?
     
  2. Kier

    Kier XenForo Developer Staff Member

    XenForo relies heavily on functionality provided by InnoDB over MyISAM so you should definitely make the change, however you should note that not all XenForo tables use InnoDB, so be careful when running your queries.
     
    Markos likes this.
  3. Deebs

    Deebs Well-Known Member

    Innodb is certainly a better option for your tables if you have a high number of concurrent writes. When you activate the Innodb plugin (or it could be statically compiled) there are an additional number of entries you need to put into my.cnf.

    Firstly, Innodb does not use the keybuffer memory used for MyIsam tables, it also does not let the OS cache the files, instead you can use the following:

    Code:
    innodb_buffer_pool_size=6G     
    innodb_log_file_size=256M
    innodb_additional_mem_pool_size=20M
    innodb_log_buffer_size=4M
    innodb_thread_concurrency=8
    innodb_flush_log_at_trx_commit=2
    innodb_file_per_table
    # ^ Enable per table space
    
    innodb_flush_method=O_DIRECT
    
    innodb_data_file_path=inno_data_1:50M;inno_data_2:50M;inno_data_3:50M;inno_data_4:50M
    
    innodb_data_home_dir = ibdata         
    # Set the location of ib data files
    
    innodb_log_group_home_dir = iblogs
    # Set the location of ib log files
    
    These are taken from my SQL server so you would need to tune them. Pay particular attention to innodb_buffer_poll_size, this sets the amount of memory Innodb can use for it's index and data pages.

    I personally like having per table namespace enabled, and the Innodb log files and tablespace files in their own directories. Innodb_thread_concurrency will need to be tuned for your server. How you set this variable will depend on the version of MySQL and if using the plugin or the inbuilt Innodb.
     
    DeltaHF and Rigel Kentaurus like this.
  4. Rigel Kentaurus

    Rigel Kentaurus Well-Known Member

    That's awesome advise. Thanks, Deebs
     
  5. Kier

    Kier XenForo Developer Staff Member

    Darkimmortal likes this.
  6. Deebs

    Deebs Well-Known Member

    Interesting read but having done some research into this I am not sure it holds much weight especially with the Innodb 1.1+ architecture and the improvements they have made there. For completeness I do not use the stock MySQL branch from Oracle but rather the rather excellent Percona tree. Currently testing Percona Server 5.5.8-20 and liking it quite a lot!

    For me, my server is not I/O bound running on a raid1 arrary (the databases will shortly be moved to a SSD) and I would rather have the flexibility of multiple files which are certainly a lot easier to manage than a massive single tablespace.
     
  7. p4guru

    p4guru Well-Known Member

  8. Marc

    Marc Well-Known Member

    Hi Kier,
    Im having some occasional slowdowns on my server and on looking at my tables all mine are also in MyISAM rather than InnoDB, and wonding if this is likley to be the issue. When you say not all tables use it, how will I find out which ones need to be changed over and which ones dont?
     
  9. Brogan

    Brogan XenForo Moderator Staff Member

  10. Marc

    Marc Well-Known Member

    You ARE the oracle arent you? LOL ...... Cheers as always Paul
     
  11. Bill.D

    Bill.D Active Member

    Hey All,

    I have been slowly testing the transition of my tables over to InnoDB. When I do a "SHOW TABLE STATUS" I have noted that the Row_format hase changed from Dynamic to Compact and the Max_data_length is changed to 0 from its previous very large number. Is this ok? Do I need to change them back?

    Thanks,
    -Bill
     
  12. speedway

    speedway Well-Known Member

    Dragging this thread back from history, sorry about that.

    I am about to convert my MyISAM tables over the InnoDB and just wanted to check - do I leave the default-storage-engine setting in my.cnf set to MyISAM or do I need to change that to InnoDB? I have already turned off the skip-innodb setting by deleting it and have InnoDB available as an option in my MariaDB installation.
     
  13. Rigel Kentaurus

    Rigel Kentaurus Well-Known Member

    XenForo does not care what your default is. On table creation they COLLATE and ENGINE is specified and the table would be created as InnoDB regardless
    This is more a question, for you .. do you have any other application running on the service that, by mistake, would end up being as InnoDB when the author expected MyISAM?

    The safest thing is to not set it at all or to leave it at MyISAM
     
  14. speedway

    speedway Well-Known Member

    Aha, cool thanks.
     

Share This Page