Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Petite Abeille]

On Jun 11, 2009, at 9:05 PM, Jim Wilcoxson wrote:

> you will have to place each on its own physical disk drive to  
> increase transaction rates.

Arguably, such micro management of what data block sits on what disk  
spindle would be better left to the underlying volume manager or such.

A bit Oracle specific, but covers a lot of relevant ground:

"Back-of-the-Envelope Database Storage Design"
http://www.oracle.com/technology/products/database/asm/pdf/back%20of%20the%20env%20by%20nitin%20oow%202007.pdf

Cheers,

--
PA.
http://alt.textdrive.com/nanoki/

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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Tim Bradshaw]

On 11 Jun 2009, at 16:19, Jim Wilcoxson wrote:

> SSD's usually have poor write performance, because to do a write, they
> have to use read, erase, write sequences across large blocks like 64K.
> Most of the SSD benchmarks that quote good write performance are for
> sequential write performance.  If you skip all over the disk doing
> small writes, like a database does

I think it's not the case that a database needs to skip all over the  
disk. For a start you can write to a log at the DB level, but even if  
you don't at least some modern filesystems are copy-on-write, so they  
never actually rewrite blocks (well, they do, but not in the read- 
modify-write sense).  ZFS is one such filesystem.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Tim Bradshaw]

On 11 Jun 2009, at 20:05, Jim Wilcoxson wrote:

> If you partition the database into multiple databases, you will have
> to place each on its own physical disk drive to increase transaction
> rates.  If your base transaction rate with one drive is T, with N
> drives it should be N*T;  4 drives gives you 4x the transaction rate,
> etc.  Each of the drives has to be completely independent - no
> filesystems crossing drives.

I think - if you are serious about the problem - you can just rely on  
a disk array to do this for you. You see something that looks like a  
disk but which is of course spread over lots of spindles and with a  
bucketload of NV cache in front of it, and which can sustain really  
high numbers of ops/second.

Of course that may be what the previous person meant by "special  
hardware".
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Jim Wilcoxson]

Yes, good point.

If you partition the database into multiple databases, you will have
to place each on its own physical disk drive to increase transaction
rates.  If your base transaction rate with one drive is T, with N
drives it should be N*T;  4 drives gives you 4x the transaction rate,
etc.  Each of the drives has to be completely independent - no
filesystems crossing drives.

If you partition into multiple databases and keep them on the same
drive, your transaction rate will likely go down, because now you are
introducing seeks back and forth between the two databases as you
commit.

I think someone mentioned using the journal_data option with ext3 to
increase performance.  In theory, it possibly could, but on my Linux
system, it didn't.  I got the same results when I tried using tune2fs
to change it, though I'm not sure it actually did anything.

Jim

On 6/11/09, Petite Abeille  wrote:
>
> On Jun 11, 2009, at 4:53 PM, Sam Carleton wrote:
>
>> I am a late comer to this discussion, so this might have already
>> been purposed...
>
> Additionally, if this was not mentioned already, you can partition
> your database across multiple physical files through the magic of
> 'attach database' or something and load balance across those.
>
> http://en.wikipedia.org/wiki/Partition_(database)
>
> CHeers,
>
> --
> PA.
> http://alt.textdrive.com/nanoki/
> ___
> 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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Petite Abeille]

On Jun 11, 2009, at 4:53 PM, Sam Carleton wrote:

> I am a late comer to this discussion, so this might have already  
> been purposed...

Additionally, if this was not mentioned already, you can partition  
your database across multiple physical files through the magic of  
'attach database' or something and load balance across those.

http://en.wikipedia.org/wiki/Partition_(database)

CHeers,

--
PA.
http://alt.textdrive.com/nanoki/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Marian Olteanu]

On Thu, Jun 11, 2009 at 1:46 AM, Florian Weimer  wrote:

>  That's 500 commits per second, right?  If you need durability, you can
> get these numbers only with special hardware.
>
Not really, you don't need special hardware (if you don't use SQLite).
The use case that Robel described requires best row locks. In a RDBMS
server, nobody stops you to commit several transactions in the same time,
using the same disk spin. With SQLite you cannot, because SQLite use
database lock, so all transactions are serialized.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Sam Carleton]

Jim,

I am about to have my first one here in a few hours.  Can you email me 
the program directly?


Sam

Jim Wilcoxson wrote:

Hey, if anybody has an SSD laying around, it would be interesting to
run that commit test program I posted a while back to see what kind of
transaction rates are possible.  Although, verifying whether the SSD
is actually doing the commits or just saying it is would be very
difficult.  With rotating media, you at least have an upper bound
(120/sec for 7200rpm, 166/sec for 10Krpm, 250/sec for 15Krpm) and if
you go outside that, you know it's a lie.  Not sure how you could
verify that commits/syncs with an SSD are actually working other than
repeatedly pulling the plug and seeing if the db survives intact.

Jim

On 6/11/09, Jim Wilcoxson  wrote:
  

