Noticeable slowdown on SELECTs on 4.x on Android with PRAGMA cipher_memory_security = ON

[b-renaut]

Remark: while this has been witnessed on Android, unless I'm mistaken this is not a android-database-sqlcipher bug as I'm using the amalgamation from the NDK side directly (a classic C program, not the Java API).

I'm running repeated (2 or 3 times per second) SELECT queries on a large table that has 26 columns, and about 30K records. The only conditional in the query is a comparison with a text field and the proper indexes set on that field (as confirmed by EXPLAIN QUERY PLAN). Most of the columns are retrieved on the SELECTs; one of them is a BLOB, but it is selected through HEX(blob_column).

Compared to previous (3.x) versions, doing those selects appears to take roughly double the amount of time on 4.x. This is confirmed by an average of the N last queries (it's not a one-shot). The database itself was a 3.x one, but this happens both when using the new 4.x config (after doing a pragma cipher_migrate+vacuum on said database) and when using 4.x with the 3.x compatibility settings (using the four pragma provided in your release changelog for that purpose).

I saw this bug, however: sqlcipher/android-database-sqlcipher#411
... and as a result tried:

PRAGMA cipher_memory_security = OFF

... and this fixes the issue entirely. The selects now run as fast, if not faster, than on 3.x.

This may not really be a "bug", mind you; the new memory wiping security feature does strongly increase security. However, the fact that it was added in 4.x, and above all the possible performance impact (double the time for my use case described above), should probably be announced and documented better.

[sjlombardo]

@b-renaut how many rows are retrieved by your query, and how much total data are you bringing back from from the results, inclusive of all columns and the BLOB?

[b-renaut]

It varies: usually several hundred (~700 or so); sometimes a little above a thousand.

[sjlombardo]

@b-renaut can you roughly estimate how much total data you are bringing back from from the results, inclusive of all columns and the BLOB?

[b-renaut]

sqlite3_analyzer says the average payload for an entry is 639.57 bytes (for a total payload for the table of ~18MB). This would mean around 437kB per SELECT, and sometimes up to 700kB; I do not select all columns, but nearly all of them (and certainly all the blob/text ones), so I think the estimate should be close. I can redo it after stripping the non-selected columns (those I discard in my SELECTs) from the table if you wish.

I created a cleartext version from the encrypted one in order to run sqlite3_analyzer on it; tell me if you wish me to provide an estimate using a different method.

[sjlombardo]

Hello @b-renaut - Just to close the loop on this, we have decided to disable this feature by default. In practice there was a huge variability in performance across devices and operating systems, particularly on Android. It can still be enabled on demand.