Hi, I just noticed you only profiled the client side. You should also analyze the server side.
About org.h2.value: as you can see in the stack trace, it's about serializing the data over TCP/IP. Closing a connection in the connection pool: I already wrote this in my previous mail. Regards, Thomas On Tue, Feb 5, 2013 at 10:35 AM, Sri <[email protected]> wrote: > I am also seeing lot of time spending in below packages is that normal? > org.h2.value: around 15-30% some times around 50-60% > org.h2.util : around 15-25% > org.h2.jdbc : around 3-9% > org.h2.jdbx : around 10% > > On Tuesday, February 5, 2013 9:58:22 AM UTC-8, Sri wrote: >> >> The profiler output I sent you is for 10 threads each one processing >> around 25 requests that would be around 250 requests means around 250 >> connections (each one processing around 15-20 queries) to H2DB. Why it is >> spending lot of time in opening and closing connections though I used >> connection pool? Once I am done with connection I am closing it to return >> to the pool shouldn't I be doing that? >> >> I am doing only read only so I don't think there is any rollback a >> pending transaction here? >> >> On Monday, February 4, 2013 10:18:14 PM UTC-8, Thomas Mueller wrote: >>> >>> 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<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/**p**erformance.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_**M**ODE 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/**ms** >>>>>>>>>>>>>> g/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.**co**m. >>>>>>>>>>>>>> > 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/**ms**g/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.**co**m. >>>>>>>>>>>>> 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/**ms**g/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.**co**m. >>>>>>>>>>>> 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.**co**m. >>>>>>>>>>> >>>>>>>>>>> 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/**grou** >>>>>>>>>>> ps/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.**co**m. >>>>>>>> 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/**grou** >>>>>>>> ps/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/**grou**ps/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.
