InnoDB about 20x slower than MyISAM when creating or deleting pages

[mestaritonttu]

This has been confirmed in two separate forum threads.
Thread with testcase
Most recent thread

[LostKobrakai]

May I ask what processwire should do about that? Seems to me like that's an issue with mysql.

[mestaritonttu]

Well, it's a bit much to ask, but it seems like PW's database would have to be redesigned in order for InnoDB perf to match MyISAM .
The SE topic adrian linked to from the thread with the testcase illustrates the nuances of the matter.

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

[LostKobrakai]

So the issue of the topic you linked were duplicated indexes on the same column(s). Processwire doesn't have such indexes. To me this still sounds like MyISAM is just faster because of it's architectual differences to MyISAM. So we'd have to find out if we can even expect similar performance in the first place and if so what exactly is causing the slowdown and if that's something we can mitigate in a reasonable way on processwire's side.

[teppokoivula]

Based on my (very limited) understanding of the differences of MyISAM and InnoDB, there are a number of factors that can affect the performance comparison – at least these:

• Type, number, and other specifics of the indexes
• Datatypes
• Queries (and thus data architecture)
• Database or table settings, such as ROW_FORMAT

Honestly I have no idea if we could tweak something – and what – to make InnoDB perform better, but it does seem like it should at least be investigated a bit before drawing the conclusion that "InnoDB is slow, deal with it".

20x sounds like a major bottleneck. I've heard before of the differences in the scale of 1.5-2x, though :)

[ryancramerdesign]

InnoDB can be a lot slower with some mass operations like emptying a very-full trash, or performing mass imports, etc. It really has to be, given the nature of it (just take row-level locking as an example). But for everyday site operation, performance should be strong, and I'd think for most the upsides outweigh the downsides. On the other hand, if you need to perform these mass operations every day (the ones where InnoDB can be slow) and they aren't something that can happen in the background, then no doubt MyISAM may be a better choice for that case. But if you are doing these things on a live site, then consider that the MyISAM version may bring the rest of the site to a halt, while the InnoDB version might take longer, but not affect the performance of the site to everyone else. It's a good compromise in my mind. I'm not aware of any specific things we can change to make InnoDB perform like MyISAM on those mass operations, but I've also expected that with the benefits of InnoDB also come the overhead of time to perform them. For most PW users, I think these time consuming mass operations are rare, so don't see it as a reason to avoid InnoDB.

[mestaritonttu]

Ryan's reply prompted me to investigate further on how to play better with InnoDB. To my delight I discovered I could import the 35,5k page test case CSV into an InnoDB PW in mere 2 min 44 sec after I edited Batch Child Editor's code to use transactions! So this actually halves the time MyISAM spends on the import.

Concretely, in the .module file before the block
foreach($childPagesArray->data as $row) {
I put

$engine = strtolower($this->wire('config')->dbEngine);
if($engine == 'innodb') {
    $this->wire('database')->beginTransaction();
}

And after the block I put

if($engine == 'innodb') {
    $this->wire('database')->commit();
}

This leaves us with the trash emptying. Ryan: would you consider adding such logic to the trash emptying?

From what I read, perhaps there is a need for having a more sophisticated logic, splitting to blocks of 1000: https://mariadb.com/kb/en/library/how-to-quickly-insert-data-into-mariadb/#inserting-data-with-insert-statements

The reason why you may want to have many BEGIN/END statements instead of just one is that the former will use up less transaction log space.

[teppokoivula]

Just for the record, the optimizations mentioned above by @mestaritonttu are similar to the ones I ran into as well while skimming through a couple of InnoDB optimization articles. Thanks for putting up a test case!

It does indeed seem that there are things we can do to speed up at least some "special cases", such as emptying the trash or importing/exporting large number of rows :)

[adrianbj]

I am certainly happy to add those transaction bits to the BatchChildEditor module. I was just waiting to get @ryancramerdesign's take on things.

I also wonder if my TableCSVImportExport will benefit from this. Also @kongondo's FieldtypeMatrix? And I bet there are others as well.

[mestaritonttu]

One of the InnoDB articles is this by Ryan himself :) https://processwire.com/blog/posts/using-innodb-with-processwire/

In the article, note the try catch with $database->rollBack(); upon exception.

By changing how BatchChildEditor works, we can also work around the trash emptying slowness, if we use BCE exclusively for mass deletions.

[LostKobrakai]

One of the InnoDB articles is this by Ryan himself :) https://processwire.com/blog/posts/using-innodb-with-processwire/

In the article, note the try catch with $database->rollBack(); upon exception.

To be fair, that's less about performance and more about one of the selling points of InnoDB over MyISAM: Being able to garantee that the database stays in a consistent state even if something goes wrong between multiple dependant changes. I actually would like to see processwire use transactions more in the core to prevent e.g. partially saved pages in the face of errors. But they're certainly not there for performance optimization reasons.

[ryancramerdesign]

I've added a $database->supportsTransaction(optional $table = ''); method if it's helpful for any of this. I experimented with wrapping the trash deletion in a transaction, but not seeing any difference here so didn't commit it yet. I hadn't really ever noticed the performance issues described, though maybe just don't have a project with the right scale to see it at present. I do have one dev site with 500k pages in the trash, which is my current "trash test" install, but thus far have not been able to successfully delete them regardless of DB engine or transactions. :)

Also, PW page manipulation/deletion actions usually involve not just database but file system, which makes transactions not quite as useful, but I'm working on a solution for that (transactions for files basically).

[mestaritonttu]

I experimented with wrapping the trash deletion in a transaction, but not seeing any difference here so didn't commit it yet.

Can you share a diff? I could give feedback on the perf. Great to hear about you tinkering with this.