Hi, Thanks! 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?
Other than that, I don't see anything special really. It's quite hard to say how to improve performance here I'm afraid. Maybe to improve performance you would have to _not_ use a database, but use the java.util.Map interface instead? That should help. Regards, Thomas On Tue, Feb 5, 2013 at 1:03 AM, Srikanth Mallikarjuna < [email protected]> wrote: > Please find attached is the profiler output. > > > On Mon, Feb 4, 2013 at 11:23 AM, Thomas Mueller < > [email protected]> wrote: > >> Hi, >> >> > I don't know what the bottleneck is, I can't seemed to figure it out? >> >> Did you try using the built-in profiler documented at >> http://h2database.com/html/performance.html ? What are the top stack >> traces? >> >> Regards, >> Thomas >> >> >> On Mon, Feb 4, 2013 at 6:37 PM, Sri <[email protected]> wrote: >> >>> I don't know what the bottleneck is, I can't seemed to figure it out? >>> >>> CPU usage stays around 65-75%, does synchronized section come into play >>> a lot even for read only? >>> >>> Yes we tried on different systems and the turn around times are very >>> similar. >>> >>> I had appended LOG=0 to the H2 url seemed to be performing slower than >>> normal one (LOG=1) will test one more time and let you know. >>> >>> 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. >> >> >> > > > > > -- 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.
