[mythtv-users] Slow MySQL query after delete

[Jay R. Ashworth]

On Wed, Sep 05, 2007 at 01:02:07PM -0400, Tom Dexter wrote:
> I've never had a problem with the fact that MythTV uses MySQL.  I
> don't understand the assumption that you have to constantly optimize
> and fix the database.  While I backup my mythconverg every night, I
> never optimize it.  At my last job a large number of us were coding an
> application that used MySQL.  In five years with many people
> developing, testing, and demoing with large amounts of data, I'm not
> aware of anyone ever having to repair or optimize a single MyISAM
> table, even though virtually none of the machines where on UPSs.  I
> personally have never had to even once.  We were all using with 4.1 or
> 5.0.  Granted, these rarely had heavy multi user access...but neither
> does MythTV.  By the way...we ended up having to support both MySQL
> and DB2.  I wouldn't wish multi-database support on the MythTV devs,
> I'll tell you that.

You've been stunningly, stunningly lucky.  And I say that as someone
who's spent the last 25 years 50/50 between 4GL apps design and coding
and desktop/server support.

> I'd be very surprised if embedding MySQL in a new MythTV data service
> would prevent you running the same standalone version of MySQL...in
> fact I'm sure it could and would be coded in a way where that would
> not be the case (just as you can have one version of shared ffmpeg
> libraries with another program that has older ffmpeg code compiled
> in).

Sure, but that wasn't my real issue.

> Having said that, I certainly wouldn't like the embedded MySQL route
> at all.  I understand the motivations...being able to use a specific
> version of MySQL without having to force users to run any specific
> stand alone MySQL...and I'm certainly not the one who has to code and
> support what's there now, so I can't gripe much in any case.  I just
> love being able to get to the database directly.

Correct; that's the issue.  We now have a frontend facility for
renaming recordings... but we didn't used to.  If the data moves beyond
the reach of PHPMyAdmin... what do we do?

> One of the only database issues I've had with MythTV was a handful of
> 'record' records that apparently got stuck in a 'deletepending'
> status, resulting in a number of livetv recordings that wouldn't go
> away.  I was able to correct it by setting the deletepending flags to
> 0 and restarting the backend (causing it to immediately expire them).
> What would happen in that situation with an embedded database?  Would
> I be able to fix it?  If so would it require actually learning the
> interface to the new data backend rather then just using SQL?

Odd.  We have one duplicate recording (shows up twice everywhere, but
only one MPG file) which might have a similar genesis.

> I sure hope the "if they don't see the database they won't complain"
> rationale isn't a big part of the decision...at best that's a poor
> reason and at worst it's frankly a bit insulting.

Depends on your audience.

My thing is "don't help the stupid people by penalizing the smart
people".

Cheers,
-- jra
-- 
Jay R. Ashworth                   Baylink                      jra at baylink.com
Designer                     The Things I Think                       RFC 2100
Ashworth & Associates     http://baylink.pitas.com                     '87 e24
St Petersburg FL USA      http://photo.imageinc.us             +1 727 647 1274