6 ms·

The link is broken at the moment. I really want to read it to learn the answer to the following: why isn't sqlite able to handle full scale loads? Why do I ha

The link is broken at the moment. I really want to read it to learn the answer to the following: why isn't sqlite able to handle full scale loads? Why do I have to swap it out? Can't someone change the implementation details so it can work at scale and leave the interface the same? Where is sqlheavy?

batbomb 10y ago
SQLite has a basic optimizer. That's basically it IMO. You can already do some things like use BDB backend if you want better transaction performance.
veli_joza 10y ago
Here's the cached link, but there are no such answers within this text. This just measures some arbitrary metrics on sqlite codebase. http://webcache.googleusercontent.com/search?q=cache:21EcxYD03cUJ:www.codergears.com/Blog/%3Fp%3D2392+ http://webcache.googleusercontent.com/search?q=cache:21EcxYD... Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.
sirtaj 10y ago
sqlheavy is available, it's called postgresql. But you could go halfway and use something like libmysqld.
acqq 10y ago
> why isn't sqlite able to handle full scale loads? The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.
dorfsmay 10y ago
You mean heavy concurrent writes? I have no special insight in SQLite, but I think because it would need to be multiprocess, which it cannot as an embedded library. Have you read https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html ? > Situations Where A Client/Server RDBMS May Work Bette > • High-volume Websites > • High Concurrency Anecdotal Observation: I like to use SQLite when running tests etc... because it's easy to make the data make sense, and easy to ship the resulting file, but recently I had to run a high number of concurrent writes and it slowed down my tests and made my laptop unusable. I re-wired my scripts to use PostgreSQL and it moved the bottleneck to what I was actually trying to test, and even though my disk led was blinking like crazy, my laptop was still usable. I checked what was going on and saw that postgres had spawn a whole bunch of processes... On other workload, especially with low-concurrency, I have found SQLite actually performing really well, and comparably to postgres. You do have to tweek a few things depending on your workload, but there are a lot of very informative blogs and SO pages on this subject out there.
- projectramo 10y ago
  Yes. But the whole point of sqlite is that i don't have to type in more code. Can't it just do all that for me? I should add that I know it is a minimal lightweight database, and that there are other ones that are much more feature rich and more complicated.
  - dorfsmay 10y ago
    No, the whole point of sqlite is to be able to use a relational data model instead of a text file. It sounds like you are needing a Relational Database server. PosgreSQL, DB2 and Oracle aren't that difficult to install on a laptop. I highly recommend using the same product in dev and prod.
  - mikeash 10y ago
    I think you're looking at the wrong layer. SQLite is a specific database implementation, there's no reason to expect it to provide a wrapper around some other implementation too. One potential problem is that not all databases are created equal. SQLite may support features that other databases don't (or vice versa), or they may both support the same feature but with different syntax, and so code that works with one backend may not work with another. In any case, it sounds like you're after some sort of wrapper that can provide access to different database backends without your code needing to know or care what the current backend is. Something like ODBC would seem to fit the bill, and there is a SQLite driver for it.
pjc50 10y ago
SQLheavy is called "Oracle". You want a feature, it's in there. You don't want a feature? Well, tough, it's in there anyway. From https://www.sqlite.org/about.html https://www.sqlite.org/about.html : "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine" Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning. Serverless imposes most of the rest of the performance penalty. If you're accessing a sqlite database from a single process, it will most likely be limited by your storage speed. But if you're writing to it from multiple processes, lock contention will quickly become an issue. The obvious technique to improve lock contention is to separate the tables so they can be locked separately, but then you lose the single-file convenience. That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()".
- creshal 10y ago
  > Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning. It also makes for a stupidly easy to use product. SQLite easily outperforms Postgres in default settings and any "optimized" snippets blog posts cough up for the workload generated by a project I'm working on. I'm seriously tempted to drop support for non-sqlite DB backends, since it's unlikely that the majority our potential customers (SMB environment) could do a better job than that at configuring database servers.
- k__ 10y ago
  > That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()". True. Point is, most software has only the need for fopen(), but instead uses something like Oracle.
- projectramo 10y ago
  I guess I don't mean more features. I just mean I want to write the same code and put in heroku or whatever and have it be ready for production load (10,000s or 100,000s of people hitting it). The code I wrote has already told the machine what I want it to do. I don't want to be involved in figuring out how to make the DB work on a larger load. Being serverless isn't a feature for me. Zeo-configuration is. Can't it handle the server stuff without asking me to type more lines of code? Maybe on heroku?
