Mailing List Archive: MythTV: Users

Slow MySQL query after delete

1 2

View All

Index | Next | Previous | View Threaded

f-myth-users at media

Nov 28, 2007, 8:41 PM

Post #1 of 29 (1865 views)
Permalink

Slow MySQL query after delete

> Date: Wed, 28 Nov 2007 22:10:07 -0500
> From: "Larry K" <lunchtimelarry [at] gmail>

> > >(Though there's a good chance you /should/ enable slow deletes.)
> >
> > Since the issue here is a slow SQL statement and not a slow file system
> > (mine is xfs and deletes have never been slow), I wonder how this will
> > help? I thought this myth-setup option was to implemented to help overcome
> > slow file system deletes.

It won't help you.

> > >Yep. Without changing your MySQL configuration, that's as good as it
> > >will get. I'll leave figuring out how to change your MySQL
> > >configuration--and the consequences of doing so--to you (as really, the
> > >fact that it takes 6-7 seconds to schedule recordings should make no
> > >difference to you).

> If the scheduler is blocking the frontend when it does this 7 second
> operation, it clearly makes a difference to me. It is very frustrating to
> sit and wait up to 10 seconds after every delete (from either the frontend
> or mythweb).

It's even worse than that for those of us for which -any- reschedule
takes 20-40 seconds or more (*)---and remember, a reschedule happens
-every- time something finishes recording!

My experience is that my frontend can't get a word in edgewise during
such a reschedule, so if I'm unfortunate enough to want to, say, skip
forward while rescheduling is happening, the request gets buffered for
up to most of a minute (!) and -then- executes. This means that
skipping an ad if I happen to want to do this near the top or bottom
of the hour is problematic---since reschedules often happen then
because that's when shows tend to end, yet of course part of the point
is to free me from having to know what the -actual- time is while
watching something recorded earlier. This isn't exactly good for
showing off the machine to people when occasionally one has to
explain, "Well, it's busy thinking and can't process the UI for the
next minute or so," while the ad plays on... [.This is particularly
weird because it's clearly not a disk-contention issue---video
playback proceeds just fine---but because the backend simply acts deaf
for a minute and then processes the queued stuff from the frontend.
Why should -anything- the scheduler is doing affect processing
commands? I'm guessing because the UI and the scheduler are sharing a
thread somehow, which seems bizarre, but I haven't looked into it.]

Deletes are also slow, of course, because deletes provoke reschedules.
It's absolutely not the time required to delete the video from the
filesystem, because I'm running JFS, where single-file deletions of
any size are in the milliseconds.

[.By the way, WHY do deletions provoke rescheduling? I've been meaning
to ask this forever, and maybe I will in -dev. It seems to me that
unless what you're deleting is (a) a keep-only-n-of-these sort of
item, or (b) a delete-and-allow-rerecord, the scheduler DOESNT NEED TO
KNOW that you've just deleted it, because it's not going to cause the
scheduler to do anything differently! I've never used either one of
these in my entire history of using Myth, and yet the scheduler runs
after -every- deletion... (Okay, I guess if you have some hand-made
Power Search rule that implements either of the above bits of
functionality, there's no way for the scheduler to know it's there
and thus it has to be maximally pessimistic about what a deletion
might do. Surely there must be a way to optimize this for the common
case, though.)]

Yes, this makes deleting things by hand from the frontend an exercise
in aggravation---the first one returns to the UI almost immediately,
but the second one can take a while, and by the time we get to the
third one, the UI is just hung for a minute or so until all the
reschedules are processed. (And lord help you if you pick the Delete
option for something that's in the process of being recorded---that
seems to hang the UI for at least -twice- as long, as if it's running
the scheduler once when the recording gets stopped, then immediately
again when the recording gets deleted, and even if it's the -only-
deletion you're doing, the popup won't pop down and you won't get your
UI back until it's completely done, a couple of minutes later...)

But queuing them up and using mythweb to delete them (via a script
that calls wget---cheesy but easy to write, and I do this because of
some custom automation we use here) is fraught as well, because not
only will the wgets jam up in the same way, waiting for the scheduler,
but mythweb is apparently able to provoke some sort of race that will
CRASH THE BACKEND if they're allowed to come in too quickly. And even
with a careful script that (a) waits 30 seconds between deletions and
(b) retries deletions if the relevant video file is still in the
backend filesystem (e.g., hasn't been deleted yet because mythweb
timed out 'cause the scheduler was still chewing on the -last-
deletion and the requested current deletion never happened), it's also
possible to crash the backend if I do anything on the -frontend- like
skipping forward while watching something.

[.In fact, the only backend crashes I've -ever- had were during scheduling.]

I'm still running an old release (0.18.1), but I'm -assuming- that at
least this mass-deletion issue might be addressable once I move to
0.20.x, because in theory I should be able to put everything I'd like
to delete into a single program group and then delete the entire group
as a unit---I'm presuming that moving things between groups does -not-
cause a scheduler run and that deleting a group does all the deletions
before trying to reschedule, but I don't have firsthand experience
yet. And of course it doesn't help at all the issue of the UI
becoming unresponsive for a minute at the top and bottom of many
hours...

[.I was hoping that this race/crash issue was solved in 0.20.x, but
recent threads about simply querying status crashing the backend make
me believe that it hasn't been solved. If someone thinks they'll be
spending the time to track this down, I can provide some procedures
that are extremely likely to crash your backend, so perhaps this can
be debugged---again, probably best on -dev.]

(*) And yes, I've documented in other threads that I've done all the
"recommended practices" re where the DB is, how it's optimized, MySQL
configuration, etc etc---but I'm still using MyISAM for all the tables.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mikerice1969 at gmail

Nov 29, 2007, 10:21 AM

Post #2 of 29 (1792 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On 11/28/07, f-myth-users [at] media <f-myth-users [at] media> wrote:
> Yes, this makes deleting things by hand from the frontend an exercise
> in aggravation---the first one returns to the UI almost immediately,
> but the second one can take a while, and by the time we get to the
> third one, the UI is just hung for a minute or so until all the
> reschedules are processed.

This is certainly the biggest problem with the frontend UI for my
family. I have slow deletes enabled but my re-schedule query takes
awhile likely due to the large number of channels I receive. On the
second delete the frontend is totally unresponsive and gives no visual
indication that it is busy or when it is done. Even after two years
of training my wife that she has to wait for it to reschedule it is
still an issue.

In most cases (at least for me) I don't think deleting a recording
needs to invoke an immediate reschedule. Maybe a "Delete Later" or
"Delete But Don't Reschedule" option would be a good thing.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Nov 30, 2007, 1:06 AM

Post #3 of 29 (1784 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Thu, 29 Nov 2007 19:44:10 +1000
> From: Rossco <rossco [at] whyza>

> I should probably add that i did actually determine that it was the
> recordedseek table that was slow for me via the myslq slow logging
> feature, before converting to innodb.

Just to be clear---this was slow during a -delete-, right?
(I can't see why a -reschedule- should be touching recordedseek.)
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Nov 30, 2007, 1:35 AM

Post #4 of 29 (1774 views)
Permalink

Slow MySQL query after delete [In reply to]

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

> 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.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Nov 30, 2007, 2:12 AM

Post #5 of 29 (1771 views)
Permalink

Slow MySQL query after delete [In reply to]

Date: Thu, 29 Nov 2007 21:38:39 -0500
From: "Michael T. Dean" <mtdean [at] thirdcontact>

> On 11/29/2007 07:55 PM, David Rees wrote:
> > On Nov 29, 2007 4:41 PM, Michael T. Dean <mtdean [at] thirdcontact> wrote:
> >
> >>> It is very frustrating to
> >>> sit and wait up to 10 seconds after every delete (from either the frontend
> >>> or mythweb).
> >>>
> >>> I am open to suggestions as to how I can improve this situation.
> >>>
> >> Do you have your MySQL database on the same drive as your recording
> >> disk? If so, that's the first thing you should consider fixing.
> >>
> > 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.

It is also completely unreasonable for you to ignore and dismiss all
the evidence people are handing you that RESCHEDULES ARE SLOW even
if one has ARLEADY DONE all of the recommended screwing around.

(Ignore the red herring of "deletions" for the moment, just to
simplify the discussion---the only relevant point there is that
deletions force a reschedule.)

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

You do realize that an increasing number of people are starting to
call you on your condescending attitude, right?

(You're not always that way, and you're often helpful. But you're
also quite often a determined Pollyanna and/or apologist for the
status quo, insisting that -all- user complaints -must- be because the
-user- is somehow confused and that Myth itself must be perfect. Just
because many users -are- confused is no reason to assume (or insist)
that they -all- are. I've gritted my teeth, grumbled to myself, and
kept quiet when you've done this, but recent traffic in other threads
shows that others are starting to come out and say it.)

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

Every additional disk---even if the disk itself is "free"---adds heat,
noise, and power cost; reduces reliability and the ability to add any
-more- disks (due to using up both space and bus connectors); and
complicates the machine's configuration.

Many people are unwilling to make those sorts of sacrifices for what
looks, at the heart of it, to be poor implementation; many others
-cannot- make that sacrifice if their backend is limited in, e.g.,
physical size.

And besides, as my evidence has shown, putting the DB on a separate
spindle doesn't help significantly. It is NOT, repeat NOT, any sort
of contention for where the disk head is hanging out, unless you count
the contention that MySQL is imposing on ITSELF by executing its queries.

> Also, you do realize that MySQL is a /network-capable/ database
> management system, right? You don't even have to put MySQL on either
> backend. It could be on the dedicated frontend. It could be on another
> computer somewhere in the house. ...

...adding even -more- heat, power, space, and unreliability, unless
the machine was already in use---in which case it just adds unreliability
and makes it more complicated to take that machine---or any intervening
piece of network hardware--- down for any reason (lest you break Myth's
ability to do -anything- while it's down).

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

And how -exactly- do you recommend that he do that, besides putting it
on a second spindle that he may not have? And how would you recommend
I fix -my- configuration, given all the info I've given you in previous
threads about all the things I've tried with marginal or zero benefit?
Replies of the form "don't keep so much history", "have fewer channels",
"you can't use even one any-channel rule", and so forth are not going
to get a good reception, since all of those are things Myth is supposed
to handle well.

There are three fundamental problems here. Solving any of them would
solve the whole mess:
(a) The BUSQ has very poor performance for some people.
(b) Large portions of Myth hang completely waiting for the BUSQ.
(c) Common UI tasks, such as deletion or scheduling, run the BUSQ.

I frankly think that (b) might be the most tractable, because it
-might- be easier to separate out the BUSQ into another thread and/or
refactor the schema to avoid weird crosslocking of tables, than it
might be to change either (a) or (c). But that's just a guess.

[.If it were up to me, I'd have solved (a) by implementing a
truth-maintenance system to do scheduling, rather than by building
unwieldy DB queries, but since the chances of -that- making it into
Myth are zero I'm not going to waste any time on it. The current DB
approach is something of a travesty because it has to redo -all- of
its work on -every- reschedule, as if it's coming into the world
brand-new with no prior knowledge; a TMS has the great advantage that
reasonable changes cause small propagations and are very fast; they
even have the advantage that explaining -why- something was or wasn't
scheduled in some way can be derived by traversing the structure and
generating the explanation, which is something that can only be
crudely approximated with the current DB-based approach.]

And of course there's also:
(d) The BUSQ seems to increase some sort of window of vulnerability
to timing races that can crash the backend if it's accessed by
something else
...which isn't a performance problem so much as evidence that there's
something screwy going on in a mutex somewhere.

> not blaming the
> BUSQ/arguing with me that the scheduler query is a problem when his

But others, like myself, are arguing that the BUSQ is a big problem
all by itself, and EVEN IF everything else about deletion was instantaneous,
we'd still be seeing UI lockups and generally bad performance, because we
-also- see them in contexts where no deletion is happening, e.g,. when
scheduling new shows via the UI, or when the BUSQ is running autonomously
because some recording has just finished on its own.

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

...or maybe he just can't believe that deleting a few thousand items
from a DB can take tens of seconds. I can't. If you're saying that
he has to defer that to some random time by using code designed to
work around filesystems that have their own problems, just to cause
the DB update to happen at some indeterminate time in the future, I
can see why he might view this dubiously as a kluge (and, for that
matter, as something that could bite him when he least expects it,
depending on when that actual DB update finally -does- happen).

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

Various devs commented that they, too, were seeing reschedule times
upwards of 30 seconds; there was a long thread with stats about that
back when the DB-vs-recording-glitches thing was playing out. I guess
the devs' systems are misconfigured, too. Maybe you should explain to
them the error of their ways, and perhaps it will enlighten us unwashed
masses, too.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

lunchtimelarry at gmail

Nov 30, 2007, 7:51 AM

Post #6 of 29 (1764 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

I agree that a condescending tone is unnecessary in this forum. It also
appears that some participants attempt to obfuscate the situation rather
than offer clear, direct advice. All I (we) need is a little guidance from
those who understand how this thing is designed (and therefore understand
its flaws).

Assuming the BUSQ is executed by the BE and not the FE, I don't understand
why a FE action (i.e., my attempt to navigate through the recordings menu
following a delete) would hang if a BE action (i.e., rescheduler) is
triggered to perform a task (i.e., BUSQ). Aren't these things running
independently and asynchronously? Unless they are somehow locking each
other inside the database, I wonder why they are affecting each other. I
come from a Sybase/Oracle point of view on this, but I suppose it is
possible that the BUSQ could be holding share locks that prevent another
database transaction from acquiring exclusive locks necessary for an
update/delete. But I am not sure what this could be given the use-case in
question here. Is it possible that something else is blocking the FE action
apart from the BUSQ? Could the BUSQ be a red herring?

FYI, I enabled slow deletes and so far I am not convinced it has helped
any. This was a useful suggestion that perhaps I dismissed prematurely.

Several people have suggested we convert the recordedseek table to innoDB,
but that table does not participate in the BUSQ. Further, this table has
never shown up in my slow-sql log, nor have I seen any evidence that the
BUSQ (or any other query) is blocked by another database activity using the
recordedseek table. The BUSQ consistently runs in 7 seconds, even if I run
it ad-hoc when nothing else is going on. I remain puzzled as to how the
recordedseek table could be a factor. Additionally, I know that making
changes to the schema *can* break future upgrades, so I am reluctant to do
this unless it is absolutely necessary. Is this innoDB change one that
could break the next upgrade? I suppose if it is, I could just convert it
back and then do the upgrade....

Larry

On 11/30/07, f-myth-users [at] media <f-myth-users [at] media> wrote:
>
> Date: Thu, 29 Nov 2007 21:38:39 -0500
> From: "Michael T. Dean" <mtdean [at] thirdcontact>
>
> > On 11/29/2007 07:55 PM, David Rees wrote:
> > > On Nov 29, 2007 4:41 PM, Michael T. Dean <mtdean [at] thirdcontact>
> wrote:
> > >
> > >>> It is very frustrating to
> > >>> sit and wait up to 10 seconds after every delete (from either
> the frontend
> > >>> or mythweb).
> > >>>
> > >>> I am open to suggestions as to how I can improve this situation.
> > >>>
> > >> Do you have your MySQL database on the same drive as your
> recording
> > >> disk? If so, that's the first thing you should consider fixing.
> > >>
> > > 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.
>
> It is also completely unreasonable for you to ignore and dismiss all
> the evidence people are handing you that RESCHEDULES ARE SLOW even
> if one has ARLEADY DONE all of the recommended screwing around.
>
> (Ignore the red herring of "deletions" for the moment, just to
> simplify the discussion---the only relevant point there is that
> deletions force a reschedule.)
>
> > > 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?
>
> You do realize that an increasing number of people are starting to
> call you on your condescending attitude, right?
>
> (You're not always that way, and you're often helpful. But you're
> also quite often a determined Pollyanna and/or apologist for the
> status quo, insisting that -all- user complaints -must- be because the
> -user- is somehow confused and that Myth itself must be perfect. Just
> because many users -are- confused is no reason to assume (or insist)
> that they -all- are. I've gritted my teeth, grumbled to myself, and
> kept quiet when you've done this, but recent traffic in other threads
> shows that others are starting to come out and say it.)
>
> > 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?
>
> Every additional disk---even if the disk itself is "free"---adds heat,
> noise, and power cost; reduces reliability and the ability to add any
> -more- disks (due to using up both space and bus connectors); and
> complicates the machine's configuration.
>
> Many people are unwilling to make those sorts of sacrifices for what
> looks, at the heart of it, to be poor implementation; many others
> -cannot- make that sacrifice if their backend is limited in, e.g.,
> physical size.
>
> And besides, as my evidence has shown, putting the DB on a separate
> spindle doesn't help significantly. It is NOT, repeat NOT, any sort
> of contention for where the disk head is hanging out, unless you count
> the contention that MySQL is imposing on ITSELF by executing its queries.
>
> > Also, you do realize that MySQL is a /network-capable/ database
> > management system, right? You don't even have to put MySQL on
> either
> > backend. It could be on the dedicated frontend. It could be on
> another
> > computer somewhere in the house. ...
>
> ...adding even -more- heat, power, space, and unreliability, unless
> the machine was already in use---in which case it just adds unreliability
> and makes it more complicated to take that machine---or any intervening
> piece of network hardware--- down for any reason (lest you break Myth's
> ability to do -anything- while it's down).
>
> > 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,
>
> And how -exactly- do you recommend that he do that, besides putting it
> on a second spindle that he may not have? And how would you recommend
> I fix -my- configuration, given all the info I've given you in previous
> threads about all the things I've tried with marginal or zero benefit?
> Replies of the form "don't keep so much history", "have fewer channels",
> "you can't use even one any-channel rule", and so forth are not going
> to get a good reception, since all of those are things Myth is supposed
> to handle well.
>
> There are three fundamental problems here. Solving any of them would
> solve the whole mess:
> (a) The BUSQ has very poor performance for some people.
> (b) Large portions of Myth hang completely waiting for the BUSQ.
> (c) Common UI tasks, such as deletion or scheduling, run the BUSQ.
>
> I frankly think that (b) might be the most tractable, because it
> -might- be easier to separate out the BUSQ into another thread and/or
> refactor the schema to avoid weird crosslocking of tables, than it
> might be to change either (a) or (c). But that's just a guess.
>
> [.If it were up to me, I'd have solved (a) by implementing a
> truth-maintenance system to do scheduling, rather than by building
> unwieldy DB queries, but since the chances of -that- making it into
> Myth are zero I'm not going to waste any time on it. The current DB
> approach is something of a travesty because it has to redo -all- of
> its work on -every- reschedule, as if it's coming into the world
> brand-new with no prior knowledge; a TMS has the great advantage that
> reasonable changes cause small propagations and are very fast; they
> even have the advantage that explaining -why- something was or wasn't
> scheduled in some way can be derived by traversing the structure and
> generating the explanation, which is something that can only be
> crudely approximated with the current DB-based approach.]
>
> And of course there's also:
> (d) The BUSQ seems to increase some sort of window of vulnerability
> to timing races that can crash the backend if it's accessed by
> something else
> ...which isn't a performance problem so much as evidence that there's
> something screwy going on in a mutex somewhere.
>
> > not blaming
> the
> > BUSQ/arguing with me that the scheduler query is a problem when his
>
> But others, like myself, are arguing that the BUSQ is a big problem
> all by itself, and EVEN IF everything else about deletion was
> instantaneous,
> we'd still be seeing UI lockups and generally bad performance, because we
> -also- see them in contexts where no deletion is happening, e.g,. when
> scheduling new shows via the UI, or when the BUSQ is running autonomously
> because some recording has just finished on its own.
>
> > 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. 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.
>
> ...or maybe he just can't believe that deleting a few thousand items
> from a DB can take tens of seconds. I can't. If you're saying that
> he has to defer that to some random time by using code designed to
> work around filesystems that have their own problems, just to cause
> the DB update to happen at some indeterminate time in the future, I
> can see why he might view this dubiously as a kluge (and, for that
> matter, as something that could bite him when he least expects it,
> depending on when that actual DB update finally -does- happen).
>
> > 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.
>
> Various devs commented that they, too, were seeing reschedule times
> upwards of 30 seconds; there was a long thread with stats about that
> back when the DB-vs-recording-glitches thing was playing out. I guess
> the devs' systems are misconfigured, too. Maybe you should explain to
> them the error of their ways, and perhaps it will enlighten us unwashed
> masses, too.
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

mtdean at thirdcontact

Nov 30, 2007, 9:17 PM

Post #7 of 29 (1755 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On 11/30/2007 05:12 AM, f-myth-users [at] media wrote:
> It is also completely unreasonable for you to ignore and dismiss all
> the evidence people are handing you that RESCHEDULES ARE SLOW even
> if one has ARLEADY DONE all of the recommended screwing around.
>

How many times do I have to say, "His system takes the same amount of
time for a scheduling run as my system and my system does /not/ lock
up--or show /any/ delay at all--after a delete," before it's apparent
that my system can somehow run a "SLOW" reschedule without affecting UI
responsiveness? Am I the only one to whom this implies that the "SLOW"
reschedule is /not/ the issue?

> (Ignore the red herring of "deletions" for the moment, just to
> simplify the discussion---the only relevant point there is that
> deletions force a reschedule.)

Never optimize before profiling. IMHO, picking something--no matter how
"likely" to be the problem--and changing it without actually proving it
is the source of the problem is just a waste of time.

Oh, and--just in case--saying that a 0.18.1 system is slow is adding
nothing to the argument of whether Myth needs fixing in
November/December, 2007.

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Dec 1, 2007, 1:03 AM

Post #8 of 29 (1775 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Sat, 01 Dec 2007 00:17:43 -0500
> From: "Michael T. Dean" <mtdean [at] thirdcontact>

> On 11/30/2007 05:12 AM, f-myth-users [at] media wrote:
> > It is also completely unreasonable for you to ignore and dismiss all
> > the evidence people are handing you that RESCHEDULES ARE SLOW even
> > if one has ARLEADY DONE all of the recommended screwing around.

> How many times do I have to say, "His system takes the same amount of
> time for a scheduling run as my system and my system does /not/ lock
> up--or show /any/ delay at all--after a delete," before it's apparent
> that my system can somehow run a "SLOW" reschedule without affecting UI
> responsiveness? Am I the only one to whom this implies that the "SLOW"
> reschedule is /not/ the issue?

There are two points here. One is that reschedules are slow no matter
what you're doing, for some users. (E.g., just ignore that this
thread started out talking about deletions and still mentions them in
the subject line.) This slowness is the cause of numerous problems.
For example, note my comments that I cannot ask my FE to skip, pause,
or stop or start playing during a reschedule, including reschedules
caused because some program being recorded has just finished. Since
skipping around in playback should have -nothing- to do with what's
being scheduled to record in the future, the fact that there's -any-
interaction here is symptomatic of some sort of misdesign.

The second is that deletions -will- cause a reschedule, so the above
scheduling problems are going to bite on a deletion, no matter how
fast a deletion otherwise runs. (Even if it -wasn't- locking the
UI---but my UI is certainly locked---it would lock other things, such
as making any changes to the current recording rules, etc.)

Now, as for your own performance, can you please run a couple of tests
and let us know how -your- system performs?

(1) Pick five recordings you don't care about. Make sure they are not
manual recordings. (I don't remember whether the scheduler runs after
these & haven't tried retesting.) Using the frontend (not mythweb),
delete them as fast as you can, individually (e.g., don't cheat and
put them into a single recording group and then delete that). Do you
see the UI pause at any point and prevent you from continuing?

(2) If the answer to this is "no", turn off slow deletes and try
again with five more. If the answer is -still- no, then something
very odd indeed is going on.

If the answer at any point becomes "yes", try renaming the actual
video files, touch new ones into their place, and repeat the test.
(This eliminates your ext3fs filesystem from the benchmark; it should
also allow you to turn slow deletes on & off to eliminate it from the
discussion.)

If you cannot ever get the UI to pause while doing this, I'm sure many
of us would be interested to know the size of your oldrecorded tables
and the number and makeup of your recording rules, as mentioned in
that old thread about scheduler performance from a few months ago.
Also interesting would be the match/place lines from your sched logs,
and the contents of your MySQL conf (e.g., memory allocations, etc).

> > (Ignore the red herring of "deletions" for the moment, just to
> > simplify the discussion---the only relevant point there is that
> > deletions force a reschedule.)

> Never optimize before profiling. IMHO, picking something--no matter how
> "likely" to be the problem--and changing it without actually proving it
> is the source of the problem is just a waste of time.

You seem to be completely ignoring the fact that you've been told
repeatedly that simple reschedule -is- a big problem, whether or not
a deletion is happening. Sure, the OP was talking about deletion
originally, but I and others have been talking about scheduling
-in general-. Do we have to put this in a separate thread just
to make it clear to you what we're talking about?

(For example: If I create a recording rule for something via mythweb,
no further scheduling-related actions in mythweb will complete without
waiting 30-60 seconds for the scheduler run to completion. If I try, my
browser will either hang, or report incomplete information (e.g., trying
"Find other showings of this program" will show -none- of them scheduled
to record until the scheduler catches up; clicking on the program I just
scheduled will present the mythweb screen one gets when it is -not-
being scheduled [no "record never/record this one in particular"], etc).
This means that doing schedule maintenance is quite slow, because every
change involves waiting 30-60 seconds before the next one is allowed.
This is also true if I've just deleted something, or if a program has
just finished recording, because both of those run the scheduler as well.)

> Oh, and--just in case--saying that a 0.18.1 system is slow is adding
> nothing to the argument of whether Myth needs fixing in
> November/December, 2007.

The devs who reported slow scheduling behavior in the threads around
March certainly weren't running 0.18.1, and as far as I'm aware there
haven't been spectacular improvements in the scheduler since then,
though there have been some optimizations. Users running 0.20.x and
SVN who've been reporting slow scheduler performance would also seem
to agree with that, no matter how fast -your- queries are running.

P.S. One way of verifying your contention that it's the actual
removal of the recordedseek info that's causing deletions to
be slow would be to have the OP manually delete those entries
(via the appropriate SQL command) and wait (say) 30 seconds
-before- trying a deletion. If the delete is still slow, then
the recordedseek hypothesis is busted. (A better test would be
to do this for several programs and then delete them all as fast
as possible; an even better test would (a) zero out the video
files first and (b) include backing up the DB beforehand... :)
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Dec 1, 2007, 1:05 AM

Post #9 of 29 (1759 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Fri, 30 Nov 2007 11:04:14 -0800
> From: "David Rees" <drees76 [at] gmail>

> > Several people have suggested we convert the recordedseek table to innoDB,
> > but that table does not participate in the BUSQ.

> Please convert ALL tables to InnoDB (except for nestitle, see my post
> from last night), not just the recordedseek table. Let us know how it
> works.

For those using InnoDB instead of MyISAM:

Do schema upgrades still complete correctly? Are there any other
recommended changes to MySQL's configuration (e.g., memory) that
might be relevant or recommended? In sort, what might be the
downsides of doing such a conversion, if any?
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

gull at gull

Dec 1, 2007, 12:52 PM

Post #10 of 29 (1739 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On Nov 30, 2007, at 9:17 PM, Michael T. Dean wrote:
> How many times do I have to say, "His system takes the same amount of
> time for a scheduling run as my system and my system does /not/ lock
> up--or show /any/ delay at all--after a delete,"...

It will if you immediately try a second delete. That will force a
second scheduler query, which will block because the first one still
has the tables locked. Same if you go in Mythweb and try to edit two
schedules in quick succession.

I suspect a lot of people never see this issue because they let auto-
expire take care of most of their deletions, so they rarely actually
try to trigger two scheduler events close to each other.

_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mikerice1969 at gmail

Dec 1, 2007, 2:13 PM

Post #11 of 29 (1744 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On Dec 1, 2007 12:52 PM, David Brodbeck <gull [at] gull> wrote:
>
> On Nov 30, 2007, at 9:17 PM, Michael T. Dean wrote:
> > How many times do I have to say, "His system takes the same amount of
> > time for a scheduling run as my system and my system does /not/ lock
> > up--or show /any/ delay at all--after a delete,"...
>
> It will if you immediately try a second delete. That will force a
> second scheduler query, which will block because the first one still
> has the tables locked. Same if you go in Mythweb and try to edit two
> schedules in quick succession.

Right. So from looking at my logs carefully I think I see what
is happening. When the front end deletes it does a:

UPDATE record SET last_delete = <date>.

That's ok since it is the first delete and the BUSQ has not
started yet. Now if you time it correctly and start another
delete after the BUSQ starts, the UPDATE record will be
blocked and the frontend hangs until the BUSQ completes
(or unlocks the table).

That explains why I didn't see it EVERY time I did two
deletes. It is all in the timing.

That's my theory anyway... If that's true I don't know
what can be done about it... I can try removing that
update and see what happens but I have to go buy a
Christmas tree and let some more kid shows record
before I test anymore. :)
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Dec 1, 2007, 4:04 PM

Post #12 of 29 (1749 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On 12/01/2007 04:03 AM, f-myth-users [at] media wrote:
> > Date: Sat, 01 Dec 2007 00:17:43 -0500
> > From: "Michael T. Dean" <mtdean [at] thirdcontact>
>
> > On 11/30/2007 05:12 AM, f-myth-users [at] media wrote:
> > > It is also completely unreasonable for you to ignore and dismiss all
> > > the evidence people are handing you that RESCHEDULES ARE SLOW even
> > > if one has ARLEADY DONE all of the recommended screwing around.
>
> > How many times do I have to say, "His system takes the same amount of
> > time for a scheduling run as my system and my system does /not/ lock
> > up--or show /any/ delay at all--after a delete," before it's apparent
> > that my system can somehow run a "SLOW" reschedule without affecting UI
> > responsiveness? Am I the only one to whom this implies that the "SLOW"
> > reschedule is /not/ the issue?
>
> There are two points here. One is that reschedules are slow no matter
> what you're doing,

Fine. Look. I'm not saying that it can't be improved--there's not a
single place in Myth that can't be improved, as nothing is ever
perfect. I'm simply saying that the "recent OP"--the guy who has a
problem and restarted this thread--can make his system perform much
better. It seems there are far too many people trying to convince him
that there's nothing he can do because Myth is broken and the scheduler
was improperly designed.

Daniel and Janne have actually made some changes that make the
scheduling faster (maybe only committed to multirec so far, though--I
don't remember). Chris Pinkham promised you that he would make (and he
has already made some) changes which make the DB locking less of an
issue. Others may have also made some changes.

Though I can't guarantee it--because I'm a non-dev and because I don't
make decisions (so arguing with me is really a waste of both our time),
I'm pretty certain no major rewrite of the scheduler will be done in
0.20-fixes (stable) branch. I'm even more certain that no major rewrite
of the scheduler will be done in the 0.18-fixes (archive) branch.

> for some users.
>

And /there/ is the important point. Find out why it's only /some/ users
and--if, in fact, the configuration difference is a Myth bug, I will fix
it for you.

> Now, as for your own performance, can you please run a couple of tests
> and let us know how -your- system performs?

That will have to wait until I can find the time.

> If you cannot ever get the UI to pause while doing this, I'm sure many
> of us would be interested to know the size of your oldrecorded tables
>

7072 in oldrecorded, 2903907 in recordedseek, 4577 in recordedmarkup

> and the number and makeup of your recording rules,

78 in record (all are currently always/any channel rules--though I often
add up to 20 find once/any channel rules for movies), 413 in
recordmatch, 437 in recorded.

Every table in the DB is MyISAM with the exception of the new
MythWeather tables, which are InnoDB (see
http://www.gossamer-threads.com/lists/mythtv/dev/302941#302941 ).

Oh, and, I've deleted at least 10 shows today, so those numbers were higher.

> as mentioned in
> that old thread about scheduler performance from a few months ago.
> Also interesting would be the match/place lines from your sched logs,
> and the contents of your MySQL conf (e.g., memory allocations, etc).
>

It's the default one that's installed from a source install--with the
one exception that I disabled (commented) log-bin.

> > Never optimize before profiling. IMHO, picking something--no matter how
> > "likely" to be the problem--and changing it without actually proving it
> > is the source of the problem is just a waste of time.
>
> You seem to be completely ignoring the fact that you've been told
> repeatedly that simple reschedule -is- a big problem,

I've been told a lot of things. I believe very little of of what I'm
told until I have proof.

> P.S. One way of verifying your contention that it's the actual
> removal of the recordedseek info that's causing deletions to
> be slow

I _never_ said that removal of recordedseek info causes deletion to be
slow. As a matter of fact, I have never even agreed that deletion is
slow--as it isn't for me.

I simply made a comment which tried to point out that--despite what many
had said in the thread--enabling slow deletes on a fast filesystem
/does/ have an effect. I did not say that effect would fix the
slowness. I did not say that removing records from recordedseek is slow.

Far too many people seem to be forgetting that there's a /lot/ going on
in the system and that few people have a clear picture of all that
happens. I know enough about what happens to know that I don't have a
clear picture of all that happens. Because of this, even things that
don't seem to be relevant may actually be relevant.

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

lunchtimelarry at gmail

Dec 2, 2007, 9:11 AM

Post #13 of 29 (1738 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

When I read you post about the icons, I thought maybe that could be a
factor. I say that because my channels table had nothing in the icon
column, but as it turns out, having nothing is not a problem if you don't
care about seeing the icons. I guess when I switched from zap2it to
SchedulesDirect, I lost the icons and never really noticed. I populated the
icons (per the wiki) and now I have them, but nothing has changed otherwise.

Since you believe this has nothing to do with the slow scheduler query, and
since enabling slow deletes made no difference. and since yours works
without the use of InnoDB, can you offer any other suggestions as what this
could be? Issues to investigate? Configuration changes to consider?
Verbose logging that might shed light on it? Anything?

On 12/1/07, Michael T. Dean <mtdean [at] thirdcontact> wrote:
>
> On 12/01/2007 04:03 AM, f-myth-users [at] media wrote:
> > > Date: Sat, 01 Dec 2007 00:17:43 -0500
> > > From: "Michael T. Dean" <mtdean [at] thirdcontact>
> >
> > > On 11/30/2007 05:12 AM, f-myth-users [at] media wrote:
> > > > It is also completely unreasonable for you to ignore and dismiss
> all
> > > > the evidence people are handing you that RESCHEDULES ARE SLOW
> even
> > > > if one has ARLEADY DONE all of the recommended screwing around.
> >
> > > How many times do I have to say, "His system takes the same amount
> of
> > > time for a scheduling run as my system and my system does /not/
> lock
> > > up--or show /any/ delay at all--after a delete," before it's
> apparent
> > > that my system can somehow run a "SLOW" reschedule without
> affecting UI
> > > responsiveness? Am I the only one to whom this implies that the
> "SLOW"
> > > reschedule is /not/ the issue?
> >
> > There are two points here. One is that reschedules are slow no matter
> > what you're doing,
>
> Fine. Look. I'm not saying that it can't be improved--there's not a
> single place in Myth that can't be improved, as nothing is ever
> perfect. I'm simply saying that the "recent OP"--the guy who has a
> problem and restarted this thread--can make his system perform much
> better. It seems there are far too many people trying to convince him
> that there's nothing he can do because Myth is broken and the scheduler
> was improperly designed.
>
> Daniel and Janne have actually made some changes that make the
> scheduling faster (maybe only committed to multirec so far, though--I
> don't remember). Chris Pinkham promised you that he would make (and he
> has already made some) changes which make the DB locking less of an
> issue. Others may have also made some changes.
>
> Though I can't guarantee it--because I'm a non-dev and because I don't
> make decisions (so arguing with me is really a waste of both our time),
> I'm pretty certain no major rewrite of the scheduler will be done in
> 0.20-fixes (stable) branch. I'm even more certain that no major rewrite
> of the scheduler will be done in the 0.18-fixes (archive) branch.
>
> > for some users.
> >
>
> And /there/ is the important point. Find out why it's only /some/ users
> and--if, in fact, the configuration difference is a Myth bug, I will fix
> it for you.
>
> > Now, as for your own performance, can you please run a couple of tests
> > and let us know how -your- system performs?
>
> That will have to wait until I can find the time.
>
> > If you cannot ever get the UI to pause while doing this, I'm sure many
> > of us would be interested to know the size of your oldrecorded tables
> >
>
> 7072 in oldrecorded, 2903907 in recordedseek, 4577 in recordedmarkup
>
> > and the number and makeup of your recording rules,
>
> 78 in record (all are currently always/any channel rules--though I often
> add up to 20 find once/any channel rules for movies), 413 in
> recordmatch, 437 in recorded.
>
> Every table in the DB is MyISAM with the exception of the new
> MythWeather tables, which are InnoDB (see
> http://www.gossamer-threads.com/lists/mythtv/dev/302941#302941 ).
>
> Oh, and, I've deleted at least 10 shows today, so those numbers were
> higher.
>
> > as mentioned in
> > that old thread about scheduler performance from a few months ago.
> > Also interesting would be the match/place lines from your sched logs,
> > and the contents of your MySQL conf (e.g., memory allocations, etc).
> >
>
> It's the default one that's installed from a source install--with the
> one exception that I disabled (commented) log-bin.
>
> > > Never optimize before profiling. IMHO, picking something--no
> matter how
> > > "likely" to be the problem--and changing it without actually
> proving it
> > > is the source of the problem is just a waste of time.
> >
> > You seem to be completely ignoring the fact that you've been told
> > repeatedly that simple reschedule -is- a big problem,
>
> I've been told a lot of things. I believe very little of of what I'm
> told until I have proof.
>
> > P.S. One way of verifying your contention that it's the actual
> > removal of the recordedseek info that's causing deletions to
> > be slow
>
> I _never_ said that removal of recordedseek info causes deletion to be
> slow. As a matter of fact, I have never even agreed that deletion is
> slow--as it isn't for me.
>
> I simply made a comment which tried to point out that--despite what many
> had said in the thread--enabling slow deletes on a fast filesystem
> /does/ have an effect. I did not say that effect would fix the
> slowness. I did not say that removing records from recordedseek is slow.
>
> Far too many people seem to be forgetting that there's a /lot/ going on
> in the system and that few people have a clear picture of all that
> happens. I know enough about what happens to know that I don't have a
> clear picture of all that happens. Because of this, even things that
> don't seem to be relevant may actually be relevant.
>
> Mike
> _______________________________________________
> mythtv-users mailing list
> mythtv-users [at] mythtv
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>

f-myth-users at media

Dec 3, 2007, 12:42 AM

Post #14 of 29 (1709 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Sat, 01 Dec 2007 19:04:43 -0500
> From: "Michael T. Dean" <mtdean [at] thirdcontact>

> Fine. Look. I'm not saying that it can't be improved--there's not a
> single place in Myth that can't be improved, as nothing is ever
> perfect. I'm simply saying that the "recent OP"--the guy who has a
> problem and restarted this thread--can make his system perform much
> better. It seems there are far too many people trying to convince him
> that there's nothing he can do because Myth is broken and the scheduler
> was improperly designed.

That's not what I'm trying to do. I -am- trying to keep him from
wasting too much time following cargo-cult advice. If he finds that
turning on slow-deletes works, great! (But he sent mail that it
didn't help.) I got burned by that sort of advice twice (once before
I even joined this list, as my very first experience with Myth ever,
when I tried [and abandoned] KnoppMyth when it had a serious bug that
"reinstall a zillion times---clearly -you- must be screwing up and
doing it wrong" sorts of advice (from its maintainer, even) never
fixed---while I wasted an entire week at it---until another user found
the critical DB bug in that version of KnoppMyth that absolutely
prevented any separate-machine FE/BE install from working; and once
here, when I was trying to reduce the number of serious glitches in my
recordings and was given a whole lot of things to do that chewed up a
lot of time and were, in the end, unrelated to actually fixing it
(that was Chris' fix to the locking problem w/writing streams vs DB
locks).

I'm not saying necessarily that your advice here is cargo-cultish,
but OTOH, I -was- giving a pretty obvious bit of advice to pay
attention to how the scheduler may be screwing the OP, and in fact
that seems so far to be getting borne out for at least some posters.

> Daniel and Janne have actually made some changes that make the
> scheduling faster (maybe only committed to multirec so far, though--I
> don't remember). Chris Pinkham promised you that he would make (and he
> has already made some) changes which make the DB locking less of an
> issue. Others may have also made some changes.

Yes, and they're all good. I appreciate them, and I'm sure others do, too.

> Though I can't guarantee it--because I'm a non-dev and because I don't
> make decisions (so arguing with me is really a waste of both our time),
> I'm pretty certain no major rewrite of the scheduler will be done in
> 0.20-fixes (stable) branch.

Of course not. But I do assume that it's helpful that those who might
be in the position to make patches continue to be aware that scheduler
performance is awful in some cases, and that either it needs to get
fixed, or those things that tend to hang on the awful performance need
to get disentangled somehow so the bad performance isn't noticeable.
That's all.

> I'm even more certain that no major rewrite
> of the scheduler will be done in the 0.18-fixes (archive) branch.

Strawman argument. All the other users reporting either (a) slow
deletes or (b) poor scheduler performance have been running recent
revisions, so the fact that I'm not doesn't invalidate -their- reports.

> > for some users.

> And /there/ is the important point. Find out why it's only /some/ users
> and--if, in fact, the configuration difference is a Myth bug, I will fix
> it for you.

That's a very generous offer; thank you.

Unfortunately, figuring this out may require some cooperation from
users who never see slow deletes or poor scheduler performance.

> > Now, as for your own performance, can you please run a couple of tests
> > and let us know how -your- system performs?

> That will have to wait until I can find the time.

Okay, but then you should be cautious attributing all of the reported
problems to "configuration errors" until you -do- have the time; otherwise,
it looks like you may be trying to dodge perhaps being shown that even
-your- system may suffer from the same problems.

A really quick suggestion would be to simply -not- delete anything for
the next few hours or days until you have a stack of 3-5 things that
you'd really like to delete. Then try to delete them, one at a time,
as fast as possible from the frontend UI and see if it ever seems to
pause. That test shouldn't take substantially longer than it would
have taken you to do each delete singly; it just requires remembering
what you meant to delete (and being able to navigate to each one fast
enough to show up the problem; if it takes you 30 seconds of scrolling
around in the listing to find each thing you want to delete, you may
not see the bug).

Actually, you also won't see it if your scheduler runs really fast;
I assume you have verbose logging enabled on your backend, so what's
a typical time for your

Scheduled 1247 items in 30.0 = 1.96 match + 28.08 place

lines? Mine averages 43.2 seconds across a sample of the last 35
items scheduled; smallest 27.52, largest 57.14. Of that, match is
almost always 0.00, but half a dozen were around 0.1 seconds, and
three were large: 4.75, 2.19, 1.96. Scheduled 1247 +- 1 items each
run until mfdb intervened; then scheduled 1317 until a few hours ago,
then 1312. So that gives you the order of magnitude of things.

(Of course, those 1247/1312 numbers are before duplicate & deactivated
elimination! I have 169 items scheduled to my current horizon 13 days
from now, or 13/day.)

> > If you cannot ever get the UI to pause while doing this, I'm sure many
> > of us would be interested to know the size of your oldrecorded tables

> 7072 in oldrecorded, 2903907 in recordedseek, 4577 in recordedmarkup

10797 in oldrecorded, 2624120 in recordedmarkup (no recordedseek
'cause that's not in 0.18.1)

> > and the number and makeup of your recording rules,

> 78 in record (all are currently always/any channel rules--though I often
> add up to 20 find once/any channel rules for movies), 413 in
> recordmatch, 437 in recorded.

411 in record (of which maybe a dozen are always/any channel and most
of the rest are always/one channel), 2090 in recordmatch, 372 in recorded.

Btw, 125 in channel, and 6 tuners. Everything in SD, FWIW.

Note that I seem to have 5 times as many entries in record, and 5
times as many in recordmatch, as you seem to; I'd be surprised if
scheduler performance was linearly related to those ratios, but it
could be 5 to 25 times worse for me than for you if so. And that's
assuming no O(n^2) steps anywhere. (I'd rather hope that things are
more like linear or sublinear, but I haven't analyzed the computational
complexity of either the BUSQ or how the scheduler uses it, so I dunno.)

> Every table in the DB is MyISAM with the exception of the new
> MythWeather tables, which are InnoDB (see
> http://www.gossamer-threads.com/lists/mythtv/dev/302941#302941 ).

All of my tables are MyISAM.

> Oh, and, I've deleted at least 10 shows today, so those numbers were higher.

Ach! You could have run the simple test! :)

> > as mentioned in
> > that old thread about scheduler performance from a few months ago.
> > Also interesting would be the match/place lines from your sched logs,
> > and the contents of your MySQL conf (e.g., memory allocations, etc).

> It's the default one that's installed from a source install--with the
> one exception that I disabled (commented) log-bin.

Mine is nonstandard; making the changes helped a great deal and
speeded up everything having to do with scheduling, but not enough,
and making the numbers even larger didn't seem to help much or at all
(though I don't recall if it was because the tables weren't getting
fully used, or because then I ran into swapping instead):

# ++ The "doubled" values, plus the tmp_table_size.
myisam_sort_buffer_size = 128M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 4M
table_cache = 2048
thread_cache_size = 128
max_allowed_packet = 32M
query_cache_limit = 2M
query_cache_size = 64M
# Increased from 16M (above, now commented-out) to 64M on 1/17/07:
[and then doubled again]
key_buffer = 128M
# New.
tmp_table_size = 128M
# --

[The original values were as follows:
# ++ The "undoubled" values.
# myisam_sort_buffer_size = 64M
# join_buffer_size = 1M
# read_buffer_size = 1M
# sort_buffer_size = 2M
# table_cache = 1024
# thread_cache_size = 64
# max_allowed_packet = 16M
# query_cache_limit = 1M
# query_cache_size = 32M
# # Increased from 16M (above, now commented-out) to 64M on 1/17/07:
# key_buffer = 64M
## --
]

The backend has 1G RAM and doesn't ever use the swap it has available;
perhaps half of that is taken up by large ivtv 0.4.1 buffers for the 5
tuners in that machine. (I could get an exact figure if necessary; I
documented it and even sent it in traffic to the ivtv list maybe a year
or two ago.)

> > > Never optimize before profiling. IMHO, picking something--no matter how
> > > "likely" to be the problem--and changing it without actually proving it
> > > is the source of the problem is just a waste of time.
> >
> > You seem to be completely ignoring the fact that you've been told
> > repeatedly that simple reschedule -is- a big problem,

> I've been told a lot of things. I believe very little of of what I'm
> told until I have proof.

What sort of proof would you find acceptable? Will you not believe
that the scheduler is slow until you see it on your own machine? Will
you not believe that such slowness is not something you can conveniently
blame on the user until it bites you personally? You of all people
should be aware that there are plenty of configurations for which Myth
is supposed to work but which are buggy; just because one user is in
such a situation doesn't make the bug for -her- configuration any less
valid just because it doesn't come up for you. (E.g., if we were
talking about something that broke PAL, you'd never see it---but that
wouldn't make it any less of a bug for UK users.)

> > P.S. One way of verifying your contention that it's the actual
> > removal of the recordedseek info that's causing deletions to
> > be slow

> I _never_ said that removal of recordedseek info causes deletion to be
> slow.

You seemed to imply it when you said this:

> I really think it's
> a file system thing--i.e. disk thrashing as XFS tries to delete the
> recording at full speed 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).

and this:

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

If that's not what you meant, I apologize for tarring you with that brush.
But if that's not what you meant in those two excerpts, what -did- you mean?

(Are the -entries- removed slowly? Why are they delayed -at all- if
the point of slow deletes is to compensate for -filesystems- that are
slow to delete big files? And if they're simply -delayed- but then
deleted all at once, how does -that- help? If the point was that
deleting those entries made other things slow, it just moves the
inevitable hang out to sometime in the future... unless it's your
contention that "fast-delete" filesystems are in fact not "fast"
unless absolutely no other disk activity is happening. I disbelieve.
If a typical deletion of a large file takes tens of milliseconds, it's
very hard to see how that small an amount of disk access can get in
the way of MySQL's accesses, or vice versa. And, of course, we can
-completely- eliminate this hypothesis by renaming the original and
substituting a zero-length file before deletion, as I suggested earlier.)

> As a matter of fact, I have never even agreed that deletion is
> slow--as it isn't for me.

You haven't tried the critical experiment (so far as you've told us),
which is to do MORE THAN ONE in quick succession.

> I simply made a comment which tried to point out that--despite what many
> had said in the thread--enabling slow deletes on a fast filesystem
> /does/ have an effect. I did not say that effect would fix the
> slowness. I did not say that removing records from recordedseek is slow.

Okay. I'd be interested know know exactly what effect this has for
people who run fast filesystems (e.g., anything but ext3fs), whether
or not they think deletion is slow on their machines. I've certainly
never seen -any- unlink under JFS -ever- be slow -or- cause the disk
to thrash and slow down other processes, so now -I'm- doubting -you-.
So how does slow-delete help in this situation?

> Far too many people seem to be forgetting that there's a /lot/ going on
> in the system and that few people have a clear picture of all that
> happens. I know enough about what happens to know that I don't have a
> clear picture of all that happens. Because of this, even things that
> don't seem to be relevant may actually be relevant.

Of course. But by the same token, I (and others) are talking about
something (scheduling) that doesn't at first glance seem relevant to
deletion, either, but it -is-.
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Dec 3, 2007, 4:51 AM

Post #15 of 29 (1708 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On 12/03/2007 03:42 AM, f-myth-users [at] media wrote:
> (Are the -entries- removed slowly? Why are they delayed -at all-

They're deleted after the file is deleted. Typically, a file is deleted
at a rate of 1GiB/2min 10sec or so, so...

> if
> the point of slow deletes is to compensate for -filesystems- that are
> slow to delete big files? And if they're simply -delayed- but then
> deleted all at once, how does -that- help?

Because it means fewer things are happening /at the same time/. That is
the /only/ point I'm trying to make. Do (enough) fewer things at the
same time and the computer doesn't tend to seem to lock up...

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Dec 3, 2007, 1:13 PM

Post #16 of 29 (1696 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Mon, 3 Dec 2007 14:37:32 -0500 (EST)
> From: mons37318 [at] mypacks

> someone said (but i deleted the attribution - sorry)

That was me.

> >A really quick suggestion would be to simply -not- delete anything for
> >the next few hours or days until you have a stack of 3-5 things that
> >you'd really like to delete. Then try to delete them, one at a time,
> >as fast as possible from the frontend UI and see if it ever seems to
> >pause. That test shouldn't take substantially longer than it would
> >have taken you to do each delete singly; it just requires remembering
> >what you meant to delete (and being able to navigate to each one fast
> >enough to show up the problem; if it takes you 30 seconds of scrolling
> >around in the listing to find each thing you want to delete,

> if whatever version my might be using (wasn't quite able to follow
> that) has Recording groups) put all of those show is one group, filter
> to show just that group, and then delete. you'll be able to remember
> them *and* find, without scrolling around.

I thought of that, but the message was long enough as it was, and I
didn't want to confuse the situation. After all, the test probably
isn't valid if the whole -group- gets deleted at once instead, and
while I knew Mike wouldn't do that instead, someone else might and
muddy the waters, and I didn't want to have to get into it. (And I
have no idea---but I'd be surprised---if mere presence in the same
playback or record group would change the outcome, but I didn't want
to chance it.)
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

ylee at pobox

Dec 3, 2007, 7:17 PM

Post #17 of 29 (1708 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

f-myth-users [at] media <f-myth-users [at] media> says:
> Actually, you also won't see it if your scheduler runs really fast;
> I assume you have verbose logging enabled on your backend, so what's
> a typical time for your
>
> Scheduled 1247 items in 30.0 = 1.96 match + 28.08 place
>
> lines? Mine averages 43.2 seconds across a sample of the last 35
> items scheduled; smallest 27.52, largest 57.14.

5.96 mean, 4.5 median for past 73 items over the past 28
hours. Smallest 1.5, largest 43.7 (very much an outlier; other than
two others at 12.2 and 14.8 seconds during a mythfilldatabase run, no
other times are above 10 seconds).

> Of that, match is almost always 0.00, but half a dozen were around
> 0.1 seconds, and three were large: 4.75, 2.19, 1.96.

21 at 0.00, ten at 0.01, five at 0.02, one at 0.03, two at 0.04, two
at 0.09, 26 at 0.20-0.29, one at 0.37, one at 0.41, one at 0.45, one
at 1.46, one at 2.30, one at 4.33.

> Scheduled 1247 +- 1 items each run until mfdb intervened; then
> scheduled 1317 until a few hours ago, then 1312.

789 items +- ten items each run until I did my "look over the schedule
over the next two weeks and schedule the items I want" routine, which
I do every two weeks, after which the figure got as high as 890 (but
without a perceptible jump in the scheduler time; the 890-item run
finished in 4.5 seconds, or 0.25 + 4.28.)

> > 7072 in oldrecorded, 2903907 in recordedseek, 4577 in
> > recordedmarkup
>
> 10797 in oldrecorded, 2624120 in recordedmarkup (no recordedseek
> 'cause that's not in 0.18.1)

11053 in oldrecorded
9890378 in recordedseek
5017 in recordedmarkup

> > 78 in record (all are currently always/any channel rules--though I
> > often add up to 20 find once/any channel rules for movies), 413 in
> > recordmatch, 437 in recorded.
>
> 411 in record (of which maybe a dozen are always/any channel and
> most of the rest are always/one channel), 2090 in recordmatch, 372
> in recorded.

192 in record, 1192 in recordmatch, 1096 in recorded.

> Btw, 125 in channel, and 6 tuners. Everything in SD, FWIW.

224 in channel, four tuners, all HD capable.

> > Every table in the DB is MyISAM with the exception of the new
> > MythWeather tables, which are InnoDB (see
> > http://www.gossamer-threads.com/lists/mythtv/dev/302941#302941 ).
>
> All of my tables are MyISAM.

All of my tables are InnoDB except for nestitle; however, I did not
see any difference in performance with MyISAM.

The relevant portions of my /etc/my.cnf:

skip-locking
key_buffer = 384M # Ideally enough for all .MYI files
max_allowed_packet = 16M
table_cache = 256
sort_buffer_size = 48M
net_buffer_length = 8M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 6M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

set-variable=thread_stack=256k

> The backend has 1G RAM and doesn't ever use the swap it has
> available; perhaps half of that is taken up by large ivtv 0.4.1
> buffers for the 5 tuners in that machine. (I could get an exact
> figure if necessary; I documented it and even sent it in traffic to
> the ivtv list maybe a year or two ago.)

Pentium 4 3.0GHz frontend/backend with 2GB here. The database is on a
separate partition of the boot drive. This backend handles all the
recording duties to both the local 1.5GB RAID 5 array and the remote
backend's 7GB RAID 6 array, and does not run any user jobs;
conversely, the remote backend does commflagging and transcoding but
not recording. All relevant file systems use JFS and CIFS.

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

It is important to note that I have long run my own patched versions
of the ATrpms 0.20.x distribution. (I really ought to release my
patched src.rpm, with lots more patches--many backported from
0.21--one of these days.) Patches relevant to the discussion are:

Attachments:

patch-1660.cpp (14.9 KB)

patch-ThreadedFileWriter.cpp (0.98 KB)

patch-mainserver.cpp (0.74 KB)

patch-1660-2.cpp (4.21 KB)

mtdean at thirdcontact

Dec 4, 2007, 7:49 AM

Post #18 of 29 (1689 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

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.

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

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.

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

f-myth-users at media

Dec 4, 2007, 6:45 PM

Post #19 of 29 (1676 views)
Permalink

Slow MySQL query after delete [In reply to]

> Date: Tue, 04 Dec 2007 10:49:45 -0500
> From: "Michael T. Dean" <mtdean [at] thirdcontact>

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

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

> 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. After all, we -know-
that ext3 is a dog when it comes to deleting large files...
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mtdean at thirdcontact

Dec 4, 2007, 9:16 PM

Post #20 of 29 (1691 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

On 12/04/2007 09:45 PM, f-myth-users [at] media 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,
then delete.

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

Mike
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

cpinkham at bc2va

Dec 4, 2007, 10:42 PM

Post #21 of 29 (1667 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

* On Wed Dec 05, 2007 at 12:16:45AM -0500, Michael T. Dean wrote:
> 2007-12-04 22:00:08.327 scheduler: Scheduled items: Scheduled 265 items
> in 2.8 = 0.00 match + 2.79 place

It probably doesn't affect you much, but tonight I found an issue
in the interaction between the delete code on the frontend and the
scheduler on the backend. In current SVN, with the new Watch List,
we update the 'last_delete' field in the record table whenever we
delete a recording. This is done on the frontend before it tells
the backend to delete the recording. This update hangs if the
scheduler is currently running because the scheduler has an implicit
read-only lock on the record table. So, for people who have long
scheduler runs, if they try to delete a recording while the
scheduler is running, they will experience a hang on the frontend
until the scheduler's BUQ is finished.

I'm sure you saw it, but for other's reading this thread, I did
commit a patch tonight which will eliminate the reload of the complete
recording list every time you delete a recording. This was happening
twice for every recording deleted (unless you did them fairly close
together and have slow deletes turned off). With the current code,
the frontend will delete the recording's info from its in-memory
program cache rather than reloading the complete list from the
backend. This applies whenever a recording is deleted, whether it
is manual by the user or by the autoexpirer or by another user on
another frontend.

http://svn.mythtv.org/trac/changeset/15059

--
Chris
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

mikerice1969 at gmail

Dec 4, 2007, 11:07 PM

Post #22 of 29 (1668 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

hOn Dec 4, 2007 10:42 PM, Chris Pinkham <cpinkham [at] bc2va> wrote:
> * On Wed Dec 05, 2007 at 12:16:45AM -0500, Michael T. Dean wrote:
> > 2007-12-04 22:00:08.327 scheduler: Scheduled items: Scheduled 265 items
> > in 2.8 = 0.00 match + 2.79 place
>
> It probably doesn't affect you much, but tonight I found an issue
> in the interaction between the delete code on the frontend and the
> scheduler on the backend. In current SVN, with the new Watch List,
> we update the 'last_delete' field in the record table whenever we
> delete a recording. This is done on the frontend before it tells
> the backend to delete the recording. This update hangs if the
> scheduler is currently running because the scheduler has an implicit
> read-only lock on the record table. So, for people who have long
> scheduler runs, if they try to delete a recording while the
> scheduler is running, they will experience a hang on the frontend
> until the scheduler's BUQ is finished.

That's exactly the problem I've been seeing (also the hanging in the
delete dialog mentioned in the ticket).
Thanks for looking into these!
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

ylee at pobox

Dec 5, 2007, 6:02 AM

Post #23 of 29 (1670 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

Michael T. Dean <mtdean [at] thirdcontact> says:
> 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 on me.

What about deleting original recording files after transcoding? Does
0.21 use the same slow-delete code for this purpose, or do you have
the system save original files and delete them manually at non-busy
times, or do you never transcode?

--
Frontend/backend: P4 3.0GHz, 1.5TB software RAID 5 array
Slave backend: Quad-core Xeon 1.6GHz, 6.6TB sw RAID 6
Video inputs: Four high-definition over FireWire/OTA
Accessories: 47" 1080p LCD, 5.1 digital, and MX-600
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

cpinkham at bc2va

Dec 5, 2007, 6:12 AM

Post #24 of 29 (1679 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

* On Wed Dec 05, 2007 at 06:02:48AM -0800, Yeechang Lee wrote:
> What about deleting original recording files after transcoding? Does
> 0.21 use the same slow-delete code for this purpose, or do you have
> the system save original files and delete them manually at non-busy
> times, or do you never transcode?

No, these files are no longer under Myth's control (since mythtranscode
renamed them), so mythtranscode deletes them with a simple call
to unlink(). We din't want to create a command on the backend
to allow deleting any file, so this has to be this way for now.

Perhaps when I get around to finishing my multiple-file-per-recording
patch, then the backend can do this actual deletion. Or maybe someone
will get fed up with it enough to add slow-deletion code to
mythtranscode. :) I run all my transcoding jobs after midnight,
so it doesn't bother me too much right now.

--
Chris
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

bolek-mythtv at curl

Dec 5, 2007, 6:33 AM

Post #25 of 29 (1672 views)
Permalink

Re: Slow MySQL query after delete [In reply to]

Michael T. Dean wrote:
> 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,
> then delete.

Are you sure about this part? Delete from DB has it's own separate lock
from the truncate lock. From MainServer::DoDeleteThread():

deletelock.lock();

[...]

DoDeleteInDB(ds);

if (pginfo->recgroup != "LiveTV")
ScheduledRecording::signalChange(0);

deletelock.unlock();

if (slowDeletes && fd != -1)
TruncateAndClose(pginfo, fd, ds->filename, size);

So all the deletes from DB (and unlinks) happen right away and only the
slow truncates are delayed, no?

Bolek
_______________________________________________
mythtv-users mailing list
mythtv-users [at] mythtv
http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users

1 2

View All

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads