Skip to main content
Question

Convert MySQL MyISAM to MySQL InnoDB


Forum|alt.badge.img+6

This was mentioned in the presentation as it moves from a table lock to row lock process.

Wondering if any have done this and what steps you took to complete.

We have found references:
http://www.ryadel.com/en/mysql-convert-all-tables-of-one-or-more-databases-to-myisam-or-innodb/

44 replies

Forum|alt.badge.img+5
  • Contributor
  • 35 replies
  • October 19, 2016

Bumping for interest


Forum|alt.badge.img+31
  • Honored Contributor
  • 2721 replies
  • October 24, 2016

In the DataBase.xml in Tomcat you set a <DefaultStorageEngine>InnoDB</DefaultStorageEngine> so that any schema payloads in the future always create InnoDB tables instead of MyISAM.

Not sure if that is the exact XML tag but it is something like that you could confirm with Jamf Support.

Otherwise you can write a MySQL script to convert all MyISAM tables to InnoDB. I will say that InnoDB has a higher support cost because there are so many specific settings you can set in the configs. MyISAM dynamically takes resources as needed, but as you mentioned row lock versus table lock can be a huge performance hit with MyISAM.


donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • November 11, 2016

@tlarkin I'm just curious, has this been blessed by Jamf?

We have been told by Jamf that InnoDB is not recommended and not supported.

I ask because I know you used to work for them, maybe we missed an announcement. :) #FingersCrossed


Forum|alt.badge.img+31
  • Honored Contributor
  • 2721 replies
  • November 11, 2016

@donmontalvo

I do not know the capacity in which it is supported or QA'd, but I can tell you lots of large environments run InnoDB simply because of the table lock versus row lock. InnoDB has a higher cost of support because you need to be able to configure and scale it based off your environment and workflows. Basically there is not any one-size-fits-all config that just works for everyone. This is why Jamf (jamf?) may not fully support it because it could be hard to support it. We don't have a choice because MyISAM just won't work for us.

So, if you choose to go InnoDB I would recommend someone on staff that has some DBA skills or at least MySQL skills.


Forum|alt.badge.img+7
  • Contributor
  • 77 replies
  • November 11, 2016

We were also told by JAMF support that they don't recommend it and if we choose to use InnoDB, it may limit their ability to provide support to us. They mentioned a potential defect, but we're going to try it in our test environment since we have dedicated DB admins who are pushing for it and will support it.

Current Defect: Deadlocks in MDM processing can occur when using InnoDB I think the one that was mentioned multiple times was that InnoDB is not a supported engine through us. It would make getting support if something were to happen to the database pretty difficult.

donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • November 11, 2016

Thanks @tlarkin!

Jamf (jamf?)

Yea, I know...a little confusing. :D


Forum|alt.badge.img+31
  • Honored Contributor
  • 2721 replies
  • November 11, 2016

Well I can tell you any large environment is going to run InnoDB because of the performance boost you get over MyISAM. Sure there is a higher support cost so I would recommend a class, or a book, or even just reading through lots of percona blog posts until you feel comfortable with it.

InnoDB is the standard default engine on out of the box MySQL installs. The jamf app payloads the MyISAM schema so even MySQL makes InnoDB the default engine from a default install.


donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • November 11, 2016

@tlarkin wrote:

InnoDB is the standard default engine on out of the box MySQL installs. The jamf app payloads the MyISAM schema so even MySQL makes InnoDB the default engine from a default install.

Interesting, thanks for responding and sharing all your knowledge/insight.

FWIW, we have DBAs that build and manage large clusters of various database solutions (MSSQL, SQL, Oracle, MySQL). For us its more a matter of being contractually bound to ensure full vendor support.

So changes like this, however beneficial, are easier to get approved/implemented when the vendor (Jamf) recommends and supports it.

Sure makes you wonder why Jamf is setting default engine to MyISAM, if InnoDB is the default, if the later is such an improvement (less table lock hell). :)

Don


bradtchapman
Forum|alt.badge.img+20
  • Valued Contributor
  • 588 replies
  • November 11, 2016

Educated guess: when JAMF evolved from an imaging tool to a full management suite over 10 years ago, they built it to run on the only MySQL data structure available at the time: MyISAM. InnoDB was new and not well established, and until recently Jamf had not invested the engineering R&D to build and test it on newer designs.

Hopefully @deanhager hired a lot of smart database engineers.


donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • November 11, 2016

Interesting...

InnoDB (Wikipedia)


Forum|alt.badge.img+31
  • Honored Contributor
  • 2721 replies
  • November 11, 2016

So if you want to get technical about it, you would get vendor support for MySQL from Oracle, not jamf. Oracle Enterprise MySQL licenses are fairly cheap in the grand scheme of things. That is where you would get your vendor support, not from jamf officially for MySQL. So really if you are contractually bound to have full vendor support you should be paying for support through Oracle, since they own MySQL and offer support for money.

InnoDB isn't that hard to manage or learn, but it does take more effort than MyISAM. Personally I would always put InnoDB in my environments because it out performs MyISAM by a ton.


donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • December 14, 2016

On a call today we were, um, well, alerted to the fact that MyISAM is no longer actively developed...


Forum|alt.badge.img+7
  • Employee
  • 34 replies
  • December 14, 2016

