Convert Tables from MYISAM to InnoDB

[sadams101]

I would like to convert my tables from MYISAM to InnoDB. I've run some tests using commands like this on each table:

ALTER TABLE `table_nam` ENGINE=InnoDB;

but some of my tables throw errors like this:

Error
SQL query:


ALTER TABLE `ibf_core_widgets` ENGINE=InnoDB
MySQL said: Documentation

#1071 - Specified key was too long; max key length is 767 bytes

I was wondering if anyone has a workaround for this issue, or has developed a script that will handle such a conversion? 

[Bluto]

The below should list what needs to be run to convert the tables.

WILL CONVERT EVERY INNODB TABLE ON SERVER to INNODB / DYNAMIC

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB ROW_FORMAT=DYNAMIC;') 
FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';

WILL CONVERT EVERY MYISAM TABLE ON SERVER to INNODB / DYNAMIC

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB ROW_FORMAT=DYNAMIC;') 
FROM information_schema.TABLES WHERE ROW_FORMAT = 'compact';

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB ROW_FORMAT=DYNAMIC;') 
FROM information_schema.TABLES WHERE ENGINE = 'myisam';

Hope this helps.

[sadams101]

This is great, thank you! Regarding the two lines shown to convert myisam to innodb, do I need to run both lines? Also, I suspect that I would need to replace TABLE_NAME with the actual table name...is there a wild card for this?

[bfarber]

Those are variables. The error you are seeing is due to indexes on some tables being too large.

[sadams101]

I seem to be trading one error for another. In my experiments I get errors like this when trying to run the commands:

Error
Static analysis:

1 errors were found during analysis.

This option conflicts with "ENGINE". (near "ROW_FORMAT" at position 76)
SQL query:

ALTER TABLE wheatfre_forum.ibf_cms_database_fields_thumbnails ENGINE=InnoDB ROW_FORMAT=DYNAMIC

MySQL said: Documentation

#1071 - Specified key was too long; max key length is 767 bytes

Any ideas?

[sadams101]

PS - I did manage to convert 95% of my tables, but am stuck on about 5-6 that throw the above error.

[Bluto]

Make sure you have 

innodb_large_prefix=on;

https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes

 

[bfarber]

You can often drop any indexes on the table except the primary key, change the engine, and then run the support tool afterwards to automatically add any missing indexes.

[sadams101]

Thank you guys, just to be clear, which support tool in PHPMyAdmin do you mean, optimize? Repair?

[Nathan Explosion]

The support tool in the ACP....

[sadams101]

Ok, I'll try to finish this. Do either of you happen to know whether IPB is compatible with MariaDB 10.2 ?  I am running 10.1 now, and when I go to upgrade I get a warning about how 10.2 uses strict mode, and to make sure my applications are compatible with strict mode.

[bfarber]

Invision Community is developed with strict mode enabled and is compatible.

[sadams101]

I can verify that MariaDB 10.2 works fine with IPB 4.3. I am still getting the error below on 5 or so tables, even after adding this to my.cnf: innodb_large_prefix=on;

@bfarber mentioned this: "You can often drop any indexes on the table except the primary key, change the engine, and then run the support tool afterwards to automatically add any missing indexes" but I am sorry to say that I don't follow you here. I am not sure how to drop an index on those tables (I assume you do this in phpmyadmin), or which support tool you mean to re-index those...sorry, but I've just never done this before and any extra guidance would be very helpful, and appreciated.

#1071 - Specified key was too long; max key length is 767 bytes

[bfarber]

Using phpmyadmin you can go to the table in question and drop indexes besides the primary key. Convert the table, then run the support tool in the AdminCP which checks the database structure and allows you to automatically fix any problems.

[sadams101]

Thank you! I'll try this soon and report back. 

Also, I am getting mixed messages about InnoDB vs. MyISAM (what I'm using now), as far as which is faster. I am reading that InnoDB may be slightly faster, but probably not much, and some people even say that I might not notice any difference at all.

Before making such a big change, I would love to hear from anyone who has experience with making this change in IPB, and whether their site speed improved enough to make it worth it. 

My DB has nearly 1 million posts, and over 100K topics, and is about 2 gigs in size.

[sadams101]

So on my test site I finally got around to dealing with the 5 tables that thew the errors when trying to run the command to convert them to InnoDB. I was planning to do the delete index route beforehand, but decided to try one last time to run the commands in PHPMyadmin, example:

ALTER TABLE mysite_forum.ibf_cms_database_fields_thumbnails ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

This time I did this one by one, and got time out errors on some (I've been doing this in PHPAdmin, not on the command line). However, even through I saw timeout errors, it looks like those table were successfully converted to InnoDB.

I will try all of this one more time on test databases before trying it on my live site. I have a few questions, some probably go beyond the scope of this board, but what the heck:

1) Would it be best to do this from the MYSQL command line to avoid timeout and other issues?

2) For a site that gets up to 100 simultaneous connections with up to 48K unique visitors a day, are there some basic recommended my.cnf settings I'd better put in place before going live? I am just not used to InnoDB. I've heard that 1/3 of my memory should be dedicated to InnoDB.

