PRAGMA REKEY performance

#1

Hello,
Long story short I’m having difficulties getting a decent time for an update operation.

Long story:
I have two databases on an Android device.

  1. The main db that stores all the offline data on which the app functions
  2. A partial db which is an update downloaded from the server or uploaded directly inside the device.

The main db can and will get to around ~1GB in size.
While the partial db usually stores updates and should be decent in size. (max ~100MB)

Now, the idea is that the merge takes a decent amount of time given the data that contains blobs and other stuff but I will have to change the password after the update.

For 1GB main db size the Rekey can take ~15mins which locks the app for that period of time.

I also considered creating a new database with the new password and inserting everything into it to remove the rekey option but I’m still getting bad performance.

Can you please guide me with this matter? I would really appreciate it.

Thank you,
D

#2

Hello @DragosIancu - rekey operations are expected to be fairly time consuming because they have to decrypt and re-encrypt the entire database. That said, 15 minutes sounds excessive. In some cases create a new database and inserting into it can be faster, if the origin data is not encrypted because it saves having to decrypt every page in the database.

One potential optimization you might look at is disabling memory security:

PRAGMA cipher_memory_security = OFF;

This may have a fairly substantial performance impact given the amount of data you are dealing with. Can you give that a try and let us know if it helps?

#3

Hi @sjlombardo and thank you for the reply.

The difference between “cipher_memory_security = OFF/ON;” is a few seconds and I already had it set to OFF :(.

I know rekey is a pretty heavy operation but I was thinking I could squeeze some time out of it given that no one believes me that it’s at cap performance and my last resort was contacting you guys after researching all the forums.

Also the device is pretty limited, the android version is 4.4.4(API 19) and that is also a problem.

If you have any other suggestion I would really appreciate it.

Thank you,
D