[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Sqlite3 transactions without Durability
From:       Simon Slavin <slavins () bigfraud ! org>
Date:       2011-09-25 13:34:12
Message-ID: 62DA615C-4B24-4504-83BD-2BF143C70AED () bigfraud ! org
[Download RAW message or body]


On 25 Sep 2011, at 12:12pm, Artyom Beilis wrote:

> On of the things that is supported by "big" databases is non-durable
> transactions that make the database updates much faster.
> 
> For example PostgreSQL has an option synchonous_commits 
> 
> and MySQLhas an option innodb_flush_log_at_trx_commit

I can strip down my old car by taking out the seats and carpets.  It'll still never \
reach the top speed of a more expensive newer more efficient car.  Compare the speed \
of these system using those tricks with SQLite not using any tricks.

> Basically I want to do many inserts and reads
> using the database and I want to keep ACI of ACID
> part. I mean I don't care that some of the transactions
> are get lost, but I do care that the database state
> would remain consistent in case of catastrophic fault.

You could use an in-memory database.  A blank database is perfectly consistent.

> The simplest solution is to start a transaction and commit
> it once in a while it would make it **very fast**
> because fsync would be called only once in a while.


SQLite supports transactions.  And using them properly does speed things up.

> The problem is that it works for one connection only
> and I can't do the same job from multiple connections
> and/or from multiple processes.

Have all your processes use the same database connection.  But actually this won't \
help because ...

> Is there any way to solve this problem, any custom VFS
> or module?

SQLite does extremely little work internally.  Unlike most DBMS systems which do \
background processing or have client/server architectures, processing and network \
traffic are almost never a bottleneck.  The bottleneck turns out to be access to the \
hard disk your database file is on.  And since any operation on a database will need \
access to the hard disk the file is kept on, there's nothing you can do about it.

There are, however, a lot of things you can do to speed up SQLite.  Which one you do \
depends on what part of the current operation is causing your application to be too \
slow.  So write your application first, using transactions properly, and test it.  If \
it does turn out to be too slow then you can do things like use an in-memory database \
or DROP and reCREATE INDEXES.

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


[prev in list] [next in list] [prev in thread] [next in thread]