Skip to content

Conversation

@MariusVolkhart
Copy link
Contributor

Configure SQLite to run in a mode comparable to H2.

The performance docs are updated with approximations of SQLite performance on these tests, but not with detailed numbers.


Anecdotal results collected off my laptop (not a reliable benchmark) for reference. Strangely, the BenchB test executes 24,088 fewer statements in the SQLite run than H2, but there are no exceptions or other indicators in the run output.

Test CaseUnitH2SQLite
Simple: Initms813520
Simple: Query (random)ms487274
Simple: Query (sequential)ms7771829
Simple: Update (sequential)ms9342241
Simple: Delete (sequential)ms9503282
Simple: Memory UsageMB192
BenchA: Initms572563
BenchA: Transactionsms5882399
BenchA: Memory UsageMB132
BenchB: Initms661576
BenchB: Transactionsms16780
BenchB: Memory UsageMB192
BenchC: Initms15371262
BenchC: Transactionsms860648
BenchC: Memory UsageMB203
Executed statements#22220322197724
Total timems834613674
Statements per second#/s266239160722

@andreitokar
Copy link
Contributor

Indeed,

PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

makes tremendous difference in SQLite performance, so now it become feasible to include it into testing.
Unfortunately, numbers in my test runs look quite different from what is provided here (in relative terms, of course).
SQLite looks to be on par with Derby, meaning 4-5 times slower than H2.

Test Case Unit H2 SQLite
Simple: Init ms 1294 1577
Simple: Query (random) ms 661 976
Simple: Query (sequential) ms 1403 6398
Simple: Update (sequential) ms 1794 9650
Simple: Delete (sequential) ms 1739 21876
Simple: Memory Usage MB 18 1
BenchA: Init ms 820 1249
BenchA: Transactions ms 1376 8360
BenchA: Memory Usage MB 11 1
BenchB: Init ms 925 1276
BenchB: Transactions ms 360 2174
BenchB: Memory Usage MB 14 1
BenchC: Init ms 2418 2515
BenchC: Transactions ms 1682 1292
BenchC: Memory Usage MB 17 2
Executed statements # 2222032 2209516
Total time ms 14472 57343
Statements per second #/s 153540 38531

It would be interesting to figure out what is the cause for such discrepancies. Are you running on SSD or spinning drive, how many cores in your laptop, what OS, JDK is used?

Mine is quad core, hyper-threaded with SSD, Linux, Oracle JDK1.8.

Strangely, the BenchB test executes 24,088 fewer statements in the SQLite run than H2

BenchB is the only test in that bunch that is actually concurrent.
If you print exceptions which are quietly swallowed now by this test, it shows:
org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked),
The missing statements in the count are those that failed
This tells me that SQLite is somewhat a single-connection engine, so it's really apples-to-oranges comparison.

@MariusVolkhart
Copy link
Contributor Author

Are you running on SSD or spinning drive, how many cores in your laptop, what OS, JDK is used?

Mine is quad core, hyper-threaded with SSD, Linux, Oracle JDK1.8.

SSD, MacOS, 8-core hyper-threaded, Adoptium JDK 17.0.2.

Running with JDK 1.8 (I included Derby to give another datapoint),

Test CaseUnitH2DerbySQLite
Simple: Initms6902444668
Simple: Query (random)ms316618353
Simple: Query (sequential)ms62425642173
Simple: Update (sequential)ms68228232392
Simple: Delete (sequential)ms82236995316
Simple: Memory UsageMB17131
BenchA: Initms4572621569
BenchA: Transactionsms53517562284
BenchA: Memory UsageMB1291
BenchB: Initms5802968571
BenchB: Transactionsms16657474
BenchB: Memory UsageMB16101
BenchC: Initms123044891396
BenchC: Transactionsms8031227680
BenchC: Memory UsageMB17102
Executed statements#222203222220322197688
Total timems69052578316476
Statements per second#/s32180086182133387

BenchB is the only test in that bunch that is actually concurrent.
If you print exceptions which are quietly swallowed now by this test, it shows:
org.sqlite.SQLiteException: [SQLITE_BUSY] The database file is locked (database is locked),
The missing statements in the count are those that failed
This tells me that SQLite is somewhat a single-connection engine, so it's really apples-to-oranges comparison.

This was very helpful! I'm not deeply familiar with SQLite, but what I found suggested that indeed, SQLite doesn't like multiple connections. It seems the recommendation is to either synchronize in Java and do all database work on a single thread, or share a single JDBC connection across multiple threads. I tried playing with SQLite busy handlers a bit, but still wasn't able to get it to play nicely with BenchB. As you said, apples to oranges.

So, I'd like to do the following:

  1. Update the performance docs to reflect "2-5x slower, depending on system configuration; these are unreliable numbers; try it out on your production hardware; etc."
  2. Update the performance docs to reflect that capabilities of SQLite don't match capabilities of H2, so the current benchmarks can't be used, hence no published numbers for SQLite.
  3. Merge the SQLite test code, but don't activate it in the properties file. This makes it relatively easy for others to try the benchmark, but clearly BenchB makes including SQLite benchmarks pointless.

Alternatively, if the maintainers would like, I could look at updating BenchB or the test harness or something to be friendly to reusing a connection across multiple threads. For example, non-SQLite could use a unique connection per thread, while SQLite uses the same connection across threads. Or all DBs could use the same connection across threads. Or there are multiple variants of BenchB. Lots of options here, but I'm also not sure what the goal of this would be, so would definitely like some direction before starting anything like this.

@andreitokar
Copy link
Contributor

@MariusVolkhart Items 1,2, and 3 are fine, of course. As far as updating BenchB, I am not sure what would be the purpose. It's known that some of those engines are single-user and therefore are failing this test, and yes, you can work around it with synchronization, and performance numbers will get worse. Results were published just to show that H2 performance is at least in the same range or better with other products. Test is composite and different aspects of the test can be tweaked (weighted differently) to emphasize various features, like concurrency, read, update, delete etc., so you mileage may vary.
I would not spend a lot of time on those improvements, but if you want to...

Configure SQLite to run in a mode comparable to H2.

The performance docs are updated with approximations of SQLite performance on these tests, but not with detailed numbers.
@MariusVolkhart
Copy link
Contributor Author

Docs updated, SQLite execution commented out, but support for sqlite left in place.

@andreitokar andreitokar merged commit c8df0f1 into h2database:master Mar 28, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants