[Gluster-users] SQLite3 on 3 node cluster FS?

[Paul Anderson]

Hi,

tl;dr summary of below: flock() works, but what does it take to make
sync()/fsync() work in a 3 node GFS cluster?

I am under the impression that POSIX flock, POSIX
fcntl(F_SETLK/F_GETLK,...), and POSIX read/write/sync/fsync are all
supported in cluster operations, such that in theory, SQLite3 should
be able to atomically lock the file (or a subset of page), modify
pages, flush the pages to gluster, then release the lock, and thus
satisfy the ACID property that SQLite3 appears to try to accomplish on
a local filesystem.

In a test we wrote that fires off 10 simple concurrernt SQL insert,
read, update loops, we discovered that we at least need to use flock()
around the SQLite3 db connection open/update/close to protect it.

However, that is not enough - although from testing, it looks like
flock() works as advertised across gluster mounted files, sync/fsync
don't appear to, so we end up getting corruption in the SQLite3 file
(pragma integrity_check generally will show a bunch of problems after
a short test).

Is what we're trying to do achievable? We're testing using the docker
container gluster/gluster-centos as the three servers, with a php test
inside of php-cli using filesystem mounts. If we mount the gluster FS
via sapk/plugin-gluster into the php-cli containers using docker, we
seem to have better success sometimes, but I haven't figured out why,
yet.

I did see that I needed to set the server volume parameter
'performance.flush-behind off', otherwise it seems that flushes won't
block as would be needed by SQLite3.

Does anyone have any suggestions? Any words of widsom would be much appreciated.

Thanks,

Paul