Here is the server configuration... Server Machine Configuration:
CPU 2.13GHz Memory 4GB System 64-bit Windows Server 2008 R2 Standard -Sri On Thursday, February 21, 2013 9:36:52 AM UTC-8, Sri wrote: > > 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.
