> > 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. >
OK its good to hear that you do see a level of improvement, you are right in so much that it is cheating, but may allow a system to keep running, while other solutions are put into place . The first thing to note is that this work does not result in the data tables being 'in sequence' but rather that when a disk page is read in there is a much higher chance of it containing a number of data pages (rather than a mix of data and index pages). If you can try changing the page size you should see much the same result. Also as you are working against an SSD you may like to try a large page size such as 8K or even 16K and also increase the file system block size to the same. The next thing to look at is the amount of cache available to H2, details can be found here http://www.h2database.com/html/features.html#cache_settings The correct size will be hard to tell, but its unlikely that you will have enough memory to hold the whole of this table so this table scan will always result in a lot of I/O. What I can say is that you are likely to see improvements if you throw a lot of memory at the problem :) -- 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.
