Any details around the Database Improvements?

[tmiranda]

Originally Posted by stuckless

Or... If a UserRecord had the concept of an owner (ie, a Plugin) and SageTV could just clean them up automatically once a plugin is uninstalled. Having plugin authors do it, is good, but I have feeling not many would actually handle the event. It'll be like the windows registry

That's a good idea too.

[Taddeusz]

Originally Posted by stanger89

FWIW2, 80MB is nothing these days, I've got 80MB pictures.

Depends on your perspective. That doesn't take that much drive storage but that 80MB is being loaded into memory in its entirety.

My SageTV service's memory footprint is nearly 900MB with a 43MB wiz.bin, all the plugins, and 2 extenders. Not sure how that breaks down beyond what I see in the task manager.

[stanger89]

That puts your wiz.bin at less than 5% of your memory footprint. If we're going to fix memory usage/heap size issues/problems/limitations with Sage, the wiz.bin isn't the first place I'd look, I'd be looking at putting each miniclient in it's own VM or something like that, much bigger bang for the buck as it were.

[Narflex]

It's pretty easy to clear all of the historical stuff out of the DB. Just set these properties:

wizard/retain_airings_from_completed_recordings=false
(this'll prevent it from retaining data from things only because they were recorded)
wizard/clear_profile=true
(this'll clear all data relating to intelligent recording, everything watched, all the explicit and implicit don't likes)
wizard/clear_watched=true
(this'll only clear the Watched data vs. clear_profile, which clears a lot more than that)
wizard/last_maintenance=0
(this'll cause DB maintenance to happen after load, which'll then clear all of the expired records from the DB...and if you set the above flags...there'll be lots of those.. and shrink it back down in size; normally this gets run at least once a day)

The clear_watched and clear_profile properties will reset themselves after being read.

[dvd_maniac]

Originally Posted by stanger89

Why? Are you having a problem? The only problems I've every had with Sage were not related to the database, they were always related to orphan files that no longer existed.

FWIW, since the beginning I've seen lots of people wanting to "improve" the Sage database, but rarely, if ever, have I seen anyone provide evidence of an actual problem with Sage's current database, performance or otherwise. It all seems largely academic/philosophical, people assume because it's not a standard format it's not good or something.

FWIW2, 80MB is nothing these days, I've got 80MB pictures.

You are right that I might just be assuming but when I limit my Imported Videos folder my performance issues go away and Sage always shows all the videos.
I was using Fuzzy's advice to build a slave server for imported videos and pointed the slave to import all the same video folders as the main server. and that wiz.bin is only 31MB. The only difference between the 2 is that the slave does not have any EPG data and no plug-ins are installed. The 2 main plug-ins on the main server are CMT & BMT.
Perhaps the issue is in one of them.

Originally Posted by Narflex

It's pretty easy to clear all of the historical stuff out of the DB. Just set these properties:

wizard/retain_airings_from_completed_recordings=false
(this'll prevent it from retaining data from things only because they were recorded)
wizard/clear_profile=true
(this'll clear all data relating to intelligent recording, everything watched, all the explicit and implicit don't likes)
wizard/clear_watched=true
(this'll only clear the Watched data vs. clear_profile, which clears a lot more than that)
wizard/last_maintenance=0
(this'll cause DB maintenance to happen after load, which'll then clear all of the expired records from the DB...and if you set the above flags...there'll be lots of those.. and shrink it back down in size; normally this gets run at least once a day)

The clear_watched and clear_profile properties will reset themselves after being read.

What if I wanted to get rid of all deleted recordings that are not currently marked as watched?

[stanger89]

Originally Posted by dvd_maniac

You are right that I might just be assuming but when I limit my Imported Videos folder my performance issues go away and Sage always shows all the videos.

Last time I had spinning circle performance issues it was a combination of Comskip Playback and "phantom" files. Comskip tries to lookup every file in whatever view you look at, to see if there's an associated EDL or whatever.

The problem for me was, I'd moved or renamed something so I had items in my database that no longer existed, but Sage was keeping them around (for safety) since it could not access the path to know they were not there. This caused the comskip plugin to "hang" while it waited for each file to timeout trying to access. The solution is this:

seeker/disable_root_path_existence_check=true
http://forums.sagetv.com/forums/show...ble_root_path*

It tells Sage to not save files it can't find the parent/root path for (like old UNC paths for servers that got renamed or don't exist, drives that don't exist, etc).

I'm actually having that problem right now since I've migrated to my VM server full time. So I have stuff pointed to \\sagetv, that can't be found since that server is disabled, (\\sagetvm is the new one). I still need to copy my old recordings to the new server, or at least go figure out if there's anything on that I really care about.

I was using Fuzzy's advice to build a slave server for imported videos and pointed the slave to import all the same video folders as the main server. and that wiz.bin is only 31MB. The only difference between the 2 is that the slave does not have any EPG data and no plug-ins are installed. The 2 main plug-ins on the main server are CMT & BMT.
Perhaps the issue is in one of them.

I would definitely suspect one or both of those. And it might not necessarily be their "fault". Simple test is to install those plugins on the slave server. I'd also give the disable_root_path... property a try, maybe you have some phantom files floating around on the main server that Sage is having to wait and time out on.

[Narflex]

Originally Posted by dvd_maniac

What if I wanted to get rid of all deleted recordings that are not currently marked as watched?

Setting this should do it (after a DB maintenance cycle complete):
wizard/retain_airings_from_completed_recordings=false

[dvd_maniac]

Going to try both of these...

seeker/disable_root_path_existence_check=true
wizard/retain_airings_from_completed_recordings=false

I want to state that the server has 16GB of memory and is pretty beefed up. I am not getting spinning wheels and the extenders for the most part are swift. My only real issue is that sometimes when a scan of the imported videos runs it seems to only find about 60-70% of the videos.
When this happens it it usually universal (server/client/extenders) but sometimes is is not...
I'm only guessing that it is either I'm reaching a limit on the amount of imported objects or that my database has got junk in it from a plug-in or something...

[stuckless]

Originally Posted by dvd_maniac

Going to try both of these...

seeker/disable_root_path_existence_check=true
wizard/retain_airings_from_completed_recordings=false

I want to state that the server has 16GB of memory and is pretty beefed up. I am not getting spinning wheels and the extenders for the most part are swift. My only real issue is that sometimes when a scan of the imported videos runs it seems to only find about 60-70% of the videos.
When this happens it it usually universal (server/client/extenders) but sometimes is is not...
I'm only guessing that it is either I'm reaching a limit on the amount of imported objects or that my database has got junk in it from a plug-in or something...

If sage is not finding all your files, then I'd almost put money on the fact that it is dying scanning one of your 1000000s of video files. I've seen many times where sage would die when scanning a .mkv file that it didn't like. I've also seen it die because of UTF8 characters in the filenames. This should be fixed in v9 but in v7 it would likely still be an issue.

[dvd_maniac]

Originally Posted by stuckless

If sage is not finding all your files, then I'd almost put money on the fact that it is dying scanning one of your 1000000s of video files. I've seen many times where sage would die when scanning a .mkv file that it didn't like. I've also seen it die because of UTF8 characters in the filenames. This should be fixed in v9 but in v7 it would likely still be an issue.

I am currently on v9. I only upgraded because I was hopinig the database enhancements would fix my issue.
Is there something in the debug log that would let me know which file? And why would it work 9 out of 10 times if a particular file is a problem or is it one of several parameters that causes the issue?
I also notice that the Memory being used for SageTV sometimes goes as high as 1.2GB but stays under 800MB when I limit my imports... I think the issue happens more so when the memory usage is high.

[Monedeath]

Originally Posted by Fuzzy

I think the largest chunk of Wizard.java is likely detecting and repairing errors. In any case, I think that separating out 'live' and 'archived' data would make everything else far more complicated. You'd have to make dummy copies of every array that the wiz.bin contains, and have some way to move items in and out of them, while still providing the full contents on demand. At current memory prices, I can't see that ever really being worth it. The real advantage of keeping everything in java objects in the JVM is speed. Database lookups are very fast because of this, and because database lookups are so fast, much of the operations sage does with the database are coded for simplicity and robustness, and NOT for efficiency (repetetive iterations through an array, for instance). Slowing down those accesses is likely to cause a very noticeable slowdown on the end result. I don't believe a 90MB database, or even 256MB database size should be a problem, and the only real issue is that many people are now using a majority of miniclients instead of 'fat clients', so the JVM has been easily maxed in normal use, which i don't think was ever the design basis. This is an issue with the overall system architecture, not the database, so it should be fixed by changing the architecture. That's either going to a 64-bit JVM, or breaking out the MiniClientRenderer's to their own miniserver JVM's.

Agreed, my attention on the wiz.bin when directed that way is about conditions regarding the JVM as it stands today. Either obtaining a larger space to operate in beyond the ~1gig JVM point, getting the miniclient into their own JVM memory space (only really beneficial on 64 bit systems though), and/or getting many of the plugins out of the core servers JVM memory space are all far more likely to produce more lasting performance improvements for the proverbial buck.

[stuckless]

Originally Posted by dvd_maniac

I am currently on v9. I only upgraded because I was hopinig the database enhancements would fix my issue.
Is there something in the debug log that would let me know which file? And why would it work 9 out of 10 times if a particular file is a problem or is it one of several parameters that causes the issue?
I also notice that the Memory being used for SageTV sometimes goes as high as 1.2GB but stays under 800MB when I limit my imports... I think the issue happens more so when the memory usage is high.

I guess depending on your JVM size you could be hitting an OutOfMemory error.. Have you enabled debug logging in the Sage.properties? This when I've had files that would stop the sagetv import process... i'd see it show up in the sagetv_0.txt log. It might not be obvious like, "SageTV is aborting file scanning", but if I recall it did show me the file it was having issues with, and remove it, it would then get all the files. Either way, enable debug logging, and do a scan, and see what happens. (I'm pretty sure sagetv will do a full scan on start, so you'll see when you start sagetv).

Originally Posted by Monedeath

Agreed, my attention on the wiz.bin when directed that way is about conditions regarding the JVM as it stands today. Either obtaining a larger space to operate in beyond the ~1gig JVM point, getting the miniclient into their own JVM memory space (only really beneficial on 64 bit systems though), and/or getting many of the plugins out of the core servers JVM memory space are all far more likely to produce more lasting performance improvements for the proverbial buck.

As much as some people may not like the fact that wiz.bin contents is loaded into memory and that it consume 20-100+mb on disk, it really isn't that important compared to the total jvm size. As Stranger89 pointed out someone that it was 5% of their jvm. Also consider that "80mb loaded into memory", doesn't mean it's actually copied byte by byte into memory. It means the contents are processed and cached into memory. This could actually mean that it takes more than 80mb, or it could be less, we don't really know. If you are hitting limits, then "fixing" the wiz.bin isn't going to help. Let's say someone decided to do the effort to totally replace wiz.bin (which would not be easy), and got the storage down to half, let say 40mb on disk for an 80mb wiz.bin. Almost immediately people are going to go, things aren't a fast as they used to be, and then the devs will go, don't worry, we'll speed it up, we'll load it into memory... I deal with databases every day, and almost the first thing you end up doing when you work with a database system that needs near real time speeds is to load it into memory (or build data caches). So before long, you'll be back to where you where in terms of memory (or much worse), since this is one of the most critical pieces of sagetv to make it perform well. If I had to start from scratch today, would I write my own database? no. But I'm willing to bet that when this project started there weren't that many (if any) lean embedded database, like we have today (sqlite, h2, etc). We can debate how getting an 80mb database down to 40mb is going to be the greatest thing ever, but I'm not sure many developers will be convinced (I'm not). There are a few reasons that I'd like to see a sql database used, but not one of them has to do with saving 40mb on the filesystem, or 40mb in memory.

[Monedeath]

80 megs out of a 400mb (default) JVM is more like 20% which wold be significant for someone in that situation. For a 1 gig JVM it is 8%, which is much less so. I was alluding to the current version and steps that can be taken now by users until such time that other options are implemented. Something you can do now that might help is more significant than something that might be available months from now.

From the programming/development standpoint, yes, pursuit of optimizations of the wiz.bin is likely to not produce any significant improvement over other options already mentioned. We're talking past each other on this.

[stanger89]

FWIW, the "normal" size of the wiz.bin is probably less than 50MB. Mine's a bit over 50MB but it's got almost 10 years of watched history. New users are unlikely to run into any issues with the wiz.bin or the 400MB default JVM size.

When you start getting more advanced, it's trivially easy to up the JVM size, but even then you really only need to do that when you start adding lots of extenders or plugins.

Either way, the wiz.bin is probably the most tightly integrated, important, and hard to change part of SageTV and changing it would probably provide the least improvement in performance or memory usage, so I just don't see the point of messing with it.

This seems to be a classic case of fearing what you (the collective you) don't understand. People don't understand the wiz.bin because it's a custom format, so they fear it, they assign all sorts of problems to it that it doesn't deserve, and just want to change it to something more familiar.

[stuckless]

Not that this is an improvement, per se, but something down road, that might be cool, would be a jdbc wrapper for the Wiz.bin. If you had that, then people could use tools like Squirrel SQL to connect to the "database" to better inpect it, etc. It doesn't change the internal format, just how you access it, to make it more familiar

[Fuzzy]

Originally Posted by stuckless

Not that this is an improvement, per se, but something down road, that might be cool, would be a jdbc wrapper for the Wiz.bin. If you had that, then people could use tools like Squirrel SQL to connect to the "database" to better inpect it, etc. It doesn't change the internal format, just how you access it, to make it more familiar

Sounds like a decent plugin to make - but no reason to put that in the core.

[tmiranda]

I'd be terrified if the database was rewritten. I remember the bad old days (version 4) when a corrupt wiz.bin was fairly common. When was the last time anybody had a corrupt wiz.bin these days? (And issues during upgrading from v7 to v9 don't count.) hardly ever. The current DB is fast and reliable. Now that it's opened sourced we are already seeing people writing tools to manipulate and export the data.