Syncthing on SQLite -- help test!

Development
1

I’ve been working on migrating the Syncthing database from leveldb to SQLite, and it’s reached the point where I would like to ask for help in testing.

Why

The database layer has been a murky corner for a long time, a source of real or potential bugs both in our code and the now unmaintained leveldb database code. Leveldb lacks things like actual transactions and our maintaining indexes ourselves has been a source of inconsistencies. We see a lot of odd crashes in the database layer.

The new database layer uses a SQLite database, with tables for files, folders, size accounting, etc. These are relational and indexed with proper keys and foreign keys. Metadata is maintained by triggers that execute in the same transaction as the change itself. There are a lot of changes all over the place to accommodate this.

How you can help

Run the new testing release on one or more of your devices. See how it works and which new problems show up. When you do see problems, be prepared to share logs, screenshots and a full copy of the database – with me, if not with the entire world.

I’ve run it in my home setup for a couple of weeks (one Mac, one amd64 Linux server and a Raspberry Pi). It hasn’t eaten any of my data as far as I can tell and I don’t think it will eat your data either, but I can’t guarantee it. I don’t use all of Syncthing’s features and haven’t tested everything. Make sure you have a backup if you run it on important data.

What you can expect

It’s best to do the migration when everything is in sync and quiescent.

On first startup Syncthing will convert the existing database to SQLite. This can take quite a while. The old database is renamed to index-v0.14.0.db.migrated. When it next connects to devices it will exchange full indexes with everyone. This can also take quite a while and things will possibly look out of sync for the duration. This will happen for every migrated device.

Some things are slower in the new system. One advantage of a simple key-value database like leveldb is that inserts and changes are incredibly fast. Now, changes entail triggers, multiple indexes to update, and transactions.

Some other things are quite a lot faster, for example listing the out of sync files for a remote device, which now uses indexes instead of a full database traversal in leveldb.

How to revert to the old system

Make sure your files are in sync, as far as possible. Shut down the SQLite version, rename index-v0.14.0.db.migrated back to index-v0.14.0.db, start again. You might get a few sync conflicts but hopefully it’s not too bad.

Let’s go!

Here are builds: Release SQLite preview 1 · calmh/syncthing · GitHub. There is also a Docker image at ghcr.io/calmh/syncthing:v2.0.0-beta.1.

The Windows and macOS binaries are not code signed unfortunately, so may be a little more annoying than usual to get to run.

“2.0.0?!” Yes, for now at least. It prevents auto upgrade back to non-SQLite versions, and if/when this gets into mainline, it prevents auto upgrade from previous versions into SQLite.

There are several reasons we might want to not do auto upgrades from previous versions. This table is one of them:

Screenshot 2025-03-13 at 10.37.22
Screenshot 2025-03-13 at 10.37.221690×1040 130 KB

Building Go with SQLite is nontrivial, as SQLite is not written in Go. We can build and include the C library, which is what happens in the green checkbox scenarios. That gives us a binary that now depends on libc, which we didn’t use to. That means it may not run on some systems that could previously run Syncthing. There is also a translation of the C SQLite source into Go, along with a significant part of the C runtime libraries, which is what you get in the yellow circle scenarios. That’s a marvelous machine that I’m amazed it works at all, but it actually does seem to. It doesn’t support all platforms though, and there is a third alternative, a WASM compilation of SQLite…

Currently 95-something percent of users are covered by the green builds. Adding windows/386, which is possible with some effort, would bring that up to 99%. For the other platforms I think it’s good that we provide a functioning binary, but ideally they would get platform native builds from their package managers. It’s not that it’s impossible to build SQLite with C for NetBSD for example, it’s just that it’s tricky to do without a NetBSD build machine.

16 Likes
3

I have hardly any experience with Go, but after my own unsuccessful attempts to statically compile Go programs that use SQLite, I stumbled across Building SQLite with CGo for (almost) every OS. However I’m not sure if this would tick off more boxes.

4

The last bastion of 32-bit Windows hosts is set to lose support with the end of Windows 10 in October.

3 Likes
5

That blog article is literally the three green ticks at the top left in my table. :slight_smile:

tenor

6

It’s easy to miss but the article author is using zig cc as the compiler for CGO. If that is useful :man_shrugging:

7

Neat. This can be paired with musl to create static binaries:

1 Like
8

Compilation aside, did any of you try it out? :wink:

1 Like
9

I’ll try it this weekend with my test setup on GNU/Linux and Android and perhaps Windows.

Note that for Linux package maintainers the question how to build this against the system sqlite3 library will arise as this is often the preference/policy. So I’ll also try that. (I suppose one has to specify some tag for that.)

10

Only with test data, no problems so far. My main setup utilizes containers on some nodes, but there are no images available?! Will syncthing/syncthing:edge be updated to 2.0 after the beta?

11

There’s a container image on ghcr.io/calmh/syncthing:v2.0.0-beta.1

The regular container images will indeed be updated if and when this reaches main etc.

1 Like
12

Obvious disadvantage of limited portability with this build:

Could not start dynamically linked executable: ./syncthing2 NixOS cannot run dynamically linked executables intended for generic linux environments out of the box.

13

:sweat_smile: I mean that applies to most binaries in the world so I think that’s mostly on you. But it is interesting, and validates that this is not a straight upgrade.

14

Almost all (statically linked) binaries I’m syncing across different systems do run everywhere (including Syncthing on NixOS). But most users just use packages so this is irrelevant.

15

Sqlite on Linux can be statically built using musl-gcc:

1 Like
16

Can we for the moment leave the compilation details to the side and focus on the functionality or lack thereof?

