[mythtv] recordedmarkup table, commercial flagging,

[Geoffrey Hausheer]

On Sun, 25 Jul 2004 14:30:52 -0400 (EDT), Chris Pinkham wrote:
> > 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).
> 
> My /var/lib/mysql/mythconverg/ directory is about 85MB, but my daily backups
> are only about 8MB each, so a week's worth are less than 60MB (which is
> smaller than the original DB usage).  I backup my database using
> mysqldump and then gzip the output.  Are you just copying the original
> datafiles?  Are you shutting down the database to do it or just copying the
> files/directory over?  If you switched to using mysqldump and gzip, you'd
> save over a gig if that's your concern.  A whole 30-60 minutes of recording
> time depending on bitrate). :)
Well, that  is a royal pain in the ass.  My system is used for more
than myth, and writing special methods for backing up myth as opposed
to everything else is not something I really want to do.

> 
> > 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.
> 
> If you absolutely want to start changing things in your DB, you could
> remove all MARK_KEYFRAME records for transcoded programs and for programs
> that you know recorded correctly and have a positionmap in the .nuv file.
> The MARK_KEYFRAME entries are only used for software-encoded videos and are
> there as a backup for videos that are terminated early because of a problem
> or because of file corruption.

As I said, mythtranscode already does this.

> 
> > 2) The recordedmarkup table is filled with ~~99%  type '6' rows
> > (MARK_GOP_START)  (over 2 million rows)
> 
> These are used by the mpegrecorder.cpp it seems so I assume that's the
> recorder you're using.  These are absolutely necessary unless you want to
> wait for the player to scan your video file everytime you hit fast-forward.

They are also used in avformat decoder, which is what confused me.  It
is true that these were originally mpeg2 recordings that have been
transcoded to mpeg4.   The question is, I guess, are these left-over
vestiges of the original mpeg2 recroding?  Or does avformat decoder
generate MARK_GOP_FRAME for nuv's too? (the mpeg2 GOP is bascially
equivalent to a key-frame in nuv..I know the GOP contains header info
too...)  Since the number of MARK_GOP_START frames increased once I
commercial-flagged my (already  transcoded) mpeg4 nuv streams, I
assumed that avformat decoder was generating them.
> 
> > 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
> 
> The MARK_GOP_START entries should be there anyway since they are saved during
> recording.  If the commercial detection code notices that a keyframe/gop list
> does not exist for a recording, it will tell the decoder to save the current
> positionmap to the database so that commercial skips will be near-instantaneous
> rather than having to scan the video file to get to the desired frame.

As I said, the transcoder saves the keyframe list, so it is gauranteed
that this is availiable.
> 
> > 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)?
> 
> GOPs are mpeg2, KEYFRAMEs are for the nuppel/mpeg4 stuff.  KEYFRAMEs are
> stored in the .nuv, but are in the DB as explained above also for several
> reasons.
Yes I know all about KEYFRAMES, I am wondering why MARK_GOP_START is
getting generated for mpeg4 nuvs when I run the commercial flagger

> 
> > 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)
> 
> For software encoding, the Blank Frame info is actually captured during
> recording and can be used by itself immediately after recording to do
> commercial skipping.  The scene change info is in there so it could be
> utilized later, but right now I don't think it is being used anywhere and
> could probably be removed.  The player can only use the blank frame info
> and the commercial skip list info currently.  If the commercial skip list
> does not exist it will try the blank frame info if that exists.  I've also
> used the blank frame info from other people occasionally to checkout bugs
> in the commercial detection code rather than having to have them send me
> their whole 2-Gig video file. :)  May be able to get rid of the blank frame
> info after the cutlist is successfully written now though.

Okay, thiis makes sense.  As I said, it is less than 1% of the db, no
reason to deal with it now.  I may need to think aout it more
carefully though.  After transcoding I'm not 100% sure the blank-frame
list will be correct if it was really generated durinng recording.  It
should be, but if it ends up off by a couple frames, that'd be kinda
bad.