Hi, > queries were taking longer as I added more concurrent
Yes, this is somewhat normal. If you use MULTI_THREADED=1 it should be less of a problem, but maybe you have hit the maximum throughput of the computer, or a limitation of H2 itself. If you can't use MULTI_THREADED=1 then H2 can't get faster with multiple threads. Regards, Thomas On Wed, Jan 30, 2013 at 10:11 PM, Sri <[email protected]> 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/**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.**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 [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.
