Changing page size of sqlite databases, would it pose any problem?

According to the ZFS GitHub, in order to optimize sqlite is being advices to set recordsize and page size of the sqlite databases to 64K.

Would it pose any foreseeable problem, if I changed page size of the databases to 64K?

See: Workload Tuning — OpenZFS documentation

ok so internally change the page size to 64k? Do you have an underlying file system with matching cluster/stripe size, or just a penchant for write amplification? ok so maybe on SSD, not too bad…
Most databases suggest 64k, some require it. And if default database is placed with the node does it have also have 64k cluster?.. not good for node efficiency.
But, if I were you I’d try that, cuz you can - it’s not totally unreasonable, since you can make a dataset to match. Not sure it would not just bloat the db though.
2 cents

I wouldn’t recommend to change it. We have had some weird issues related to a changed page size in the databases.

I actually think this is different, at first nowhere is stated that those databases had another page size to start with. Second, the TS is talking about increasing page size, like it was lower before. But I’m quite sure it wasn’t, unless he had some outdated sqlite or did this on purpose: the default page size has been 4096 since 2016. Furthermore, it looks like he fully rebuilt the database. Which in my experience solve most unspecified problems with sqlite and Storj.

Hitherto: I think it had nothing to do with the problem at all, and the rebuilding solved his problem.

Page size itself shouldn’t be an incompatibility issue. Source: Change in Default Page Size in SQLite Version 3.12.0

But don’t know whether there are other unknown/unwanted side effects.

In ZFS you can chose the recordsize. The benefits of matching those exactly the same. 16-64K is the range for most databases, with the side benefit that those databases are quite good compressible (up to 1/3th of original size) and write/read-sizes are assigned with the filesystem.

1 Like

Then you likely may try to do that.

A little bit of history…
The first post about the stopping working databases was before 2019, seems when we used a RocketChat, so I’m unable to find it now, this one post was a closest to the one of the side effects discovered with the databases.
The short version of the issue: the node was created on the different filesystem with a different platform (32 bit). The page size was autoselected as 1024 (or 2048 I do not remember), when the node was migrated to a 64 bit platform with a bigger storage, the node started to crash (we used the database to store important data about pieces and if the database is lost, the node was lost too, so re-creating the database was not a solution). And the solution was to rebuild the database to use 4096 page size at the end.

Learn something every day around here, thanks you two: JWvdV & Alexey.

1 Like

The results:

  1. Any page size 2^12-2^16 does function well, in the sense the dashboard is working and so on.
  2. Compression varies from an average of -40% with recordsize=16K (up to -55%) to -50% with recordsize=64K (up to -75%).
  3. More complex functions like VACUUM take ages the bigger the page size becomes. I don’t know why. But it’s actually only a problem with the piece-expiration-database. PRAGMA integrity_check even failed now and then with 64K page_size.

In the end, since 16K already shows a fine compressratio of 1.5-2.0 in ZFS I decided to stick with that; ignoring the kind of more official recommendations.

I actually scripted it this way, for others who want to try for themselves:

# Database check before starting up the storagenode
# Check integrity of databases
iBlockSize="$( stat -fc %s "$sDBFolder" )"
echo "Block size [${sDBFolder}]: $iBlockSize"
for sDB in "$sDBFolder/"*".db"; do
	[[ -f "$sDB" ]] || {
		# copy old databases from disk/back-up location
		cp "$sNodeMnt/storage/"*".db"* "$sDBFolder/"
		break
	}
	
	if [[ "$( sqlite3 "$sDB" "PRAGMA page_size" )" == "$iBlockSize" ]] && [[ "$( sqlite3 "$sDB" 'PRAGMA integrity_check;' )" == "ok" ]]; then
		# Vacuum in case of >10% unused free paged.
		if (( $( sqlite3 "$sDB" 'PRAGMA page_count; PRAGMA freelist_count;' | awk 'BEGIN{i=0}{l[i++]=$0}END{printf "%1d", ( l[1]/l[0] > 0.1) }' ) )); then
			echo "Vacuuming: ${sDB##*/}"
			sqlite3 "$sDB" "PRAGMA page_size=${iBlockSize}; VACUUM;"
		else
			echo "OK-as-is: ${sDB##*/}"
		fi
		
		# remove db incl. -wal/-shm from back-up location, then save current db to back-up location
		rm "$sNodeMnt/storage/${sDB##*/}"*
		cp "$sDB" "$sNodeMnt/storage/${sDB##*/}"
	else
		echo "Recovering: ${sDB##*/}"
		echo -e ".mode insert\n.dump" | sqlite3 "$sDB" | grep -v "^\(BEGIN TRANSACTION\|ROLLBACK\|COMMIT\)" >> "${sDB%.db}.sql"
		rm "$sDB"
		sqlite3 "$sDB" "PRAGMA page_size=${iBlockSize}; VACUUM;"
		sqlite3 "$sDB" < "${sDB%.db}.sql"
		rm "${sDB%.db}.sql"
	fi
done
2 Likes