Convert MySQL MyISAM to MySQL InnoDB

ChrisLeeSSD
New Contributor II

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 44

sgorney
New Contributor III

Bumping for interest

tlarkin
Honored Contributor

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
Esteemed Contributor III

@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

--
https://donmontalvo.com

tlarkin
Honored Contributor

@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
Contributor II

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
Esteemed Contributor III

Thanks @tlarkin!

Jamf (jamf?)

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

1bee157ebb5e479cbd216a34b30eeaa6

--
https://donmontalvo.com

tlarkin
Honored Contributor

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
Esteemed Contributor III

@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

--
https://donmontalvo.com

bradtchapman
Valued Contributor II

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
Esteemed Contributor III

Interesting...

InnoDB (Wikipedia)

--
https://donmontalvo.com

tlarkin
Honored Contributor

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
Esteemed Contributor III

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

757980d59f7a42f6998e76a8d7635bb0

--
https://donmontalvo.com

joe_bloom
New Contributor III

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
Valued Contributor

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

mrben
New Contributor III

@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
Honored Contributor

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
New Contributor III

@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
Valued Contributor II

@joe.bloom has this information been posted anywhere?

joe_bloom
New Contributor III

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

donmontalvo
Esteemed Contributor III

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.

--
https://donmontalvo.com

joe_bloom
New Contributor III

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
Contributor

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

Kumarasinghe
Valued Contributor

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

Thanks

bentoms
Release Candidate Programs Tester

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

joe_bloom
New Contributor III

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
New Contributor III

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
Contributor III

So, no utility currently?

joe_bloom
New Contributor III

@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
Valued Contributor III

@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
Valued Contributor

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
Valued Contributor

I'm using a Linux server BTW

kerouak
Valued Contributor

@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
Valued Contributor III

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

joe_bloom
New Contributor III

@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
Contributor II

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
Valued Contributor III

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
Valued Contributor II

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
________________
Looking for a Jamf Managed Service Provider? Look no further than Rocketman
________________


Virtual MacAdmins Monthly Meetup - First Friday, Every Month

kerouak
Valued Contributor

drhoten

RHEL 7.5 running on an independent server.

JamfPro V 6.2

kerouak
Valued Contributor

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