Optimizing SQLite Table Scan Performance on High-Speed NVMe Storage

Diagnosing Suboptimal Sequential Read Throughput in SQLite Table Scans

Issue Overview: Suboptimal I/O Utilization During Full Table Scans

A user encountered a scenario where sequential table scans in a 132GB SQLite database achieved only 10% of the NVMe drive’s maximum measured throughput (370 MB/s vs. 3,722 MB/s). The database contained a single table with a primary key and BLOB columns, with queries performing trivial operations like SELECT SUM(1) FROM table. Key characteristics of the environment included:

• Hardware Configuration: AMD Ryzen 7 3800X CPU, X570 chipset motherboard, 64GB RAM, Sabrent Rocket NVMe SSD
• Software Stack: Windows 10 Pro, SQLite3 command-line tool, and Python 3 API
• Query Characteristics: Full table scans (--SCAN query plan) with minimal computational overhead
• Observed Behavior: Consistent 350–400 MB/s read speeds across multiple access methods (CLI, Python), with CPU and RAM utilization not being bottlenecks

The schema’s structural details played a critical role:

CREATE TABLE subdoc (
  aaa INTEGER PRIMARY KEY,
  bbb INTEGER,
  ccc BLOB,
  ddd BLOB,
  eee BLOB,
  FOREIGN KEY(bbb) REFERENCES doc(id)
);

Only aaa (integer primary key) and ccc (1.2KB BLOB per row) contained data. The default 4KB page size allowed only ~3 rows per page due to BLOB overhead, forcing SQLite to read many small pages. This directly influenced I/O efficiency, as NVMe drives excel at parallelized access but suffer when handling numerous small, non-sequential reads.

The problem was resolved by increasing the page size to 64KB, which improved throughput to 900 MB/s. However, this solution emerged only after systematic analysis of SQLite’s storage engine behavior, operating system I/O handling, and hardware capabilities.

Possible Causes: Page Size, I/O Patterns, and Storage Engine Behavior

1. Inefficient Page Size for BLOB-Heavy Workloads

SQLite’s default 4KB page size is suboptimal for tables with wide rows or BLOBs. Each page stores row data contiguously, with overflow pages used for large BLOBs. In this case, 1.2KB BLOBs combined with row metadata left room for only ~3 rows per 4KB page. This forced SQLite to read 4KB blocks frequently, resulting in:

• Excessive I/O Operations: Reading 132GB of data at 4KB/page requires ~33 million I/O operations.
• Underutilized NVMe Parallelism: NVMe drives handle deep queues (e.g., 32 queues in CrystalDiskMark tests) efficiently, but single-threaded table scans issue sequential reads that mimic 1-queue workloads.
• CPU Overhead: Parsing page headers and row structures for small pages increased CPU cycles per MB processed.

2. Windows I/O Subsystem Limitations

Windows imposes constraints on scatter-gather I/O and command queueing for file-based operations:

• Lack of Direct I/O Bypass: Unlike benchmark tools that bypass filesystem layers, SQLite relies on the OS cache and synchronous writes. The FILE_FLAG_NO_BUFFERING flag is rarely used in practice.
• Single-Threaded Access: SQLite’s thread-safe but single-threaded design prevents parallelized page reads, capping throughput at what one CPU core can handle.
• Driver and Filesystem Overhead: NTFS journaling and driver stack latency add microseconds per I/O, noticeable at high throughputs.

3. Database Fragmentation and Access Patterns

While VACUUM defragments databases, its impact is limited when:

• Primary Key Order Mismatches Storage Order: Auto-incrementing integer primary keys typically align with physical storage order, but deletions/updates can fragment pages.
• BLOB Storage Overhead: Large BLOBs force overflow pages, scattering data across the file.
• Index-Only Scans Not Applicable: Queries requiring full row access (e.g., SELECT *) cannot benefit from covering indexes.

4. Suboptimal Pragma Settings

Default settings for cache_size, mmap_size, and journal_mode may not align with high-throughput workloads:

• Cache Thrashing: Small caches (default: 2MB) fail to retain frequently accessed pages (e.g., non-leaf B-tree pages).
• Memory-Mapped I/O Disabled: Without mmap_size, SQLite uses standard file I/O, missing opportunities for zero-copy reads.
• Write-Ahead Logging Overhead: WAL mode improves concurrency but adds fsync latency during checkpoints.

