Convert MySQL MyISAM to MySQL InnoDB

[ChrisLeeSSD]

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/

[sgorney]

Bumping for interest

[tlarkin]

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]

@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

[tlarkin]

@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.

[rdwhitt]

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]

Thanks @tlarkin!

Jamf (jamf?)

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

[tlarkin]

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]

@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]

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]

Interesting...

InnoDB (Wikipedia)

[tlarkin]

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]

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

[joe_bloom]

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]

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

[mrben]

@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.

[tlarkin]

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.

[joe_bloom]

@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]

@joe.bloom has this information been posted anywhere?

[joe_bloom]

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

[donmontalvo]

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.

[joe_bloom]

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]

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

[Kumarasinghe]

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

Thanks

[bentoms]

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

[joe_bloom]

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.

[joe_bloom]

Update on this topic. We are keeping the command-line utility for converting to InnoDB in beta until we feel it is ready, given the importance of this functionality. I expect that we will release it later, after 10.6, rather than with the 10.6 release, so that we can further validate it.

[mhegge]

So, no utility currently?

[joe_bloom]

@mhegge coming very soon in the September 10.7.0. We weren't confident in it and had to hold it until this next release.

[blackholemac]

@mhegge Given something as fundamentally central as the database, I wouldn't jump on anything that hasn't been released until they are ready to release it.

That being said, you might ask your Jamf buddy if they are accepting applications to test new versions of Jamf Pro in your sandbox environment. Sometimes you get early access to stuff to see how it works before it goes into support and before you put your production environment on the new builds.

[kerouak]

So, I'm attempting to run this, I get as far as Pre-Conversion then as far as this and get the following error
"- [418/420] Repairing table 'vpp_user_accounts'
- [419/420] Repairing table 'wallpaper_auto_management_settings'
- [420/420] Repairing table 'webhook_display_fields'
- Applying database settings
- Restarting database
failed to restart the database
Error: pre-conversion step failed: unable to restart database: fork/exec /bin/systemctl: no such file or directory"

anyone?

[kerouak]

I'm using a Linux server BTW

[kerouak]

@joe.bloom

Hi Jope, Any Ideas on this?
Running RHEL

"Error: pre-conversion step failed: unable to restart database: fork/exec /bin/systemctl: no such file or directory"

[blackholemac]

@kerouak Try posting this in the beta forums...I’m guessing you may get a faster response

[joe_bloom]

@kerouak I'll share this with our dev team but I would suggest opening a support case too if you haven't already. Our team will need to know what you've shared above and probably things like what Jamf Pro version you're running. Maybe more.

[drhoten]

Hello @kerouak

I work with the team developing the Jamf Pro Server Tools, and shared this error with them last night.

Chatting with them again this morning, would you be able to let us know the following?

• OS name and version. Our initial thought, is this error may be related to the distro you are using.
• Output of jamf-pro version.
• Jamf Pro version installed

Thanks, and look forward to hearing back from you!

[blackholemac]

Now that it’s been released I will note I faced the same issue as @kerouak today. The conversion process failed to restart MySQL on Windows

I solved it in my case, but it was one of two things that did it.

The first was that I renamed the standard Mysql57 service name to make scripting easier originally. When studying the issue, I uninstalled and reinstalled MySQL this time accepting the default name. I also added the C:Program FilesMySQLMySQL Server 5.7in Folder as a system variable in Windows.

One of those two actions fixed my issue FWIW.

[Hugonaut]

We Have a RHEL Server JUST for our MySQL database, Went to InnoDB Yesterday and I HIGHLY Recommend it. Load times increased BIG TIME!!! GO JAMF!

@ChrisLeeSSD

https://www.jamf.com/jamf-nation/articles/531/converting-the-mysql-database-storage-engine-from-myis...

heres the MySQL Commands I was advised to do that got the job done ..in order..i think this is all of them...I would recommend contacting JAMF if unsure as I am not proficient in MySQL

#this command shows your tables, will show all MyISAM, after you convert, run again, should reflect all Innodb
select table_name,engine from information_schema.tables where tables.table_schema="jamfsoftware";

<DefaultStorageEngine>InnoDB</DefaultStorageEngine>

#this will convert MyISAM to Innodb
*MYSQL PATH HERE* -u root -p PASSWORDHERE -N -e "select concat('alter table ', table_name, ' engine =InnoDB;') from information_schema.tables where table_schema = 'jamfsoftware' and engine = 'MyISAM' and table_name != 'downloadable_file_chunk_data'" > jamfsoftware_innodb.sql

#This Will Import After Conversion (Thank @bdelamarche ) - FORGOT THIS PART
MYSQL PATH HERE -u root -p PASSWORDHERE < jamfsoftware_innodb.sql

#again, this will show the newly converted MyISAM tables are now Innodb
select table_name,engine from information_schema.tables where tables.table_schema="jamfsoftware";

ADD THE FOLLOWING to /etc/my.cnf on RHEL Sql Server NOTE * innodb_buffer_pool_size=#G --> The # value "#G" reflects 75% of your SQL servers RAM, in my case we have 16GB of ram so our value is 12G.

please see my comment in this post for further detail of why this is going here

###***This is a comment in the /etc/my.cnf file 
# Added innodb values September 20th after Update to Jamf 10.7 & MySQL Conversion from MyiSAM to Innodb
innodb_buffer_pool_size=12G
innodb_file_per_table=1
key_buffer_size = 16M
innodb_flush_log_at_trx_commit = 0

[kerouak]

drhoten

RHEL 7.5 running on an independent server.

JamfPro V 6.2

[kerouak]

Error: pre-conversion step failed: unable to restart database: fork/exec /bin/systemctl: no such file or directory"

If someone can shed a light on the file that it's trying to access, that may make things slightly easier to resolve???

ta