View Issue Details

IDProjectCategoryView StatusLast Update
0002796Composrcore_database_driverspublic2019-11-14 22:12
ReporterPatrick SchmalstigAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0002796: Deprecate MyISAM; use INNODB (on hold)
DescriptionCurrently, Composr uses the MyISAM database engine for MySQL installations. MyISAM is no longer maintained and probably should not be used.

Instead, Composr should use the INNODB engine as it has active development and has some performance improvements over MyISAM (for large sites).
Additional InformationSwitching to INNODB means Composr's minimum MySQL version will need to be bumped up to 5.6 or higher. INNODB does not support full text searching prior to MySQL 5.6 .
TagsRisk: Database change, Risk: Deprecates functionality, Roadmap: v12
Attach Tags
Time estimation (hours)0.5
Sponsorship open

Relationships

related to 0002356 non-assigned Support referential integrity via foreign key constraints 

Activities

Chris Graham

2016-09-05 18:35

administrator   ~0004293

So as of Composr we do support InnoDB if someone wants to manually switch all their tables to it. For that we did have to make improvements, as there are subtle performance ramifications with InnoDB -- particularly that COUNT(*) queries are massively slower on InnoDB. InnoDB will be slower for individual queries, but faster under load due to smarter locking. InnoDB is also harder to recover if corrupted, while MyISAM crashes far too much. So it's not a trivial comparison.

I'd do the switch today though, if we could rely on MySQL 5.6 being on web hosts. As it stands we're already pushing things with our requirement for MySQL 5.5.3.

Patrick Schmalstig

2016-09-06 00:54

administrator   ~0004300

Yeah InnoDB doesn't offer much benefit over MyISAM, I'll agree. I wish there was a really good widely-supported database engine out there. But they all have their own pros and cons.

And when innodb corrupts, you have a VERY valid point there. I have never been able to recover a corrupted InnoDB database before.

Chris Graham

2016-09-06 02:42

administrator   ~0004305

Actually row-level locking is a huge advantage of InnoDB, so I would recommend it for any large site.

Chris Graham

2016-10-19 16:52

administrator   ~0004443

For now we will leave it MyISAM. In a few years when we can be sure everyone has at least MySQL 5.6, we will re-visit and likely fully drop MyISAM in favour of InnoDB.

I don't want us to officially support both as supporting just one right now allows us to give better advice. For example, one of the tutorials references the [tt]myisamchk[/tt] tool to repair tables.

Also I've recently seen actually bugginess on InnoDB, https://bugs.mysql.com/bug.php?id=81031 happened to a customer recently.

Chris Graham

2017-04-10 17:38

administrator   ~0004983

We actually will need to impose a restriction of MySQL 5.7+, or innodb_large_prefix being set to ON. Otherwise we are not able to use the same keys we currently use due to a limit of 767 bytes on the index (which is just one VARCHAR(255) on utf8mb3).

Chris Graham

2017-04-10 17:40

administrator   ~0004984

Another point of note is that InnoDB doesn't have the 64 index limit MyISAM has. So we could remove the check code we currently have (well, better, code the database drivers to know what the limit is).

Issue History

Date Modified Username Field Change
2016-08-24 17:14 Patrick Schmalstig New Issue
2016-08-24 17:14 Patrick Schmalstig Tag Attached: Risk: Deprecates functionality
2016-08-24 17:16 Patrick Schmalstig Tag Attached: Risk: Database change
2016-09-05 18:35 Chris Graham Note Added: 0004293
2016-09-05 18:35 Chris Graham Time estimation (hours) => 0.5
2016-09-06 00:54 Patrick Schmalstig Note Added: 0004300
2016-09-06 02:42 Chris Graham Note Added: 0004305
2016-10-19 16:52 Chris Graham Note Added: 0004443
2017-04-10 17:36 Chris Graham Relationship added related to 0002356
2017-04-10 17:38 Chris Graham Note Added: 0004983
2017-04-10 17:40 Chris Graham Note Added: 0004984
2017-05-01 16:03 Chris Graham Category core => core_database_drivers
2018-02-09 19:12 Chris Graham Summary Deprecate MyISAM; use INNODB => Deprecate MyISAM; use INNODB (on hold)
2019-11-14 22:12 Chris Graham Tag Attached: Roadmap: v12