SSD's usually have poor write performance, because to do a write, they
have to use read, erase, write sequences across large blocks like 64K.



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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Robel Girma]

Thank you all for the wonderful advices. I guess the only thing left now is
to dive into writing the app and stress test to find out :)

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jim Wilcoxson
Sent: Thursday, June 11, 2009 11:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the
following app.

SSD's usually have poor write performance, because to do a write, they
have to use read, erase, write sequences across large blocks like 64K.
 Most of the SSD benchmarks that quote good write performance are for
sequential write performance.  If you skip all over the disk doing
small writes, like a database does, I suspect you'll see pretty bad
performance with most SSD's.  In most of the SSD benchmarks I've seen,
random write performance is worse than rotating media.  Actually, most
of the SSD benchmarks I've seen completely skip over this point,
perhaps not even testing random write performance, but only sequential
writes.

Using a separate copy of the database on SSD for queries would
probably work well, assuming the database doesn't fit into RAM.  If
the db does fit in RAM, SSD won't buy you anything.

Companies are putting all kinds of smarts into SSD's to try to
minimize the effects of the read, erase, write cycle, usually by some
form of caching, but then you are also playing with losing the
transaction guarantees of a commit.  Can't really have it both ways.

Jim

On 6/11/09, Sam Carleton  wrote:
> Jim Wilcoxson wrote:
>> 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.
>
> I am a late comer to this discussion, so this might have already been
> purposed...
>
> Ever consider having all the updates are done on none rotating media
> (SSD) and then having 1 process that reads in blocks of the data and
> puts it into it's final home?  Purge the SSD SQLite db simply by rolling
> to a new one from time to time and delete the old once once all the
> connections have moved on to the new SQLite db.
>
> Sam
>


-- 
Software first.  Software lasts!
___
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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Jim Wilcoxson]

Hey, if anybody has an SSD laying around, it would be interesting to
run that commit test program I posted a while back to see what kind of
transaction rates are possible.  Although, verifying whether the SSD
is actually doing the commits or just saying it is would be very
difficult.  With rotating media, you at least have an upper bound
(120/sec for 7200rpm, 166/sec for 10Krpm, 250/sec for 15Krpm) and if
you go outside that, you know it's a lie.  Not sure how you could
verify that commits/syncs with an SSD are actually working other than
repeatedly pulling the plug and seeing if the db survives intact.

Jim

On 6/11/09, Jim Wilcoxson  wrote:
> SSD's usually have poor write performance, because to do a write, they
> have to use read, erase, write sequences across large blocks like 64K.

-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Jim Wilcoxson]

SSD's usually have poor write performance, because to do a write, they
have to use read, erase, write sequences across large blocks like 64K.
 Most of the SSD benchmarks that quote good write performance are for
sequential write performance.  If you skip all over the disk doing
small writes, like a database does, I suspect you'll see pretty bad
performance with most SSD's.  In most of the SSD benchmarks I've seen,
random write performance is worse than rotating media.  Actually, most
of the SSD benchmarks I've seen completely skip over this point,
perhaps not even testing random write performance, but only sequential
writes.

Using a separate copy of the database on SSD for queries would
probably work well, assuming the database doesn't fit into RAM.  If
the db does fit in RAM, SSD won't buy you anything.

Companies are putting all kinds of smarts into SSD's to try to
minimize the effects of the read, erase, write cycle, usually by some
form of caching, but then you are also playing with losing the
transaction guarantees of a commit.  Can't really have it both ways.

Jim

On 6/11/09, Sam Carleton  wrote:
> Jim Wilcoxson wrote:
>> 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.
>
> I am a late comer to this discussion, so this might have already been
> purposed...
>
> Ever consider having all the updates are done on none rotating media
> (SSD) and then having 1 process that reads in blocks of the data and
> puts it into it's final home?  Purge the SSD SQLite db simply by rolling
> to a new one from time to time and delete the old once once all the
> connections have moved on to the new SQLite db.
>
> Sam
>


-- 
Software first.  Software lasts!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Sam Carleton]

Jim Wilcoxson wrote:

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.


I am a late comer to this discussion, so this might have already been 
purposed...


Ever consider having all the updates are done on none rotating media 
(SSD) and then having 1 process that reads in blocks of the data and 
puts it into it's final home?  Purge the SSD SQLite db simply by rolling 
to a new one from time to time and delete the old once once all the 
connections have moved on to the new SQLite db.


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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Jim Wilcoxson]

I should have mentioned, if it were me, I'd write the mini server
first as a single process in a loop, and make it as fast as possible.
If you try to do db updates with multiple processes, you'll have
concurrency issues.  It might make sense to use multiple processes if
you also have lots of queries, and have only 1 process (or thread)
writing, while a pool of processes handles queries.  Not sure how well
SQLite handles this situation, but since you are doing group commits,
it will greatly decrease your write load and potential concurrency
issues.

