> > 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. > > Thanks for the elaborate explanation. I see that by adding the index later I get them in one chunk (or at least in the vicinity of each other) which helps performance. Unfortunately it is a live system that continuously gets pretty random inserts/deletes/gets (into several other tables as well), and I believe that I need the index to sppedup lookups during runtime. I haven't tried changing the page size yet though. However, I made an experiment. I made a "script to" / "runscript from" combo and recreated the data in an empty database (took hours, approx 60 GB database files). This creates the indexes at then ends and makes the data of each table come nicely in sequence, basically defragmentation I guess. Selecting all the rows of the table is increased by a factor of 10, and now CPU bound. This is however a bit like cheating, as the table will slowly get fragmented during runtime.
-- 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.
