[mythtv-users] Slow MySQL query after delete
lunchtimelarry at gmail.com
Thu Nov 29 01:16:43 UTC 2007
I am also having trouble with the BUSQ when I delete recordings, with query
times no better than 7-8 seconds. This is on an Athlon 2500+ with 512MB
ram. Myth 0.20 and MySQL 5.0.37, I think. Before I upgraded to 0.20, I
was running 0.18 and this problem did not exist for me. I'm guessing this
BUSQ was introduced after 0.18.
I cleaned up my recording schedules to eliminate duplicate schedules (there
were a few), and I also tried to avoid schedules on 'any channel'. I
currently have 4,800 rows in oldrecorded, 550 rows in recordmatch, and 100
rows in record. None of this seems excessive to me. I also optimized my
database, and it still takes 6-7 seconds to run this query.
I wonder if 6-7 seconds is as good as it gets for me? Have I hit the
wall? Can anyone with a config similar to mine get this query to run in say
key_buffer = 16M
table_cache = 128
sort_buffer_size = 2M
myisam_sort_buffer_size = 8M
query_cache_size = 16M
On 11/24/07, crs23 <pvr at groundhog.pair.com> wrote:
> I used MythWeb to both discover duplicates by inspection and delete them.
> That took a long time because of the very problem I was trying to fix but
> each deletion went faster than the last as the query got faster and
> A way to delete multiple recordings or recording rules at once would be
> but I wasn't about to try deleting records directly from the database
> because I don't know if that would cause some kind of inconsistency
> Larry K wrote:
> > What was your method to determine the duplicates? And did you simply
> > issue
> > a SQL delete to get rid of them, or some other mechanism?
> > On 9/6/07, crs23 <pvr at groundhog.pair.com> wrote:
> >> Michael T. Dean wrote:
> >> >
> >> > I don't think much testing has been done on systems having identical
> >> > recording rules. Though it shouldn't cause issues, cleaning out the
> >> > duplicates (triplicates? whatever) would significantly simplify the
> >> > query's processing, so doing so would be very much to your advantage.
> >> >
> >> Done and the improvement was substantial. There were several multiple
> >> entries. One program had about a dozen duplicate entries and that just
> >> happened to be the program with the most previously recorded
> episodes. I
> >> also changed the recordings to be on a particular channel instead of
> >> channel which I think helped to.
> >> I'm now down to ~150,000 rows examined in 5.5 seconds, 696 rows
> >> returned. 5
> >> or 6 seconds is hugely better but still a bit of an annoyance. But I'm
> >> very
> >> pleased with the results so far and the incredible support I've
> >> on
> >> this forum. Thank you to everyone!
> >> --
> >> View this message in context:
> >> Sent from the mythtv-users mailing list archive at Nabble.com.
> >> _______________________________________________
> >> mythtv-users mailing list
> >> mythtv-users at mythtv.org
> >> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> > _______________________________________________
> > mythtv-users mailing list
> > mythtv-users at mythtv.org
> > http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
> View this message in context:
> Sent from the mythtv-users mailing list archive at Nabble.com.
> mythtv-users mailing list
> mythtv-users at mythtv.org
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the mythtv-users