Re: Interpreting SYSTEM_CACHEINFO.FILE_LOST_BYTES for CACHED tables

Obviously there is a calculation error. I think the total was being
added to the previous total at checkpoints.

The code in this area has been reviewed and improved since 2.3.2 and the
next version should report a better approximation.

Also note HSQLDB tables are copied completely when adding / removing
indexes and making other changes to table structure. The space for the
old copy is not recorded as lost space.

Fred Toussi

On Wed, Aug 6, 2014, at 18:20, [email protected] wrote:
> Hi,
> 
> I'm embedding HSQLDB v2.3.2 in an application, and I'm trying to use the 
> FILE_LOST_BYTES column to determine the amount of wasted space and the 
> percentage of fragmentation of my cache file and trigger either a 
> CHECKPOINT DEFRAG or SHUTDOWN COMPACT on the embeded database during 
> application startup when above a configurable percentage or amount.
> I'm doing this on application startup rather than using the "SET FILES 
> DEFRAG" statement to avoid the database going down for a relatively long 
> time (DB is a couple of GBs) at unpredictable moments while the 
> application is running.
> 
> The issue I'm having is that I'm getting weird values in the 
> FILE_LOST_BYTES column after a day or two of mixed reads and 
> insert/update/delete activity. The values in this column grow very large 
> and above the actual .data file size, most of the time thousands of time 
> bigger than it, and very much above the capacity of the disk hosting the 
> database (600 TB...).
> 
> For reference, the query I'm using:
> SELECT CACHE_FILE, FILE_LOST_BYTES FROM 
> INFORMATION_SCHEMA.SYSTEM_CACHEINFO
> 
> Is there something I'm missing ? Maybe the unit for the column isn't 
> BYTES as the column name implies, or is there something wrong with the 
> way this information is made available in the information schema ?
> 
> Best regards,
> 
> Xavier BRUGGHEMAN
> 
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls. 
> Build a bridge from your legacy apps to the future.
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> Hsqldb-user mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/hsqldb-user

------------------------------------------------------------------------------
Infragistics Professional
Build stunning WinForms apps today!
Reboot your WinForms applications with our WinForms controls. 
Build a bridge from your legacy apps to the future.
http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk