H2 page size and caching

[Sven Mathijssen]

Thomas,

I was wondering about the following things:

- Does H2 use paging by default? If not, is it possible to use this?
- What is the default page size used (assuming paging is used to store
data on disk).
- Are there any guidelines somewhere about checking out the source
code in an IDE (I use Eclipse). It should be possible to checkout from
the SVN repository and build using Maven, but all I get when checking
out the project using subclipse is
- How does H2 behave when using a cache size of 0? I connect to the
database using an URL like: jdbc:h2:~/experiments/
db;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=0. Background: it's for running
some experiments for my master's project, and I'm trying to force the
system into swapping from and to disk, counting the number of I/O
operations in the process).

Thanks!

Sven

[Vince Bonfanti]

RE:

> - Are there any guidelines somewhere about checking out the source
> code in an IDE (I use Eclipse). It should be possible to checkout from
> the SVN repository and build using Maven, but all I get when checking
> out the project using subclipse is

I don't know about Maven, but I checkout the H2 trunk using subclipse
and build using the Ant build.xml script that's in the project root
directory. I've also built successfully using the built.bat
command-line script.

[Sven Mathijssen]

Vince,

Thanks for your answer.

> I don't know about Maven, but I checkout the H2 trunk using subclipse
> and build using the Ant build.xml script that's in the project root
> directory. I've also built successfully using the built.bat
> command-line script.

Yes, I understand, only when I checkout the program using subclipse,
the root of the project is trunk/h2/src/installer. I expected it to be
at trunk/ or something, because now I am unable to access the src
directory from the path mentioned. No problem in building, but I'm
interested in the source. I once managed to check out correctly. Going
to try again now!

> - How does H2 behave when using a cache size of 0? I connect to the
> database using an URL like: jdbc:h2:~/experiments/
> db;DB_CLOSE_ON_EXIT=FALSE;CACHE_SIZE=0.

Besides, what I mean by this is does H2 reserve at least SOME space
for buffering data at all? Cache size of 0 is of course not
preferable, but I want to have the effect of swapping buffer contents
to disk as soon as possible. Is there some lower limit on the cache
size? The amount of page transfers is very, very high. I expected it
to be high, but my own storage engine prototype (very simple one) uses
< 16K for buffering data from disk and generates a number of page
transfers orders of magnitude lower than H2 does, which surprises me a
little. All this still *assumes* the use of buffers each having a size
equal to the page size used and also *assumes* that the paging model
from database theory is used, which I am not able to figure out yet.

[Sven Mathijssen]

On 5 okt, 20:02, Sven Mathijssen <svenmathijs...@gmail.com> wrote:
> Yes, I understand, only when I checkout the program using subclipse,
> the root of the project is trunk/h2/src/installer. I expected it to be
> at trunk/ or something, because now I am unable to access the src
> directory from the path mentioned. No problem in building, but I'm
> interested in the source. I once managed to check out correctly. Going
> to try again now!

No problems now :) checkout works. I think it had something to do with
the fact that I tried checking out a Maven project or something.

[Sven Mathijssen]

Found some interesting things in the source (DiskFile.java):

* There are 'blocks' of 128 bytes (DiskFile.BLOCK_SIZE). Each objects
own one
* or more pages; each page size is 64 blocks
(DiskFile.BLOCKS_PER_PAGE). That
* is 8 KB page size. However pages are not read or written as one
unit; only
* individual objects (multiple blocks at a time) are read or written.

As I expected, 8 KB page size (which is, I think, kind of usual for a
database system). Maybe the high values for number of I/O operations
are due to the last comment: Only individual objects are read or
written.

This information is extracted by issuing

SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME =
'info.FILE_DISK_READ' ORDER BY NAME ASC;

Both before and after any statement is sent to the database. In this
way, I was hoping to count page transfers, but as I can see now, what
I'm probably doing is counting I/O *block* operations.

[Thomas Mueller]

Hi,

> - Does H2 use paging by default? If not, is it possible to use this?

The 'page store' storage engine (PageStore.java) is now used by
default in version 1.2.x. Before that, another algorithm is used
(DiskFile.java). I guess the page store is more interesting for you
(it is also faster).

> - What is the default page size used (assuming paging is used to store
> data on disk).

Currently 2 KB.

> - Are there any guidelines somewhere about checking out the source
> code in an IDE (I use Eclipse).

See http://www.h2database.com/html/build.html

> - How does H2 behave when using a cache size of 0?

You can set the cache size to zero, but there will still be a small
cache (16 items or so).

> it's for running
> some experiments for my master's project, and I'm trying to force the
> system into swapping from and to disk, counting the number of I/O
> operations in the process).

Modern software is not optimized for this scenario, because there is
always a lot of memory available (more than 1 MB). I guess older
software behaves better (but worse if there _is_ a lot of memory).

Regards,
Thomas

[Sven Mathijssen]

Hi Thomas,

Thanks for your reply, it's really useful info.

One more thing: Is it possible with the page store to retrieve
statistics on disk reads and writes (preferably number of pages, but
number of bytes would also be OK?). I used to query for

SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME =

'info.FILE_DISK_WRITE' ORDER BY NAME ASC;

SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME =
'info.FILE_DISK_READ' ORDER BY NAME ASC;

And count the differences before and after each statement so I knew
how much data was read or written during execution of the statement.
These seem to be not in use anymore since the DiskFile is now sort of
deprecated. Is there any way to obtain this information? It should be
possible to construct a trace and analyze the trace, but i'm
interested in these statistics per statement and not in total. I can
see that PageStore.java contains a writeCount, but i am not sure
whether this contains the information I am looking for.

Thanks,
Sven

On 11 okt, 10:22, Thomas Mueller <thomas.tom.muel...@gmail.com> wrote:
> Hi,
>
> > - Does H2 use paging by default? If not, is it possible to use this?
>
> The 'page store' storage engine (PageStore.java) is now used by
> default in version 1.2.x. Before that, another algorithm is used
> (DiskFile.java). I guess the page store is more interesting for you
> (it is also faster).
>
> > - What is the default page size used (assuming paging is used to store
> > data on disk).
>
> Currently 2 KB.
>
> > - Are there any guidelines somewhere about checking out the source
> > code in an IDE (I use Eclipse).
>

> Seehttp://www.h2database.com/html/build.html

[Thomas Mueller]

Hi,

> One more thing: Is it possible with the page store to retrieve
> statistics on disk reads and writes (preferably number of pages, but
> number of bytes would also be OK?). I used to query for
>
> SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME =
> 'info.FILE_DISK_WRITE' ORDER BY NAME ASC;
> SELECT * FROM INFORMATION_SCHEMA.SETTINGS WHERE NAME =
> 'info.FILE_DISK_READ' ORDER BY NAME ASC;

With the page store, this information is incorrect. I will add the
following information in the next release:

info.FILE_WRITE_TOTAL // since the database was created
info.FILE_WRITE // since the database was opened
info.FILE_READ // since the database was opened
info.CACHE_MAX_SIZE
info.CACHE_SIZE

Regards,
Thomas