Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

[James K. Lowden]

On Sat, 22 Nov 2014 23:25:16 -0500 (EST)
Joseph Fernandes  wrote:

> 2) Using the changelog to feed the db has another issue i.e freshness
> of data in the DB w.r.t the IO. Few of our data maintainer scanners
> would require the freshness of the feed to be close to real. [...]
> Your thoughts on this. 

If your in-memory LRU structure suffices to describe all "hot" files,
you're in good shape.  Rather than dumping periodically, I would
consider placing it in shared memory and write a virtual table
function for it in SQLite, such that it can be queried directly as
needed.  

To me based on your description your choice isn't how best to use
SQLite in line with I/O, but how best to capture the data such that
they can be aggregated with SQLite at time of update.  That choice is
one of two: 1) capture each I/O event in a sequential file, always
appending, or 2) maintain per-file counts in a hash or map.  Which is
better depends on how much you're willing to pay for each I/O.  By
paying the lookup cost of #2 each time, the total space is smaller and
the maintenance-time computation less.  

> 3) Now that we would use Sqlite3(with WAL) to be direcly feed by the
> IO path(in the absence of changelog) we are looking to get the best
> performance from it. 

Metadata updates to Posix filesystems are seen as so costly that 
fsync(2) on the datafile descriptor doesn't update them.  A separate
sync on the directory is required.  Compared to an in-memory update
(of metadata, in kernel space) and a single fsync call, the price of a
SQLite transaction is enormous, at a guess an order of magnitude more.
Bear in mind that WAL buys you not efficiency but consistency, the very
thing you don't really need.  The data are written sequentially to the
log and then inserted into the table.  You can expect no better than
O(n log n) performance.  Filesystems generally would never tolerate
that, but for your application you'd be the judge. 

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

[Joseph Fernandes]

Ok I get it now. Yes we are using a single db connection object, But few 
questions, 
1) how would making sqlite3 single thread that improve the performance? Is 
there a special advantage in this mode than the multithread one?
2) Will it not block (wait or give a SQLITE_BUSY) the multiple thread of the 
app (in our case glusterfs IO threads)
as now they have to wait for the single thread to complete the task?

~Joe


- Original Message -
From: "Eduardo Morras" 
To: sqlite-users@sqlite.org
Sent: Monday, November 24, 2014 12:29:33 AM
Subject: Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in 
Glusterfs

On Sat, 22 Nov 2014 11:01:45 -0500 (EST)
Joseph Fernandes  wrote:

> Thanks Eduardo.
> 
> Answers inline JOE>>
> 

> a)If you use Sqlite in single thread, compile it without thread
> support.
> JOE>> We are running it in multithread mode, as the database will be
> JOE>> fed by multiple File IO thread. 

Yes and no, your app can be multithread, but if only one thread has the sqlite3 
pointer, you can use single thread sqlite3. This way, you have multiple working 
threads and one sqlite3 dedicated thread that recives the data to write and the 
data queries.

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Sqlite3 as a Change Time Recording Data Store in Glusterfs

[Eduardo Morras]

On Sat, 22 Nov 2014 11:01:45 -0500 (EST)
Joseph Fernandes  wrote:

> Thanks Eduardo.
> 
> Answers inline JOE>>
> 

> a)If you use Sqlite in single thread, compile it without thread
> support.
> JOE>> We are running it in multithread mode, as the database will be
> JOE>> fed by multiple File IO thread. 

Yes and no, your app can be multithread, but if only one thread has the sqlite3 
pointer, you can use single thread sqlite3. This way, you have multiple working 
threads and one sqlite3 dedicated thread that recives the data to write and the 
data queries.

---   ---
Eduardo Morras 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users