View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 2796 | Composr | core_database_drivers | public | 2016-08-24 17:14 | 2019-11-14 22:12 |
| Reporter | PDStig | Assigned To | Guest | ||
| Priority | normal | Severity | feature | ||
| Status | new | Resolution | open | ||
| Summary | 2796: Deprecate MyISAM; use INNODB (on hold) | ||||
| Description | Currently, 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 Information | Switching 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 . | ||||
| Tags | Risk: Database change , Risk: Deprecates functionality , Roadmap: Over the horizon | ||||
| Attach Tags | |||||
| Time estimation (hours) | 0.5 | ||||
| Sponsorship open | |||||
|
|
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. |
|
|
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. |
|
|
Actually row-level locking is a huge advantage of InnoDB, so I would recommend it for any large site. |
|
|
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. |
|
|
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). |
|
|
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). |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2016-08-24 17:14 | PDStig | New Issue | |
| 2016-08-24 17:14 | PDStig | Tag Attached: Risk: Deprecates functionality | |
| 2016-08-24 17:16 | PDStig | 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 | PDStig | 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 2356 |
| 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 | |
| 2024-03-26 00:58 | PDStig | Tag Renamed | Roadmap: v12 => Roadmap: Over the horizon |
