[mythtv-users] Slow MySQL query after delete

David Rees drees76 at gmail.com
Fri Nov 30 08:55:51 UTC 2007


On Nov 29, 2007 6:38 PM, Michael T. Dean <mtdean at thirdcontact.com> wrote:
> On 11/29/2007 07:55 PM, David Rees wrote:
> > 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 don't know, my system has been running for at least a couple years
now with a single disk (upgraded a couple times after disk failures),
a single CPU (started with a Duron 800, now a Sempron 3000+). Right
now I have a single 300gb ATA drive. And I haven't any issues with the
BUSQ screwing things up or slow deletes messing with things or hanging
the UI for more than a second or two. I'm even running ext3 which is
notorious for slow delete performance, but thanks to the slow-delete
option, it works just fine. On a previous disk I ran jfs which also
was fine.

> >  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?

Now you're just being condescending.

> >  - 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.

So does each of your backends have 2 disks? No? Just separate
partitions? Tsk tsk...

> > 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.

Isn't that what I suggested by switching MySQL from MyISAM to InnoDB
tables? You seem to be insisting that he needs multiple
backends/frontends/disks and a dedicated MySQL server to run smoothly.

> 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.

I'm confused by what you are trying to say there. How much of that was
said with tongue in cheek?

> 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.

I suspect your mythconverg database is using InnoDB instead of MyISAM.
BTW, you'll have to leave the nestitle table on MyISAM because InnoDB
doesn't support full-text indices.

Anyone having issues with long frontend hangs during deletes, please
try it and see what happens.

-Dave


More information about the mythtv-users mailing list