Hello,
"Clay Dowling" <[EMAIL PROTECTED]>
09/11/2004 02:39 AM
Please respond to sqlite-users
To: [EMAIL PROTECTED]
cc:
Subject: Re: [sqlite] Client/Server Environment
> Richard Boehme said:
> > Hi there. How well does SQLite do in a client/server environment where
> > the SQLite database is handling a batch of 100 inserts and the same
> > number of queries a every 0.5 seconds? The inserts would be batched
into
> > a transaction. The queries would most likely not.
> I'm not trying to run down SQLite here, but don't you think that this
kind
> of load justifies a pretty heavy duty database? SQLite is a disk based
> database, not a client server database. For that kind of throughput,
the
> traditional choice has been a large scale client server database, and
> there's been a pretty good reason why that's been so.
If you are talking about scaling the application to a large number of
machines to try and balance load, the big end databases may suit you. If
you are talking about putting a big iron database to work on a single
server and single disk array to get higher performance than sqlite...
forget it. My figures are a little out of date now, but I looked for a
high performance database for my own application a couple of years back
and found that sqlite blew the popular alternatives[1] out of the water in
data throughput. Since that time SQLite has become faster for many
functions while other databases continue to get fatter.
As with any performance-sensitive application you must profile the
alternatives you are considering on your own target hardware with your own
projected target load to get a reliable indication as to which will
perform. I would certainly not discount sqlite based on a mailing list
post. Try it out and see. I was pleasantly surprised, and suspect you will
be also. Please report back, also. Your experience may be a useful data
point for someone else in your position in future.
> 100 separate
> clients trying to access the same file on disk is bound to cause a fiew
> problems. The capacity of the database driver is probably the least of
> your worries.
100 readers does not equal 100 separate clients trying to access the same
file on disk. It may be equal to 100 clients trying to access the same
piece of cache memory. That's certainly something you can do more than 100
times in half a second. The quality of your operating system will have
some impact here, but I believe that even Microsoft can manage to do this
properly these days. The main difference between the way sqlite handles
performance and the way other databases do is that SQLite relies on the
well-worn file operations of your operating system more than other
databases. They try to implement these operations themselves. In bygone
days this was a good thing because OS file handling was often poor. These
days, It's a good thing. It keeps you lean and allows the operating system
to do the heavy lifting in making you perform.
If sqlite is going to be a problem under the proposed loading, it will
most likely be related to locking and possible delays introduced by
clients having to sleep to get their locks in. As presented, though, I
suspect that won't be a huge problem. It would largely depend on the size
and time-cost of the queries being performed.
Benjamin
[1] Mysql, postgres, sapdb, oracle.
