Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
3  
It's been some time since you asked this now. I was wondering how you got on with SQLite on GlusterFS (or alternative). I'm looking for a good block-level FS to mirror/load-balance some low-usage Django sites that need ~100% uptime. –  Oli Jan 11 '10 at 15:45
    
I would also like to know how this turned out for you. please do share if you ended up going this route. –  jberryman Apr 9 '10 at 23:13
add comment

4 Answers

up vote 3 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks for this! Did you file a bug report or get a response from any gluster developers on this issue? –  jberryman Sep 9 '11 at 20:24
    
Noticed your edit. This latest version with explicit locking works for me with three clients/one server. In the previous version I get frequent "unable to open database file" errors, any idea why? The usual explanation seems to be improper permissions of the parent directory, but not here. –  jberryman Sep 12 '11 at 18:33
    
More to add: just ran your test that doesn't use explicit locks concurrently on a single NFS client, and locally on the NFS server. The NFS client fails on "count.sql" after ~30 runs. –  jberryman Sep 12 '11 at 18:50
add comment

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.

share|improve this answer
    
Sounds like the locking problem has been solved above –  jayunit100 May 6 '13 at 14:39
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.