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 ? 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] <javascript:>>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 [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.
