[mythtv-users] Slow MySQL query after delete

Larry K lunchtimelarry at gmail.com
Thu Nov 29 02:08:48 UTC 2007


BUSQ refers to the Big Ugly Slow Query that is driving everyone crazy.  It
joins several tables, but not recordedseek.  That's why I wondered if
converting this table would help me.

I assume you just did an alter table recordedseek engine innodb?


On 11/28/07, ross camm <rossco at whyza.net> wrote:
>
> I must admit i am not familiar with the term BUSQ
>
> I am also a sql novice...but since I did have significant performance
> issues, including deletions, I investigated mysql further, and achived
> significant performance gains.
>
> My understanding is the MyISAM locks the whole table, where as innoDB
> supports row locking, hence other sql threads can still access the
> table.
>
> my understanding also is that MyISAM relies on the OS file system cache,
> whereas innoDB will cache directly. On a busy mythbackend, the os
> filesystem cache is not that useful due to the amount of data that the
> backend is constantly moving.
>
> And since recordedseek hold all the keyframes, it is a very heavily used
> table that needs to be accessed very regularly, from what I can tell,
> such as when recording a program, loading a recorded program in the
> frontend, or deleting programs.
>
> I was getting timeouts loading recorded programs in the frontend that
> where more than 2 hours in length....but a retry would work instantly.
>
> This was all fixed with the conversion to innoDB
>
> Hope this helps.
>
>
>
> On Wed, 2007-11-28 at 20:32 -0500, Larry K wrote:
> > 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
> >
> > _______________________________________________
> > 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/00a8d7e4/attachment.htm 


More information about the mythtv-users mailing list