[mythtv-users] Slow program listings and deleting
Rob Willett (Mythtv)
rob.mythtv2 at robertwillett.com
Wed May 16 14:32:59 UTC 2007
Bingo. This worked. It pulled my recordmatch table down to 136 rows from
4012. The execution time is now 0.5 sec as opposed to 70 secs or so.
I'll add this to my crontab file as well as the optimise_mythdb.pl.
Many thanks for your help,
Michael T. Dean wrote:
> On 05/16/2007 07:58 AM, Rob Willett (Mythtv) wrote:
>> I'm trying to analyse why my MythTV system occassionally works slowly on
>> program listings and deleting recorded programs.
>> My system is Mythtv V0.19 running on an AMD 2800XP box. It has 512MB RAM
>> and is dedicated to MythTV. I have two DVB cards in it. It has worked
>> pretty well since I built it Feb last year (2006). I have not upgraded
>> as it runs well, V.20 of MythTV doesn't really have a lot extra that I
>> need and it works. If it ain't broken, don't change it!
>> I checked on my system and I notice that when I do these listings,
>> mysqld jumps to the top of TOP and stays there for a while.
> ... <BSQ removed> ...
>> based on reading the mysql manual it appears that this query took 54
>> seconds to complete. It also returns a lot of lines, 8024, which does
>> appear to be rather a lot. When I ran the query manually and looked at
>> the results, it returns programs that were recorded but have been
>> deleted. This may be the right behavior, I have no idea and guidance
>> here would be welcome.
>> I looked at some of the tables mentioned here and got a count of the size:
>> recordmatch - 4012 records. Which is interesting as it is half the
>> number of rows returned. I also noticed that the above monster query
>> seems to return duplicates.
>> Googling for recordmatch doesn't give me any more information on what
>> recordmatch does. Looking at the top 10 entries seems to show the last
>> 10 things I recorded. I wonder if this table is growing and not being
>> pruned? I looked through the installation again and can't see anything
>> about clearing out old records from tables. No crontab entries. I also
>> looked and ran the optimise_database script. No changes to the results.
>> So my questions are, since you've got this far,
>> 1) Should the table recordmatch be so large? Is 4,000 entries a lot?
>> 2) Should data be deleted from this table? if so how?
>> 3) What sizes are other peoples recordmatch tables?
>> 4) Does the monster SQL statement above look familiar? Does it have form
>> as a slow query? Are there additional indexes that could be created? I
>> did an EXPLAIN on it, but it doesn't give me any real help.
>> 5) Any suggestions as to what to do?
>> My DB skills are limited, I'm a network engineer, so am wary of going
>> into DB's and making changes willy-nilly.
> My guess--based on your location and your symptoms--is that you're using
> EIT to populate your listings and that you do /not/ run
> mythfilldatabase. If that's the case, run "mythfilldatabase
> --no-delete" to clean out old data from your DB. If all your video
> sources are set to EIT only or No Grabber, this will do nothing to your
> In 0.20-fixes and below, it is /mandatory/ that everyone run
> mythfilldatabase "every once in a while" to maintain optimal performance
> because it cleans out the old data from the database. Even if you're
> not using it to populate your listings, you need to run it. So, how
> often is "every once in a while?" Well, I run it every day (but then
> again, it populates my listings :). If you're using EIT only, you could
> set up a daily/weekly/monthly cron job to run it. I think most of the
> devs who use EIT only used to run it about once a month or two.
> "Used to run it?" In SVN trunk (and 0.21 when it's released), running
> mythfilldatabase will no longer be required as all the cleanup has been
> moved to the backend.
> Once you've let mfdb clean out the old data, run optimize_mythdb.pl from
> the contrib directory. Also, I highly recommend you run
> optimize_mythdb.pl in a cron job (I run it daily, but even weekly is
> mythtv-users mailing list
> mythtv-users at mythtv.org
More information about the mythtv-users