Re: [sqlite] Advice on which to use (SQLite or SQL Server) for the following app.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
-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.
* 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.
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.
-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.
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