Hi, Please don't double post: http://stackoverflow.com/questions/18782331/how-can-i-stop-h2-being-in-blocking-my-hibernate-querys-so-much
Regards, Thomas On Fri, Sep 13, 2013 at 12:02 PM, Thomas Mueller < [email protected]> wrote: > 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.
