How do I profile server side? I tried TRACE_LEVEL_FILE=3 which spits all the SQLs and execution times to trace file, and I do see rollbacks.
On Tuesday, February 5, 2013 10:43:15 AM UTC-8, Thomas Mueller wrote: > > 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] <javascript:>>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] <javascript:>. >> To post to this group, send email to [email protected]<javascript:> >> . >> 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.
