[mythtv-users] Slow MySQL query after delete
Michael T. Dean
mtdean at thirdcontact.com
Wed Dec 5 05:16:45 UTC 2007
On 12/04/2007 09:45 PM, f-myth-users at media.mit.edu wrote:
> > Date: Tue, 04 Dec 2007 10:49:45 -0500
> > From: "Michael T. Dean"
> > On 12/03/2007 10:17 PM, Yeechang Lee wrote:
> > > f-myth-users says:
> > >
> > >> You haven't tried the critical experiment (so far as you've told us),
> > >> which is to do MORE THAN ONE in quick succession.
> > > One delete for me (whether of a single item or a playlist) from
> > > mythfrontend results in a UI pause of no more than a second. Two
> > > deletes in quick succession results in a pause of . . . no more than a
> > > second or two.
> > I got a chance to do 2 in a row (deleted one by one). Deleting one at a
> > time results in a delay of about 1/2 a second. Deleting two in a row
> > results in 2 delays of about 1/2 a second (i.e. just long enough to
> > clear the UI and reset the "cursor"). This is with slow deleted
> > enabled. I did delete them back to back--they were the same show, so I
> > didn't have to scroll to find the second to delete.
> Can you post your match/place times for typical reschedules? (If you
> did, I've overlooked them somehow.)
2007-12-04 22:00:08.327 scheduler: Scheduled items: Scheduled 265 items
in 2.8 = 0.00 match + 2.79 place
These times (and the number of items) are very low (thanks to the
writers' strike--guess we can all thank the union for making our Myth
boxes faster). Normally during the season, my match + place is about 6
seconds (with about double the items--that's what causes the doubled
times) on my Athlon XP 2400+, which is almost identical to Larry's times
on his Athlon XP 2500+.
> If those times are also subsecond,
> then none of the other tests will yield any interesting results, of
> course, since if it's really scheduling delays that are hanging deletes,
> you won't show the effect if your delays are tiny (and then the question
> becomes, "Why is your scheduler running in less than a second when many
> others are reporting tens of seconds?")
> Err---I've -have- been assuming all along that slow deletes really do
> tell the scheduler to immediately recompute (as soon as you delete the
> program). If -that- is delayed as well until all the video is deleted
> and all the table entries vanish, then of course that delay will be
> hidden sometime in the future. But I was assuming the reschedule
> happens immediately, since otherwise you run the risk of missing a
> show if, e.g., you delete a "keep n" show and there's another of it
> coming up very soon.
It's done "immediately," where that means it waits about 3 seconds to
let the frontend catch up and reload the recording list. Then, it
deletes the info from the DB (including recordedseek info). Finally, it
deletes the file.
However, when deleting multiple recordings at once, the deletions occur
sequentially, so when slow deletes are enabled, the first delete request
is made, data is removed from the DB, a reschedule is requested, then
the 1GB/2min10sec deletion occurs. If, during that deletion process,
another recording is deleted, the DB data deletion and reschedule waith
until the first recording is gone. When the first file's deletion
completes, the backend waits another 3 seconds for the frontend to catch
up (though by now it surely already has), then removes data from the DB,
reschedules, and begins the deletion process. Any additional deletion
requests wait in the queue for their turn to remove DB data, reschedule,
> > I've been saving up some shows for deleting 5 in a row (with and without
> > slow deleted). I seriously expect deleting 5 in a row with slow deletes
> > enabled will result in 5 delays of about 1/2 second. (I used to do this
> > all the time after a business trip--I'd delete all the shows that I
> > watched while sitting around in the airport/on planes/at the hotel--but
> > I've been going through my old DVD library lately, so I haven't done
> > this for a quite a few months.)
> Certainly I'd think that if it hasn't gotten wedged by 3, it probably
> won't get wedged by 5.
You're right. It didn't. Deleted 5 with slow deletes enabled and there
were no pauses > ~ 1/2 sec.
> > I also expect deleting the shows with slow deletes disabled will result
> > in a pause of approximately 9 to 19 seconds based on the filesize (from
> > 4 to 8.5 GiB per file) and the fact that it takes my ext3-based
> > filesystem approximately that long to delete a file of the given sizes.
> > If that's the case, I'm sure the scheduler query (accessing a completely
> > different disk drive) time will be lost in the noise.
> Of course---since -you- run ext3, the way to make this test tell us
> anything is to first rename the actual video files out of the way and
> substitute zero-length files in their places.
Almost. That would cause the preview generator to fire every time I
scroll over the show's title, which would cause the UI to pause for
about 2 seconds while it determines there is no valid video in the
file. In theory, if I disable display of previews pixmaps and preview
video, the UI won't pause (though it would still attempt preview
generation). However,about it was a lot easier (and a more realistic
test--since it prevents preview generation from triggering) just to give
it 10MB of the file so it can do a preview, then scroll over all the
shows (to generate the preview) before doing the deletes.
I did 4 deletes this way. For 3 of the 4, there was the same ~ 1/2 sec
pause. For one (the third delete) the pause was almost 1 second--though
I can't say for sure what caused the extra 1/2 second delay (could be
anything from the filesystem to the scheduler query to some other part
of Myth to something completely unrelated to Myth/the delete).
> After all, we -know-
> that ext3 is a dog when it comes to deleting large files...
Yep. But, it's rock solid stable and since Bolek was kind enough to
write the slow delete code, the filesystem's one drawback has no effect
More information about the mythtv-users