>
>
> I am a bit amazed that the throughput of my 8m record database is so much
> lower than my 400k record database, but then I read in another post that
> there might be an issue if the records have been heavily inserted/deleted,
> which they have, in particular in the big DB.
> I'll try and backup/re-build the DB to see if it makes any difference
>
>
Look at what you are doing it is likely that this is because your 400K
record test database is being cached in memory while you scan across it,
while the 8m+ record database is so large that the caching is ineffective
and so a far higher percentage of disk I/O is required.
To be clear about the indexes on this table, you end up with
- Hidden, system generated index for a BIGINT that is used as the
primary key
- Index on binA
- Index on binB
This is because H2 can only create primary indexes on INT based fields -
see here
http://stackoverflow.com/questions/3312857/h2-database-clustered-indexes-support
Things to try
1 - up the page size of the table, the default is just 2K which means a
lot of small reads take place during your scan
http://www.h2database.com/html/features.html#page_size
2 - create the 2 addtional indexes after you have populated the table
and not during the process.
3 - increase the H2 cache, as you are doing a table scan this may not
help as I think its a record cache and not a block cache.
Background reasons for the above. As you add your 8M records the records
are added to the table as 2K pages, at the same time 2K pages are allocated
to each of the additional indexes as well. All these pages are stored in a
single file that is extended as required. As the btree indexes are grown
they are rebalanced, which means additional pages and old pages being freed
up.
The result is that your data is now spread across a very large file as
small 2K pages in something of a random order, intermixed with 2K index
pages that you do not which to use at this time. If you have a file system
set to 4,8,16K pages each requested read is not going to bring back much
useful information (hence the idea of increasing the page size). Also as
the data is not in any natural order its very unlikely that the extra pages
read via the OS read ahead feature will help. By adding the indexes after
the main data pump you separate the data pages they create from the data
pages, so when the OS does a disk read it is going to be bringing back
mostly data pages rather than a mix of data and index pages.
The one limitation is that page size is set per database, rather than per
table (all the tables in a database are all held within a single file) so
other parts of your database may have performance problems if you use very
large pages, but at least try matching the disk page size and maybe 2x the
disk page size.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
