3 ms·

What would be a bottleneck / limit for SQLite? If you have to many SELECT query because your site is too busy, what would happen?

by neals 9y ago

odonnellryan 9y ago
SQLite can probably handle a lot of SELECT queries at once, but only one process can be writing to the database at once. I think it's a hard question to answer, "what's the upper limit on SQLite?" Better question would probably be, "We have this app, what database should I use?" I use SQLite for a lot of smaller apps. Applications that have a focus on single-business, low-volume use. They might do a good amount of computation and data-fetching, but they don't make heavy use of the database. If you need features Postgres has, use that. If you need features MS SQL has, use that... if you don't need anything special, think about using SQLite :)
shoover 9y ago
While not strictly related to computation, sqlite is a non-starter for deploying to PaaS without a persistent file system. "SQLite on Heroku" [1], essentially: don't do it, switch to postgres. I suppose it would work with AWS Beanstalk and EBS. [1] https://devcenter.heroku.com/articles/sqlite3 https://devcenter.heroku.com/articles/sqlite3
- tracker1 9y ago
  If you're already in that environment RDS is probably a safer bet though.
andyjpb 9y ago
I've benchmarked SQLite on my laptop at just north of 20,000 queries (SELECTs) per second for the workload I was interested in at the time. However, the limit depends heavily on your workload. If your working set (the data that all the simultaneous queries require) fits in memory then you'll be limited by memory bandwidth or the language you're calling from: GC stalls and query preparation can really hammer the throughput; if you're doing any actual work with the results of the query then I'd hope that time spent there would dominate the data access. If your data doesn't fit in memory then you'll be limited by the speed of your disk system. If you have a fixed working set then you can increase the performance by adding RAM until the working set fits again. If you have a streaming workload then you'll never be able to fit it in RAM (by definition) so you'll be limited by the speed of the IO systems involved; either network or disk. If things are still too busy then stuff will start to queue up. This will cause each individual process to run slower as it'll be contending with the others. This, in turn will cause more incoming work to queue up. At some point the machine will run out of some other resource (memory for incoming connections, network sockets, space in queues, etc). How the system responds to that depends on how it's configured but, traditionally, your system is no longer providing service.
zxcvbn4038 9y ago
SQLite only allows one writer at a time, so depending on your access pattern that might be an issue, but if your site is getting a million requests a day then your only processing an average of 11.5 requests per second, and what percentage of them are writes? Front your app with varnish, nginx (w/cache), or cloudflare and absorb some additional load. Though as far as I can tell www.sqlite.com is running on a single linode instance with no fancy hacks.

What would be a bottleneck / limit for SQLite? If you have to many SELECT query because your site is too busy, what would happen?

by neals 9y ago

What would be a bottleneck / limit for SQLite? If you have to many SELECT query because your site is too busy, what would happen?

odonnellryan 9y ago

SQLite can probably handle a lot of SELECT queries at once, but only one process can be writing to the database at once. I think it's a hard question to answer, "what's the upper limit on SQLite?" Better question would probably be, "We have this app, what database should I use?" I use SQLite for a lot of smaller apps. Applications that have a focus on single-business, low-volume use. They might do a good amount of computation and data-fetching, but they don't make heavy use of the database. If you need features Postgres has, use that. If you need features MS SQL has, use that... if you don't need anything special, think about using SQLite :)

shoover 9y ago

While not strictly related to computation, sqlite is a non-starter for deploying to PaaS without a persistent file system. "SQLite on Heroku" [1], essentially: don't do it, switch to postgres. I suppose it would work with AWS Beanstalk and EBS. [1] https://devcenter.heroku.com/articles/sqlite3 https://devcenter.heroku.com/articles/sqlite3

tracker1 9y ago
If you're already in that environment RDS is probably a safer bet though.

andyjpb 9y ago

I've benchmarked SQLite on my laptop at just north of 20,000 queries (SELECTs) per second for the workload I was interested in at the time. However, the limit depends heavily on your workload. If your working set (the data that all the simultaneous queries require) fits in memory then you'll be limited by memory bandwidth or the language you're calling from: GC stalls and query preparation can really hammer the throughput; if you're doing any actual work with the results of the query then I'd hope that time spent there would dominate the data access. If your data doesn't fit in memory then you'll be limited by the speed of your disk system. If you have a fixed working set then you can increase the performance by adding RAM until the working set fits again. If you have a streaming workload then you'll never be able to fit it in RAM (by definition) so you'll be limited by the speed of the IO systems involved; either network or disk. If things are still too busy then stuff will start to queue up. This will cause each individual process to run slower as it'll be contending with the others. This, in turn will cause more incoming work to queue up. At some point the machine will run out of some other resource (memory for incoming connections, network sockets, space in queues, etc). How the system responds to that depends on how it's configured but, traditionally, your system is no longer providing service.

zxcvbn4038 9y ago

SQLite only allows one writer at a time, so depending on your access pattern that might be an issue, but if your site is getting a million requests a day then your only processing an average of 11.5 requests per second, and what percentage of them are writes? Front your app with varnish, nginx (w/cache), or cloudflare and absorb some additional load. Though as far as I can tell www.sqlite.com is running on a single linode instance with no fancy hacks.