Learning is not a spectator sport

Colleague Martin Berger posted this recently

and it got me thinking: If we take out the network (by running a program locally on the database server) how many requests per second can a modern but affordable/consumer level PC do?

After all, I’ve lost track of the number of posts I see on social media where people are saying you must create a 128-node distributed cluster of machines in order to handle 100 requests per second.

Now I must stress – I’m not claiming any “benchmark level rigour” here. This is solely my curiosity on how many concurrent requests my database can handle by making those requests as basic as possible.

Thus I’m doing the bare minimum here – a simple SELECT from DUAL, and iterating over this call 200,000 times in a Java program.

Quick screen shot of the source below and if you are excessively enthusiastic 🙂 and want to run this yourself you can grab the source here

On Windows, it is easy to throw together a quick batch script to launch a bunch of concurrent executions of a program

set CLASSPATH=C:\oracle\jdbc236\ojdbc17.jar;.
start java SimpleDual2
start java SimpleDual2
start java SimpleDual2
start java SimpleDual2
start java SimpleDual2
start java SimpleDual2
...
...

After tinkering with the number of concurrent executions, the sweet spot came out around 14 (unsurprising given my machine has 20 cores in total, 8 performance cores and 12 efficiency cores), and the elapsed time for 14 concurrent executions averaged 11.3 seconds.

A little bit of math yields

14 jobs * 200,000 requests / 11.3 seconds = ~240,000 requests per second!

If you’re thinking (like I was)  “Wow!” then remember that even though we have eliminated the network transmission cost, we are still incurring the cost of having the database “respond” to a request coming from an external program.

What if we moved that request inside the database, ie, we ran all those requests in PL/SQL?  That’s easy to do

set timing on
declare
  x int;
begin
  for i in 1 .. 500000 loop
    select 1 into x from dual;
  end loop;
end;
/

Notice I’ve bumped up the iteration count to 500,000. That’s probably a hint that this was so fast I had to bump up the numbers to get reasonable timing!

The results?

14 jobs * 500,000 requests / 6.2 seconds = ~1,130,000 requests per second!

All on a home PC which by the way, like most people, had the usual 30 browser tabs open whilst the benchmarks were running 🙂

Modern computers are just insanely powerful. From someone who began their Oracle database work on a Sun Sparc 10 running at 33Mhz, I have to keep telling myself “We ain’t in Kansas anymore”