I had profiled client side and Thomas looked into it and said "It seems to me that most of the time is spent on closing and opening a pooled connection. Actually the database session is re-used, but there is a small overhead each time (rollback a pending transaction if there is any). I don't think this could be improved much within H2. I wonder how many connections are opened / closed?"
I haven't profiled on server side. All of the queries perform very well within few milliseconds but not able to scale concurrently. We know H2 is memory and CPU intensive but didn't realize that we reach the limit only for 10 threads we were hoping close to 100 concurrent threads. We are in pre-production phase since December-January and keep on postponing the production date due to performance issues. On Wed, Feb 20, 2013 at 10:14 PM, Ryan How <[email protected]> wrote: > Apart from trying to optimise the queries, use a Faster CPU?. You could > profile H2 and see where most of the CPU time is being used and see if you > can make any improvements to the H2 code. > > But you are probably at the limit of what you can do with this > architecture? > > > > On 21/02/2013 1:42 PM, Sri wrote: > > We are testing on one more different machine now and we see the CPU is > being close to 100% (around 96%) all the time for 10 threads. It seems like > we are hitting CPU bound. What are the options do I have now? > > -Sri > > On Monday, February 4, 2013 3:57:57 AM UTC-8, Kartweel wrote: >> >> Did you work out what the bottleneck is? >> >> It sounds like it isn't the disk if in-memory is no faster. So it must be >> CPU bound. It might be as fast as H2 can go with that level of concurrency. >> If CPU usage isn't hitting near 100% then it maybe it is a synchronised >> section of code that is the bottleneck? >> >> Have you tried it on another system with faster or slower cpu to see if >> the times scale accordingly?. That would hint that it is CPU bound. If that >> is the case without hacking the H2 source to try and get better concurrency >> I can't see that you would get it any faster? >> >> >> >> On 1/02/2013 1:52 AM, Sri wrote: >> >> with the 100 concurrent users/threads it is taking around 2.5sec I am >> trying to get that down to under a sec. It is taking around 100ms for >> single thread. >> >> On Wednesday, January 30, 2013 5:49:58 PM UTC-8, Kartweel wrote: >>> >>> You could try cluster mode. >>> >>> Did you work out what the bottleneck is? If memory mode didn't make a >>> difference I'd imagine it is CPU bound? >>> >>> What kind of performance increase are you trying to get? >>> >>> Ryan >>> >>> >>> On 31/01/2013 8:55 AM, Sri wrote: >>> >>> Is there any way I can run multiple H2 instances on one machine and load >>> balance them to see if it helps my concurrent issue? >>> >>> -Sri >>> >>> On Wednesday, January 30, 2013 1:11:27 PM UTC-8, Sri wrote: >>>> >>>> I had added all necessary indexes required for my queries and they >>>> perform very well. >>>> It's been performing very good with single/few user/thread and is >>>> getting degraded as I add more concurrent users/threads. >>>> >>>> I had also profiled using built-in profiler and queries were taking >>>> longer as I added more concurrent users/threads. >>>> >>>> -Sri >>>> >>>> On Wednesday, January 30, 2013 12:55:26 PM UTC-8, Thomas Mueller wrote: >>>>> >>>>> Hi, >>>>> >>>>> > I tied using in-memory db doesn't improve the performance at all. >>>>> >>>>> Did you read the performance docs yet - http://h2database.com/html/** >>>>> performance.html <http://h2database.com/html/performance.html> ? >>>>> Specially the built-in profiler and indexes. >>>>> >>>>> > LOG=0, LOCK_MODE=0 and FILE_LOCK=NO has any effect on the >>>>> performance? >>>>> >>>>> Not if indexes are missing, queries are slow and so on. LOG=0 might >>>>> double performance, but that's it. lock mode and file lock don't typically >>>>> improve performance, they are just dangerous. >>>>> >>>>> Regards, >>>>> Thomas >>>>> >>>>> >>>>> >>>>> On Wed, Jan 30, 2013 at 8:53 PM, Sri <[email protected]> wrote: >>>>> >>>>>> I tied using in-memory db doesn't improve the performance at all. >>>>>> Both in-memory and server have similar turn around times. >>>>>> >>>>>> I was looking at some of the H2 documentation and came across below >>>>>> one. As I mentioned earlier DB is only for reads so by doing LOG=0, >>>>>> LOCK_MODE=0 and FILE_LOCK=NO has any effect on the performance? >>>>>> >>>>>> "Some features are known to be dangerous, *they are only supported >>>>>> for situations where performance is more important than reliability*. >>>>>> Those dangerous features are: >>>>>> >>>>>> - Disabling the transaction log or FileDescriptor.sync() using >>>>>> LOG=0 or LOG=1. >>>>>> - Using the transaction isolation level READ_UNCOMMITTED (LOCK_**MODE >>>>>> 0) while at the same time using multiple connections. >>>>>> - Disabling database file protection using (setting FILE_LOCK to >>>>>> NO in the database URL). >>>>>> - Disabling referential integrity using SET REFERENTIAL_INTEGRITY >>>>>> FALSE." >>>>>> >>>>>> >>>>>> -Sri >>>>>> >>>>>> On Tuesday, November 13, 2012 7:32:35 PM UTC-8, Kartweel wrote: >>>>>>> >>>>>>> Other people might have some suggestions, but I guess if you try >>>>>>> it on a solid state disk or just trial as an in memory database and see >>>>>>> if >>>>>>> it performs faster. >>>>>>> >>>>>>> Or you could also try it on a ram disk and see if it improves >>>>>>> performance. That way you don't need to try any other hardware. >>>>>>> >>>>>>> At least then you'll know the disk was the bottleneck. >>>>>>> >>>>>>> Ryan >>>>>>> >>>>>>> On 14/11/2012 7:03 AM, Sri wrote: >>>>>>> >>>>>>> Sorry I was looking into some other things...now I got back to this.. >>>>>>> >>>>>>> How do we determine if disk io is capped? >>>>>>> >>>>>>> I do see disk io is varying (up and down from 40kb- 200kb and >>>>>>> occasionally shoots up to 950kb) all the time when I observed windows >>>>>>> resource monitor. >>>>>>> >>>>>>> -Sri >>>>>>> >>>>>>> On Friday, November 2, 2012 2:23:03 PM UTC-7, Kartweel wrote: >>>>>>>> >>>>>>>> Hi, >>>>>>>> >>>>>>>> Makes sense to me. If cpu isn't the issue (which I doubt it would >>>>>>>> be in a database, but maybe the encryption adds a lot of overhead?) >>>>>>>> then >>>>>>>> adding more threads would increase the time proportionally + >>>>>>>> synchronisation overhead. Also there would be more work for the disk >>>>>>>> seeking between all the different locations. >>>>>>>> >>>>>>>> So are you saying that disk io is increasing with each thread you >>>>>>>> add? or is it capped? both bandwidth and iops ? >>>>>>>> >>>>>>>> On 3/11/2012 4:44 AM, Sri wrote: >>>>>>>> >>>>>>>> Disk IO looks good too...can't seem to find what is the issue... >>>>>>>> >>>>>>>> On Thursday, November 1, 2012 2:36:21 PM UTC-7, Kartweel wrote: >>>>>>>>> >>>>>>>>> How about disk io?, usually the disk is the bottleneck. >>>>>>>>> >>>>>>>>> On 2/11/2012 3:44 AM, Sri wrote: >>>>>>>>> > Hi, >>>>>>>>> > >>>>>>>>> > I am running H2 DB in server mode and using it for read only. >>>>>>>>> It's >>>>>>>>> > been performing very good with single user/thread and the >>>>>>>>> performance >>>>>>>>> > is getting degraded as I add more concurrent users/threads. >>>>>>>>> > >>>>>>>>> > Single user/thread --> about 100ms >>>>>>>>> > 10 users/threads --> about 230ms >>>>>>>>> > 15 users/threads --> about 320ms >>>>>>>>> > 20 users/threads --> about 440ms >>>>>>>>> > 25 users/threads --> about 550ms >>>>>>>>> > 40 users/threads --> about 900-1000ms >>>>>>>>> > 50 users/threads --> about 1300-1400ms >>>>>>>>> > >>>>>>>>> > Please see the attached screenshot for CPU, heap and thread >>>>>>>>> > monitoring. I do not see the problem of CPU being max out or not >>>>>>>>> > enough memory or not scaling threads as I add more users. >>>>>>>>> > >>>>>>>>> > H2 Version:h2-1.3.166 >>>>>>>>> > Url: >>>>>>>>> > jdbc:h2:tcp://localhost:9092/<**<DB absolute >>>>>>>>> > path>>;MULTI_THREADED=1;CACHE_**SIZE=<<cashesize>>;CIPHER=AES;**IFEXISTS=TRUE >>>>>>>>> >>>>>>>>> > >>>>>>>>> > <<cashesize>> ==> tried different values, defualt-16mb, 128mb, >>>>>>>>> 256mb, >>>>>>>>> > 512mb and 1024mb (supplied in KB though) >>>>>>>>> > >>>>>>>>> > FYI, >>>>>>>>> > Each thread is executing lot of queries (around 15-20) and some >>>>>>>>> of >>>>>>>>> > them are recursive queries (to fetch heirachy data). >>>>>>>>> > >>>>>>>>> > >>>>>>>>> > Please let me know if anybody run into the same problem and how >>>>>>>>> did >>>>>>>>> > you resolve. >>>>>>>>> > >>>>>>>>> > Thanks in advance. >>>>>>>>> > -Sri >>>>>>>>> > -- >>>>>>>>> > You received this message because you are subscribed to the >>>>>>>>> Google >>>>>>>>> > Groups "H2 Database" group. >>>>>>>>> > To view this discussion on the web visit >>>>>>>>> > https://groups.google.com/d/**msg/h2-database/-/hbZ9WV8cFWEJ<https://groups.google.com/d/msg/h2-database/-/hbZ9WV8cFWEJ> >>>>>>>>> **. >>>>>>>>> > To post to this group, send email to [email protected]. >>>>>>>>> > To unsubscribe from this group, send email to >>>>>>>>> > h2-database...@googlegroups.**com. >>>>>>>>> > For more options, visit this group at >>>>>>>>> > http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en>. >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>> You received this message because you are subscribed to the Google >>>>>>>> Groups "H2 Database" group. >>>>>>>> To view this discussion on the web visit >>>>>>>> https://groups.google.com/d/**msg/h2-database/-/njQ5VJHkPvEJ<https://groups.google.com/d/msg/h2-database/-/njQ5VJHkPvEJ> >>>>>>>> **. >>>>>>>> To post to this group, send email to [email protected]. >>>>>>>> To unsubscribe from this group, send email to >>>>>>>> h2-database...@googlegroups.**com. >>>>>>>> For more options, visit this group at http://groups.google.com/** >>>>>>>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>>>>>>> . >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>> You received this message because you are subscribed to the Google >>>>>>> Groups "H2 Database" group. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/**msg/h2-database/-/YscoajbGnT4J<https://groups.google.com/d/msg/h2-database/-/YscoajbGnT4J> >>>>>>> **. >>>>>>> To post to this group, send email to [email protected]. >>>>>>> To unsubscribe from this group, send email to >>>>>>> h2-database...@googlegroups.**com. >>>>>>> For more options, visit this group at http://groups.google.com/** >>>>>>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>>>>>> . >>>>>>> >>>>>>> >>>>>>> -- >>>>>> 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 h2-database...@googlegroups.**com. >>>>>> >>>>>> To post to this group, send email to [email protected]. >>>>>> Visit this group at http://groups.google.com/** >>>>>> group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>>>>> . >>>>>> For more options, visit >>>>>> https://groups.google.com/**groups/opt_out<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 h2-database...@googlegroups.**com. >>> To post to this group, send email to [email protected]. >>> Visit this group at >>> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >>> . >>> For more options, visit >>> https://groups.google.com/**groups/opt_out<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 h2-database...@**googlegroups.com. >> To post to this group, send email to [email protected]. >> Visit this group at >> http://groups.google.com/**group/h2-database?hl=en<http://groups.google.com/group/h2-database?hl=en> >> . >> For more options, visit >> https://groups.google.com/**groups/opt_out<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?hl=en. > 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?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- Srikanth 858-371-1240 (C) 858-790-6673 (O) -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