3 Likes
17

It’s been over 24 hours since switching to v2.0.0-beta.1, and so far no data has been eaten (yet)… :grinning:

Pair of Linux file servers at work. Syncthing has read-only access on the production system plus there are automated backups and its warm standby twin isn’t mission critical so using a beta release on both servers isn’t an issue:

  • Quad-core KVMs with 8GB RAM (host has an AMD EPYC CPU; hashing performance is 1103.89 MB/s).
  • Production is send-only while standby is receive-only. Vanilla sync without versioning, untrusted devices, etc.
  • One of the Syncthing folders is ~83GB consisting of >1.1 million files. Files are constantly being added/removed 24/7 (btrfs filesystem on SSD).
  • LevelDB to SQLite migration took ~15 minutes.

As expected, average CPU usage is up a bit, but nothing shocking:

v2.0.0-beta.1
v2.0.0-beta.11725×369 28.2 KB

(Area in the yellow box is during the conversion to SQLite along with followup rescans and while I tested a few things.)

After a few hours, a dry-run of rsync looked normal.

I tested reverting back to LevelDB on the standby server. Other than a bit of time out-of-sync, it caught up and was working fine.

Launching the beta version without an existing database, Syncthing promptly said no database was available to migrate and exited. I’m assuming that the mainline release will just start with a fresh SQLite database.

6 Likes
18

I tested it on GNU/Linux and Android and it generally works as far as I can tell. (I only tested limited features on a test setup.)

About performance: I also tested with a folder that has many files (262226 files, 75485 dirs, 6.93 GiB) and it took a notable while until these statistics showed fully up on the new test device with SQLite3 (as global statistics). Maybe the is an effect of the slower insertions. It was still acceptable in this case. Unfortunately I didn’t measure the time (but it would maybe be useful to do that and compare against the current release on the same system/hardware).

I also ran into the following errors when trying to fully sync this folder on GNU/Linux:

Listen (BEP/tcp): Accepting connection: accept tcp [::]:34861: accept4: too many open files
Error on folder projects: global: unable to open database file: too many open files

I haven’t run into these error message when trying to sync this and similarly big folders before on this setup. So it could be a regression. Maybe this is also unrelated, though. (Despite seeing these error messages Syncthing was able to continue its operation.)

After running out of disk space in my Android VM I noticed that the database now needs quite a lot of disk space. With the mentioned folder (262226 files, 75485 dirs, 6.93 GiB) I ended up with a DB file that is almost 700 MiB - just for that folder. I compared this with the size of the leveldb files of my regular setup. It is only a little bit more than 800 MiB but this setup has many more (big) folders.

Browsing the global tree doesn’t seem to work; one gets a 500 response:

[2025-03-15T01:27:31] Unable to browse "projects": Error transferring http://127.0.0.1:8384/rest/db/browse?folder=projects&levels=1 - server replied: Internal Server Error
Request URL: http://127.0.0.1:8384/rest/db/browse?folder=projects&levels=1

If you cannot reproduce this yourself I can try to get more debug output. (With default log verbosity and no additional debugging setup there were no error messages on the Syncthing log.)

On Android SQLite seems to have one advantage over leveldb: When moving the Syncthing database to the SD-card one so far wasn’t able to start Syncthing because it couldn’t create the database lock file (even when staying within the private app folder within the SD-card). In both cases (SD-card and internal storage) Android uses FUSE. So I guess the problem is that the actual underlying filesystem on the SD-card (e.g. exFAT) doesn’t support locking in the way leveldb requires it. With SQLite3 this problem seems no longer apparent. (I’m only wondering whether concurrent accesses are handles as well by SQLite then.)

I also rebased my C-bindings for these tests. This wasn’t a big deal but it would be nice if the migrateDatabase function was part of a library module (instead of main.go) so I could just reuse it. (At this point I simply copied it over from main.go into my bindings module and replaced all os.Exit(…) calls with return … which works but is not nice.)

Building against the system-provided SQLite3 library was very easy. One just had to add -tags libsqlite3 to the list of arguments when invoking the Go build system. I tested this when compiling for GNU/Linux with a shared and static build of SQLite3 (x86-64). I also tested it when building for Android with a shared build of SQLite3 (aarch64 and x86-64). It worked when compiling for these targets and seem to behave correctly at runtime.

I still have to figure out Windows because there it cannot find the symbols sqlite3_enable_load_extension and sqlite3_load_extension despite using -DSQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION=1 when compiling SQLite3. If I cannot figure this out I can supposedly still just fallback to the bundled SQLite3 build which makes not a big difference on Windows anyway. It is possible to workaround it by adding the tag sqlite_omit_load_extension. With this I was able to build for Windows using static and shared system SQLite3 (i686, x86-64, aarch64).

Note that I still provide i686 Windows builds of Syncthing Tray that also include Syncthing itself. So users could still use those builds on 32-bit Windows 10.

4 Likes
19

Thanks for the testing!

Interesting. I ran into this also in my setup, and then set the max number of database connections (i.e., open file descriptors on the database file) to 128. It would be interesting to know if you are running with a fairly low limit, or if I’m leaking database connections/FDs somewhere. Syncthing should try to increase the rlimit to the hard max on startup.

I also noticed the database is bigger, about twice the size on my systems. I attributed this to indexes and stuff. I experimented with using lz4 on stored FileInfos and block lists, but it made no relevant difference. (The block lists are mostly random data anyhow.)

I’ll look into it! Found and fixed, needed an ORDER BY to be compatible with the old setup.

2 Likes
20

That’s not what I coded :smiley: It should just not migrate anything and carry on as usual.