[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