[mythtv-users] Slow MySQL query after delete

f-myth-users at media.mit.edu f-myth-users at media.mit.edu
Fri Nov 30 09:35:52 UTC 2007


    > Date: Thu, 29 Nov 2007 19:41:11 -0500
    > From: "Michael T. Dean" <mtdean at thirdcontact.com>

    > Do you have your MySQL database on the same drive as your recording
    > disk?

I'm going to jump in here and say that -my- DB isn't on the same
spindle and -I- still see terrible performance, even after doing ALL
of the frequently-recommended performance "improvement" tasks.  Go
back and look at all those threads I posted in talking about corrupted
recordings (eventually fixed by not hanging the writing thread on DB)
and see the enormous litany:  different spindle, huge memory, screwing
around w/IO scheduling algorithms, constant DB optimization, yadda
yadda yadda.

-My- terrible performance is on BOTH deletions AND simple reschedules.
Even though I have fairly few (less than a dozen?) any-channel rules.
But I do have a lot of rules and a large oldrecorded, and that's what
Myth is -supposed- to be able to handle.

It's certainly -possible- that deletions are even slower than
reschedules because of recordedseek (I haven't checked), but the
resched is the lion's share, as is trivially discoverable from the
logs saying "Scheduled n items in m = q match + r place" where m
and r are huge (e.g,. 60 seconds).

(Assuming you're wrong about XFS deletes -themselves- being slow, the
easy way to check, of course, is to simply do a delete from recordedseek
that deletes a few thousand records, and time it.  That's two lines of
SQL:  One to clone all the records from a recording to one w/a different
starttime, and the second to delete all matching starttimes.)

    >        If so, that's the first thing you should consider fixing.  I
    > don't think it's the "slow" MySQL query causing your problem (especially
    > since it's running in almost exactly the same time as mine does and I
    > don't notice /any/ issue when deleting recordings).  I really think it's
    > a file system thing--i.e. disk thrashing as XFS tries to delete the
    > recording at full speed

This sounds -extremely- unlikely.  Certainly JFS never takes more than
milliseconds, and XFS doesn't, either, if I can believe the benchmarks.

Easy way to test:  Before deleting a recording, delete (or rename) the
actual video file, then touch the same filename to create a zero-length
one.  THEN delete and see if the filesystem is involved at all.

    >                         and MySQL on the same disk tries to delete the
    > recordedseek entries for the recording (i.e. finding 7200 needles (per
    > hour of recording you're deleting) in a 1.1M straw haystack).

If this is truly the problem, then either Myth or MySQL is horribly
misdesigned.  Are you actually saying that this database can't delete
a few thousand items in less than 10 seconds or so?

How about indexing recordedseek better?  Maybe not using strings?
Maybe giving each recording its OWN table (e.g., recordedseek-chanid-starttime)
so a deletion is as simple as DROP TABLE RECORDEDSEEK-chanid-starttime?
Of course, all of these would have to be benchmarked.


More information about the mythtv-users mailing list