deleted 10y ago
[deleted]
bryanlarsen 10y ago
SQLite 4 supports multiple engines. So hopefully sometime in the future it will be possible to start a project with the awesome default configuration-less, serverless SQLite, and then if & when you run into scaling problems, swap in a beefier engine.
nicerobot 10y ago
https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
andyjpb 10y ago
That depends on what your "full scale loads" are. SQLite can easily scale out to read workloads that can consume an entire (large) machine, especially when using its mmap'd IO modes. However, it doesn't have good concurrency for writers and concurrency for readers also drops when any writer is large enough to spill the cache. Writer concurrency can be managed down by building a custom write pipeline in front of it but that is not trivial. ...and, when you do that, you then have to manage your own consistency and Read/Modify/Write hazards so you're no better off than any of the first generation NoSQL stores. If you think about your data model up front then that might be OK, otherwise it's a very difficult road. An important other difference between SQLite and other database engines (RDBMS or otherwise) is that SQLite is in-process rather than client/server. This has a lot of implications for the latency costs associated with data access. In particular, SQLite allows you to mostly ignore query round-trip times: because there aren't any. If you change your engine to something that's not in-process you suddenly start having to think about batching and prefetching for things that might initially look like a simple point query. This can have a serious performance impact and result in profound refactoring requirements for your app. Things that were previously running at several 10s of thousands of point queries per second and complete in nanoseconds now require 10s of thousands of sockets and take 10s of milliseconds. For my part, I've built production web apps with lots of users on SQLite and the database engine was the smallest part of our performance work. Reducing garbage generation in the render pipeline and moving query generation out of the request path were where we got most of our performance gains.
- coleifer 10y ago
  Regarding concurrency, the folks at Oracle welded sqlites front-end onto berkeleydb. Since bdb does page-level locking you can potentially see much greater concurrency with the bdb implementation.
da_chicken 10y ago
> why isn't sqlite able to handle full scale loads? The same reason an Access database doesn't scale. Because it's not designed to. It doesn't have a complex query optimizer. It isn't designed to use tremendous amounts of memory to optimize execution speeds with a well-managed cache. It isn't designed to be clustered or sharded or partitioned or replicated. It doesn't have GIS or geometry functions. It doesn't support point-in-time recovery. It doesn't support many ANSI SQL functions and features. It's capable of being thread safe, but limited to a single process. The real limitation that people tend to run into, however, is that writing to the database locks the whole database. You can switch to WAL mode which allows reading while writing, but you never get more than one writer and by default writing blocks reading. In other RDBMSs, you have table locks, page locks, or row locks. You can have multiple writers going on in multiple connections managed by multiple processes with multiple threads, and reading is (in general) not blocked. Furthermore, databases for high load applications often support multiple, concurrent connections from multiple discrete applications. You might have 3 different web servers all connecting to your SQL servers in the background. You might have a main application and a reporting application. You may need to ETL data from the database to another database for another purpose. You can't do any of that with SQLite. Hell, I support an app that has six different application servers that connect to it (two main app servers, one secondary app server, two "task" servers that run long execution processes, and a reporting server). And that's just the app itself. There's about a dozen ETL tasks that run against the DB for other applications. Sure, there are some third party add-ons that can do some of these things (SQLitereplica, SQLitening) but I've never seen anybody implement them instead of moving to a heavier RDBMS, which is likely to be better supported. > Can't someone change the implementation details so it can work at scale and leave the interface the same? They could, but then it wouldn't be a lightweight RDBMS anymore. Making a system scale takes more complexity, and therefore more code. > Where is sqlheavy? Oracle, MySQL, PostgreSQL, MS SQL Server, DB2, Firebird, Cassandra, MongoDB, etc. With varying degrees of heaviness.
- k__ 10y ago
  > you never get more than one writer Is this a file system issue (because only one file is used) or a SQLite problem? Like, file systems only allow one writer to a file, even if they would write at different locations.
coleifer 10y ago
Succinctly, sqlite3 uses database locks for write transactions. This means only one writer can exist at a time, limiting concurrency. Secondly, sqlite is an embedded database and doesn't have a separate server process.

batbomb 10y ago

SQLite has a basic optimizer. That's basically it IMO. You can already do some things like use BDB backend if you want better transaction performance.

veli_joza 10y ago