Troubleshooting Steps: Page Size Optimization, I/O Tuning, and Workload Analysis

1. Adjust Page Size and Vacuum the Database

Step 1: Increase Page Size to 64KB
Larger pages reduce the number of I/O operations and better utilize NVMe bandwidth:

PRAGMA page_size = 65536; -- Before creating the database  
VACUUM; -- Rebuilds the database with the new page size  

• Impact: Storing ~49 rows per 64KB page (vs. 3 rows/4KB) reduces I/O operations by 16x.
• Limitations: Page sizes >64KB may not improve performance further due to diminishing returns and memory alignment issues.

Step 2: Monitor I/O Patterns with sqlite3_analyzer
Use the sqlite3_analyzer tool to profile page utilization and overflow counts:

sqlite3_analyzer subdoc.db > analysis.txt  

• Key Metrics: Page density, Overflow pages, and Average payload per row indicate storage efficiency.

2. Tune Operating System and SQLite Pragmas

Step 3: Enable Memory-Mapped I/O
Configure SQLite to map the database file into process memory, reducing syscall overhead:

PRAGMA mmap_size = 30000000000; -- 30GB for large databases  

• Considerations: Ensure sufficient virtual address space (64-bit builds required) and avoid contention with other memory consumers.

Step 4: Increase Cache Size for B-Tree Pages
Expand the page cache to retain non-leaf B-tree pages, reducing disk accesses:

PRAGMA cache_size = -100000; -- 100MB cache (negative values denote KiB)  

Step 5: Disable Journaling for Read-Only Workloads
If writes are infrequent, use DELETE journaling or read-only mode:

PRAGMA journal_mode = DELETE;  
-- Or open the database in immutable mode:  
sqlite3 "file:subdoc.db?immutable=1"  

3. Validate Hardware and Filesystem Configuration

Step 6: Verify NVMe Driver and Firmware

• Driver Settings: Ensure the NVMe driver uses "High Performance" mode in Windows Device Manager.
• Firmware Updates: Check manufacturer tools (e.g., Sabrent Rocket Control Panel) for firmware upgrades.

Step 7: Benchmark Raw Filesystem Throughput
Use dd (Linux) or diskspd (Windows) to measure raw file read speeds:

diskspd.exe -b64K -d60 -o32 -t8 -Sh -L -W testfile.dat  

• Parameters: 64KB blocks, 32 outstanding I/O, 8 threads, no filesystem caching.

Step 8: Compare Against Alternative Storage Engines
Test RocksDB or LMDB with equivalent workloads to isolate SQLite-specific bottlenecks:

import rocksdb  
options = rocksdb.Options()  
options.create_if_missing = True  
db = rocksdb.DB("test.rocksdb", options)  
# Benchmark sequential scans via iterators  

4. Advanced SQLite Engine Modifications

Step 9: Custom VFS for Asynchronous I/O
Implement a custom VFS leveraging Windows Overlapped I/O:

// Example: Queue I/O requests using ReadFileEx/WriteFileEx  
static int asyncIoWrite(sqlite3_file* file, const void* buf, int amt, sqlite3_int64 offset) {  
  OVERLAPPED ov = { .Offset = (DWORD)offset, .OffsetHigh = (DWORD)(offset >> 32) };  
  BOOL rc = WriteFile(file->h, buf, amt, NULL, &ov);  
  return rc ? SQLITE_OK : SQLITE_IOERR;  
}  

Step 10: Profile CPU Utilization with VTune or perf
Identify CPU bottlenecks in SQLite’s bytecode interpreter or OS layers:

perf record -g ./sqlite3 subdoc.db "SELECT SUM(1) FROM subdoc;"  

• Key Areas: Look for excessive time spent in sqlite3VdbeExec, os_win.c functions, or kernel I/O routines.

By systematically addressing page layout, pragma settings, and OS I/O handling, users can achieve significant throughput improvements—from 370 MB/s to over 900 MB/s in this case. The interplay between SQLite’s storage engine design and modern NVMe capabilities requires careful tuning to unlock full performance potential.