[mythtv-users] Slow DB with large Previous Recordings

[Michael T. Dean]

On 08/27/06 18:12, Ben Curtis wrote:

>So I've been running Myth for many a year now, and recorded about a
>billion shows.  I like the idea that re-runs never get recorded, but I've
>noticed that the more I've recorded, the slower the database responds to
>things like pulling up a guide, etc.  Every time I put together a system
>for friends, I'm amazed at how fast it is compared to mine, especially
>when it's on systems half the speed (I'm running an Intel 3ghz P4).
>

It's unlikely that the oldrecorded table (the one that contains the 
information about what you've previously recorded) is slowing you down.

On my system, MythWeb's Statistics module shows me:

Number of shows: 550
Number of epsodes: 4193
First recording: Monday May 3rd, 2004
Last recording: Saturday August 26th, 2006
Total Time: 139 days 11 hrs 47 mins wasted

So, that's 4139 programs recorded.  However, even though my 
/var/lib/mysql/mythconverg directory has a total size of 223MiB, 
oldrecorded is only:

-rw-rw----  1 mysql mysql 1015320 Aug 27 19:00 oldrecorded.MYD
-rw-rw----  1 mysql mysql  756736 Aug 27 19:00 oldrecorded.MYI

Yes.  That's less than 1MiB (991.5kiB).  So, what's taking up all the space:

-rw-rw----  1 mysql mysql 46947228 Aug 27 19:10 recordedmarkup.MYD
-rw-rw----  1 mysql mysql 41223168 Aug 27 19:10 recordedmarkup.MYI
-rw-rw----  1 mysql mysql 48571712 Aug 27 19:26 recordedseek.MYD
-rw-rw----  1 mysql mysql 42945536 Aug 27 19:26 recordedseek.MYI
-rw-rw----  1 mysql mysql 20994988 Jul 14 19:51 romdb.MYD
-rw-rw----  1 mysql mysql 13894656 Jul 14 19:55 romdb.MYI

So, nearly all of the space is in my recordedmarkup and recordedseek 
tables (and romdb--which is for MythGame takes up most everything 
else).  If I wanted to decrease the size of recorded{markup,seek} I 
could a) delete all my existing recordings, or b) transcode all my 
existing recordings to MPEG-4.

Even my program table (with 2 weeks of listings for 13 channels) is less 
than 2MiB:

-rw-rw----  1 mysql mysql 2033208 Aug 26 23:35 program.MYD
-rw-rw----  1 mysql mysql 1174528 Aug 27 17:51 program.MYI

I really think if you run the optimize_mythdb.pl script, it will help 
out a lot.

Mike