Can SQLite be used with GlusterFS?

Question

I want to build a distributed storage based on GlusterFS with Automatic File Replication (AFR) to store users files in a fault-tolerant way.

But I also want to access an SQLite3 database stored on the GlusterFS volume (and so replicated on several servers) from multiples clients. Is it possible? Does the concurency between the several clients will be well handled and will not lead to corruption?

Or is there a better alternative to GlusterFS to distribute the SQLite3 database?

Answer 1

I wrote a sqlite_shared_fs_check.sh script to simulate many reads and writes to the sqlite3 database. It is supposed to run in the same GlusterFS directory on several client machines.

I tested following configurations:

• GlusterFS 3.2 and sqlite3 3.5.9 (ubuntu 8.10). The DB did not become corrupted.
• GlusterFS 3.2 and sqlite3 3.6.22-1 (ubuntu 10.04). TBD.
• Ext3 and sqlite3 3.5.9 (ubuntu 8.10): The test was run from two terminal windows at once. The test finished without any problems whatsoever.

Results of the last test (ext3+sqlite3) put the blame on POSIX locking non-conformance onto GlusterFS 3.2.

Here is the script I used to do the testing:

#!/bin/bash

function ErrorExit()
{
  echo Error: $@
  exit 1
}

# Microseconds
timeout=5000

if [ ! -f test.sqlite3 ];
then
  touch test.sqlite3
  echo 'create table test1 (id integer primary key autoincrement,datetime text,hostname text);' | sqlite3 test.sqlite3 || ErrorExit "Create"
fi

if [ ! -f /tmp/insert.sql ];
then
  echo .timeout $timeout > /tmp/insert.sql
  echo "insert into test1 values (NULL,datetime('now','localtime'),'$HOSTNAME');" >> /tmp/insert.sql
fi

if [ ! -f select.sql ];
then
  echo .timeout $timeout > select.sql
  echo "select * from test1 order by id desc limit 1;" >> select.sql
fi

if [ ! -f count.sql ];
then
  echo .timeout $timeout > count.sql
  echo "select count(*) from test1;" >> count.sql
fi

i=1
while [ $i -le 1000 ];
do
  lockfile-create --retry 20 test.sqlite3 || echo -n "?"
  sqlite3 test.sqlite3 < /tmp/insert.sql
  lockfile-remove test.sqlite3

  # Sleep a bit to allow other users
  sleep 0.5
  lockfile-create --retry 20 test.sqlite3 || echo -n "?"
  sqlite3 test.sqlite3 < select.sql >/dev/null || ErrorExit select [$i]
  sqlite3 test.sqlite3 < count.sql >/dev/null || ErrorExit count [$i]
  lockfile-remove test.sqlite3
  let i++
  echo -n "."
done

Note that I had to use the lockfile-create utility to acquire locking on the db, as sqlite's internal locking is not reliable enough.

Answer 2

GlusterFS supports full POSIX file / record level locking even in replication mode. It should work fine.

Answer 3

I think the locking might be the hard part of it. Imagine the write process has to lock the sqlite3 database(file) when it is writing into it. The question is what level of concurrency do you need? I think you are going to face possible performance issues with a write bound application.

Answer 4

You'll have locking issues where (in effect) one client at a time can be writing to the file.