Compile new branch of SQLite for PHP and Python

sqlite.md

High performance SQLite. WAL2, BEGIN CONCURRENT.

Tested, Ubuntu 22.04 +

• WAL: Writes no longer block reads.
• WAL2: Applying WAL no longer blocks writes (2 staggered WAL files).
• BEGIN CONCURRENT: Writes no longer block entire file, only page-level. Transactions defer write until COMMIT; instead of entire transaction.

Find branch and get source

• https://www.sqlite.org/src/brlist
• https://github.com/sqlite/sqlite
• Choose your compile flags: https://www.sqlite.org/compile.html (sane setup below!)
• Get newest stable or branch such as begin-concurrent-pnu-wal2

Compile SQLite

• git clone --branch begin-concurrent-pnu-wal2 --depth 1 https://github.com/sqlite/sqlite.git sqlite-src
• cd sqlite-src

# https://www.sqlite.org/compile.html
make clean
export OUTPUT_TARGET="/usr/local"
export CFLAGS="\
	-DHAVE_INT16_T=1 \
	-DHAVE_INT32_T=1 \
	-DHAVE_INT8_T=1 \
	-DHAVE_STDINT_H=1 \
	-DHAVE_UINT16_T=1 \
	-DHAVE_UINT32_T=1 \
	-DHAVE_UINT8_T=1 \
	-DHAVE_USLEEP=1 \
	-DSQLITE_DEFAULT_CACHE_SIZE=-64000 \
	-DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
	-DSQLITE_DEFAULT_MEMSTATUS=0 \
	-DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 \
	-DSQLITE_DEFAULT_SYNCHRONOUS=1 \
	-DSQLITE_ENABLE_COLUMN_METADATA \
	-DSQLITE_ENABLE_DBSTAT_VTAB \
	-DSQLITE_ENABLE_DESERIALIZE \
	-DSQLITE_ENABLE_FTS3 \
	-DSQLITE_ENABLE_FTS3_PARENTHESIS \
	-DSQLITE_ENABLE_FTS3_TOKENIZER \
	-DSQLITE_ENABLE_FTS4 \
	-DSQLITE_ENABLE_FTS5 \
	-DSQLITE_ENABLE_JSON1 \
	-DSQLITE_ENABLE_LOAD_EXTENSION \
	-DSQLITE_ENABLE_MATH_FUNCTIONS \
	-DSQLITE_ENABLE_PREUPDATE_HOOK \
	-DSQLITE_ENABLE_RTREE \
	-DSQLITE_ENABLE_SESSION \
	-DSQLITE_ENABLE_STAT4 \
	-DSQLITE_ENABLE_STMTVTAB \
	-DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION \
	-DSQLITE_ENABLE_UNLOCK_NOTIFY \
	-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT \
	-DSQLITE_HAVE_ISNAN \
	-DSQLITE_LIKE_DOESNT_MATCH_BLOBS \
	-DSQLITE_MAX_SCHEMA_RETRY=25 \
	-DSQLITE_MAX_VARIABLE_NUMBER=250000 \
	-DSQLITE_OMIT_DEPRECATED \
	-DSQLITE_OMIT_GET_TABLE \
	-DSQLITE_OMIT_LOOKASIDE \
	-DSQLITE_OMIT_TCL_VARIABLE \
	-DSQLITE_SOUNDEX \
	-DSQLITE_TEMP_STORE=2 \
	-DSQLITE_THREADSAFE=2 \
	-DSQLITE_TRACE_SIZE_LIMIT=32 \
	-DSQLITE_USE_URI \
	-O2 \
	-fPIC"
export LIBS="-lm"
./configure --disable-tcl --enable-shared --enable-tempstore=always --prefix="$OUTPUT_TARGET"
make -j32
sudo make install

Restart your PHP-FPM or Python server daemon.

Tips

• For more write throughput, use multiple database files. You can 2x-4x your writes this way.
  • Example: main.db and analytics.db
• For PHP, pdo-sqlite is great once upgraded.
• For Python, just use sqlite3 !!
  • apsw, pysqlite3 both have terrible INSERT performance compared to normal sqlite3
  • aiosqlite has bad performance unless you're performing multiple queries with gather() at the same time.
  • omnilib/aiosqlite#97
• Increasing page_size past the default will not help write performance on nvme, just saturate the drive.
  • pragma page_size=65536 up to 3x slower than default of pragma page_size=4096 on INSERT.

References

• https://vikborges.com/articles/making-php-8-use-the-latest-version-of-sqlite3-on-ubuntu-20-04-lts/
• https://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/
  • (Missing SQLITE_ENABLE_COLUMN_METADATA)