[mythtv] recordedmarkup table, commercial flagging,
cpinkham at bc2va.org
Sun Jul 25 14:30:52 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).
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). :)
> 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.
> 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.
> 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.
> 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
> 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.
> 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?
Not sure about this or the people table you mentioned.
More information about the mythtv-dev