Is PRAGMA mmap_size supported?

[lbe]

While working through some performance testing, I realized that I was not able to get PRAMA mmap_size assigned either from a URI DSN or from a direct query. Does go-sqlite3 support this pragma?

I created the following test program to show what I am doing and the results that I am seeing.

package main

import (
	"database/sql"
	"fmt"
	"log"
	"os"

	_ "github.com/ncruces/go-sqlite3/driver"
	_ "github.com/ncruces/go-sqlite3/embed"
)

func main() {
	// Test 1: mmap_size in URI DSN
	testDSNPragma()

	// Test 2: mmap_size set via SQL PRAGMA
	testSQLPragma()
}

func testDSNPragma() {
	dbPath := "test_dsn.db"
	os.Remove(dbPath)
	defer os.Remove(dbPath)

	dsn := fmt.Sprintf("file:%s?mmap_size=268435456", dbPath)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		log.Fatalf("Failed to open DB with DSN: %v", err)
	}
	defer db.Close()

	var mmapSize int64
	err = db.QueryRow("PRAGMA mmap_size").Scan(&mmapSize)
	if err != nil {
		log.Fatalf("Failed to query mmap_size: %v", err)
	}

	fmt.Printf("DSN Pragma Test - mmap_size: %d\n", mmapSize)
	switch mmapSize {
	case 268435456:
		fmt.Println("  Result: SUCCESS (value matches DSN setting)")
	case 0:
		fmt.Println("  Result: FAILED (mmap_size not set via DSN)")
	default:
		fmt.Println("  Result: PARTIAL (value set but different)")
	}
}

func testSQLPragma() {
	dbPath := "test_sql.db"
	os.Remove(dbPath)
	defer os.Remove(dbPath)

	dsn := fmt.Sprintf("file:%s", dbPath)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		log.Fatalf("Failed to open DB: %v", err)
	}
	defer db.Close()

	_, err = db.Exec("PRAGMA mmap_size = 268435456")
	if err != nil {
		log.Fatalf("Failed to set mmap_size via PRAGMA: %v", err)
	}

	var mmapSize int64
	err = db.QueryRow("PRAGMA mmap_size").Scan(&mmapSize)
	if err != nil {
		log.Fatalf("Failed to query mmap_size: %v", err)
	}

	fmt.Printf("\nSQL Pragma Test - mmap_size: %d\n", mmapSize)
	switch mmapSize {
	case 268435456:
		fmt.Println("  Result: SUCCESS (value matches PRAGMA setting)")
	case 0:
		fmt.Println("  Result: FAILED (mmap_size not set via PRAGMA)")
	default:
		fmt.Println("  Result: PARTIAL (value set but different)")
	}
}

The resulting output is:

sqlite-dbconnpool on 🍣 main [📝 🛤️ ] via 🐹 v1.26.0 via  14GiB/24GiB
[11:57:58] ➜  go run ./cmd/testNcrucesMmapSize/.
DSN Pragma Test - mmap_size: 0
  Result: FAILED (mmap_size not set via DSN)

SQL Pragma Test - mmap_size: 0
  Result: FAILED (mmap_size not set via PRAGMA)

I have searched the documentation, discussions, wiki and issues and not found anything. I noticed a couple of test cases that contain mmap_size but am too ignorant regarding wazero and WASM to know how to interpret them. I'm hopeful this means yes and I'm just doing something wrong.

Thanks in advance,

lbe

[ncruces]

No, SQLITE_MAX_MMAP_SIZE is set to zero.

Given the way Wasm sandbox works, and how much memory is available to it, it's hard to see a way to support it that actually improves performance.

[lbe]

Thank you for the quick confirmation

[ncruces]

@lbe you may want to give #417 a spin.

[lbe]

I will give it a spin in my sqlite3 read benchmark sometime this weekend.

[lbe]

The following are run on Go 1.27 using github.com/lbe/sqlite-read-benchmark for ncruces/go-sqlite3