Jim

On 6/11/09, Jim Wilcoxson  wrote:
> Here's what I'd try:
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Jim Wilcoxson]

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  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 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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Pavel Ivanov]

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 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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Robel Girma]

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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[John Stanton]

Aqlite is not the DB for your application.  You need a server like 
PostgreSQL or Oracle.

Robel Girma wrote:
> Hello,
>
> I am in need for a database to hold a couple of tables with max 10,000 rows
> each that I will update frequently and query frequently. 
>
> Example, 5000 users connect to our server every 10 seconds and each time
> they connect, I need to update a table with their IP and Last_connect_time. 
>
> Also, every 10 seconds or so, a couple of hundred users will query this
> table with simple select statements (Select ip from table1 where
> last_connect_time is greater than 20seconds).
>
>  
>
> I'm trying to choose the most efficient db for this application and my main
> criteria is response time. Will  SQLite do this more efficiently than SQL
> Server. I'm planning to allocate upto 1GB memory. 
>
> I appreciate any input we can give on this.
>
> Robel
>
> ___
> 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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Simon Slavin]

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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robel Girma wrote:
> but rather trying to
> find out if my app will work with SQLite. 

SQLite will definitely work and at the very least you will it useful
during (rapid) development and demos.  Quite simply SQLite will get you
results far quicker than server based approaches which have to marshal
the data across the network.

> in reality, the web app will be doing all the writing and reading to SQLite.

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.

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.)

> I'm trying to avoid using a traditional database server if SQLite can handle
> this. I wanted to check here first for guidance to see if SQLite can do
> this.

I'd suggest just going ahead and doing a mock implementation and see
what you get.  If you have a URL that does representative work then you
can use tools like ab (the Apache Benchmark tool that comes with Apache
but works with any server) to run the number of concurrent requests you
specify.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkowsQoACgkQmOOfHg372QQsRQCeIHiikyM8k/h/oTeitLCPzHpA
IaYAoM9dEjvqD2GZWIkiWZCnni2H28GH
=LzSr
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Florian Weimer]

* Robel Girma:

> Example, 5000 users connect to our server every 10 seconds and each
> time they connect, I need to update a table with their IP and
> Last_connect_time.

That's 500 commits per second, right?  If you need durability, you can
get these numbers only with special hardware.

SQL Server might offer better performance, assuming it can group
commits.  However, it might be easier to to just keep the data in
memory and log the changes to disk.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Robel Girma]

Thanks for the reply Roger and I have read the section you mentioned, very
informative. I'm not trying to compare the 2 products, but rather trying to
find out if my app will work with SQLite. I don't necessarily require a
server. My app can work as a web app or web service where clients hit this
service and my app will collect the necessary info and write to the DB. So
in reality, the web app will be doing all the writing and reading to SQLite.
I'm trying to avoid using a traditional database server if SQLite can handle
this. I wanted to check here first for guidance to see if SQLite can do
this.
Thanks,
Robel 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns
Sent: Thursday, June 11, 2009 1:31 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the
following app.

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robel Girma wrote:
> I'm trying to choose the most efficient db for this application and my
main
> criteria is response time. Will  SQLite do this more efficiently than SQL
> Server. I'm planning to allocate upto 1GB memory. 

SQLite doesn't operate as a server so there isn't a straight forward
comparison.  I suggest reading http://www.sqlite.org/whentouse.html
which gives good advice.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkowlowACgkQmOOfHg372QQ0NQCbBwYmOVAQOAvhRwM70+cpioZz
94MAoI62fP0VKlFN/9K2rjFp5Bv49oZ4
=okkY
-END PGP SIGNATURE-
___
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

Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Robel Girma wrote:
> I'm trying to choose the most efficient db for this application and my main
> criteria is response time. Will  SQLite do this more efficiently than SQL
> Server. I'm planning to allocate upto 1GB memory. 

SQLite doesn't operate as a server so there isn't a straight forward
comparison.  I suggest reading http://www.sqlite.org/whentouse.html
which gives good advice.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkowlowACgkQmOOfHg372QQ0NQCbBwYmOVAQOAvhRwM70+cpioZz
94MAoI62fP0VKlFN/9K2rjFp5Bv49oZ4
=okkY
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Advice on which to use (SQLite or SQL Server) for the following app.

[Robel Girma]

Hello,

I am in need for a database to hold a couple of tables with max 10,000 rows
each that I will update frequently and query frequently. 

Example, 5000 users connect to our server every 10 seconds and each time
they connect, I need to update a table with their IP and Last_connect_time. 

Also, every 10 seconds or so, a couple of hundred users will query this
table with simple select statements (Select ip from table1 where
last_connect_time is greater than 20seconds).

 

I'm trying to choose the most efficient db for this application and my main
criteria is response time. Will  SQLite do this more efficiently than SQL
Server. I'm planning to allocate upto 1GB memory. 

I appreciate any input we can give on this.

Robel

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