Performance & other implications of compile flags

(1) By Dmitri Gaskin (Branch) (dmitri-branch) on 2022-02-01 21:45:32 [link] [source]

Hi there, I have a few questions about various compile time flags.

Are there any impacts on ANALYZE latency, database size, or on SELECT statements latency from enabling SQLITE_ENABLE_DBSTAT_VTAB option? As far as I can tell, there are none, but I wanted to ask just to be 100% sure.

Are there any impacts on ANALYZE latency, database size, or on SELECT statements latency of enabling SQLITE_ENABLE_STAT4 option? It seems a bit unclear from the documentation; if I'm reading correctly, SELECT performance will generally improve but in some cases can degrade. And ANALYZE will be slightly slower?

Are there other options (short of optimizing queries or schemas) we should consider to help with any of these things?

Thank you!

(2) By Keith Medcalf (kmedcalf) on 2022-02-01 22:24:58 in reply to 1 [link] [source]

The largest impact on performance (if performance is defined as in-process concurrency) would be the setting of the SQLITE_DEFAULT_MEMSTATUS compile option, which defaults to enabled (1).

Enabling this alows the SQLite3 library to have a better handle (knowledge of) memory allocations but has the side effect of wrapping the memory allocators in a mutex (ie, only one thread of execution at a time may access the memory allocator).

Disabling this will disable memory tracking and remove the enforcement of serialization around the memory allocators.

(3) By Dmitri Gaskin (Branch) (dmitri-branch) on 2022-02-07 23:04:02 in reply to 2 [link] [source]

Thank you - this is super helpful.

Do you know if the SQLITE_ENABLE_DBSTAT_VTAB or SQLITE_ENABLE_STAT4 options have any performance impact?

(4) By Keith Medcalf (kmedcalf) on 2022-02-07 23:24:04 in reply to 3 [source]

SQLITE_ENABLE_DBSTAT_VTAB enables the dbstat virtual table. This will make the executable bigger, but should not impact performance if it is not used.

SQLITE_ENABLE_STAT4 will impact performance (and, of course, take up space). It will make ANALYZE; take much longer as it gathers statistics on the data distribution of indexes. This data may, in turn, cause the optimizer to make better decisions and improve the performance of queries. On the other hand, queries for which the plan depends on parameter values will be internally re-prepared after parameter binding. If the same statement is used multiple times, it will be re-prepared each time.

(5) By Dmitri Gaskin (Branch) (dmitri-branch) on 2022-02-08 05:58:03 in reply to 4 [link] [source]

Thank you very much!

(6) By ddevienne on 2022-02-08 07:58:46 in reply to 2 [link] [source]

The largest impact on performance [...] would be the setting of the SQLITE_DEFAULT_MEMSTATUS:

Funny coincidence, yesterday I was running a benchmark written by a colleague on that very point.
As you can see, the more threads, the more contention on that one mutex. Up to a 4x difference on this machine. --DD

PS: Makes one wonder whether it should be OFF by default, instead of ON.

[2022-Feb-07 20:11:50] Info: Default configuration:
[2022-Feb-07 20:11:51] Info: Concurrent Sqlite select using   1 thread  in 0.320s (user: 0.230s) 10 MB
[2022-Feb-07 20:11:51] Info: Concurrent Sqlite select using   2 threads in 0.350s (user: 0.490s) 12 MB
[2022-Feb-07 20:11:52] Info: Concurrent Sqlite select using   4 threads in 0.460s (user: 1.170s) 16 MB
[2022-Feb-07 20:11:52] Info: Concurrent Sqlite select using   8 threads in 0.770s (user: 2.910s) 27 MB
[2022-Feb-07 20:11:54] Info: Concurrent Sqlite select using  16 threads in 1.540s (user: 6.050s) 45 MB
[2022-Feb-07 20:11:57] Info: Concurrent Sqlite select using  32 threads in 3.460s (user: 15.850s) 86 MB
[2022-Feb-07 20:12:04] Info: Concurrent Sqlite select using  64 threads in 7.020s (user: 31.490s) 163 MB

[2022-Feb-07 20:12:18] Info: SQLITE_CONFIG_MEMSTATUS=0:
[2022-Feb-07 20:12:18] Info: Concurrent Sqlite select using   1 thread  in 0.210s (user: 0.160s) 163 MB
[2022-Feb-07 20:12:18] Info: Concurrent Sqlite select using   2 threads in 0.290s (user: 0.380s) 163 MB
[2022-Feb-07 20:12:19] Info: Concurrent Sqlite select using   4 threads in 0.360s (user: 0.870s) 163 MB
[2022-Feb-07 20:12:19] Info: Concurrent Sqlite select using   8 threads in 0.310s (user: 1.760s) 163 MB
[2022-Feb-07 20:12:20] Info: Concurrent Sqlite select using  16 threads in 0.520s (user: 4.510s) 163 MB
[2022-Feb-07 20:12:20] Info: Concurrent Sqlite select using  32 threads in 0.850s (user: 14.080s) 163 MB
[2022-Feb-07 20:12:22] Info: Concurrent Sqlite select using  64 threads in 1.640s (user: 26.750s) 163 MB