Web应用程序中的数据库连接 [英] Database Connection in Web Application

问题描述

以下是我们可以在Web应用程序环境中用于数据库连接的不同技术.

The following are the different techniques we can use for database connection in a web application envrioment.

1. Application Context Database Connection:只有一个数据库连接在所有请求之间共享.
2. Database Pooling:打开固定数量的数据库连接，并在所有请求之间共享连接.
3. New Database Connection per request:为每个请求打开一个连接.

1. Application Context Database Connection: only one database connection shared among all requests.
2. Database Pooling: open a fixed number of database connection and share the connection among all requests.
3. New Database Connection per request: Open a connection for each request.

这些技术的优点和缺点是什么?开发人员应该使用哪一个?

What are the advantage and disadvantage of these techniques? Which one should a developer use?

推荐答案

选项1.在所有Web用户之间共享单个数据库连接注定会因某种原因而失败.一个长时间运行的查询和整个服务器将停止运行.对于所有现代应用程序(甚至非基于Web的应用程序)中的99.9％来说，这是一成不变的否".

Option 1. Sharing a single database connection among all web users is bound to fail for one reason or another. One long running query and your entire server will grind to a halt. That's an hard and fast "no" for 99.9% of all modern applications, even non-web based applications.

选项2.连接池.可能是Web应用程序连接到数据库的第二种最常用的技术.首先，如果数据库和池/Web应用程序位于同一台计算机上，则收益将非常有限.池和Web应用程序可以很容易地存在于同一硬件上.这样做的好处是，数据库连接的打开开销很大，而保持打开的开销较小.打开连接需要CPU使用率和内存分配.池连接可以使几十个连接几乎可以立即附加"到连接.内存将已经分配，​​并且大多数设置工作已经完成，因此与池连接和断开连接比较便宜.池通常始终保持一定数量的连接处于打开状态，并随着流量的增加而增长.在流量过大的情况下，池通常会将连接请求放入队列中，以使DB不会过载..位于队列后面的用户会遇到延迟，但是系统崩溃了，并且由于内存不足，通常不太可能停止运行和磁盘交换.

Option 2. Connection pooling. Probably the 2nd most commonly used technique for a web app to connect to a DB. First, the benefits are extremely limited if the DB and the Pool/web app are on the same machine. The pool and the web app, however can easily exist on the same hardware. The benefit here is that a database connections are expensive to open AND to a lesser extent, expensive to keep open. Opening a connection requires CPU usage and memory allocation. Pooling connections can keep a few dozen connections available to be "attached to" nearly instantly. The memory will already be allocated and most of the setup work done already, so connecting and disconnecting from the pool is relatively cheep. Pools usually keep a certain number of connections open at all times and grow as traffic increases. In times of excessive traffic, pools typically queue connection requests so that the DB is not overloaded.. users at the back of the queue experience delays, but the system churns away and generally is less likely to grind to a halt due to lack of memory and disk swapping.

选项3.每个请求的新数据库连接.在轻到中等使用的系统上，这不是一个糟糕的选择，并且通常很容易在以后升级到Pooler.但是请记住，每个数据库连接(每个页面加载)都需要打开和关闭一个数据库连接，这涉及CPU和内存分配.实际上，如果您的页面快速加载，您的用户群较小且一致，并且您的访问量相当一致，则可以正常工作.许多DEV，CAT和QA环境在没有池化程序的情况下直接连接.缺点是#1，绝对没有办法控制与DB的连接.如果查询挂起，则可能有数百个连接突然杀死数据库，有时需要重新启动或重新启动数据库才能纠正这种情况.

Option 3. New Database Connection per request. On a light to moderately utilized system, it's not a terrible option, and it's usually easy to upgrade to a pooler at a later date. Keep in mind, however, that each database connection (every page load) will require opening and closing a DB connection, which involved CPU and Memory Allocation. In practice, if your pages load quickly, your userbase is small and consistent, and your traffic is fairly consistent, it can work just fine. Many DEV, CAT and QA environments are connected directly without a pooler. The disadvantage is #1, there is absolutely no way to control connections to the DB. If queries hang, you can have hundreds of connections suddenly killing your DB and sometimes a reboot or restart of the DB is required to rectify the situation.

例如:您在网站的首页上写了1条错误的查询，这导致它需要3​​秒钟而不是0.3秒钟才能运行.最终，您的网站在任何时刻运行1-2页，现在可能会增加到10-20.现在，这10到20页= 10到20个DB连接不断打开和关闭，平均有10到20个打开.此问题将逐渐蔓延，使用越来越多的内存，直到达到连接限制为止(或更糟的是，耗尽所有内存，现在所有内容都在交换).此时，一切都停止了.

For example: You write 1 bad query that's on the homepage of your site, that causes it to take 3 seconds to run instead of .3 seconds. Eventually, your site that had 1-2 pages running at any one moment, now may bump up to 10-20. Now those 10-20 pages = 10-20 DB connections opening and closing constantly, with 10-20 being open on average. This problem will creep up, using more and more memory until you reach the connection limit (or worse, use up all memory and everythings now swapping). At this point, everything's ground to a halt.

请记住，连接占用了数据库和应用程序服务器/池上的资源.在大多数情况下，当数据库分页到磁盘时，如果不进行任何重置就无法进行正常恢复，所有希望都将落空-显然，这是有可能发生的，但是如果没有代码修复，您通常会重新启动以给自己更多的时间，直到不可避免地出现问题为止再次发生，希望到那时，您已经找到了错误的查询或错误的配置并进行了修复.

Keep in mind that connections take up resources both on the DB and the app server/pooler. When your DB is paging to disk, most of the time, all hope is lost for a graceful recovery without resetting something-- Obviously it can happen, but without a code fix, you usually reboot to give yourself some more time until the problem inevitibly happens again, and hopefully by that time, you've found the bad query, or bad config and fixed it.

选项2为您提供最多的选项.通常这不是管理上的麻烦，但是如果您在一台机器上全部运行，则收益是有限的.如果您至少有两台计算机(应用程序服务器和数据库服务器)，这是一个简单的解决方案，通常可以防止许多系统过载.

Option 2 gives you the most options. It's usually not a management headache, but if you're running it all on 1 machine, the benefits are limited. If you have at least 2 machines (app server and DB server), it's an easy solution that usually prevents many system overloads.

这篇关于Web应用程序中的数据库连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！