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

Statistics collection is disabled by default. It can be enabled with PRAGMA cipher_stat_memory = ON and PRAGMA cipher_stat_crypto = ON. Once enabled, 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            | 248     |
| encrypt-bytes            | 995936  |
| encrypt-time             | 1018    |
| decrypt-count            | 239     |
| decrypt-bytes            | 959808  |
| decrypt-time             | 1272    |
| kdf-count                | 2       |
| kdf-bytes                | 64      |
| kdf-time                 | 192940  |
| hmac-count               | 488     |
| hmac-bytes               | 1965545 |
| hmac-time                | 4388    |
| random-count             | 248     |
| random-bytes             | 19840   |
| random-time              | 1316    |
| memory-free-time         | 154     |
| memory-free-count        | 4138    |
| memory-malloc-time       | 2335    |
| memory-malloc-count      | 4881    |
| memory-realloc-time      | 1       |
| memory-realloc-count     | 6       |
| sqlite-memory-used       | 2181400 |
| sqlite-malloc-count      | 743     |
| sqlite-memory-used-high  | 3349904 |
| sqlite-malloc-count-high | 990     |
+--------------------------+---------+

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 cryptographically secure random bytes.
memory-free-time The total clock time in microseconds (μs) that SQLCipher has spent freeing memory allocations, inclusive of memory sanitization and unlocking if Enhanced Memory Security is enabled.
memory-free-count The total nmber of memory free operations.
memory-malloc-time The total clock time in microseconds (μs) that SQLCipher has spent allocating memory, inclusive of memory locking if Enhanced Memory Security is enabled.
memory-malloc-count The total number of memory allocation operations.
memory-realloc-time The total clock time in microseconds (μs) that SQLCipher has spent increasing the size of existing memory allocations.
memory-realloc-count The total number of memory reallocation operations (i.e. memory allocation size increases)
sqlite-memory-used The current amount of memory used by the library as reported by SQLite’s runtime status interface (i.e. SQLITE_STATUS_MEMORY_USED)
sqlite-malloc-count The current number of memory allocations used by the library as reported by SQLite’s runtime status interface (i.e. SQLITE_STATUS_MALLOC_COUNT)
sqlite-memory-used-high The maximum amount of memory ever used by the library as reported by SQLite’s runtime status interface (i.e. SQLITE_STATUS_MEMORY_USED)
sqlite-malloc-count-high The maximum number of memory allocations ever used by the library as reported by SQLite’s runtime status interface (i.e. SQLITE_STATUS_MALLOC_COUNT)

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.