Moving from MyISAM to InnoDB

[allen]

This had been discussed internally before a while back but never formalised.

Now that InnoDB offers all the capabilities of what MyISAM provides and much more, it is time to change this over in Symphony.

[michael-e]

This may cause trouble, e.g. with backup systems. InnoDB tables can not backed up using mysqlhotcopy.

In short words, what is advantage of InnoDB?

[brendo]

• ACID compliant
• Foreign Key support
• Speed (varies depending on what your site does)
• Row level locking (instead of table level)

[michael-e]

Sounds like a good deal then. :-)

[nickdunn]

Any tables that rely on fulltext indices must remain MyISAM since InnoDB does not support this.

From what I have read, InnoDB is superior in high-write contexts where you need transactional support and row locking. However MyISAM is supposed to be faster in high-read contexts. My gut feeling is that Symphony is predominantly used in high-read situations (every page load performs dozens of select queries), so MyISAM sounds like the obvious choice.

That said, I am no expert, and would like to be educated on the possible benefits.

[nickdunn]

InnoDB full-text search (FTS) is finally available in MySQL 5.6.4 release

Oooh.

[allen]

Two articles worth reading:

• http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB
• http://www.ovaistariq.net/460/why-do-i-recommend-switching-over-from-myisam-to-innodb/

The former link is a balanced view of the two engines. The latter is a almost a sales pitch for InnoDB, but is written clearly to explain the benefits of InnoDB.

I think we can all agree that Symphony typically have a high degree of select calls. Given the unique structure of Symphony's DB structure though, if the tables were optimised to utilise foreign keys, would we see a significant improvement in performance, even more than what MyISAM's baseline could offer?

[allen]

If we believe the first article's claims, then it seems like MyISAM being more performant on read speed is a myth.

[designermonkey]

This may cause trouble, e.g. with backup systems.

I was just about to chime in about this too. It could cause massive problems. From experience, InnoDB and backup nearly cost us an entire 10 years content loss, and a very unhappy client. IMO, best to leave alone.

[allen]

I'd like to know more about the backup issues you've had. What backup process did you guys use? Did you use InnoDB's Hot Backup? http://www.innodb.com/doc/hot_backup/manual.html

[allen]

My main motivation to look at this more seriously stems from the fact that MySQL has decided to go with InnoDB as their default storage engine: http://dev.mysql.com/doc/refman/5.5/en/innodb-default-se.html.

MySQL's own article for going with InnoDB is pretty convincing.

As a side note, I'm not sure if anyone remembers, but Symphony began with using InnoDB before switching over to MyISAM.

[designermonkey]

I'm all for following the trend that MySQL themselves have set. My experiences may have been down to how the IT support dept backed up data.

I know that InnoDB is stored in a different location than that of the standard data location, so backup is complicated.

[brendo]

There is backup options available for InnoDB. The files it generates are different from MyISAM but it's still possible.

My guess is that IT are using an older product that was built for MyISAM only and hasn't really kept with the times :)

[nickdunn]

Am I right in saying this would bump up Symphony's requirement to MySQL 5.5? Anyone know what the penetration level is like?

[brendo]

No, InnoDB is supported since 4 or 5 from memory, it's just it became default in 5.5.6 (?) and the plugin was significantly improved.

[nickdunn]

I mean... when were fulltext indices added to InnoDB? This has traditionally been the thing holding us back.

[brendo]

Ah right, yes, these are in 5.6.

Moving to InnoDB can be done partially and just involve the core tables at the moment... although in saying that the more we move to XML based structure, the less core tables we actually have to worry about!

[designermonkey]

Ok. So I just had the exact problem I had before with InnoDB.

I had to do a rebuild of my MacBook, and my TimeMachine backup was corrupted (hence the massive bad mood on Twitter all day).

Luckily, my mysql data folder was 90% intact; Well, the MyISAM table data was, as each table is stored in three files:

tablename.frm
tablename.MYD
tablename.MYI

All these tables could be dropped back into a new instance of mysql on my machine. All tables that are created using InnoDB are stored in a single file, which sadly was corrupt due to it's size and the invasive method I was forced to use to retrieve the data. So the odd table here and there throughout my entire list of Symphony install's databases, was therefore missing, and irretrievable.

I am lucky to have found a manual backup I did a while back so my data loss isn't too bad, but still bad enough. Think what would happen if this was a client server?!

MySQL creates InnoDB data in a single file by default, but it can be set to use individual tables similar to MyISAM, but this is a manual change to be made in my.cnf and will only affect new tables, not already existing ones.

[mysqld]
innodb_file_per_table

I honestly believe that MyISAM is safer in these situations. The easiest situation for many sysops for servers is to use mysqlbackup or mysqlhotcopy as @michael-e said above, but as far as I can find, neither of these methods backup InnoDB data!!! The only way to get around this is to zip the entire folder up as a backup, which is heavy.

If anyone can find a way to ensure we can backup data securely and efficiently and safely without doing a dump, then let me know. If not, I will stand by this argument.

[creativedutchmen]

@designermonkey When you are absolutely sure you don't want a dump (which I find the most reliable method, since you can be sure it can be reapplied anywhere, even if it's a temporary shared hosting package), there are a few things you can do:

1. Use ibbackup as an alternative to mysqlhotcopy. If the documentation is correct, this should to do the appropriate locking and flusing of the tables when it is running. This is not a true hot backup (but again, neither is mysqlhotcopy).
2. Set up replication and do a truly hot backup on the slave. This is actually much easier than it sounds, and there are a few very big benefits to it: your mysql clients will never notice when you are making a backup: there is zero downtime, even when the backup process would hang in the middle of a backup. Also, it is possible to setup the slave on a separate machine, so you don't strain the master server when the backup has to be encrypted or archived.
3. Set up replication, but only use the binlogs for backups. This is a rather nice approach, because it is incremental and you can actually go back to any point in time.

With that said, doing a mysqldump every now and then is never a bad idea. With InnoDB, the dump can even be done without locking the entire database by adding the --single-transaction flag to the mysqldump program. My policy is to make an incremental backup every hour. edit: To clarify, I flush the logs every hour, then copy the log files. The log files themselves are continuously updated. And I do an additional sql dump every night. That way I know I won't lose more than an hour worth of data, and restoring it is easy and painless.

[nilshoerrmann]

This discussion is very old – am I right that this will not be changed in the near future and so this issue can be closed?

[creativedutchmen]

@nilshoerrmann even though the issue is closed I think it's still very relevant. Many things that are very nice to have (mysql clustering via galera, for example) do not work for MyISAM.

I hope this issue can stay open for as long as there is no fix or workaround available. At the very least I hope developers to be aware of this and remove the explicit statement of the type from the create statements when it is not absolutely required.

[nilshoerrmann]

Personally, I'd like to keep this Github issue closed – this does not mean that we shouldn't work on switching from MyISAM to InnoDB, if you think this is appropriate (I simply don't know which is better and why).

The reason why I'd like to keep the issue closed here though, is that we are a really small community with a lot of great ideas but with very, very limited resources. We all tend to throw ideas here and then run away hoping someone else will take care of it. If we continue to use this tracker as a general nice to have and would be cool list, we'll always end up with long lists no one knows how to tackle. We need small entities to work with.

Let's move discussions and feature ideas somewhere else until we have a profound plan and a maintainer and let's keep this tracker for bugs (issues) and feature implementations (pull requests) mainly. Otherwise we'll most likely loose focus. Issues without a maintainer will never be handled – we all know that.