the end of an ethernet network cable

A little bit of PLSQL and a little bit of network

Posted by

So after my post yesterday about PL/SQL being a no-brainer to get an easy boost to application performance, there was an interesting collection of responses on social media from both sides of the fence.

From the “the database is the universe” camp, there was criticism that the 30% benefit of PL/SQL was in fact understating the benefits because I ran the benchmark completely on a single machine. Thus I wasn’t taking into account the fact that database calls from the application tier (in this case Java) are more likely to be some “distance” from the database tier, and that the network latencies should also be taken into consideration (even though I explicitly stated I was deliberately keeping that out of the comparison Smile).

On the other side of the fence from the “the database is purely for data” camp, there was criticism that I was running the benchmark in a single thread, and thus was introducing a bias to the results, because in a true mid-tier application scenario there would be a pool of application processes all concurrently serving workload.

It seems it’s hard to please anybody nowadays Smile

Anyway, such criticisms are trivial to address because Swingbench is easy to configure for any desired number of concurrent users, and given that my lounge room looks like a laptop graveyard, I’ve got plenty of machines to let me create a benchmark configuration where the database tier and the application tier are separated by a network. In my case, it will be a gigabit wired network. I’d love to say that this is nothing like a true data centre network, but I’m always surprised at just how poorly treated the network can be inside modern data centres. Anyway, I digress.

I’m keeping the think time at zero, so we’re throwing calls as fast as we can from the application server to the database, and I’m setting the number of concurrent users to 4 to keep my database laptop from throwing it’s toys out of the pram. Firstly, here are the results of Swingbench with all SQL calls coming directly from the Java client.


C:\oracle\swingbench\winbin>java -cp ../launcher LauncherBootstrap -executablename charbench charbench -c ..\configs\SOE_Client_Side.xml -a -v tps -nc
Swingbench
Author  :        Dominic Giles
Version :        2.6.0.1163

Results will be written to results.xml

Time     TPS
08:19:13 0
08:19:14 177
08:19:15 471
08:19:16 492
08:19:17 456
08:19:18 507
08:19:19 512
08:19:20 499
...
...
08:19:57 509
08:19:58 508
08:19:59 539
08:20:00 520
08:20:01 636
08:20:02 596
Saved results to results.xml

Completed Run.

C:\oracle\swingbench\winbin>grep Transactions results.xml
<TotalCompletedTransactions>24902</TotalCompletedTransactions>
<AverageTransactionsPerSecond>508.2</AverageTransactionsPerSecond>
   
   

Excluding the initial ramp up, I’ll be generous and call this ~550 transactions per second, and I want to be fair to the middle tier fans here. This result shows that a well written Java application that takes advantage of all of the Oracle scalability features (ie, uses binds, prepared statements, statement caching etc) does a great job at crunching through database transactions.

Now I’ll repeat the exercise with the PL/SQL based configuration of the order entry benchmark. Just to ensure no-one thinks I’m gaming the system, this is still a genuine Java application executing a call for each transaction. We still have to go across the network for each transaction, but we are bundling up as many calls as we can into a single trip. I wanted to mention this because I had a question (aka accusation of cheating Smile) that my PL/SQL benchmark was a single call from Swingbench at the start of the benchmark and that it never returned to the app until the benchmark was complete. This is definitely not the case.


C:\oracle\swingbench\winbin>java -cp ../launcher LauncherBootstrap -executablename charbench charbench -c ..\configs\SOE_Server_Side_V2.xml -a -v tps -nc
Swingbench
Author  :        Dominic Giles
Version :        2.6.0.1163

Results will be written to results.xml

Time     TPS
08:21:03 0
08:21:04 755
08:21:05 1113
08:21:06 1092
08:21:07 1138
08:21:08 1115
08:21:09 1051
08:21:10 1237
08:21:11 1256
08:21:12 1292
08:21:13 1079
...
...
08:21:54 1350
08:21:55 1280
08:21:56 1304
08:21:58 1293
08:21:59 1342
08:22:00 1298
Saved results to results.xml

Completed Run.

C:\oracle\swingbench\winbin>grep Transactions results.xml
<TotalCompletedTransactions>74046</TotalCompletedTransactions>
<AverageTransactionsPerSecond>1234.1</AverageTransactionsPerSecond>
   

So I hope everyone is happy now Smile

You can get good performance with your Java application when its running across the network and you’re not using PL/SQL. The only problem is, you can get somewhere between double and triple that performance with the same application, the same network, the same application server and the same database server if you throw a little PL/SQL into the mix.

Or perhaps think of it in terms your IT senior management will understand:

You’re spending three times more money than you need to be

One comment

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.