3) I've got my "save my butt" cheat sheet commands for MyISAM, which have saved me several times--these are force repair of a table or tables, rebuilding their indexes, etc. Can anyone share similar commands with me for InnoDB that are used when things get bad?

[Rhett]

8 minutes ago, sadams101 said:

So on my test site I finally got around to dealing with the 5 tables that thew the errors when trying to run the command to convert them to InnoDB. I was planning to do the delete index route beforehand, but decided to try one last time to run the commands in PHPMyadmin, example:

ALTER TABLE mysite_forum.ibf_cms_database_fields_thumbnails ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

This time I did this one by one, and got time out errors on some (I've been doing this in PHPAdmin, not on the command line). However, even through I saw timeout errors, it looks like those table were successfully converted to InnoDB.

I will try all of this one more time on test databases before trying it on my live site. I have a few questions, some probably go beyond the scope of this board, but what the heck:

1) Would it be best to do this from the MYSQL command line to avoid timeout and other issues?

2) For a site that gets up to 100 simultaneous connections with up to 48K unique visitors a day, are there some basic recommended my.cnf settings I'd better put in place before going live? I am just not used to InnoDB. I've heard that 1/3 of my memory should be dedicated to InnoDB.

3) I've got my "save my butt" cheat sheet commands for MyISAM, which have saved me several times--these are force repair of a table or tables, rebuilding their indexes, etc. Can anyone share similar commands with me for InnoDB that are used when things get bad?

1. it doesn't matter where you start the query from, it will continue to process, phpmyadmin may timeout on the page, but the query will continue to run until it's finished. You can also use the command line if you wish.

2. You would need to use something like mysqltuner or have a database administrator tune to your setup, it's not a one size fits all type of setup, and based on your resources available. InnoDB is very memory hungry, the more memory you can toss at it the better, based on what is available and the rest of your configuration the amount you allocate to mysql can vary. 

3. Backups, backups, backups, be sure you process proper backups, innodb doesn't repair/correct as easily as MyISAM.  In the event of a major failure, you can start mysql in recovery mode to try and recover/repair the data, having a solid backup plan is the best option.

 

[sadams101]

Thank you for this, it is very helpful. So if I get no errors converting a table, I will assume that there is no need to drop an index and re-index anything, or is it good practice to re-index after doing this?

Also, one issue I am having is converting the ibf_core_search_index table, which is larger than my posts table. From command line it ran all night and never seemed to finish, so I stopped it. A look at the table in PHPMyadmin shows that it did covert to InnnoDB, however I am skeptical that something did not go right with it. In a worst case, is it ok to empty this table out, then rebuild the search index?

[Rhett]

53 minutes ago, sadams101 said:

Thank you for this, it is very helpful. So if I get no errors converting a table, I will assume that there is no need to drop an index and re-index anything, or is it good practice to re-index after doing this?

Also, one issue I am having is converting the ibf_core_search_index table, which is larger than my posts table. From command line it ran all night and never seemed to finish, so I stopped it. A look at the table in PHPMyadmin shows that it did covert to InnnoDB, however I am skeptical that something did not go right with it. In a worst case, is it ok to empty this table out, then rebuild the search index?

5

Go into phpmyadmin, truncate the ibf_core_search_index table, then convert it, then rebuild search after from the admincp.

[sadams101]

So last night I finally converted my databases from MyISAM to innoDB. So far in my testing I've not seen any speed increase, and in fact, my TTFB (time to first byte) seems to be slower.

I've not yet tweaked the my.cnf settings much, and have pulled mine from here (I have 64G of memory):

https://www.percona.com/blog/2016/10/12/mysql-5-7-performance-tuning-immediately-after-installation/

Here are my my.cnf settings...any suggestions would be appreciated. My board's MyISAM post table was ~1G, and now it is ~4x that size.

