Skip to content

sqlite: set SQLITE_THREADSAFE flag as multithreaded#63645

Open
geeksilva97 wants to merge 1 commit into
nodejs:mainfrom
geeksilva97:sqlite-improve-get-performance
Open

sqlite: set SQLITE_THREADSAFE flag as multithreaded#63645
geeksilva97 wants to merge 1 commit into
nodejs:mainfrom
geeksilva97:sqlite-improve-get-performance

Conversation

@geeksilva97
Copy link
Copy Markdown
Contributor

@geeksilva97 geeksilva97 commented May 29, 2026 •
edited
Loading

We should consider setting SQLITE_THREADSAFE as multithreaded.

node:sqlite currently runs on serialized mode. with that we pay some price on mutex acquiring/releasing. But the nature of Node allows code to work safely without these locks.

a tiny sample taken from node::sqlite::StatementExecutionHelper::ColumnToValue call 
    +                             ! : | + ! 93 node::sqlite::StatementExecutionHelper::ColumnToValue(node::Environment*, sqlite3_stmt*, int, bool)  (in node) + 188  [0x102d9bef0]  node_sqlite.cc:2806
    +                             ! : | + ! : 41 sqlite3_column_bytes  (in node) + 252  [0x1043291c0]  sqlite3.c:94923
    +                             ! : | + ! : | 22 _pthread_mutex_firstfit_unlock_slow  (in libsystem_pthread.dylib) + 196,140,...  [0x188ee2050,0x188ee2018,...]
    +                             ! : | + ! : | 8 _pthread_mutex_unlock_init_slow  (in libsystem_pthread.dylib) + 20,72,...  [0x188ee1efc,0x188ee1f30,...]
    +                             ! : | + ! : | 6 DYLD-STUB$$pthread_mutex_unlock  (in node) + 4  [0x1052ca31c]
    +                             ! : | + ! : | 5 pthread_mutex_unlock  (in libsystem_pthread.dylib) + 12  [0x188ee1e24]
    +                             ! : | + ! : 16 sqlite3_column_bytes  (in node) + 48  [0x1043290f4]  sqlite3.c:94922
    +                             ! : | + ! : | 11 _pthread_mutex_firstfit_lock_slow  (in libsystem_pthread.dylib) + 148,48,...  [0x188ee1d74,0x188ee1d10,...]
    +                             ! : | + ! : | 3 pthread_mutex_lock  (in libsystem_pthread.dylib) + 12,172  [0x188ee18e0,0x188ee1980]
    +                             ! : | + ! : | 2 _pthread_mutex_lock_init_slow  (in libsystem_pthread.dylib) + 100  [0x188ee1a40]
    +                             ! : | + ! : 13 sqlite3_column_bytes  (in node) + 48,124,...  [0x1043290f4,0x104329140,...]  sqlite3.c:94922
    +                             ! : | + ! : 5 sqlite3_column_bytes  (in node) + 200,232  [0x10432918c,0x1043291ac]  sqlite3.c:94923
    +                             ! : | + ! : 4 _pthread_mutex_firstfit_lock_slow  (in libsystem_pthread.dylib) + 308  [0x188ee1e14]
    +                             ! : | + ! : 4 _pthread_mutex_lock_init_slow  (in libsystem_pthread.dylib) + 116,112  [0x188ee1a50,0x188ee1a4c]
    +                             ! : | + ! : 4 pthread_mutex_lock  (in libsystem_pthread.dylib) + 176  [0x188ee1984]
    +                             ! : | + ! : 3 _pthread_mutex_unlock_init_slow  (in libsystem_pthread.dylib) + 104,108  [0x188ee1f50,0x188ee1f54]
    +                             ! : | + ! : 2 sqlite3_column_bytes  (in node) + 4  [0x1043290c8]  sqlite3.c:94921
    +                             ! : | + ! : 1 pthread_mutex_unlock  (in libsystem_pthread.dylib) + 156  [0x188ee1eb4]
    +                             ! : | + ! 92 node::sqlite::StatementExecutionHelper::ColumnToValue(node::Environment*, sqlite3_stmt*, int, bool)  (in node) + 260  [0x102d9bf38]  node_sqlite.cc:2806
    +                             ! : | + ! : 32 sqlite3_column_bytes  (in node) + 252  [0x1043291c0]  sqlite3.c:94923
    +                             ! : | + ! : | 23 _pthread_mutex_firstfit_unlock_slow  (in libsystem_pthread.dylib) + 196,140,...  [0x188ee2050,0x188ee2018,...]
    +                             ! : | + ! : | 5 _pthread_mutex_unlock_init_slow  (in libsystem_pthread.dylib) + 20,24  [0x188ee1efc,0x188ee1f00]
    +                             ! : | + ! : | 4 pthread_mutex_unlock  (in libsystem_pthread.dylib) + 12  [0x188ee1e24]
    +                             ! : | + ! : 21 sqlite3_column_bytes  (in node) + 48  [0x1043290f4]  sqlite3.c:94922
    +                             ! : | + ! : | 11 _pthread_mutex_firstfit_lock_slow  (in libsystem_pthread.dylib) + 148,48,...  [0x188ee1d74,0x188ee1d10,...]
    +                             ! : | + ! : | 5 _pthread_mutex_lock_init_slow  (in libsystem_pthread.dylib) + 24,100  [0x188ee19f4,0x188ee1a40]
    +                             ! : | + ! : | 3 DYLD-STUB$$pthread_mutex_lock  (in node) + 4  [0x1052ca304]
    +                             ! : | + ! : | 2 pthread_mutex_lock  (in libsystem_pthread.dylib) + 12  [0x188ee18e0]
    +                             ! : | + ! : 14 sqlite3_column_bytes  (in node) + 48,40,...  [0x1043290f4,0x1043290ec,...]  sqlite3.c:94922
    +                             ! : | + ! : 6 _pthread_mutex_unlock_init_slow  (in libsystem_pthread.dylib) + 108,104  [0x188ee1f54,0x188ee1f50]
    +                             ! : | + ! : 5 pthread_mutex_unlock  (in libsystem_pthread.dylib) + 156  [0x188ee1eb4]
    +                             ! : | + ! : 5 sqlite3_column_bytes  (in node) + 192,232,...  [0x104329184,0x1043291ac,...]  sqlite3.c:94923
    +                             ! : | + ! : 4 pthread_mutex_lock  (in libsystem_pthread.dylib) + 176  [0x188ee1984]
    +                             ! : | + ! : 3 _pthread_mutex_firstfit_lock_slow  (in libsystem_pthread.dylib) + 308  [0x188ee1e14]
    +                             ! : | + ! : 1 _pthread_mutex_lock_init_slow  (in libsystem_pthread.dylib) + 116  [0x188ee1a50]

