database SQLite 作为低流量站点的生产数据库？

SQLite as a production database for a low-traffic site?

提问by carson welsh

I'm considering using SQLite as a production database for a site that would receive perhaps 20 simultaneous users, but with the potential for a peak that could be many multiples of that (since the site would be accessible on the open internet and there's always a possibility that someone will post a link somewhere that could drive many people to the site all at once).

我正在考虑使用 SQLite 作为一个网站的生产数据库，该网站可能会同时接收 20 个用户，但峰值的潜力可能是该网站的数倍（因为该网站可以在开放的互联网上访问，并且总是有一个可能有人会在某处发布一个链接，可以同时将许多人吸引到该网站）。

Is SQLite a possibility?

SQLite 有可能吗？

I know it's not an ideal production scenario. I'm only asking if this is within the realm of being a realistic possibility.

我知道这不是理想的生产场景。我只是问这是否在现实可能性的范围内。

回答by Bayard Randel

SQLite doesn't support any kind of concurrency, so you may have problems running it on a production website. If you're looking for a 'lighter' database, perhaps consider trying a contemporary object-document store like CouchDB.

SQLite 不支持任何类型的并发，因此您可能会在生产网站上运行它时遇到问题。如果您正在寻找一个“更轻”的数据库，也许可以考虑尝试像 CouchDB 这样的现代对象文档存储。

By all means, continue to develop against SQLite, and you're probably fine to use it initially. If you find your application has more users down the track, you're going to want to transition to Postgres or MySQL however.

无论如何，继续针对 SQLite 进行开发，您可能一开始就可以使用它。但是，如果您发现您的应用程序有更多用户，那么您将希望过渡到 Postgres 或 MySQL。

The author of SQLite addresses this on the website:

SQLite 的作者在网站上解决了这个问题：

SQLite works great as the database engine for most low to medium traffic websites (which is to say, most websites). The amount of web traffic that SQLite can handle depends on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite. The 100K hits/day figure is a conservative estimate, not a hard upper bound. SQLite has been demonstrated to work with 10 times that amount of traffic.

The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015), it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage. This setup runs on a single VM that shares a physical server with 23 others and yet still keeps the load average below 0.1 most of the time.

SQLite 非常适合作为大多数中低流量网站（即大多数网站）的数据库引擎。SQLite 可以处理的网络流量取决于网站使用其数据库的程度。一般来说，任何每天点击量低于 100K 的网站都应该可以很好地使用 SQLite。10 万次点击/天的数字是一个保守的估计，而不是一个硬上限。SQLite 已被证明可以处理 10 倍的流量。

