One of my apps uses a dbisam table for blob storage. The data turns around quite
quickly, typical lifetime is about 10 minutes before it is deleted again. But some
databases are growing to 26Gb or more, and they should be re-using more.

Is there anything I can do to optimise it while the app is running? The app runs
24/7, so something I could do to make it better use the table space would be good.

/Matthew Jones/

My understanding of DBISAM optimize is that it requires a table level lock, so you can't
do it while writing to the DB.

The only work around I can think of (using DBISAM rather than trading up to ELEVATE) would
be to partition the logic of your app so that you use more than 1 table for BLOBs and
somehow "roll" from using one to another. This may be totally impossible within the logic
of your application.

Sorry not to be more helpful.

Matthew

You can add a field "expired" to the desired table and for each operation
you can update this field.
For new adds you can "use" expired records. But this method is exactly what
DBISAM (and I think ElevateDB) does when you delete a record and insert a
new one.

When you say "optimize" the table you are saying optimize method of the
tables to recycle deleted records ? Or I am missing something ?

Eduardo

Matthew


My first guess would be to look at the blobblocksize you're using and the average blob size you're filing. I don't know how Tim's algorithm's work but the one's I've had sight of source code for will sometimes not reuse "part sized" blocks and go for a new block.

Roy Lambert [Team Elevate]

Matthew,

<< One of my apps uses a dbisam table for blob storage. The data turns
around quite quickly, typical lifetime is about 10 minutes before it is
deleted again. But some databases are growing to 26Gb or more, and they
should be re-using more. >>

Are you using the latest DBISAM 4.29 build ?  There was this issue:

http://www.elevatesoft.com/incident?action=viewaddr&category=dbisam&release=4.28&type=f&incident=3037

that was fixed in 4.28.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ahah! I'm on 4.25, so I'll update.

/Matthew Jones/

Tim's answer shows it was a bug, but I think that migrating each day was probably
going to be my solution.

/Matthew Jones/

My code was doing a check every ten minutes for "expired" content, and iterating
through them deleting them. (FWIW, I found that a query to delete all in one go was
too expensive in that it stopped all access, so I locate them, and delete them
individually).

/Matthew Jones/

I think the block size is fair, given the blobs can be anything from dinky to
humongous.

/Matthew Jones/

Matthew,

<< Ahah! I'm on 4.25, so I'll update. >>

Hold on - the 4.28 fix was for 4.28 or higher because the bug was introduced
during the BLOB re-design for 4.28.  If you are using 4.25, then you weren't
exposed to the bug in the first place, and the issue is something else.

Also, because of the BLOB redesign in 4.28, you need to be careful when
moving clients over to it.  We sent out an email detailing this issue, did
you receive it ?

--
Tim Young
Elevate Software
www.elevatesoft.com