Here's the cached link, but there are no such answers within this text. This just measures some arbitrary metrics on sqlite codebase. http://webcache.googleusercontent.com/search?q=cache:21EcxYD03cUJ:www.codergears.com/Blog/%3Fp%3D2392+ http://webcache.googleusercontent.com/search?q=cache:21EcxYD... Some of advice within should be taken with a grain of salt. Exiting function as early as possible might be OK for you, but it goes against some coding standards that dictate only single return statement per function.

sirtaj 10y ago

sqlheavy is available, it's called postgresql. But you could go halfway and use something like libmysqld.

acqq 10y ago

> why isn't sqlite able to handle full scale loads? The goal of sqlite is to be easily embeddable: that means not too much code, not too much resources used. It serves that purpose well: it's widely used in a lot of programs where you never even thought that they could have it. https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html So the goal was never to suit your "full scale loads" the way you probably define them. But I don't know what you actually understand under the "full scale loads" and once you state what you would exactly expect to achieve it can be discussed more. Until then, simply stated, there is a reason why the big server software is big and resource hungry, unlike sqlite.

dorfsmay 10y ago

You mean heavy concurrent writes? I have no special insight in SQLite, but I think because it would need to be multiprocess, which it cannot as an embedded library. Have you read https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html ? > Situations Where A Client/Server RDBMS May Work Bette > • High-volume Websites > • High Concurrency Anecdotal Observation: I like to use SQLite when running tests etc... because it's easy to make the data make sense, and easy to ship the resulting file, but recently I had to run a high number of concurrent writes and it slowed down my tests and made my laptop unusable. I re-wired my scripts to use PostgreSQL and it moved the bottleneck to what I was actually trying to test, and even though my disk led was blinking like crazy, my laptop was still usable. I checked what was going on and saw that postgres had spawn a whole bunch of processes... On other workload, especially with low-concurrency, I have found SQLite actually performing really well, and comparably to postgres. You do have to tweek a few things depending on your workload, but there are a lot of very informative blogs and SO pages on this subject out there.

projectramo 10y ago
Yes. But the whole point of sqlite is that i don't have to type in more code. Can't it just do all that for me? I should add that I know it is a minimal lightweight database, and that there are other ones that are much more feature rich and more complicated.
- dorfsmay 10y ago
  No, the whole point of sqlite is to be able to use a relational data model instead of a text file. It sounds like you are needing a Relational Database server. PosgreSQL, DB2 and Oracle aren't that difficult to install on a laptop. I highly recommend using the same product in dev and prod.
- mikeash 10y ago
  I think you're looking at the wrong layer. SQLite is a specific database implementation, there's no reason to expect it to provide a wrapper around some other implementation too. One potential problem is that not all databases are created equal. SQLite may support features that other databases don't (or vice versa), or they may both support the same feature but with different syntax, and so code that works with one backend may not work with another. In any case, it sounds like you're after some sort of wrapper that can provide access to different database backends without your code needing to know or care what the current backend is. Something like ODBC would seem to fit the bill, and there is a SQLite driver for it.

pjc50 10y ago

SQLheavy is called "Oracle". You want a feature, it's in there. You don't want a feature? Well, tough, it's in there anyway. From https://www.sqlite.org/about.html https://www.sqlite.org/about.html : "SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine" Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning. Serverless imposes most of the rest of the performance penalty. If you're accessing a sqlite database from a single process, it will most likely be limited by your storage speed. But if you're writing to it from multiple processes, lock contention will quickly become an issue. The obvious technique to improve lock contention is to separate the tables so they can be locked separately, but then you lose the single-file convenience. That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()".

creshal 10y ago
> Zero-configuration is actually quite an onerous design requirement, as it immediately eliminates most features which shouldn't be on by default. It also eliminates tuning. It also makes for a stupidly easy to use product. SQLite easily outperforms Postgres in default settings and any "optimized" snippets blog posts cough up for the workload generated by a project I'm working on. I'm seriously tempted to drop support for non-sqlite DB backends, since it's unlikely that the majority our potential customers (SMB environment) could do a better job than that at configuring database servers.
k__ 10y ago
> That's why that page also says "Think of SQLite not as a replacement for Oracle but as a replacement for fopen()". True. Point is, most software has only the need for fopen(), but instead uses something like Oracle.
projectramo 10y ago
I guess I don't mean more features. I just mean I want to write the same code and put in heroku or whatever and have it be ready for production load (10,000s or 100,000s of people hitting it). The code I wrote has already told the machine what I want it to do. I don't want to be involved in figuring out how to make the DB work on a larger load. Being serverless isn't a feature for me. Zeo-configuration is. Can't it handle the server stuff without asking me to type more lines of code? Maybe on heroku?

