Comprimate Database

[Richard Naninck]

Scott,

I just saw that my database had grown to a large 127.340.544 bytes (127 MB). After running it through Access, it shrank to 1.896.448 bytes (a small 2 MB). I have never seen my database become this huge. I know that once a month it gets comprimated and that a registry setting is in place for backing it up more often. Is it possible to up the comprimate frequency a bit?

[Richard Naninck]

What should I make of these two files. A backup was created 2 days ago (small) and an even bigger database sits in the backup directory dating from the 21st of jan.

[ScottBot]

The HBData.autobackup.mdb file is the one that gets backed up automatically by HouseBot. It is a compressed/cleaned version of the active HBData.mdb file when the backup happened. I'm not sure what the large HBData.mdb file is. I think it may be a result of the source database before the compression is applied. However, the difference in dates on yours doesn't really help that theory.

The database will grow large when it is constantly being updated, and every time a Property Value changes, the database gets updated. This is normal.

I know that once a month it gets comprimated and that a registry setting is in place for backing it up more often. Is it possible to up the comprimate frequency a bit?

Changing the frequency will only make a difference if HouseBot is restarted within that time. It only compresses and backups when HouseBot is starting and the last update was greater than the update interval. So even if you change it to daily, it will only get updated daily if you restart the server daily.

If you want to play with your registry, you can set (you will need to add a dword value) the DB Compressed Time Period value for the General Settings key in the HouseBot section to a lower value (number of seconds). It defaults to 2629743 (number of seconds in a month).

[Richard Naninck]

Thanks, that is what I was looking for however I already have that in my registry and it is set to 86400 which equals one day. Because of lots of testing I don't get very good uptimes so I would expect a smaller database.
My current uptime is 1 day and two hours and after having written the first post in this thread, my database grew to 91MB currently. Hopefully this growing is not linear in time because that would mean it can grow to 1GB in less than 10 days
I just reset the server and the registry setting works because the dbase shrank to 2MB again.
I know I have lots of selfupdating (time related etc) large alphalist properties for rss news feeds, weather data and all kinds of other Meedio database related stuff which probably cause this increase, but I have never seen it grow this fast.

Are there any limits to its size?
Does HB slowdown when size increases?

[roussell]

It's a general rule that if you allow an Access DB to grow to 2Gb then you will be very unhappy. I've had several users push Access to it's limits and it's not pretty. I believe there is a hard limit at or just over 2GB but I wouldn't get that close.

Scott - SQLite, mysql, SQL express ????

Terry

[ScottBot]

It's a general rule that if you allow an Access DB to grow to 2Gb then you will be very unhappy. I've had several users push Access to it's limits and it's not pretty. I believe there is a hard limit at or just over 2GB but I wouldn't get that close.

Scott - SQLite, mysql, SQL express ????

Terry

Access is just sooo much easier to use, since the jet db support is built into MFC and the drivers come with Windows. If there seems to be ongoing issues with it, then I'll consider switching, but not without a little work first. I think I need to get a better understanding of just why it's growing so large. My assumption is due to the fact that everytime a Property Value changes, HouseBot updates a record in the database to remember it's last value so that when the system starts up again it can resume where it left off. I think access creates a new record, even though the intent is to just update a record. It may be the case that certain configurations that are constantly updating a Property Value are causing this. It may make sense to add an option for a Property as to whether it should be persistent (for example, all of the system time properties do not need to remember their state and they get updated alot!). If this could be turned off for some Properties that are always being changed, then that may be a solution. I probably don't even check for the same value change of a value and even update it when it doesn't need updated.

Anyway, any details you have that could give me a clue as to why this is happening would be helpful.

[Richard Naninck]

It's a nice feature to have HB restart with all of its last set values, but in most cases not a must. Histories are gone anyways after a restart.

So if all property changes take up space even though it concerns an update, I can understand why my system grows out of hand so fast. Since I moved house, I incorporated the ELK M1 alarm, FS20 HA stuff and some other generic serial gadgets. Both the ELK and FS20 produce much data on the Received data property and not too long ago a Received Hex Data property was added as well. FS20 produces about 20 messages each containing around 15 bytes per minute all day long due to the HVAC controllers talking to eachother. The ELK produces ongoing messages as well with more intens message traffic when people are moving through motion detected zones. All of these serial messages are processed and flip theme properties on and off for display purposes. Then all gets logged into alphalists as well. So if you are right about the creation of new properties on updates, it explains atleast why my file gets this big in such a short time.
Uptime now is 1 day and 3 hours and the database is already 111MB large which is consistent with what I saw yesterday. I will monitor this growing large part to see if it is linear with time. Still hope that it is not...

[Richard Naninck]

Small update:

Uptime is 3 days and 14 hours.
dbase size is 210 MB.
This seems linear in time with normal usage of the swremote.

[ScottBot]

It sounds like in your case with the received data properties, this is data you could live without having stored in the DB for when the server is restarted; correct?

Since I know you like to roll up your sleeves with HouseBot, here's something to try.

• Close HouseBot and open your HBData.mdb file.
• Open the Devices table and find the Device(s) that you believe to be doing excessive updates and you don't really need to have the data persistent between HouseBot restarts.
• Click on the (+) to expand the record, or find the relational records in the DevicePropertyMap table for the Device(s).
• Find the Properties in the list and change the PersistentValue to 0.
• Save DB and restart HouseBot

If you do that, it may very well help the issue.

[Richard Naninck]

I just took out a few candidates prown to being the cause but I have to test for a while to see how it turns out. At least I have a reference now to growth.

Please explain this:

I put a zero in the Received Data and Received Hex Data properties of my FS20 Definition. Then I restarted HB and set the Hardware device to be disabled to make sure no new data is received. Then I restarted HB again and still I see the last value in the those properties. I would expect these properties to be empty. Unless of course I am missing something.

[ScottBot]

I should have also told you to delete any value in the CurrentValue field of the records that you change the PersistentValue to 0. It still has the old CurrentValue which will forever display initially when HouseBot is restarted, unless you reset it too.

[Richard Naninck]

I managed to fing the currentvalue and cleared it and it has the correct result.
Now I will just have to wait and see what ot does over time.
Lets say that this does the job, how can this turn into a solution? Maybe make this setting part of the propety manager so that one can choose if it is important to maintain or not? That would mean a good explanation in the help file and a default to value 1 I guess.
I will report back the results when I got something constructive!

[ScottBot]

... Lets say that this does the job, how can this turn into a solution? Maybe make this setting part of the propety manager so that one can choose if it is important to maintain or not? ...

It would be an option on the Property Value page along with the 'same value change' checkbox. The functionality is there, and you can do it when working with the Device API in the SDK. It was just never exposed in the server UI. My thought was that whoever is creating the Device probably has a better understanding of whether the value should be persistent. Most users wouldn't really understand what the option was for unless they read the online help and really thought about it.

[Richard Naninck]

It makes sense to put it next to the same value change checkbox.
This evening I will zeroize some more properties to get some better numbers. Right now the dbase is 67 MB with 19 hours runtime. Not that much different from what it used to be but I only took out a few properties so I will increase the number.

[Richard Naninck]

Hypothetically speaking: shouldn't the database just keep it's original size or even fluctuate a bit in getting larger and smaller again depending upon the size of property data?
If I understand this correctly, it grows because Access creates a new entry for each update. Do other dbase programs like mysql or sqlite do the same thing? Just interested in the matter. I don't think changing from Access to something else is going to be easy and since nobody else is having issues with this it doesn't make sense to change it at all. Time will tell..