[mythtv] recordedmarkup table, commercial flagging, and general db size

Geoffrey Hausheer mythtv0368 at phracturedblue.com
Sun Jul 25 11:02:31 EDT 2004

I just enabled commercial flagging on my mythtv box, and noticed that
the recordedmarkup table grew by about 30%.  It is now an order of
magnitude larger than any other table.  programgenres and people are
also huge.  The overall database is now nearly 200MB, which would be
fine, except that with my rotating daily backups, this is almost 2
gigs (every file in the db changes every day so even my incremental
backups don't help).

So I'm looking for ways to prune the db, and noticed several things:
1) I transcode all of my programs, which gaurantees that the index is
stored in the nuv, and I shouldn't need it in the recordedmarkup
table.  The transcoder removes all type '7' rows (MARK_KEYFRAME), but
leaves all the rest of the rows. This still seems like the right thing
to do.

2) The recordedmarkup table is filled with ~~99%  type '6' rows
(MARK_GOP_START)  (over 2 million rows)
3) The reason the recordedmarkup tabble grew so much was because the
commercial flagging needs to decode every program, resulting in
generating MARK_GOP_START for every GOP in every recording

Looking at the code, I can't really tell what the difference between
MARK_GOP_START and MARK_KEYFRAME  is.  Why do we need both of these? 
Is there any way we could consolidate them (and store them in the .nuv
file instead of the db)?

The next question is why does the commercial flag code store
MARK_BLANK_FRAME and MARK_SCENE_CHANGE?  Don't these get pprocessed
and turned into MARK_COMM_START and MARK_COMM_END? (not that it is a
real big deal since it is only a few thousand rows, but just curious
if there is a need)

And lastly, can we clean up the programgenres table?  Why does it
contain the program type for every program since it  was created?  Why
isn't it part of the 'program' table?

I guess I can understand why the people table can't be pruned, and in
theory it should grow at a reasonably low rate after the first few
weeks, but  it i still about 10 Megs in size for what is basically a
way  to speed up searches.


More information about the mythtv-dev mailing list