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.
