On Sat, 22 Nov 2014 23:25:16 -0500 (EST) Joseph Fernandes <josfe...@redhat.com> 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
