Changing tables from MyISAM to InnoDB

[Rigel Kentaurus]

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

I just notice this randomness in my file my.cnf

[mysqld]
skip-innodb

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?

 

[Kier]

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.

 

[Deebs]

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:

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.

 

[Rigel Kentaurus]

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:

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.

That's awesome advise. Thanks, Deebs

 

[Kier]

FWIW, there's some debate over the performance of innodb_file_per_table:

http://umangg.blogspot.com/2010/02/innodbfilepertable.html

 

[Deebs]

FWIW, there's some debate over the performance of innodb_file_per_table:

http://umangg.blogspot.com/2010/02/innodbfilepertable.html

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.

 

[p4guru]

FWIW, there's some debate over the performance of innodb_file_per_table:

http://umangg.blogspot.com/2010/02/innodbfilepertable.html

Really depends on how mysql/innodb is tuned and what version of mysql is used http://vbtechsupport.com/606/ - guess info there applies to xenforo as well

 

[Marc]

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.

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?

 

[Paul B]

Here's a list of the default table types.

http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/page-2#post-134517

 

[Marc]

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

 

[Bill.D]

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

 

[speedway]

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.

 

[Rigel Kentaurus]

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.

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

 

[speedway]

Aha, cool thanks.