On 4 Dec 2002 at 19:39, Sten Karlson wrote:

> Hi all!
>
> We're developping an application that will store measuredvalues from a
> meassuring/control system that we are developing.
>
> Our problem is that the database is constantly growing larger and
> larger. Since the aplication will run unattended 24 hour per day,
> every day, we have problems doing backup and restore.
>
> Here is a short description of our system.
>
> There is a single computer (running win 2000 and IB 6.01) attached to
> the measuring system. On this computer we run two applications. One
> that collect data through the serial port and stores data in a data
> table called DATA. The other application is a user interface and it
> reads parameters and data values from the DATA table.
>
> DATA contains about 100 records, one for each parameter or data tag.
> The data collecting application reads serial data and uppdates the
> corresponding tag in the DATA table.
>
> We also have a trigger (after UPDATE) on the DATA table. This
> trigger also writes data to a trend table that stores
> TAG number, Date & Time and the value.
>
> We have some prestanda problems and we can't run procedures that take
> long time and/or take much CPU power.
>
> The problem is that we must remove old data from the trend table. We
> are supposed to store data for a week which means about 4-6 million
> rows. I haven't been able to write such question to database (delete
> everything that is older than a week) that dosn't take very long time
> and use a lot of CPU :( And even if you do such thing, the size of the
> database doesn't shrink a single byte!!!

First backup and restore, backup is an absolute must, if your not backing up the
data sometime, your gonna wish you did, probably right after the server makes a
really funny noise like a circular saw, and then goes really quiet. A restore isn't
needed all that often, but you need to check the mechanism, by restoring to another
file once a month or so, and whenever you add or change table definitions.

The time element, your probably best to put an index on the date field, add the
primary key field to the end of the index to make sure that the selectivity is high,
otherwise your selectivity is low, and performance goes down the commode.

File shrinking, databases don't do it, they store everything in pages, when a page is
empty it's added to the end of the empty page list, when they need a page they take
one from the empty page list, if there are no empty pages, then it extends the file.
This is because asking the OS for more space is a very expensive operation (time
and resource wise), shrinking the file would be even more expensive, because the
empty page is most likely not at the end of the file, so it would need to load a page,
shift it's location, then write it into it's new location. Then it would need to return the
now empty pages space to the OS, just to turn around and ask for more space the
next time records need to be added. A restore works differently, it creates a brand
new file, so there are no empty pages.

> We tried to use an table attached to an external file instead. But the
> database grow alot anyway. Why????
>
> Is there any one that have done something similar and solved this
> problem? I guess that all logger applications have this problem.
>
> Why is the database growing even if we only do UPDATE on one TABLE and
> stores data in an external table?
>
> Is it somthing I can so to eliminate the constant growth of the
> database.
>
> I'm getting a bit scarred. The hole project is relying on the
> database concept and I must make it work some how.

The database that grows and grows and grows, probably has a OIT problem, OIT is
the oldest interesting transaction, with a normal, well behaving application, the
difference between the Oldest Interesting Transaction, and the Next Transaction, is
twice the number of users. When those numbers get further and further apart, it
usually means that the OIT is stuck (often caused by a workstation crashing, or
losing power), then you need to do a garbage collection, or a backup/restore. Most
systems have a less busy period at some point, so you can schedule a backup with
garbage collect at that time to make sure everything is moving along.

Otherwise, you have a design problem, why are you updating both tables?