[mythtv-users] Slow MySQL query after delete
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
> > 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
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.
More information about the mythtv-users