[mythtv-users] Slow DB with large Previous Recordings

Michael T. Dean mtdean at thirdcontact.com
Sun Aug 27 23:39:25 UTC 2006

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.


More information about the mythtv-users mailing list