sqlite: adjust page size

[rvagg]

lotus/lib/sqlite/sqlite.go

Lines 21 to 32 in 9b86cc5

	var pragmas = []string{
	"PRAGMA synchronous = normal",
	"PRAGMA temp_store = memory",
	"PRAGMA mmap_size = 30000000000",
	"PRAGMA page_size = 32768",
	"PRAGMA auto_vacuum = NONE",
	"PRAGMA automatic_index = OFF",
	"PRAGMA journal_mode = WAL",
	"PRAGMA wal_autocheckpoint = 256", // checkpoint @ 256 pages
	"PRAGMA journal_size_limit = 0", // always reset journal and wal files
	}

page_size is set at the creation of a new db (or a vacuum when not in WAL mode, which we use) and it's fixed once that's done. For some reason we ramp it right up to 32K, but I'm not sure that's a great idea and maybe we should bring it back down to a smaller, or even default value. I'm suspecting this page was the source of some of the original values: https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ - I'm very dubious that our write patterns are going to have the right characteristics for such large pages so I think this is wrong.

https://www.sqlite.org/pragma.html#pragma_page_size

One unfortunate side effect of this being set on creation is that wal_autocheckpoint is a multiple of whatever value it is for the db. So we can't be consistent in changing that value and know that it'll work the same across different nodes: anyone starting a new node after the point at which we change page size will have a different checkpoint size, but users with older nodes will have a different checkpoint size. So we've opted to leave these settings alone (mostly).

Introduction of ChainIndexer is a great time to address this setting because we're removing old dbs and creating an entirely new one, for everyone. wal_autocheckpoint is currently targeting 8MiB, so any change to page size will need to change this multipler to target appropriately. The default value is 1000, which combined with a default page_size value of 4096 gives a checkpoint of ~4M, which is also fine. So, maybe we should just lock in both of these defaults in our pragmas and be done with it.

Ideally we'd do some perf testing to figure out the best value for page size, but I'm not sure the effort of doing so is worth eeking out a tiny bit more performance. Googling suggests there are perf gains to be had but they're subtle.

[rvagg]

going to call this complete with #12421