[mythtv-users] Slow MySQL query after delete

[Larry K]

My recordedseek table has 1.1M rows.     But, I don't think that table is
part of the BUSQ.  Why would converting this table to innoDB affect the
BUSQ?

On 11/28/07, ross camm <rossco at whyza.net> wrote:
>
>
> do you have many rows in recordedseek ?
>
> i do, nearly 1.5 million rows, and converting from MyISAM to InnoDB for
> the recordedseek table reduced my query time from 20-30 secs down to 2-3
> secs or less.
>
> as such deletions are now nearly instant.
>
> On Wed, 2007-11-28 at 20:16 -0500, Larry K wrote:
> > 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 1 second?
> >
> > /etc/my.conf:
> >
> > [mysqld]
> > datadir=/var/lib/mysql
> > socket=/var/lib/mysql/mysql.sock
> > key_buffer = 16M
> > table_cache = 128
> > sort_buffer_size = 2M
> > myisam_sort_buffer_size = 8M
> > query_cache_size = 16M
> > log_slow_queries=/var/log/slowsql.log
> > long_query_time=5
> >
> > [mysql.server]
> > user=mysql
> > basedir=/var/lib
> >
> > [safe_mysqld]
> > err-log=/var/log/mysqld.log
> > pid-file=/var/run/mysqld/mysqld.pid
> >
> >
> >
> > 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 faster.
> >         A way to delete multiple recordings or recording rules at once
> >         would be nice
> >         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 between
> >         tables.
> >
> >
> >         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 any
> >         >> 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 received
> >         >> on
> >         >> this forum.  Thank you to everyone!
> >         >>
> >         >> --
> >         >> View this message in context:
> >         >>
> >
> http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a12534738
> >         >> 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:
> >
> http://www.nabble.com/Slow-MySQL-query-after-delete-tf4366877s15552.html#a13926650
> >         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
>
> _______________________________________________
> mythtv-users mailing list
> mythtv-users at mythtv.org
> http://mythtv.org/cgi-bin/mailman/listinfo/mythtv-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20071128/9f76e201/attachment.htm