Engine performance very differs between H2 and Postgres

I perform some kind of performance analysis of Camunda engine and wrote a test program https://pastebin.com/VUYgfHvA. It runs a couple of threads and every of them finishes a task or starting a new process. So when it uses H2 as backend then instance successfully utilizes all resources (I see the processor load is growing to 100%) and the peak result is 2900 tasks per second (200 threads). But when it uses Postgresql then there are a lot of free RAM, CPU, HDD and the peak result is 800 tasks per second (40 threads).
History is always enabled, jdbc looks like: “jdbc:postgresql://localhost:5432/postgres” and “jdbc:h2:tcp://localhost/~/test”

Schema is pretty simple:
test_PT.bpmn (17.8 KB)

I use: the last release of Camunda v7.6.0, just required jars:

postgresql-9.6.3-1-windows-x64 (I have tryied on 9.4 but results are the same) and h2-setup-2017-04-23 and oracle jre1.8.0_121

The question is - Why so huge difference between DBMSes (H2 allows a load 3.5 times greater the Postgres does)? Is it a bug in Engine or it could be fixed some way?

Hi,

you compare two different databases and found out that H2 is faster then postgres.
Why should this be a bug of the engine?
You can also see the performance test’s of H2 to see this difference.

I have to ask, do you use the embedded or server setting of H2? Because embedded databases will always be faster then others, as you can see here.
If H2 is used in Server mode the performance looks different, see here.

Nevertheless how do you test with 200 threads, what kind of machine do you have?
Keep in mind that H2 is a single threaded database and the other ones are multithreaded.

Greets
Chris

2 Likes

You are rigth, H2 seems at least 2 times faster than PostgreSQL in mentinoned articles.
I use H2 in server mode as you can see in jdbc: “tcp”
This code creates and starts threads:

for( int i = 0; i < max && !interrupted; ) {
	for( int j = 0; j < portion && i < max && !interrupted; j++ , i++ ) {
		UserThread2 ut = new UserThread2();
		ut.start();
		threads.add( ut );
	}
	System.out.println( "Threads count: " + i );
	Thread.sleep( msecbetpor );
}

max = 150
portion = 10
msecbetpor = 5000
UserThread2 executes method mentioned in my first post (pastebin)

I perform test on Win7 64, 14G RAM (max consumption is 1-2G), and HDD (Max IO is 1Mb\sec for Postgres and 3Mb\sec for H2), CPU i5-760 (max load for Postgres is 30%, for H2 is 100%). (I place measures during the test in brackets)

The weird fact is H2 utilizes all hardware resources, but PostgreSQL doesn’t. The common behaviour is program firstly consume some kind of resource then slow down should happens, isn’t it? In my observe slow down allready happened, but consuption is not happened.

Another fact is I perform test on 2 different machines, and results for PostgreSQL are the same. So I came to conclusion engine with postgresql are not scalable.

I have tested PostgreSQL on 2 mentioned machines and found a similar performance.

pgbench --port=6543 -c 100 -j 4 User

latency average = 230.614 ms
tps = 433.624171 (including connections establishing)
tps = 439.126752 (excluding connections establishing)

pgbench -U postgres -c 100 -j 4 postgres

latency average = 256.458 ms
tps = 389.927946 (including connections establishing)
tps = 394.693402 (excluding connections establishing)

So I conclude the issue about performance scaling of PostgreSQL on Windows.