[mythtv-users] Slow program listings and deleting

[Michael T. Dean]

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 
listings.

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 
helpful).

HTH,
Mike