deleted 10y ago

[deleted]

bryanlarsen 10y ago

SQLite 4 supports multiple engines. So hopefully sometime in the future it will be possible to start a project with the awesome default configuration-less, serverless SQLite, and then if & when you run into scaling problems, swap in a beefier engine.

nicerobot 10y ago

https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite

andyjpb 10y ago

That depends on what your "full scale loads" are. SQLite can easily scale out to read workloads that can consume an entire (large) machine, especially when using its mmap'd IO modes. However, it doesn't have good concurrency for writers and concurrency for readers also drops when any writer is large enough to spill the cache. Writer concurrency can be managed down by building a custom write pipeline in front of it but that is not trivial. ...and, when you do that, you then have to manage your own consistency and Read/Modify/Write hazards so you're no better off than any of the first generation NoSQL stores. If you think about your data model up front then that might be OK, otherwise it's a very difficult road. An important other difference between SQLite and other database engines (RDBMS or otherwise) is that SQLite is in-process rather than client/server. This has a lot of implications for the latency costs associated with data access. In particular, SQLite allows you to mostly ignore query round-trip times: because there aren't any. If you change your engine to something that's not in-process you suddenly start having to think about batching and prefetching for things that might initially look like a simple point query. This can have a serious performance impact and result in profound refactoring requirements for your app. Things that were previously running at several 10s of thousands of point queries per second and complete in nanoseconds now require 10s of thousands of sockets and take 10s of milliseconds. For my part, I've built production web apps with lots of users on SQLite and the database engine was the smallest part of our performance work. Reducing garbage generation in the render pipeline and moving query generation out of the request path were where we got most of our performance gains.

coleifer 10y ago
Regarding concurrency, the folks at Oracle welded sqlites front-end onto berkeleydb. Since bdb does page-level locking you can potentially see much greater concurrency with the bdb implementation.

da_chicken 10y ago

> why isn't sqlite able to handle full scale loads? The same reason an Access database doesn't scale. Because it's not designed to. It doesn't have a complex query optimizer. It isn't designed to use tremendous amounts of memory to optimize execution speeds with a well-managed cache. It isn't designed to be clustered or sharded or partitioned or replicated. It doesn't have GIS or geometry functions. It doesn't support point-in-time recovery. It doesn't support many ANSI SQL functions and features. It's capable of being thread safe, but limited to a single process. The real limitation that people tend to run into, however, is that writing to the database locks the whole database. You can switch to WAL mode which allows reading while writing, but you never get more than one writer and by default writing blocks reading. In other RDBMSs, you have table locks, page locks, or row locks. You can have multiple writers going on in multiple connections managed by multiple processes with multiple threads, and reading is (in general) not blocked. Furthermore, databases for high load applications often support multiple, concurrent connections from multiple discrete applications. You might have 3 different web servers all connecting to your SQL servers in the background. You might have a main application and a reporting application. You may need to ETL data from the database to another database for another purpose. You can't do any of that with SQLite. Hell, I support an app that has six different application servers that connect to it (two main app servers, one secondary app server, two "task" servers that run long execution processes, and a reporting server). And that's just the app itself. There's about a dozen ETL tasks that run against the DB for other applications. Sure, there are some third party add-ons that can do some of these things (SQLitereplica, SQLitening) but I've never seen anybody implement them instead of moving to a heavier RDBMS, which is likely to be better supported. > Can't someone change the implementation details so it can work at scale and leave the interface the same? They could, but then it wouldn't be a lightweight RDBMS anymore. Making a system scale takes more complexity, and therefore more code. > Where is sqlheavy? Oracle, MySQL, PostgreSQL, MS SQL Server, DB2, Firebird, Cassandra, MongoDB, etc. With varying degrees of heaviness.

k__ 10y ago
> you never get more than one writer Is this a file system issue (because only one file is used) or a SQLite problem? Like, file systems only allow one writer to a file, even if they would write at different locations.

coleifer 10y ago

Succinctly, sqlite3 uses database locks for write transactions. This means only one writer can exist at a time, limiting concurrency. Secondly, sqlite is an embedded database and doesn't have a separate server process.