General SQLite3 with encryption performance over years

SQLCipher
1

Hi guys,

I’m tasked with updating our database from SQLite3 v3.9.2 2016 which was implemented using rindeal/SQLite3-Encryption (now archived which was forked from wxsqlite3.

Note our application spawns executables, therefore database open times are critical. Specifically, for SQLCipher Performance I have implemented raw key usage, kdf_iter = 32000 & cipher_use_hmac = OFF. Modifying cipher_page_size did not noticeably affect our performance.

Question: Is there other PRAGMA’s I could implement to improve performance of open database sessions?

It seems like between 2016 and now, the encryption algorithm has significantly changed for all SQLite3 systems (sqlcipher, SEE, multiple ciphers etc). Because when implementing these solutions to the latest, they all have a x2 to x4 performance decrease on open database sessions or attach database queries. However other query times have slightly improved.

Question: Was the general encryption implementation for these solutions updated and subsequently effected database performance of opening encrypted databases / attach encrypted database queries?

Here is some screenshots of our unit test results (Base Creation is critical and Copy Tables is important):

ALL-DB-PERFORMANCE
ALL-DB-PERFORMANCE3196×1354 993 KB

1 Like
2

Hi @bananapeel - thanks for getting in touch about this. Before experimenting with additional PRAGMAs, I think it would be worth reviewing a few points:

  1. Even with kdf_iter set to 32K, that is still almost 3x what is used by slqeet, 8x the “new” wxSQLite3 settings, and infinitely more than the legacy wxSQLite3 settings. Because KDF is deliberately designed to be slow, this could be a significant factor.
  2. Raw key format is very specific. If not formatted correctly something that looks like a raw key could still be undergoing key derivation.
  3. Ordering of PRAGMAs is critical, if implemented improperly it is possible to be using default settings unexpectedly.

Therefore, before doing anything else:

  1. Reduce PRAGMA kdf_iter for SQLCipher, at least down to 12000 so it is comparable at least to.
  2. Double and triple check the format of raw key if you are using it.
  3. Call PRAGMA cipher_settings an open connection to verify that they were applied properly (i.e. HMAC is off, kdf_iterations is correct)

Alternately, if you’d like to post up some of the code for your test harness we could take a quick look and verify nothing else is amiss.

Finally, it’s worth noting that opening / closing connections can be expensive even without KDF. If your test harness is doing a lot of that it may be skewing your numbers for even small differences in configuration. In addition, page cache can make a big difference in performance on these kinds of tests. If you really want to test raw encryption performance you should disable it.

1 Like
3

Thank you for pointing out the default kdf_iter for the other encryption methods.
This indeed seems to have been the issue. Reducing it to sub 2000 produces similar performance to the initial implementation.

We are using SOCI to integrate SQLite3 with our applications. Here are the C++ commands in order:

sqlite3_open_v2(dbname.c_str(), &conn_, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
static const std::string sKey = "x'0123456789ABCDEF0123456789ABCDEF'";
sqlite3_key_v2(conn_, dbname.c_str(), sKey.data(), sKey.length());
sqlite3_exec(conn_, "PRAGMA kdf_iter = 32000;", 0, 0, 0);
sqlite3_exec(conn_, "PRAGMA cipher_default_kdf_iter = 32000;", 0, 0, 0);
sqlite3_exec(conn_, "PRAGMA cipher_use_hmac = OFF;", 0, 0, 0);
sqlite3_exec(conn_, "PRAGMA cipher_default_use_hmac = OFF;", 0, 0, 0);

Calling PRAGMA cipher_settings;, yields:

sqlite> PRAGMA cipher_settings;
PRAGMA kdf_iter = 32000;
PRAGMA cipher_page_size = 4096;
PRAGMA cipher_use_hmac = 0;
PRAGMA cipher_plaintext_header_size = 0;
PRAGMA cipher_hmac_algorithm = HMAC_SHA512;
PRAGMA cipher_kdf_algorithm = PBKDF2_HMAC_SHA512;

I have now disabled cache for the unit tests with PRAGMA cache_size = 0.

2 Likes
4

Hello @bananapeel - thanks for confirming. As an aside, for raw key format the hex string length must be either exactly 64 or 96 characters in order for sqlcipher to interpret it as a raw key.

Now that you have correct settings in place, I am curious about the performance you are seeing? Can you post an update?

1 Like