[mythtv-users] Slow MySQL query after delete

[f-myth-users at media.mit.edu]

    > Date: Tue, 4 Sep 2007 14:03:04 -0700
    > From: David Brodbeck <gull at gull.us>

    > On Sep 4, 2007, at 1:34 PM, f-myth-users at media.mit.edu wrote:
    > > ...e.g., the rather unfortunate reactions when
    > > someone a few months ago was trying to allow migration to Postgres,
    > > which -doesn't- seem to require the daily table-fixing cargo cult
    > > ritual to stay operational and performant, and doesn't seem to get
    > > the same sorts of snickers from database people that MySQL does.]

    > I dunno.  Different isn't necessarily better.  I run the repair/ 
    > optimize script weekly and at each reboot, on my box, and it works  
    > fine.  I've never seen it find a problem, even after an unclean  
    > shutdown, but I run it just to play it safe.  IMHO often the repair/ 
    > optimize script is a solution looking for a problem.  It's like when  
    > tech support people tell you to unplug a problematic device and plug  
    > it back in.  Most of the time it doesn't help, but it does just often  
    > enough that it's the first thing people try.

There are two issues here; one is crashed databases (which seem to
bite people a lot here, but it's probably because all the random
screwy devices Myth users have tend to lead to crashed machines at
inopportune times), and one is performance.  Certainly MySQL's
performance degrades fairly steadily in between optimizations when
Myth is using it; obviously I have no idea if Postgres would do
likewise, since I can't test Myth under Postgres.  (My claim of
performance degradation is based on how long common actions and/or
several repeats of the same query [to precache, etc] take with a
DB that hasn't been optimized in a few weeks vs one that was optimized
yesterday, etc.)

Actually ("nobody ever expects the..."), the -third- issue, and the
one I was really alluding to, was the unexpectedly heated reaction
when someone said, "I want to use a different DB; here are some simple
things to do that would enable using any DB that complies with certain
standards, and here are patches to start implementing that," and got
totally stomped on.  He basically submitted one set of patches and
then vanished, having apparently decided it wasn't worth his time
getting yelled at for something that was eventually totally squashed
by fiat, patches or not---"we'll reject any patches to use anything
other than MySQL, even if they work," was my impression.  Can't say I
blame him.  But it -was- a peculiar reaction...  Sorta reminded me of
the -months- the softpadding guys spent trying every possible way to
appease every possible objection ("do it this way! no, the other way!
no, the first way again!") and then had their work rejected after all
that work anyway.  It looked pretty discouraging, viewed from the
outside.

But I'll stop here; I'm -not- trying to incite a flamewar.

    > It's been a long time since I ran a Postgres database, but I recall  
    > having to run a 'vacuum' script on it on a regular basis to keep it  
    > running properly.   So, probably six of one, half a dozen of the  
    > other.  I can run VACUUM, or I can run OPTIMIZE TABLE.  I don't  
    > really care which it is.

I don't, either, but one of the issues that came up with the whole
scheduler-causes-locking-causes-video-data-loss was the way MySQL was
handling issues of contention, locking, and so forth, and it was my
impression that it was doing things in a way that Postgres would have
handled better.  (I'd have to go back through that thread to confirm
exactly what that was, though.)  This didn't directly address the bug
that was fixed (e.g., don't allow threads dumping video to get hung up
on the scheduler in the first place, no matter how well or badly that
query was doing), but it would have helped.

    > One thing I've noticed about relational database people is they all  
    > develop a religious fervor for their own favorite database, and  
    > consider using any other to be the most horrible form of heresy.  I  
    > take all claims that one database engine is superior to another with  
    > a big grain of salt. ;)

Indeed.  And I think a lot of the snickers are due to MySQL's history
("we don't see how to do transactions quickly, so we'll omit them and
still claim to be a concurrent database", etc), and maybe the snickerers
aren't totally up-to-date on current MySQL (where they've been forced
to revisit some of those early decisions), but really, it's not changing
-that- fast, and for a long time Myth couldn't even -use- the most up-to-
date MySQL anyway. :)  But of course I can't rule out having tripped over
a biased sample of who-I-happen-to-have-discussed-databases-with, etc.

I'd still like to know what's up w/embedded MySQL, though.  Most of
what I've seen on the -dev list seem to be "we discussed this privately
and have concluded what we should do", but the rationale and methodology
don't seem to have made it back onto the public lists, unless I've just
missed them, which is certainly possible.