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 ? 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.
