[mythtv-users] Slow MySQL query after delete
Jay R. Ashworth
jra at baylink.com
Wed Sep 5 18:37:18 UTC 2007
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
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
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
More information about the mythtv-users