[mythtv-users] Slow MySQL query after delete
digitalaudiorock at gmail.com
Wed Sep 5 17:02:07 UTC 2007
On 9/5/07, Jay R. Ashworth <jra at baylink.com> wrote:
> On Wed, Sep 05, 2007 at 01:36:11AM -0700, David Rees wrote:
> > On 9/4/07, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> > > 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.
> > You really should think about dropping support for anything older than
> > 4.1. What's the point in maintaining anything that the upstream vendor
> > doesn't support themselves?
> So that my point (from another part of the thread) is clear:
> I can live with them not ever making it possibel to run atop PgSQL.
> I would be *much* less happy if they switched to *only* being able to
> use *Embedded* MySQL (as opposed to the standalone version --
> *particularly* if it made it impossible to use said standalone version
> on the same machine -- and I am clearly not alone.
> (MySQL is pretty promiscuous about which my.cnf to read; it can cause
> problems trying to run 2 on one box.)
> -- jra
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.
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
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.
My big question is weather or not it would improve much beyond easing
development. I doubt that it would improve reliability much. Others
have mentioned database issued caused by mythbackend crashes. When
the mythbackend crashes, it's never going to corrupt tables...what it
can do however is cause partial updates where two or more tables get
out of sync. The only thing that prevents that is nesting multiple
updates in transactions. Unfortunately with MySQL, transaction
support means using one of the database engines that have been bought
up by Oracle...yuk...don't even get me started on that whole travesty.
At the job I'm going to soon we're considering PostgreSQL for that
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?
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.
More information about the mythtv-users