Quantcast

Interpreting SYSTEM_CACHEINFO.FILE_LOST_BYTES for CACHED tables

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Interpreting SYSTEM_CACHEINFO.FILE_LOST_BYTES for CACHED tables

xavier.bruggheman
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Interpreting SYSTEM_CACHEINFO.FILE_LOST_BYTES for CACHED tables

Fred Toussi-2
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, [hidden email] 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
> [hidden email]
> 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
_______________________________________________
Hsqldb-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...