H2 Concurrency performace issue...Please help

[Sri]

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

[Ryan How]

How about disk io?, usually the disk is the bottleneck.

> --
> 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.
> To post to this group, send email to h2-da...@googlegroups.com.
> 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.

[Sri]

Disk IO looks good too...can't seem to find what is the issue...

[Ryan How]

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 ?

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/njQ5VJHkPvEJ.

[Sri]

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

[Ryan How]

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

To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/YscoajbGnT4J.

[Sri]

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

[Thomas Mueller]

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

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 h2-da...@googlegroups.com.

Visit this group at http://groups.google.com/group/h2-database?hl=en.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sri]

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

[Sri]

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

[Ryan How]

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

[Thomas Mueller]

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

[Sri]

I did not try clustering because of its limitation in the documentation, I am using H2 for read only.

Clustering Algorithm and Limitations

Read-only queries are only executed against the first cluster node, but all other statements are executed against all nodes. There is currently no load balancing made to avoid problems with transactions.

[Sri]

I do use MULTI_THREADED=1 in H2 URL connection.

[Sri]

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:

[Ryan How]

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?

[Sri]

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.

[Thomas Mueller]

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

[Srikanth Mallikarjuna]

Please find attached is the profiler output.

[Thomas Mueller]

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

[Sri]

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?

[Sri]

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%

[Thomas Mueller]

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 

[Sri]

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.

[Thomas Mueller]

Hi,

You could use the java command line options as described in:

http://h2database.com/html/performance.html#application_profiling

java -Xrunhprof:cpu=samples,depth=16

Or you could use the Profiler in the code (in that case you would need to start and stop the TCP server within your own program).

Regards,

Thomas

[Sri]

We are testing on one more different machine now and we see the CPU is being close to 100% (around 96%) all the time for 10 threads. It seems like we are hitting CPU bound. What are the options do I have now?

-Sri

On Monday, February 4, 2013 3:57:57 AM UTC-8, Kartweel wrote:

[Ryan How]

Apart from trying to optimise the queries, use a Faster CPU?. You could profile H2 and see where most of the CPU time is being used and see if you can make any improvements to the H2 code.

But you are probably at the limit of what you can do with this architecture?

[Srikanth Mallikarjuna]

I had profiled client side and Thomas looked into it and said 

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

I haven't profiled on server side.

All of the queries perform very well within few milliseconds but not able to scale concurrently. We know H2 is memory and CPU intensive but didn't realize that we reach the limit only for 10 threads we were hoping close to 100 concurrent threads. We are in pre-production phase since December-January and keep on postponing the production date due to performance issues.

Srikanth
858-371-1240 (C)
858-790-6673 (O)

[Sri]

Here is the server configuration...

Server Machine Configuration:

CPU                   2.13GHz

Memory          4GB

System             64-bit Windows Server 2008 R2 Standard

-Sri

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
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 h2-database+unsubscribe@googlegroups.com.

To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Sri]

Does the clustering work for read only?

-Sri