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