By setting threading mode to multithreaded we have less overhead over serialized. I compiled and benchmarked (on a 14-core Apple M4).

Node.js Benchmark comparison 
                                                                                                                                                              confidence improvement accuracy (*)   (**)   (***)
sqlite/sqlite-is-transaction.js transaction='false' n=10000000                                                                                                               -0.69 %       ±6.17% ±8.21% ±10.69%
sqlite/sqlite-is-transaction.js transaction='true' n=10000000                                                                                                                -0.45 %       ±0.46% ±0.61%  ±0.80%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO all_column_types (text_column, integer_column, real_column, blob_column) VALUES (?, ?, ?, ?)' n=100000        ***     10.29 %       ±1.73% ±2.31%  ±3.00%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO blob_column_type (blob_column) VALUES (?)' n=100000                                                           ***      8.34 %       ±2.17% ±2.89%  ±3.76%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO integer_column_type (integer_column) VALUES (?)' n=100000                                                     ***      6.36 %       ±1.57% ±2.09%  ±2.72%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO large_text (text_8kb_column) VALUES (?)' n=100000                                                             ***      2.38 %       ±1.12% ±1.48%  ±1.93%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO missing_required_value (any_value, required_value) VALUES (?, ?)' n=100000                                    ***      1.85 %       ±0.75% ±1.00%  ±1.30%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO real_column_type (real_column) VALUES (?)' n=100000                                                           ***      7.47 %       ±1.52% ±2.03%  ±2.64%
sqlite/sqlite-prepare-insert.js statement='INSERT INTO text_column_type (text_column) VALUES (?)' n=100000                                                           ***      6.80 %       ±1.52% ±2.02%  ±2.65%
sqlite/sqlite-prepare-select-all-options.js options='none' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                       ***     24.24 %       ±1.12% ±1.49%  ±1.94%
sqlite/sqlite-prepare-select-all-options.js options='none' statement='SELECT * FROM foo LIMIT 100' tableSeedSize=100000 n=100000                                     ***     26.79 %       ±0.80% ±1.07%  ±1.39%
sqlite/sqlite-prepare-select-all-options.js options='readBigInts' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                ***     23.56 %       ±1.17% ±1.57%  ±2.05%
sqlite/sqlite-prepare-select-all-options.js options='readBigInts' statement='SELECT * FROM foo LIMIT 100' tableSeedSize=100000 n=100000                              ***     26.21 %       ±0.62% ±0.82%  ±1.07%
sqlite/sqlite-prepare-select-all-options.js options='readBigInts|returnArrays' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                   ***     25.59 %       ±1.42% ±1.89%  ±2.48%
sqlite/sqlite-prepare-select-all-options.js options='readBigInts|returnArrays' statement='SELECT * FROM foo LIMIT 100' tableSeedSize=100000 n=100000                 ***     33.95 %       ±0.76% ±1.02%  ±1.32%
sqlite/sqlite-prepare-select-all-options.js options='returnArrays' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                               ***     26.55 %       ±1.20% ±1.60%  ±2.09%
sqlite/sqlite-prepare-select-all-options.js options='returnArrays' statement='SELECT * FROM foo LIMIT 100' tableSeedSize=100000 n=100000                             ***     36.24 %       ±1.29% ±1.72%  ±2.24%
sqlite/sqlite-prepare-select-all.js statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                                              ***     24.52 %       ±1.39% ±1.85%  ±2.42%
sqlite/sqlite-prepare-select-all.js statement='SELECT * FROM foo LIMIT 100' tableSeedSize=100000 n=100000                                                            ***     25.94 %       ±1.12% ±1.50%  ±1.98%
sqlite/sqlite-prepare-select-all.js statement='SELECT 1' tableSeedSize=100000 n=100000                                                                               ***     18.60 %       ±1.95% ±2.61%  ±3.41%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_8kb_column FROM foo_large LIMIT 1' tableSeedSize=100000 n=100000                                          ***      7.53 %       ±1.43% ±1.91%  ±2.50%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_8kb_column FROM foo_large LIMIT 100' tableSeedSize=100000 n=100000                                        ***      3.54 %       ±0.50% ±0.66%  ±0.86%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                                    ***     19.81 %       ±1.62% ±2.17%  ±2.83%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column FROM foo LIMIT 100' tableSeedSize=100000 n=100000                                                  ***     25.97 %       ±0.56% ±0.75%  ±0.99%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                    ***     24.80 %       ±1.43% ±1.91%  ±2.49%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column FROM foo LIMIT 100' tableSeedSize=100000 n=100000                                  ***     34.32 %       ±0.40% ±0.54%  ±0.70%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column, real_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                       ***     25.70 %       ±1.54% ±2.05%  ±2.69%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column, real_column FROM foo LIMIT 100' tableSeedSize=100000 n=100000                     ***     37.77 %       ±0.50% ±0.66%  ±0.86%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column, real_column, blob_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000          ***     22.16 %       ±1.26% ±1.67%  ±2.18%
sqlite/sqlite-prepare-select-all.js statement='SELECT text_column, integer_column, real_column, blob_column FROM foo LIMIT 100' tableSeedSize=100000 n=100000        ***     26.01 %       ±0.67% ±0.90%  ±1.17%
sqlite/sqlite-prepare-select-get-options.js options='none' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                       ***     25.02 %       ±0.96% ±1.28%  ±1.66%
sqlite/sqlite-prepare-select-get-options.js options='readBigInts' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                ***     23.22 %       ±1.14% ±1.52%  ±1.98%
sqlite/sqlite-prepare-select-get-options.js options='readBigInts|returnArrays' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                   ***     27.94 %       ±1.45% ±1.93%  ±2.52%
sqlite/sqlite-prepare-select-get-options.js options='returnArrays' statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                               ***     27.08 %       ±1.21% ±1.61%  ±2.10%
sqlite/sqlite-prepare-select-get.js statement='SELECT * FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                                              ***     24.01 %       ±1.02% ±1.36%  ±1.78%
sqlite/sqlite-prepare-select-get.js statement='SELECT 1' tableSeedSize=100000 n=100000                                                                               ***     18.58 %       ±1.87% ±2.49%  ±3.24%
sqlite/sqlite-prepare-select-get.js statement='SELECT text_8kb_column FROM foo_large LIMIT 1' tableSeedSize=100000 n=100000                                          ***      8.19 %       ±1.15% ±1.53%  ±1.99%
sqlite/sqlite-prepare-select-get.js statement='SELECT text_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                                    ***     18.05 %       ±1.35% ±1.80%  ±2.35%
sqlite/sqlite-prepare-select-get.js statement='SELECT text_column, integer_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                                    ***     24.16 %       ±1.06% ±1.41%  ±1.83%
sqlite/sqlite-prepare-select-get.js statement='SELECT text_column, integer_column, real_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000                       ***     26.91 %       ±1.49% ±1.99%  ±2.60%
sqlite/sqlite-prepare-select-get.js statement='SELECT text_column, integer_column, real_column, blob_column FROM foo LIMIT 1' tableSeedSize=100000 n=100000          ***     24.34 %       ±1.14% ±1.51%  ±1.97%

