Hi, There are threads that are blocked, that might be, yes, but I would *first* concentrate on those threads that are _not_ blocked, that means that are consuming the CPU time or disk I/O. What statements are those? Are they queries that don't use an index? Do you have indexes? Or do you unnecessarily insert / remove rows? On how to analyze performance, see http://h2database.com/html/performance.html#application_profiling
Regards, Thomas On Fri, Sep 13, 2013 at 10:56 AM, Paul Taylor <[email protected]> wrote: > 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. > -- 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.
