Hi Ive been using h2 (1.3.172) for my application ( http://www.jthink.net/songkong/ ) via hibernate for a while, and after solving some non-h2 performance issues by performance bottleneck is h2. My database is quite simple, there are 10 tables and the volumes are less than 100,000 records per table as well so well within h2 capabilities, but I think the problem is with multi-threading.
Im essentially using hibernate/h2 to keep memory consumption under control in my application. My application works on files using a pipeline approach, there is no limit on how many files can be processed so if I stored the data in memory I would quickly hit OutOfMemory. Each file typically go through ten stages of processing, each stage has its own executor service, as file moves from one stage to another it is added as a job on the relevent executor service. Very little data is stored in memory, instead as the job is started on the executor data about the file is retrieved from the database, and as the job completes data is written back to the database. So we have a number of transactions going on concurrently mostly involving a small a mount of rows. Note we typically use on session and transaction within a job, the session can be open for a while (upto a couple of minutes) , but we usually do just one get() at the start and commit() at the end of the job, I understand this to be the usual way to use Hibernate rather than having many very short transactions My application is multi-threaded and if I run a profiler against it I find most of the time my threads are in a blocked state waiting on executeQuery() or executeUpdate(). I read that h2 is single threaded so I assume the problem is caused by h2 synchronizing requests, rather than locking but I may have misunderstood this. I already have set MVCC=TRUE so that h2 does row instead of table locking, but I still get ocassional timeouts - is there something I can set to check the locks being used. I read that you had a MULTI_THREADING option but that cannot be used with MVCC, this is a shame because I feel I need both option if I remove MVCC=TRUE that would mean h2 will lock the table every time I do an insert or update, and because I only have a few tables they would almost always be locked. So my hope is with some adjustments I could massively reduce the amount of blocking done, but I'm unclear what is the real cause of this and how I should address it. -- 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.
