FWD » Core Development » Database

From the H2 mailing list:

-------- Forwarded Message --------
Subject: [h2] Re: Recent drops in performance
Date: Fri, 26 Apr 2019 07:15:34 -0700 (PDT)
From: Christian MICHON ...
Reply-To: h2-database@googlegroups.com
To: H2 Database <h2-database@googlegroups.com>

On Thursday, March 28, 2019 at 8:40:31 AM UTC+1, Evgenij Ryazanov wrote:

Hello.

Could you build a standalone test case that can be used to reproduce such slowdown? I don't see any reasons for it.

Hi,

@Joe: based on your comments, I've built a standalone performance test using JMeter. Not only you were right, but the situation has been around for some time since 1.4.197, maybe even before.

@Evgenij : it's not rocket science to build such scenario, if you need such performance test scenario, I'll share it on github (it's not confidential).

I'm surprised no one ever measured H2 performance in a statistical manner. I'm even more surprised by the results...

On these releases 1.2.147, 1.3.176, 1.4.196 and 1.4.199, the following scenario ran for 200 virtual users in a memory-only database: perform a complex CTE 1000 times on a indexed taxonomy database, with parametrized input.

This clearly shows a drop not only on latest 1.4.199, but the drop became substantial from 1.4.197 onward.

Below is a percentile graph showing that 1.2.147 (almost 9 years old!) is still the fastest H2 engine on a pure memory database... Followed by 1.3.176, then 1.4.196. Versions from 1.4.197 to 1.4.199 really came up with a new performance issue which can be felt by any of the 200 virtual users.

What dramatically changed between 1.4.196 and 1.4.197?

--
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-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

More from the mailing list:

-------- Forwarded Message --------
Subject: [h2] Re: Recent drops in performance
Date: Fri, 26 Apr 2019 08:19:32 -0700 (PDT)
From: Christian MICHON ...
Reply-To: h2-database@googlegroups.com
To: H2 Database <h2-database@googlegroups.com>

On Friday, April 26, 2019 at 4:28:00 PM UTC+2, Evgenij Ryazanov wrote:

Hello.

CTEs are experimental in H2 and have known bugs, recent versions contain some fixes, so this performance drop may be somehow related with them. But older versions of H2 do not support CTEs at all, so I don't understand how you can test a feature that does not exist.

We still need a complete standalone test case for your issue.

CTE was working with v1.2.x from what I recall and tested. I grant that CTE is overkill, but I needed the test to be compute intensive, to test the engine itself.

What I re-created just now is purely based on DDL:

DROP ALL OBJECTS;
CREATE TABLE TAXNUM ( TSN CHAR PRIMARY KEY NOT NULL, NAME VARCHAR NOT NULL, "TYPE" VARCHAR NOT NULL, PARENT VARCHAR);

I added some pauses ranging from 50ms to 100ms (we call them thinktime in performance testing) and I did only 4 versions, with similar load as described in my earlier email.

Here's the result: 1.4.197 is still slower. 1.4.198 went into errors as I believe the thinktimes are not long enough. I'll fix that.

--
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-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Please get the source for the latest version of H2 (1.4.200 at the time of this writing) and port our existing patches to it (see Building H2). Constantin has a recent patch which is not integrated into the FWD distribution yet; you will need to get that from him.

Using the test cases written for #4702 and branch 4011a_rebase (if ready) or 4011a (until 4011_rebase is ready), stress test and profile both customized builds of 1.4.197 and 1.4.200 to see if one performs noticeably better than the other. Report your results here. If there are any functional differences or bugs in either version, please report that as well.