1

I have an application whose HSQL database grows awfully large for some users, but not others. When I received one example where this problem happenned, I discovered that the database is nearly empty - it's almost 16GB of zeroes and only a couple tens of megabytes of real data. Upon performing CHECKPOINT DEFRAG, it shrinks to its real, small size.

Unfortunately, I'm not very familiar with this database engine and my online searches have produced little useful information. One interesting thing was that there is an option to automatically perform the defragmentation upon reaching a certain size, which I didn't use. However, I would expect the DB to reuse dead rows and not keep them around, and definitely not zeroed out as this must have a bad effect on performance.

Why does the database keep producing so much empty space, which is only interrupted by data here and there? How can I find out what's happening?

JohnEye
  • 173
  • 9

1 Answers1

3

One explanation is the case where lots of rows of data are created but later deleted by the application. If these rows happen to be in the memory cache throughout, they are never written to disk.

The space for deleted rows is reused for up to 500 most recent items, the rest discarded.

The CHECKPOINT DEFRAG command can be used from time to time to compact a database that has lots of empty spaces.

The database engine does not write zeros. The zeros in the file are there when some file space is allocated for use.

fredt
  • 175
  • 5