macOS SQLite performance regression (2.5x slower) #849
Description
Hi,
we are in the process of switching from our own compiled macOS Python interpreter to uv-managed Python and we noticed a 2.5x slowdown in SQLite-related code.
With the help from Claude I created a standalone benchmark to compare the different SQLite/Python versions and noticed a few interesting points.
Our Python
======================================================================
RESULTS
======================================================================
CREATE TABLE time: 0.080s ( 7.6%)
INSERT time: 0.657s ( 62.7%)
CREATE TRIGGER time: 0.310s ( 29.5%)
----------------------------------------------------------------------
TOTAL TIME: 1.048s
======================================================================
python-build-standalone
======================================================================
RESULTS
======================================================================
CREATE TABLE time: 0.272s ( 10.8%)
INSERT time: 1.258s ( 49.9%)
CREATE TRIGGER time: 0.989s ( 39.2%)
----------------------------------------------------------------------
TOTAL TIME: 2.521s
======================================================================
Key Differences
- Our interpreter is dynamically linked to the
macOSSQLitelibrary, whilepython-build-standalonehasSQLitestatically compiled into the interpreter.
Our version (fast):
SQLite version: 3.43.2
SQLite module: Built-in (bundled with Python)
Location: /Volumes/python_3.13.1/Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/sqlite3/__init__.py
System SQLite library: /usr/lib/libsqlite3.dylib
C extension: /Volumes/python_3.13.1/Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/lib-dynload/_sqlite3.cpython-313-darwin.so
Linked to: /Volumes/python_3.13.1/Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/lib-dynload/_sqlite3.cpython-313-darwin.so (architecture x86_64):
Linked to: /usr/lib/libsqlite3.dylib (compatibility version 9.0.0, current version 346.2.0)
Linked to: /Volumes/python_3.13.1/Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/lib-dynload/_sqlite3.cpython-313-darwin.so (architecture arm64):
Linked to: /usr/lib/libsqlite3.dylib (compatibility version 9.0.0, current version 346.2.0)
Python version: 3.13.1
python-build-standalone (slow):
SQLite version: 3.50.4
SQLite module: Built-in (bundled with Python)
Location: /Users/christian/.local/share/uv/python/cpython-3.13.8-macos-aarch64-none/lib/python3.13/sqlite3/__init__.py
System SQLite library: /usr/lib/libsqlite3.dylib
Python version: 3.13.8
- The
macOSsystemSQLiteuses different compiler flags, for example:
61. MUTEX_UNFAIR ???
67. THREADSAFE=2 (multi-thread mode, less locking)
vs
42. MUTEX_PTHREADS (generic POSIX mutexes, much slower on macOS)
45. THREADSAFE=1 (serialized mode, more locking overhead)
Claude said that THREADSAFE has a major performance impact, but I don't know if we can trust his analysis. Also it seems that there are some proprietary optimizations (MUTEX_UNFAIR) on top.
I'm not sure what the right solution should be:
- Use macOS-optimized compile flags (THREADSAFE=2, DEFAULT_LOOKASIDE=1200,102) when building for macOS?
- Dynamically link to the macOS SQLite library instead of static compilation?
- Accept the performance hit as a trade-off for simplicity?
As a happy ruff, ty, uv user I want to say thanks for all the great work!
Metadata
Metadata
Assignees
Labels
No labels