innodb_buffer_pool_size=25G
innodb_buffer_pool_instances=8
innodb_flush_log_at_trx_commit=1
innodb_log_file_size=5G
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=10M

 

[Lindy]

It's difficult giving tuning advice without seeing the server, what else it's being used for, etc. You can't really base it on sheer memory alone. 

With that said, I would recommend:

innodb_buffer_pool_size = 36G  # about 60% of RAM
innodb_buffer_pool_size_instances = 16
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_file_per_table = 1 # This is essential!! It should also be enabled by default if you are using 5.6 or above

Also, you can experiment with turning table cache off. With heavy write operations, turning it off can yield some noticeable gains (your mileage may vary):

query_cache_type = 0
query_cache_size = 0

The performance gains you notice will largely depend on what's happening with your community. If there's a lot of reading and not a lot of writing, you may not find a significant performance improvement with InnoDB over MyISAM (and in fact, MyISAM may have been faster.) On the other hand, on an active site with a good flow of concurrent read/write operations, InnoDB will yield a vastly better experience - especially on large tables. The table isn't being locked for every write operation, which prevents reads until that operation is complete and on busy sites, those reads will "stack" and choke the web server because all of those connections are open and waiting for that write to complete. Even 10 years ago, InnoDB was a bit like playing Russian Roulette and we just had to tolerate MyISAMs transactional shortcomings... but these days, InnoDB came a long way and is rock solid - now the verdict is in: MyISAM sucks. 🙂 

 

[sadams101]

Thank you for this...I really appreciate it. My server is dedicated to my IPB forum, and running Revive Adserver, which is a PHP banner serving app that I use to serve the banners in my forum. It is still also running MyISAM, so I should probably update that as well because I am still reserving 10G to MyISAM's key_buffer_size. In fact, even though I have 64G it looks like I really don't due to setup factors--for some reason 15G is devoted to swap...no idea why, the leased server was set up this way, and I'm not sure if that memory is available for anything else. It uses nearly zero memory for swap most of the time. I also dedicate about 4G to memcached.

Based on what I think is a more limited amount of memory available I went with the following (if the reserved memory mentioned above is not "locked" for those purposes, I can revisit this:
innodb_buffer_pool_size=26G
innodb_buffer_pool_instances=13
innodb_file_per_table=1
innodb_log_file_size=2G
query_cache_type = 0
query_cache_size = 0

 

Here are some additional settings I have based on what I've read at Percona, and what running mysqltuner.pl recommended (hopefully these help, but I'm not sure):

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_read_io_threads=8
innodb_write_io_threads=8

Last, after running the conversion with recommended commands...example:

• ALTER TABLE example_forum_db.ibf_custom_bbcode ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

is there anything I need to do, for example create indexes for the tables or do anything else?

 

[sadams101]

PS - mysqltuner recommends this setting, but I am not sure when I read up on this, should I set this to on?

• performance_schema = ON

[sadams101]

Does anyone know why after upgrading from 4.3 to 4.4 about 25 of my InnoDB tables were converted back to MyISAM?  All tables were InnoDB before the upgrade.

[sadams101]

The following tables were converted back to MyISAM when I upgraded:

forum.ibf_core_search_index
forum.ibf_core_output_cache
forum.ibf_blog_rss_import
forum.ibf_calendar_event_reminders
forum.ibf_calendar_import_feeds
forum.ibf_core_acp_notifcations_dismissals
forum.ibf_core_acp_notifications
forum.ibf_core_acp_notifications_preferences
forum.ibf_core_admin_logs
forum.ibf_core_advertisements
forum.ibf_core_announcements
forum.ibf_core_clubs
forum.ibf_core_edit_history
forum.ibf_core_error_logs
forum.ibf_core_follow
forum.ibf_core_incoming_emails
forum.ibf_core_ips_bulletins
forum.ibf_core_pfields_data
forum.ibf_core_polls
forum.ibf_core_post_before_registering
forum.ibf_core_profile_completion
forum.ibf_core_ratings
forum.ibf_core_rc_comments
forum.ibf_core_rc_index
forum.ibf_core_reputation_index
forum.ibf_core_security_answers
forum.ibf_core_sessions
forum.ibf_core_social_promote
forum.ibf_core_soft_delete_log
forum.ibf_core_streams
forum.ibf_core_sys_social_groups
forum.ibf_core_tags
forum.ibf_forums_answer_ratings
forum.ibf_forums_forums
forum.ibf_forums_question_ratings
forum.ibf_forums_rss_import