Keeping the DB compressed

[jsteele]

I've been concerned about the ever growing DB size over time. After a few days of EPG updates my database rapidly grows above 60GB and shows no signs of stopping.

So I've worked up a solution to keep the database compressed. I've set up a scheduled task to reboot the system in the early morning hours, ~ 7AM or so, when no one is normally using the system (the drawback obviously is possible recordings scheduled but I'm working on that problem.)

When the system reboots, "GB-PVR Recording Service" is set to Manual rather than Automatic start (the database must be idle for the compress to work.)

As part of the Startup menu I have modified my GBPVR start batch to
- launch MSAccess from the command line with the /compress option
- start the GB-PVR Recording Service
- launch GBPVR in the second display in fullscreen mode.

It's a very simple batch process at bootup:
----------
cd\program files\microsoft office\office
msaccess.exe "c:\program files\devnz\gbpvr\gbpvr.mdb" /compact
cd\
net start "GB-PVR Recording Service"
cd\program files\devnz\gbpvr
start gbpvr.exe -noframe -pos:1280,0,640,480 -topmost
exit
----------
This process keeps my database in the 6-10GB range which seems to help significantly when using the web interface, searches, etc.

The ideal situation would be to be able to tell when the database is idle i.e. no recordings, no one watching TV, etc. At this point the recording service would be stopped to release the database and the compress would run and the recording service restarted. This is beyond the batch capability and the logical place for this would be either in the main code or a plugin if the database status is available somehow.

[smeghead]

I too end up with a large database file and once or twice a week manually compress the file. I seem to remember in one of the threads that sub is working a fix into the next release that compresses the database periodically. sub, is this right?

Also, as part of the gbpvr release, you'll find a program called JETCOMP.exe in the gbpvr directory. I don't know if it has command line parameters but this can be used to compress the database.

[Ton]

Jetcomp has command-line parameters. I compact the databse daily (it's overkill I know, but it seems to work...).

Here's what I've got in my job that stops GBVR each night (I stop GBPVR to compact the databases and be able to copy the log-files):

Note: this happens after I stop gbpvr and have made a backup-copy......

jetcomp -src:gbpvr.mdb -dest:comp_gbpvr.mdb
del gbpvr.mdb
move comp_gbpvr.mdb gbpvr.mdb
jetcomp -src:gbpvr.mdb -dest:comp_gbpvr.mdb
del gbpvr.mdb
move comp_gbpvr.mdb gbpvr.mdb
jetcomp -src:mydatabase.mdb -dest:comp_my.mdb
del mydatabase.mdb
move comp_my.mdb mydatabase.mdb

[smeghead]

Sounds like a good plan. Do you need to stop the tray program as well?

Did you realise you compress the database twice?
Does Jetcomp automatically delete the destination file. If so you could:
copy gbpvr.mdb gbpvr_backup.mdb
jetcomp -src:gbpvr.mdb -dest:comp_gbpvr.mdb
jetcomp -src:comp_gbpvr.mdb -dest:gbpvr.mdb
jetcomp -src:mydatabase.mdb -dest:comp_my.mdb
jetcomp -src:comp_my.mdb -dest:mydatabase.mdb

[jsteele]

I used the native compress facility in MSACCESS because it also repairs any inconsistencies that may occur in the DB during operation, update, etc. I haven't tried JETCOMP but I'm sure it works fine.

The big challenge is to get the database released so the compress can run. I'd rather see some internal process that would do this but we don't have this right now. I also suspect that this is a bigger problem in the case where there are large numbers of channels; in my case we have about 150 channels defined and only 5 days worth of data. The more channels and/or days obviously increase the size of the database and this impacts things like the searches. This is especially noticable in the web interface; when the database is very large (10MB or more) searching for an episode in the web interface can result in stuttering and silent periods on the live TV display. So I thin keeping the DB compact is very important with large numbers of channels and/or days of data.

[sub]

The next release tries to compact the database automatically if it can. It does this after the EPG update. This will automatically take care of checking if any recordings are in progress, dropping any database connections etc.

It may fail to compact the DB if you've installed a plugin or utility that maintains permanent connections to the database.

[jsteele]

The next release tries to compact the database automatically if it can. It does this after the EPG update. This will automatically take care of checking if any recordings are in progress, dropping any database connections etc.

It may fail to compact the DB if you've installed a plugin or utility that maintains permanent connections to the database.

Thanks, we can stop all this glue-on silliness :-)

[Ton]

Well,

The two-times compressing is a copy/paste error. I actually do it only once.

I would like GBPVR to do it automatically, but I'm wondering what happens when the wife forgets to turn of the MVP over night (happens quite often). Will this instance of GBPVR keep the database open??

[sub]

I'm wondering what happens when the wife forgets to turn of the MVP over night (happens quite often). Will this instance of GBPVR keep the database open??

Yes, if the MVP is watching TV, that will keep the database from being compact. If its just sitting at the menu or screen saver, then it wont stop it happening.

If you do leave timeshift live tv running (like on an MVP), or have a recording scheduled for that time, then the worst that is going to happen is your database will be a bit larger for the day, and it'll be shrunk the next night, or the night after that. If this possibility bothers you, you're still welcome to manually compact.

[Ton]

Sounds good!

Looks like it's one more "tweak" I can take out of my system...