[NTLUG:Discuss] OT: SQLite

[Mike Owens]

Neither the database tables nor indexes are loaded in memory beyond what is
used in the current transaction. Even this can be regulated to some extent
by the cache_size pragma (pages exceeding this limit are flushed to the
journal file). Once the transaction completes, all dirty pages are flushed
and your total memory consumption goes back to practically nil. By my
measurements earlier this year, SQLite can manage about 500 TPS -- where the
measurement here is sheerly lock overhead, not total transaction size, and
it is constant with the number of connections to a given database with
respect to writers. Readers scale roughly proportionally. Basically, as the
size of the (write) transaction approaches zero, the maximum TPS is about
500. Again, varies with hardware, but this is a good general estimate. For
most desktop applications and even small to medium websites, this is more
than acceptable.

-- Mike

On Tue, Sep 14, 2010 at 3:32 AM, Ralph Green <sfreader at sbcglobal.net> wrote:

> On Sun, 2010-09-12 at 15:00 -0500, Fred James wrote:
> > I am seeking comment ... why would I not want to use SQLite?  Of course,
> Howdy,
>  SQLite is great for lots of things.  I have used it in several small
> applications and it did everything I needed.
>
>  As Stuart says, one real problem is that writes need to be single
> threaded in some way.  And, I think these writes create locks that slow
> down reads.  If all you are doing is adding new records with the writes,
> you are fine, though.
>
>  I'll give you a couple of reasons I think it could be a problem.  If
> anyone can comment and tell me I am wrong on these, then great.  SQLite
> seems to be memory bound.  I have some tables I have created recently
> that were hundreds of megabytes in size and soon will be gigabytes.  I
> don't want that all in memory.  I am also doing data mining on the
> tables and I am building some fairly complex select statements.  I had
> no problems with Postgresql, but I think SQLite is much more limited.
>
>  Even with those limits, SQLite is fine for most single user apps.
> SQLite is built into lots of things.  Being public domain is as open as
> you can get.  I still like GPL better, but public domain is certainly
> safe to use.  Recent Python releases have it built in, so you don't have
> to worry about whether a user has the database software loaded.
> Have a good day,
> Ralph
>
>
>
> _______________________________________________
> http://www.ntlug.org/mailman/listinfo/discuss
>