Thank you for the ongoing discussion. Please know that Jamf is looking at InnoDB as the supported method. It is too early to announce a date, but we have this in plan.

I have also updated this feature request around MySQL 5.7 and InnoDB.


iJake
Forum|alt.badge.img+21
  • Contributor
  • 279 replies
  • December 14, 2016

FWIW, it's worth we have a very large environment and have been running InnoDB for years. We could not function with MyISAM.


Forum|alt.badge.img+5
  • Contributor
  • 29 replies
  • August 12, 2017

@joe.bloom Are there any known issues with migrating tables to InnoDB? Is a one-time conversion and setting the DefaultStorageEngine setting sufficient for future JSS upgrades?

@tlarkin @iJake Can you guys elaborate on what needs to be done to convert to using InnoDB safely beyond what's mentioned in this thread and what pitfalls there might be? I am planning a 1000 endpoint roll out of JAMF and would like to use Amazon RDS w/ multi-AZ for the database. According to Amazon's documentation, RDS cannot guarantee consistently on failovers/snapshots for tables in MyISAM and so I am weighing using a script like: https://github.com/CU-CloudCollab/docker-mysql-util/blob/master/lock-and-snap.rb (which would require nightly shutdowns of the JSSs) to converting to InnoDB.


Forum|alt.badge.img+31
  • Honored Contributor
  • 2721 replies
  • August 12, 2017

There are a few ways you can do this.

1 - in the DataBase.xml file before the JSS ever connects to the database (because it needs credentials to do so) add in this XML tag, <DefaultStorageEngine>InnoDB</DefaultStorageEngine> and when the tomcat app creates the DB tables it will do so in InnoDB. As long as that flag is there all tables should be created as InnoDB tables. You will also need this flag in your DataBase.xml for any and all future upgrades as well, otherwise the app will overwrite new tables as MyISAM.

2 - a simple SQL script to alter the table's engine:

SELECT CONCAT('ALTER TABLE ',TABLE_NAME,' ENGINE=InnoDB;') FROM TABLES
WHERE ENGINE='MyISAM'
AND table_schema = 'mydatabase'

I would definitely confirm with Jamf first though. I would personally always run InnoDB at any job I have and can control it. The performance benefits out weigh the support cost, and MyISAM has been deprecated for a while. Thus Oracle has been shipping InnoDB as the default storage engine for some time now.

Test it out in your test environment first of course. I also strongly recommend contacting Jamf support to help you ensure that is the right way to do so.


Forum|alt.badge.img+7
  • Employee
  • 34 replies
  • August 14, 2017

@mrben You can run Jamf Pro with Amazon RDS using InnoDB. In fact, I would recommend doing that (converting to InnoDB) if you are going to use RDS. To make sure the conversion goes smoothly, I would recommend that you reach out to your Jamf account representative.

@tlarkin Thank you for posting additional details on this.

I'll pursue getting some documentation out there for how to do this.


jhbush
Forum|alt.badge.img+26
  • Esteemed Contributor
  • 539 replies
  • January 10, 2018

@joe.bloom has this information been posted anywhere?


Forum|alt.badge.img+7
  • Employee
  • 34 replies
  • January 10, 2018

@jhbush1973 No, we don't have documentation out there publicly yet.


donmontalvo
Forum|alt.badge.img+36
  • Legendary Contributor
  • 4293 replies
  • February 6, 2018

Looking forward to seeing it when it is posted. We're preparing InnoDB for the BBQ. We'll have plenty of ACID available. Can't wait to finally get MySQL to where it needs to be, even if we're a little late to the party.


Forum|alt.badge.img+7
  • Employee
  • 34 replies
  • February 28, 2018

Hello everyone,

Our work on InnoDB continues. As we look at that work and the complication of trying to migrate to InnoDB yourself, we believe we can help with that as part of our product changes and InnoDB preparation. As soon as we are ready, we'll have a method for you to make it easier. Hang in there. Thinking mid-year. We'll call it out in a future beta.


blinvisible
Forum|alt.badge.img+7
  • Contributor
  • 49 replies
  • April 23, 2018

@joe.bloom We're also very interested in MyISAM > InnoDB conversion for our environment, and look forward to your pending methodology release.


Forum|alt.badge.img+13
  • Contributor
  • 400 replies
  • June 20, 2018

@joe.bloom What's the update on InnoDB conversions?

Thanks


bentoms
Forum|alt.badge.img+35
  • Legendary Contributor
  • 4331 replies
  • June 20, 2018

@Kumarasinghe might be worth signing up for 10.6 beta...


Forum|alt.badge.img+7
  • Employee
  • 34 replies
  • June 20, 2018

Hello @Kumarasinghe. As @bentoms alluded to, we have our converter for MySQL to InnoDB slated for the 10.6.0 release, and the beta program is open so you can test it. There is a a command line utility.

From the 10.6.0 Beta Release Notes
The installers for Jamf Pro 10.6.0 include a new Jamf Pro command-line utility that you can use to perform server and database maintenance tasks, including: Start and stop the Jamf Pro server, Back up and restore the database, Convert the MySQL database storage engine to InnoDB.

More information is in the release notes about where to find the utility. I hope you have a chance to test it out and let us know what you think.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings