"when" you need the unique features of InnoDB (i.e. a "why?" answer) then it is better than MyISAM.

Dang it, @phranque, I think you must have written the articles I've been reading! LOL

I guess the thing is, how do I know when I need it? It's like a teenager comparing an automatic transmission to a stick shift: if you've always had stick then you don't know if you "need" an automatic.

I know that InnoDB has row level locking while MyISAM only does table level locking. So I'm imagining that if you have a "lot" of inserts then InnoDB is going to be slightly faster, right? tuning-primer.sh says "Current Lock Wait ratio = 1 : 204", but it doesn't show which tables have that high of a ratio. I have clients using Wordpress with God-only-knows how many tables in each, I have over 100 of my own custom tables... any one of them could have a high ratio that's throwing off that ratio.

So how do I track it down?

The slow query log includes table lock wait time for each query. You could use that to find opportunities where InnoDB could perhaps be faster, then run benchmarks to put that to the test. If you don't have performance issues and you don't miss any database features, I wouldn't waste time on this.

InnoDB is now the default engine, I believe, so you probably already have some InnoDB tables.

I found this in mysqltuner.pl:

Data in MyISAM tables: 8G (Tables: 624)
Data in InnoDB tables: 39M (Tables: 25)

So I'm pretty sure that the InnoDB tables are going to be the ones created by Wordpress, because I wouldn't have done that myself. And the MyISAM tables are probably the ones that I use on my sites.

But OK, I'll do some digging and see what I see. Thanks for the help!

For the sake of posterity, the "when" that I've found in the last 8 months is "when the number of inserts greatly outnumber the number of selects".

So InnoDB, in theory, makes sense for logs. An example might be if you move your PHP error log to MySQL instead of text (I do so that I can use UNIQUE and eliminate duplicates).

BUT!

I did this for a few log files, and then in May 2021 I had some sort of problem with InnoDB that crashed MySQL (well, MariaDB 10.3) and caused me to permanently lose about half of my data! By which I mean, all tables that were named beyond the first InnoDB table alphabetically* were just completely gone.

(Eg, the log table was named "error_log", so all tables beginning with f-z were deleted)

I never saw any type of performance or speed gains from using InnoDB, so while in theory it may be faster, that didn't translate to real-world usage. I cannot recommend using InnoDB at this time, there appear to be no real advantages and only disadvantages.

Innodb has real database features like ACID, foreign keys, transactions, commit etc. Also crash recovery is much better than myisam. Logical backups are supported by innodb without locking the server.

So inndb is much more mature product but perhaps for a wordpress site myisam is enough.

Can you suggest when those features (ACID, foreign keys, transactions, commit etc) would have practical uses in a real-world environment?

I do not use Wordpress, and some of my sites have a pretty good amount of traffic (around 5 million monthly pageviews, excluding bots). But I saw no performance issues between InnoDB and MyISAM at all.

You think mainly in terms of performance, those features are more about consistency and reliability of the data. That's why innodb can be slower than myisam, those features are more overhead for the database engine.

You can read more about transactions here [en.wikipedia.org...] Just google the other features to find real world use cases. You can not build a banking system with myisam.

I switched to innodb mainly because of online DDL (alter table without downtime) and the famous long select query/short update query which locks the whole table.

For the sake of posterity, the "when" that I've found in the last 8 months is "when the number of inserts greatly outnumber the number of selects".

So InnoDB, in theory, makes sense for logs. An example might be if you move your PHP error log to MySQL instead of text (I do so that I can use UNIQUE and eliminate duplicates).

Logs will result in insertions at the end of the table, and without constraint check. So, MyISAM table will be a better choice for this usage. InnoDB is better when inserting rows anywhere within a table, and when there are constraints.

sorry, this isn't an answer to your question as such ... however i've found that mariaDB are really helpful ... get on their mail list and register to go to their seminars ... i don't know where they have them although i know they do in london ... you get the usual free lunch and tea as well as the chance to actually talk to the developers - who are very open to all types of questions.

InnoDB supports transactions, granular locking and a buffer pool. MyISAM development has stopped. Probably MariaDB still fixes important bugs, I seriously doubt that MySQL does. So it's easier to say when you should use MyISAM. And it's not even that easy, because in 2021 there aren't really many use cases...

The requirement is that you use MariaDB. I wouldn't use MyISAM with MySQL, not matter the situation.

* You run a hosting. You probably don't have resources for proper redundancy. If an InnoDB table is corrupted, the whole server is not usable by customers until you repair it. If a MyISAM table is corrupted, everything except that specific table is still usable. You should be able to repair the table without stopping MariaDB.
* You have a huge amount of read-only data and other compression options are not enough for you. You can use MyISAM compressed tables. This is not a common situation. I'm linking a description of this use case from a former Booking.com DBA, if you work for a medium-small company this doesn't apply to you: [jfg-mysql.blogspot.com...]
* SELECT COUNT(*) without WHERE is slow on InnoDB, because InnoDB will access and count all rows. MyISAM has the number of rows written in tables metadata. Probably you don't need that kind of queries. If you do, probably you can use an approximation, which is written in system tables.
* Some range queries are faster on MyISAM. But adding proper indexes usually makes queries fast.