I had AI create a script to compile the benchmarks with ncruces/go-sqlite: 0.35.3, PR417 no mmap, PR417 with 64MB mmap. It then ran each binary 3 times for warmup and then 5 times for data. Summary and detail data below.

** On Linux Dev Server (lxc on Proxmox)**

Values are median reads/sec

Query	0.35.3	PR417	PR417 mmap 64MB
SQL - Raw	38,081	39,382	38,684
SQL - Prepared	51,814	51,661	52,999

Modest improvement for PR417 with 64MB mmap. PR417 without the mmap had slightly lower performance on prepared statements.

Detailed results

• Database: benchmark.db
• Reads per run: 250,000
• Goroutines: 8
• Warmup runs per binary: 3
• Measured runs per binary: 5

0.35.3

Raw

• Runs: 39,056, 38,081, 36,100, 37,866, 39,909
• Median: 38,081
• Mean: 38,202
• Stdevp: 1279.8

Prepared

• Runs: 51,070, 50,265, 51,814, 53,662, 52,499
• Median: 51,814
• Mean: 51,862
• Stdevp: 1168.4

PR417

Raw

• Runs: 38,948, 39,382, 39,006, 40,620, 39,785
• Median: 39,382
• Mean: 39,548
• Stdevp: 614.3

Prepared

• Runs: 50,591, 52,146, 51,661, 53,159, 48,728
• Median: 51,661
• Mean: 51,257
• Stdevp: 1510.7

PR417 mmap 64MB

Raw

• Runs: 38,240, 39,957, 38,043, 38,684, 39,877
• Median: 38,684
• Mean: 38,960
• Stdevp: 808.7

Prepared

• Runs: 52,193, 52,148, 53,525, 52,999, 53,043
• Median: 52,999
• Mean: 52,782
• Stdevp: 532.2

** Macbook M5 **

Values are median reads/sec

Query	0.35.3	PR417	PR417 mmap 64MB
SQL - Raw	309,685	312,689	324,621
SQL - Prepared	367,418	353,632	311,302

Modest improvement on Raw queries; however, performance for prepared regressed. The qualitative behaviors for MacOS on ARM is different than the previous Linux on Intel.

Detailed results

• Database: benchmark.db
• Reads per run: 250,000
• Goroutines: 8
• Warmup runs per binary: 3
• Measured runs per binary: 5

0.35.3

Raw

• Runs: 298,013, 309,685, 313,854, 307,288, 317,536
• Median: 309,685
• Mean: 309,275
• Stdevp: 6635.7

Prepared

• Runs: 367,426, 342,750, 367,418, 362,783, 370,654
• Median: 367,418
• Mean: 362,206
• Stdevp: 10046.4

PR417

Raw

• Runs: 328,602, 312,689, 311,989, 315,235, 310,796
• Median: 312,689
• Mean: 315,862
• Stdevp: 6533.5

Prepared

• Runs: 342,731, 352,864, 361,153, 353,632, 357,212
• Median: 353,632
• Mean: 353,518
• Stdevp: 6143.9

PR417 mmap 64MB

Raw

• Runs: 324,741, 312,772, 324,621, 328,199, 322,734
• Median: 324,621
• Mean: 322,613
• Stdevp: 5227.3

Prepared

• Runs: 311,302, 314,186, 328,530, 310,484, 308,529
• Median: 311,302
• Mean: 314,606
• Stdevp: 7195.7

[ncruces]

That's disappointing. This is what I get with speedtest1 on Linux:

               │    base     │            nommap            │               mmap                │
               │   sec/op    │   sec/op     vs base         │   sec/op     vs base              │
_speedtest1-12   137.9m ± 2%   137.6m ± 3%  ~ (p=0.863 n=9)   129.3m ± 1%  -6.23% (p=0.000 n=9)

[ncruces]

PRAGMA mmap_size is supported as of v0.35.4, and tested through mptest.

Note that if you set it larger than what each SQLite connection gets in terms of memory (currently, 256MiB by default), the setting is silently ignored. So set it lower (e.g. 64 MiB).