Here's what I'd try: 1. Write a small server that accepts connections and writes to the SQLite database using prepared statements. If you need require 500 transaction per second, it's simply not possible with rotating media. So the solution is to either turn off synchronous, which is dangerous, or use group commit, ie, batching your external transactions into larger database commits. It's possible you might lose a batch of transactions from a hardware failure, but at least your database will be usable afterwards. With synchronous=off, your database may be corrupted.
You might object to group commit because when you "ack" your external connection, you want to ensure you have done a commit before closing the connection. However, with synchronous=off, your commit is only in memory, so it's basically the same as group commit. Using group commit, it should be easy to do 500 SQL external "transactions" per second. 2. If this is still too slow, it's likely because of establishing the TCP connection. If possible, you could switch to UDP, which has a much lower overhead. You'd have to be able to live with losing or repeating data points sometimes, but maybe that would be easy to manage in your mini server above by ignoring repeated data points or using averaging to fill in missing data points. Jim On 6/11/09, Pavel Ivanov <paiva...@gmail.com> wrote: > I bet "synchronous"ness will not be your only bottleneck. Opening > connection, preparing statement and closing connection will take in > total much longer than executing statement itself. So that doing all > these operations 500 times per second will not be possible I think. If > you keep pool of connections along with already prepared statements > then your application will have chances for survival in such > contention environment. > And yes, your application will not have any chances without > "synchronous = OFF". Without it you're able to do only 10 to 20 > transactions per second. > > Pavel > > On Thu, Jun 11, 2009 at 9:53 AM, Robel Girma<rmgi...@hotmail.com> wrote: >> Thanks all for your input, very helpful. And yes, there will be 500 >> separate >> connections to the db per seconds, each updating 1 record. I've read about >> setting PRAGMA synchronous=OFF to cause SQLite to not wait on data to >> reach >> the disk surface, which will make write operations appear to be much >> faster. >> "But if you lose power in the middle of a transaction, your database file >> might go corrupt"==> I can live with this risk if it makes an huge >> improvement with the possible contention issue I'm facing. Any input with >> this setting you can provide will be greatly appreciated as always. >> Robel >> >> -----Original Message----- >> From: sqlite-users-boun...@sqlite.org >> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin >> Sent: Thursday, June 11, 2009 4:49 AM >> To: General Discussion of SQLite Database >> Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for >> the >> following app. >> >> >> On 11 Jun 2009, at 8:23am, Roger Binns wrote: >> >>> It depends very strongly on how the app is structured and in >>> particular >>> if there are a few persistent connections to the SQLite database, or >>> if >>> each request involves a separate connection to the database. If you >>> have lots of connections then there will be contention. >> >> 500 connections a second, each from a different computer. If the OP >> handles each one with a separate run of his/her application, that's >> 500 connections to the database a second, each updating one record in >> one table. >> >>> If the work done during contention is quick and simple then you are >>> fine. If it is long running then you will benefit from a server based >>> approach. But when you have commits then disk access is serialized >>> and >>> you will have performance limitations no matter what the database >>> server or SQLite. (That is the point Florian is making.) >> >> As far as I can tell, with correct programming each query would be one >> connection for all the data the query would want. So the OP's >> objective is /probably/ achievable with SQLite but I'd want to >> prototype it to be sure. >> >> Sorry, Robel, but we can only guess. Try it. >> >> Simon. >> _______________________________________________ >> 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 >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Software first. Software lasts! _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
