Hibernate is unable to place load on DB

f9smsk

Hi there,

I'm trying to measure db (12c) performance. The application is a high DML ones and wants to do 10K INSERTS per second. In fact I am measuring if db 12c is able to handle such a load. the INSERTS will be committed one by one and there is no way to do that in batch due to business requirements. So application guys developed a very simple application (JAVA HIBERNATE) and deployed it on two nodes. each node is using a connection pool of size 128. The test plan is to do 9K INSERT every 2 seconds and repeat it 15 times to measure overall performance. Monitoring V$SESSION at test start time, we noticed that the two connection pool successfully achieved their 128 connections (256 total) successfully and all of them were INACTIVE at the beginning. But unfortunately the database performance is awful and is just 500 TPS While OEM is showing just at most 8 sessions in wait in the test duration.

My problem is:

Why db waited sessions are such low while the huge DML test was running?

To test db performance in the absence of third party app I developed a procedure that will create 20 jobs each inserting thousands of rows in the destination table. The test results show 32K TPS while a 1k BLOB is inserting as a field and 100K TPS without that field. The db active and waited sessions were very high in this test case.

So I have to find the root cause why app's connection pool is unable to place high load on DB.

thanks in advance.

Regards.

Unknown

"But unfortunately the database performance is awful"

Given that your subsequent tests showed

"20 jobs each inserting thousands of rows in the destination table. The test results show 32K TPS while a 1k BLOB is inserting as a field and 100K TPS without that field"

Why do you assume it's the database that's the bottleneck and not simply that the app cannot handle the job of servicing all the connections in the connection pool at a sufficiently high data throughput, or even an issue of network throughput, especially if blobs are involved.  What are the top wait events on the database during the low TPS tests?

f9smsk

you are right. the database performance was not awful. That was the sentence that application guys say. there is no network issue because when they switch the persistence method from ORACLE to REDIS db (in a same network configuration) they gain 6500 TPS. there is not any major wait event during the test.

AndrewSayer

Inserting rows one at a time and committing between each one is never going to perform.

If your real application is doing this then I would hope ts because each of these inserted rows is one logical transaction from one user call, in that case you would certainly expect an amount of user think time between each insert, so you would never in your wildest dreams need to manage high TPS.

If your application is trying to do a bulk load then do it properly, use array binded insert statements and only commit when a logical transaction is actually complete.

The mere act of using array binded insert statements will eliminate a lot of the needless chatter between your application and the DB - instead of requiring 400 network trips to insert 100 rows (send rows, receive success message, send commit, receive success message X 100) you’d do 4 (If you are doing 100 row arrays and committing after each one still).

As a pl/sql block, the insert statement is being run from the DB server, theres no network trips to worry about, it’s only context switching.

Rather than spamming v$session queries to find out what’s happened, use v$session_event to see the aggregated wait events.

Also refer to http://www.oracle.com/technetwork/database/in-memory/overview/twp-bp-for-iot-with-12c-042017-3679918.html  for best practises when dealing with high insert workloads.

AndrewSayer

f9smsk wrote:you are right. the database performance was not awful. That was the sentence that application guys say. there is no network issue because when they switch the persistence method from ORACLE to REDIS db (in a same network configuration) they gain 6500 TPS. there is not any major wait event during the test. 

Sharing the actual code being run for this benchmark might be useful. Perhaps you’re parsing statements multiple times, this might not have so much of an effect on REDIS if it’s optimizer isn’t as complex as Oracle’s (which is quite rightly designed for parse once execute many)

John Thorton

f9smsk wrote:you are right. the database performance was not awful. That was the sentence that application guys say. there is no network issue because when they switch the persistence method from ORACLE to REDIS db (in a same network configuration) they gain 6500 TPS. there is not any major wait event during the test. 

If the slowdown is isolated to Application Connection pool, then mark this thread as ANSWERED, since root cause and fix are external to Oracle DB.

Unknown

So I have to find the root cause why app's connection pool is unable to place high load on DB.

Not possible - since you haven't posted the Java code that you have the problem with.

And the place to post it and ask that question is the JDBC forum.