[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 09/05/2007 04:27 PM, David Rees wrote:
> On 9/5/07, Michael T. Dean <mtdean at thirdcontact.com> wrote:
>   
>> On 09/05/2007 04:36 AM, David Rees wrote:
>>     
>>> 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?
>>>       
>> Talk to the distros that are currently in use.  More than that, go out
>> and talk to the people who are still running MySQL 3.23 or ...  You may
>> want to volunteer to upgrade and support their databases.  Otherwise, it
>> may seem a bit demanding of you to force them to upgrade.
>>     
> Who's still running 3.23? What distros? RHEL 4 ships with 4.1, I can't
> imagine anyone wanting to run MythTV on anything older than that.
>   

I know that up until very recently, at least, a couple of the devs were
still using 3.23.  I think they've since upgraded anticipating that
we'll pull the plug on 3.23 (perhaps more) after the 0.21 release.

>> Besides, technically, 4.0 and 4.1 are both supported.  See
>> http://www.mysql.com/company/legal/lifecycle/#calendar
>>     
> 4.0 is "Actively" supported until the end of this month. Are there
> really any significant number of users using anything older than 4.1?
>   

That I can't tell you.  I'm definitely not "in the know" about distros.

>>> My day job involves writing backend code for high performance
>>> websites. While I don't need to write database independant code, I use
>>> tools (Java and Hibernate) that allow me to swap backends with a
>>> minimal amount of fuss. With Hibernate, all I have to do is swap out a
>>> couple configuration lines and the migration is complete, no code
>>> change required.
>>>       
>> You're doing the right thing by using Hibernate, but IMHO, blindly
>> pursuing "database independence" is far worse than tying yourself to a
>> single DB engine.  Code is expensive, so code that you don't have to
>> write (i.e. code provided by some database-specific functionality) is a
>> good thing.  Just like using code provided by Hibernate is a good
>> thing.
>>     
> Definitely. Using Hibernate allows me to write database independent
> code without even having to think about it. The Hibernate team does
> all the hard work for me.
>   

Yep.

>>  Creating your own lesser quality code--lesser quality not
>> because of your skills, but because there's no way you have the
>> time/budget to spend on creating something from scratch that's as good
>> as Hibernate--would be just plain stupid (or arrogant).  But, the
>> argument for Hibernate (or, at a lower level, JDBC or ...) isn't
>> database independence, it's code reuse.  The only way that the argument
>> for Hibernate could also include database independence is if there's a
>> business requirement for database independence.  Have you ever even
>> asked what the business requirement for database independence is?  (And,
>> if the business requirement falls into the "insurance" category (in case
>> it's too expensive to renew the license, in case the DBMS vendor goes
>> out of business, in case the DBMS vendor doesn't provide timely support
>> for Windows Server 2008, ...), that's something that Open Source
>> software doesn't have to worry about--so, I don't worry about it.)
>>     
> The primary reason I use Hibernate is that it lets me get away from
> thinking about the database and SQL. We can write code faster that is
> of higher quality using Hibernate. As a bonus, Hibernate also allows
> me to use different databases should it be required or desired.
>
> That is something _any_ project should be worried about, whether it's
> Open Source or not.
>
> This is something you yourself is saying MythTV is headed towards -
> abstracting away the database! I asked it before - certainly a tool
> similar to Hibernate exists for C++? Why build your own database
> abstraction framework - that's something that has been reinvented
> plenty of times before.
>   

And I'm sure any frameworks that are license-compatible and that could
fulfill our needs would be seriously considered.  Suggestions, anyone?

>>> Using Hibernate, I've personally swapped backends between MySQL,
>>> Sybase and Postgres without changing a line of code.
>>>       
>> However, before doing that, how much testing did you have to do?  3x the
>> testing?  9x the testing?  When you're working on a project for fun,
>> increasing the amount of work required for the most tedious, boring, and
>> unappreciated part of the job is not something most want to do.
>>     
> No more testing than I would have done for any other release. That's
> what testing frameworks are for. Any half-way serious project should
> have some sort of automatic testing framework.
>   

And each developer should have MySQL (various versions), PostgreSQL, ...
installed on their networks (complete with a means of copying valid
(and/or invalid :) data across to each) to be able to test any DB-access
code changes?

As Jay mentioned, too, coming up with regression tests for Myth has
generally been considered "too much."  We're talking about needing files
that are in the multi-gigabyte range to represent some of the data
(recordings) Myth deals with.  And, somehow, the various
broadcasters/rebroadcasters still find new and interesting ways to break
the specs.  :)

Sure, we could set up a framework for testing other non-video/audio
portions (such as database stuff), but as you said, "Any half-way
serious project should have some sort of automatic testing framework." 
IMHO, Myth isn't about serious.  It's about fun.  (So, I guess that
means that the users are our "automatic testing framework." :)

>> But, wait, shouldn't you all be writing the code so it can work on
>> Oracle and DB2, also?  Shouldn't you write it so it works with Derby and
>> Firebird and Ingres and ...
>>     
> With Hibernate all of those are covered.
>   

Assuming you've covered all the SQL differences.  Any time you're
talking about a call-level language, the interpreter/compiler of the
commands becomes relevant.  Sure, maybe you're not using Native SQL
(i.e. which is useful, "if you want to utilize database specific
features"), but I'd love to see the BUSQ (Big, Ugly Scheduler Query)
done without native SQL.  (Sure, it could be done, but would probably
require a /significant/ amount of supporting code that moves finding
positives for scheduling from the DB to the Myth code.)

>>> Certainly something similar exists for C++? I'm not much of a C/C++
>>> programmer so I've never looked, but I have used perl DBI libraries
>>> for similar jobs (though none very big).
>>>       
>> Feel free to write support for other DB's into Myth.  Even if the devs
>> don't accept your patches, you have the option to fork (and then
>> maintain and test the DB independence on your own).
>>     
> If I only had the time. :-) My main point is that hopefully this is
> something that has been thought of and analyzed properly. Even if
> whatever database abstraction framework is chosen doesn't give you
> multiple DB support, at least hopefully it will make the task of
> embedding MySQL or "hiding" MySQL easier without having to reinvent
> the wheel.
>   

And that's a good point.  And, I'm sure the devs will seriously consider
any reasonable solution that someone brings forth--especially if that
someone is also able to bring experience/skills and potentially help
with the work.

Mike