[mythtv-users] 0.27: Stalls and other niggles - diagnosed

[Gary Buhrmaster]

On Sat, Jan 18, 2014 at 8:19 PM, Jay Ashworth <jra at baylink.com> wrote:
...
> Why is it again, precisely, Mike, that we *don't* use InnoDB?

I am not a MythTV dev, so one is free to ignore my response, but....

As I recall (and yes, I actually looked at the codes at one
point in the past) that some of the constructs currently used
by MythTV were known to potentially have compatibility issues
with InnoDB (sometimes only edge cases, and sometimes only
with certain other configurations).  Locking was one possibility
at the time I looked (the implicit locking of the entire table by
MyISAM may have been presumed somewhere).  And there
was also the fact that MyISAM performs reads much faster.  And
interestingly, MyISAM requires far less tuning/administration
than InnoDB, which means "out of the box" it (mostly) just works.
InnoDB will perform like crap out of the box (with almost any distro
defaults).  Another issue was that not all distros were shipping
InnoDB (at one point).  And another was that depending on
what version of InnoDB was being shipped, some features did
not exist.  Of course, for every example, there are counter
examples, and I believe one of the mythweather tables
used a construct that required InnoDB (and not MyISAM).
A particular compatibility issue that I recall is that a
SELECT COUNT(*) performs really poorly on InnoDB if one
did not use a WHERE clause (and yes, MythTV did that, or at
least did it at one point I think).  I think there were some other
issues too, but those are the ones I recall at the moment.
Some (perhaps many) of the potential issues have been
addressed over the years, but AFAIK no one has reviewed
the details recently (nor carefully).

Anyone is always free to convert to an unsupported MySQL
engine (the "you break it, you own it" solution).  As I said
in another thread, I have broken my DB and converted to
InnoDB.  I will never ask on this list for support for that DB
or the system.  That is the "you own it" agreement.  I also
have a lot of experience with tuning databases (and depending
on the date, my MySQL DB was even remote on an enterprise
(battery backed raid/ecc) system during some periods of time,
along with various disk targets).  It works for me, but not without
understanding.  I would never recommend anyone else choose
that path, but anyone is, of course, free to consider it.

There is a long term goal to embed the DB into MythTV
(so that all the required adjustments and tuning are done
automagically for you).  That would be the time to review
all existing uses to insure InnoDB compatibility.  That review
is complicated because the business logic is in the code,
not the DB, so implied constraints have to be understood,
so this is far more than just a DBA (only) exercise regarding
engine selection.  AFAIK there is no schedule/resources
currently allocated to this (one of the many) desired tasks
for the MythTV project.  Too many tasks, not enough
resources.  From my (limited) perspective, some of the
most interesting (known) tasks are what are considered
"large" ones.  The low hanging fruit has mostly disappeared.
Now it requires people who really know the code and the
implication of those codes to do the review and work.
So improvements take time.

Now, if one is volunteering to review the existing codes
"now" for InnoDB compatibility, I cannot see that being
a bad thing.  Documenting issues (and providing patches
if possible) would help whichever dev eventually takes on
the DB conversion.