[mythtv-users] Slow MySQL query after delete

ross camm rossco at whyza.net
Thu Nov 29 01:56:28 UTC 2007


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



More information about the mythtv-users mailing list