[mythtv-users] Slow MySQL query after delete

ross camm rossco at whyza.net
Thu Nov 29 01:22:58 UTC 2007


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



More information about the mythtv-users mailing list