[mythtv-users] Slow MySQL query after delete

[Michael T. Dean]

On 11/29/2007 07:55 PM, David Rees wrote:
> On Nov 29, 2007 4:41 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
>   
>>>   It is very frustrating to
>>> sit and wait up to 10 seconds after every delete (from either the frontend
>>> or mythweb).
>>>
>>> I am open to suggestions as to how I can improve this situation.
>>>       
>> Do you have your MySQL database on the same drive as your recording
>> disk?  If so, that's the first thing you should consider fixing.
>>     
> It is completely unreasonable to expect someone to have a multiple
> disk system to get decent usability from MythTV.

IMHO, it is completely unreasonable to expect Myth to be able to make
your kernel/filesystem driver/hardware be able to do something that your
kernel/filesystem driver/hardware is unable to do.

>  I would venture to
> guess that the number of single disk systems far outnumber the number
> of multi-disk systems

You do realize that Myth uses a /lot/ of storage space, right?  Do you
really think someone buys a new 750GB hard drive, then throws away the
300GB hard drive she was using for Myth?  Or buys a 300GB hard drive and
throws away the old 80GB hard drive?  You do realize that most (all?)
motherboards come with more than one disk connector, right?

>  - and those that are using multi disk systems
> are unlikely to dedicate an entire disk to the system/MySQL database
> as this would likely lead to a large amount of wasted space given the
> size of disks these days.
>   

I have 2 dedicated backends.  Each has a disk with a 16GB partition for
the system.  The rest of the disk with the system partition is not used
for (active) recording, but only for archived storage of recordings. 
It's all just a matter of configuration.  I used to have a system with a
16GB HDD for my system partition with all recordings on other disks.

Also, you do realize that MySQL is a /network-capable/ database
management system, right?  You don't even have to put MySQL on either
backend.  It could be on the dedicated frontend.  It could be on another
computer somewhere in the house. ...

> As suggested multiple times by others, the first thing to try is to
> convert the tables in mythcoverg to InnoDB from MyISAM. If that isn't
> sufficient, we'll go from there.

Fine.  I really couldn't care less what he does first.  But my point is
that he should be trying to fix the MySQL configuration, not blaming the
BUSQ/arguing with me that the scheduler query is a problem when his
system executes the query in the same amount of time as mine and my
system does /not/ have the "entire system locks up when I try to delete
a recording" issue his has.  Also, it doesn't really help that he's
refusing to try slow deletes because he has a super-fast XFS filesystem
and he's smart enough to know that a slow delete (that will cause the
removal of recordedseek/recordedmarkup entries to be delayed about 2min
10sec per GiB of recording) will have no effect on performance of his
deletes.

In other words, my system seems--to me--to be proof that a system that
takes 6-7 seconds to execute the scheduling run does not lock up when a
recording is deleted.  Perhaps I'm just naive, though, in thinking my
system acts like a properly configured Myth system.  Maybe I've
misconfigured my system and gotten unreasonably good performance because
of it.

Mike