Be aware that when doing many comparisons the risk of a false-positive result increases.
In this case, there are 41 comparisons, you can thus expect the following amount of false-positive results:
  2.05 false positives, when considering a   5% risk acceptance (*, **, ***),
  0.41 false positives, when considering a   1% risk acceptance (**, ***),
  0.04 false positives, when considering a 0.1% risk acceptance (***)

Running the better-sqlite benchmark we also see the gap reduction.

better-sqlite itself has threading mode as multithreaded.

@geeksilva97
sqlite: set SQLITE_THREADSAFE flag as multithreaded
ed472f8 
Signed-off-by: geeksilva97 <edigleyssonsilva@gmail.com>
@nodejs-github-bot
Copy link
Copy Markdown
Collaborator

nodejs-github-bot commented May 29, 2026

Review requested:

  • @nodejs/gyp
  • @nodejs/security-wg
  • @nodejs/sqlite

@nodejs-github-bot nodejs-github-bot added dependencies Pull requests that update a dependency file. needs-ci PRs that need a full CI run. sqlite Issues and PRs related to the SQLite subsystem. labels May 29, 2026
@Renegade334
Copy link
Copy Markdown
Member

Renegade334 commented May 29, 2026

We do backup operations off-thread, is this not going to be an issue?

no single database connection nor any object derived from database connection, such as a prepared statement, is used in two or more threads at the same time.

@geeksilva97
Copy link
Copy Markdown
Contributor Author

geeksilva97 commented May 29, 2026

We do backup operations off-thread, is this not going to be an issue?

no single database connection nor any object derived from database connection, such as a prepared statement, is used in two or more threads at the same time.

That's correct. We would need to redefine it. Either opening a new connection or making it work in the main thread. Both with advantages and drawbacks.

I will fill an issue so we can discuss more about it. So far I'm inclined to think that the gains on more use features (get, all, and such) pays off. But I might be missing something.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

dependencies Pull requests that update a dependency file. needs-ci PRs that need a full CI run. sqlite Issues and PRs related to the SQLite subsystem.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@geeksilva97 @nodejs-github-bot @Renegade334