[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 09/04/2007 04:34 PM, f-myth-users at media.mit.edu wrote:
>     > Date: Tue, 04 Sep 2007 12:01:06 -0400
>     > From: "Michael T. Dean" <mtdean at thirdcontact.com>
>
> To take a contrary view, with my tongue only partially in-cheek,
> and with the hope of getting actual information and -please- not
> a flamewar:
>
>     > The high-level overview is that we're going to move to an embedded MySQL
>     > (if the users don't see a DB, they can't complain about it) that can be
>
> But they can still be shafted by its misbehavior---while being less
> able to talk coherently about why things are going wrong.  And if
> things like phpmyadmin become unusable, it becomes even harder to
> poke around while debugging.
>   

Just like they can be shafted by the misbehavior of ffmpeg's libav*
libraries, right?  But, no one has complained about the fact that MythTV
doesn't use the system ffmpeg libs, but instead uses its own customized
"embedded" version of those libs.  Both are complex creatures that can
be configured in such a way that they work well in a MythTV application
or configured in such a way that they won't work that application.  By
embedding the support into MythTV, the users don't have to worry about
the configuration.

In theory, after the switch to embedded MySQL (and enough time for users
to "forget" about the old way), the MySQL should be just as much a
"background" item in Myth.

Also, I think (with no data to back up my theory) that more problems
have been caused by users having easy access to the data/database than
have been solved by it.  You have no idea how many times reading
messages on this list sends a chill through my spine.  (Seeing
everything from suggestions/"solutions" that will cause minor
issues/inconveniences/breakages to database time bombs, such as encoding
"fixes" that will cause data corruption on database upgrade--often
touted by people who didn't take the time to learn MythTV's i18n/L10n
approach and found that changing encodings on MySQL tables "fixes" the
problem).

Regardless, even MythTV with embedded MySQL will still be open source,
so if someone really wants to mess with the Myth Data, they will be
provided /all/ the required tools (and will just have to invest some
time/effort into learning a different approach or find someone who can
do so for them).

> E.g., while it might remove certain misconfiguration bugs, it also
> means users might not be able to debug certain issues as effectively
> if too much is "under the hood".  Without either scheduler or schema
> changes, it seems to me that this wouldn't actually fix the "slow
> queries" issue unless the embedded MySQL magically has vastly superior
> performance to regular MySQL, which seems unlikely.
>   

Basically, one of the biggest benefits is that we could move all efforts
to supporting embedded MySQL 5.5 or whatever and could drop support for
MySQL 3.23, MySQL 4.0, MySQL 4.1, MySQL 5.0, ...  There's a /lot/ of
performance-draining "backwards compatibility" in the code we have now. 
If all users had the same DB engine, things would be a /lot/ easier.

> [I must confess to never having seen a clear explanation of why
> embedded MySQL is on the roadmap, but we're talking about at least
> two years of traffic on the -dev list and no doubt I've forgotten the
> crucial thread; presumably you can send some cogent pointers. :)

Not without searching the archives myself.  :)

>  Ditto
> the vehement objections to making Myth interoperable with non-MySQL
> implementations at all, e.g., the rather unfortunate reactions when
> someone a few months ago was trying to allow migration to Postgres,
>   

Vehement objections by, er, the people who would have to maintain--and
worse--test all possible configurations.  No objections from the users
who at most write some patches for MythTV.***  Hmmm.  Perhaps there's a
reason for that.  (And that doesn't even go into the "all databases
speak SQL" lie.)

Maintaining support for all the "shipping in standard distributions"
versions of MySQL is hard enough.  Add in totally different DB engines
and things get exponentially worse.  Does the company you work for spend
the money to make truly DBMS-independent applications?  So, why should
Myth do so?  (If the answer to the first question was, "Yes," then
nevermind--I won't be able to catch you up. :)

***I fully admit to being one of the users who simply writes some
patches for MythTV (though I do have objections to adding support for
other DBMS's).  Doing so is nothing compared to the work the devs must
put in.

> 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.]
>
>     > configured and optimized automatically and have a "mythdata" server-type
>     > program that provides all access to the data so clients do not need to
>     > use SQL.
>
> ...but would, I hope, still have the -option- to use it, in cases
> where they are extending Myth's functionality in ways that are either
> unanticipated by the devs, or appropriate only for one particular
> site.  (E.g., we have automation that uses Myth as a frontend for
> its own work, but nobody else needs what we're doing.)  Lacking this
> sort of access, the last non-embedded Myth would be the last one we
> could use.

If I were writing the support, I wouldn't allow direct access to the DB
or direct SQL execution.  Instead, I'd expect "they" (who are extending
Myth's functionality) to make the extensions usable by Myth or other
clients by modifying the data server portion of Myth...

But I--who knows nothing about embedded MySQL--won't be writing this
support, so nothing to worry about (yet :).

Mike