SQLCipher Performance Counters and Statistics

Commercial & Enterprise Edition Feature

SQLCipher Commercial and Enterprise packages contain a special extension which provides applications with access to internal performance counters which can be useful for optimization and tuning purposes. Applications can use the sqlcipher_stat counters to obtain objective information about how SQLCipher is operating in response to applicaiton input. Here are some common example uses:

  1. Look for evidence of common performance issues (e.g. full table scans, repeated key derivation operations, etc)
  2. Measure the specific overhead introduced by SQLCipher cryptography for specific queries or an application as a whole
  3. Capture tangible “before and after” performance statistics during optimization, refactoring, or data restructuring activities
  4. Compare relative performance between different classes of device, or across different platforms
  5. Identify “hotspot” statements that negatively impact performance

Real-time statistics about the utilization of the SQLCipher cryptographic provider are accessed by querying the special sqlcipher_stat virtual table.

> select * from sqlcipher_stat;
stat           count
-------------  ----------
encrypt-count  36
encrypt-bytes  144288
encrypt-time   293
decrypt-count  239
decrypt-bytes  959808
decrypt-time   897
kdf-count      2
kdf-bytes      64
kdf-time       298110
hmac-count     276
hmac-bytes     1109661
hmac-time      5529
random-count   36
random-bytes   2880
random-time    232

Each row of the sqlcipher_stat table shows an individual counter. The values are described below:

Stat Key Name Description
encrypt-count The total number of encryption operations that have been invoked through SQLCipher. This correlates to the number of pages encrypted by SQLCipher for Full Database Encryption and the number of individual values that have been encrypted by SQLCipher using either the sqlcipher_vle_encrypt() function or the Virtual Table Interface.
encrypt-bytes The total number of bytes encrypted by SQLCipher.
encrypt-time The total clock time in microseconds (μs) that SQLCipher has spent performing encryption operations.
decrypt-count The total number of decryption operations that have been invoked through SQLCipher. This correlates to the number of pages decrypted by SQLCipher for Full Database Encryption and the number of individual values that have been decrypted by SQLCipher using either the sqlcipher_vle_decrypt() function or the Virtual Table Interface.
decrypt-bytes The total number of bytes decrypted by SQLCipher.
decrypt-time The total clock time in microseconds (μs) that SQLCipher has spent performing decryption operations.
kdf-count The total number of key derivation operations that have been invoked through SQLCipher. For full database encryption key derivation occurs the first time a connection is used after keying. For the VLE interface this will correspond to the number of times sqlcipher_vle_key() or sqlcipher_vle_pbkdf2() are called.
kdf-bytes The total number of bytes that have been output as key material from SQLCipher PBKDF2 operations.
kdf-time The total lock time in microseconds (μs) that SQLCipher has spent performing key derivation operations.
hmac-count The total number of HMAC operations that have been invoked through SQLCipher. By default HMAC operations occur for every page written or read using SQLCipher Full Database Encryption and for each value encrypted or decrypted using SQLCipher Value Level Encryption.
hmac-bytes The total number of bytes that have passed through SQLCipher HMAC interfaces.
hmac-time The total clock time in microseconds (μs) that SQLCipher has spent performing HMAC operations.
random-count The total number of CSPRNG randomness operations that have been invoked through SQLCipher. SQLCipher generates random data for all Initialization Vectors, as well as for the database salt. This will roughly correlates to the number of pages encrypted by SQLCipher for Full Database Encryption and the number of individual values that have been encrypted by SQLCipher using either the sqlcipher_vle_encrypt() function or the Virtual Table Interface.
random-bytes The total number of random bytes generated by SQLCipher.
random-time The total clock time in microseconds (μs) that SQLCipher has spent generating randomness

It is possible for an application to reset all sqlcipher_stat counters, or individual counters by executing an update statement on the sqlcipher_stat table. For example, the following statement will reset the encryption-time counter to zero:

UPDATE sqlcipher_stat SET count = 0 WHERE stat = 'encrypt-time';

Likewise, an application can reset all counters to zero by leaving off the WHERE clause, e.g.:

UPDATE sqlcipher_stat SET count = 0;

Following execution of such a reset statement SQLCipher will restart counting from the new zero baseline.