当然，SQLite 网站 ( https://www.sqlite.org/) 本身使用 SQLite，在撰写本文时（2015 年），它每天处理大约 400K 到 500K HTTP 请求，其中大约 15-20% 是接触数据库的动态页面。动态内容每个网页使用大约 200 条 SQL 语句。此设置在单个 VM 上运行，该 VM 与其他 23 台共享物理服务器，但在大多数情况下仍将平均负载保持在 0.1 以下。

So I think the long and short of it is, go for it, and if it's not working well for you, making the transition to an enterprise-class database is fairly trivial anyway. Do take care of your schema, however, and design your database with growth and efficiency in mind.

所以我认为它的长短是，继续努力，如果它不适合你，那么过渡到企业级数据库无论如何都是微不足道的。但是，请注意您的架构，并在设计数据库时考虑到增长和效率。

---

---

Here's a threadwith some more independent comments around using SQLite for a production web application. It sounds like it has been used with some mixed results.

这是一个线程，其中包含有关将 SQLite 用于生产 Web 应用程序的一些更独立的评论。听起来它已经被使用，但结果有些好坏参半。

---

---

Edit (2014):

编辑（2014）：

Since this answer was posted, SQLite now features a multi-threaded modeand write ahead logging modewhich may influence your evaluation of its suitability for low-medium traffic sites.

自从发布此答案后，SQLite 现在具有多线程模式和预写日志记录模式，这可能会影响您对其是否适合中低流量站点的评估。

Charles Leifer has written a blog postabout SQLite's WAL (write ahead logging) feature and some well-considered opinions on appropriate use cases.

Charles Leifer 写了一篇关于 SQLite 的 WAL（预写日志）功能的博客文章以及一些关于适当用例的深思熟虑的意见。

回答by nehem

The small excerpt from SQLite websitesays it all.

SQLite网站的一小段摘录说明了一切。

• Is the data separatedfrom the application by a network? → choose client/server

• Many concurrentwriters? → choose client/server

• Big data? → choose client/server

• Otherwise → choose SQLite!

• 数据是否通过网络与应用程序分离？→ 选择 客户端/服务器

• 许多并发作家？→ 选择客户端/服务器

• 大数据？→ 选择客户端/服务器

• 否则→选择SQLite！

SQLite "just works" (until it doesn't of course)

SQLite“正常工作”（直到它当然不会）

回答by Soviut

We often use SQLite for internal databases; The employee directory, our calendar of events, and other intranet services all run on lightweight databases. It would be major overkill to be running these apps at the scale we do on a "real" database like mySQL. This is especially true when you factor in that they're running along side 4 other virtual machines on a single mid-range computer.

我们经常将 SQLite 用于内部数据库；员工目录、我们的活动日历和其他 Intranet 服务都运行在轻量级数据库上。以我们在“真实”数据库（如 mySQL）上运行的规模运行这些应用程序将是严重的矫枉过正。当您将它们与 4 个其他虚拟机一起运行在一台中端计算机上时，这一点尤其正确。

At one point we had an outward facing site that ran on an sqlite db for months with only a single reboot required. Obviously, it was very low traffic, but it putted along nicely for what it did.

有一次，我们有一个面向外的站点，它在 sqlite 数据库上运行了几个月，只需要重新启动一次。显然，它的流量非常低，但它所做的一切都很好。

回答by Uri Agassi

We have encountered a similar option on an environment with absolutely no writes, and we selected using SQLite.

我们在绝对没有写入的环境中遇到了类似的选项，我们选择使用 SQLite。

See my blog poston the subject:

请参阅我关于该主题的博客文章：

Well, the main assumption which makes this solution theoretically possible is that our SQLite database is totally read-only. Our server code should never change it. This would solve any locking problems, as there are no read locks. We could find nowhere on the internet anyone saying there is a problem in high-throughput reading of SQLite when there are no writes - it could be possible!

好吧，使这个解决方案在理论上可行的主要假设是我们的 SQLite 数据库是完全只读的。我们的服务器代码永远不应该改变它。这将解决任何锁定问题，因为没有读锁。我们在互联网上找不到任何人说当没有写入时 SQLite 的高吞吐量读取存在问题 - 这可能是可能的！

回答by Uri Agassi

People speak about concurrency problems, but sqlite has a way to cache incoming requests and have them wait for some time. It doesn't timeout immediately.

人们谈论并发问题，但 sqlite 有一种方法可以缓存传入的请求并让它们等待一段时间。它不会立即超时。

I've read things about the default timeout setting begin zero, meaning it times out immediately and that's nonsense. Maybe people didn't adjust this setting?

我已经阅读了有关默认超时设置从零开始的内容，这意味着它会立即超时，这是无稽之谈。也许人们没有调整这个设置？

回答by Adam Says - Reinstate Monica

I think it would depend mostly on what your read/write ratio will be. If it's mostly reading from the database, you may be okay. Multi-user writing in SQLite can be a problem because of how it locks the database.

我认为这主要取决于您的读/写比率。如果它主要是从数据库中读取，你可能没问题。SQLite 中的多用户写入可能是一个问题，因为它如何锁定数据库。

回答by Badaro

Depends on the usage of the site. If most of the time you're just reading data, you can pretty much use anything for a DB and cache the data in the application to achieve good performance.

取决于网站的使用情况。如果大部分时间您只是在读取数据，那么您几乎可以对数据库使用任何东西并将数据缓存在应用程序中以实现良好的性能。

回答by radtek

To add to an already brilliant answer: Since you are working with a server-less solution in this case, you can say goodbye to replication, or any sort of horizontal scaling of your db, as well as other advanced options. It also isn't the best choice if you have multiple users updating the same exact chunk of information. If you were to shard the database in the future you would have to migrate the data and move to something else. Also if you have a load balancer and multiple systems involved it would be difficult to maintain data centrality if using sqlite. These are just some of the reasons why it isn't recommended. Its great for smaller projects, and great for development.

添加一个已经很好的答案：由于在这种情况下您正在使用无服务器解决方案，您可以告别复制，或任何类型的数据库水平扩展，以及其他高级选项。如果您有多个用户更新相同的确切信息块，它也不是最佳选择。如果您将来要对数据库进行分片，则必须迁移数据并转移到其他地方。此外，如果您有一个负载平衡器并且涉及多个系统，那么使用 sqlite 将很难保持数据中心性。这些只是不推荐它的一些原因。它非常适合较小的项目，也非常适合开发。

回答by BlogueroConnor

I am using it in a very low traffic web server (it is a genomic database) and I don't have any problems. But there are only SELECT statements, no writing to the DB involved.

我在一个非常低流量的网络服务器（它是一个基因组数据库）中使用它，我没有任何问题。但是只有 SELECT 语句，没有写入涉及的数据库。

回答by infocyde

It seems like with queuing you could also get away with avoiding a lot of the concurrency write problems with SQLite. Instead of writing directly to the sqlite db you would write to a queue that then in turn sequentially writes to the sqlite db in a first in first out mode. Not sure if your application reaches to where you would need this if it would be worth writing or just moving on to client/server DB...but a thought.

似乎通过排队，您还可以避免 SQLite 的许多并发写入问题。不是直接写入sqlite db，而是写入一个队列，然后依次以先进先出模式依次写入sqlite db。不确定您的应用程序是否可以到达您需要它的地方，是否值得编写或只是转移到客户端/服务器数据库......但有一个想法。