I'm using PHP's PDO layer for data access in a project, and I've been reading up on it and seeing that it has good innate support for persistent DB connections. I'm wondering when/if I should use them. Would I see performance benefits in a CRUD-heavy app? Are there downsides to consider, perhaps related to security?
我正在使用PHP的PDO层在项目中进行数据访问,我一直在阅读它并发现它对持久数据库连接有很好的内在支持。我想知道何时/是否应该使用它们。我会在CRUD沉重的应用程序中看到性能优势吗?是否存在需要考虑的缺点,可能与安全性有关?
If it matters to you, I'm using MySQL 5.x.
如果对你很重要,我正在使用MySQL 5.x.
7 个解决方案
#1
60
You could use this as a rough "ruleset":
您可以将其用作粗略的“规则集”:
YES, use persistent connections, if:
是,使用持久连接,如果:
- There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.
- The database is running on another server that you are accessing over the network
- An (one) application accesses the database very often
只有少数应用程序/用户访问数据库,即您不会导致200个打开(但可能是空闲)连接,因为在同一主机上共享200个不同的用户。
数据库正在您通过网络访问的另一台服务器上运行
(一)应用程序经常访问数据库
NO, don't use persistent connections, if:
不,不要使用持久连接,如果:
- Your application only needs to access the database 100 times an hour.
- You have many webservers accessing one database server
- You're using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly. (via @Powerlord in the comments)
您的应用程序只需要每小时访问数据库100次。
您有许多Web服务器访问一个数据库服务器
你在prefork模式下使用Apache。它为每个子进程使用一个连接,可以相当快地增加。 (通过评论中的@Powerlord)
Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn't make so much difference if the database is running on the same machine, but it is still a little bit faster. However - as the name says - the connection is persistent, i.e. it stays open, even if it is not used.
使用持久连接的速度要快得多,尤其是在通过网络访问数据库时。如果数据库在同一台机器上运行,它没有太大的区别,但它仍然快一点。然而 - 正如名称所说 - 连接是持久的,即它保持打开,即使它没有被使用。
The problem with that is, that in "default configuration", MySQL only allows 1000 parallel "open channels". After that, new connections are refused (You can tweak this setting). So if you have - say - 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you'll need 2000 parallel connections to the database. That won't work.
问题是,在“默认配置”中,MySQL只允许1000个并行“开放通道”。之后,拒绝新连接(您可以调整此设置)。因此,如果你有20个Web服务器,每个客户端上有100个客户端,并且每个客户端每小时只有一个页面访问权限,那么简单的数学运算将向您显示您需要与数据库建立2000个并行连接。那不行。
Ergo: Only use it for applications with lots of requests.
Ergo:仅用于有大量请求的应用程序。
#2
9
In brief, my experience says that persistent connections should be avoided as far as possible.
简而言之,我的经验表明应尽可能避免持久连接。
Note that mysql_close is a no-operation (no-op) for connections that are created using mysql_pconnect. This means persistent connection cannot be closed by client at will. Such connection will be closed by mysqldb server when no activity occurs on the connection for duration more than wait_timeout. If wait_timeout is large value (say 30 min) then mysql db server can easily reach max_connections limit. In such case, mysql db will not accept any future connection request. This is when your pager starts beeping.
请注意,对于使用mysql_pconnect创建的连接,mysql_close是无操作(无操作)。这意味着客户端无法随意关闭持久连接。当连接上没有活动发生持续时间超过wait_timeout时,mysqldb服务器将关闭此类连接。如果wait_timeout是大值(比如30分钟),则mysql数据库服务器可以轻松达到max_connections限制。在这种情况下,mysql db将不接受任何将来的连接请求。这是你的寻呼机开始发出哔哔声的时候。
In order to avoid reaching max_connections limit, use of Persistent connection need careful balancing of following variables...
为了避免达到max_connections限制,使用Persistent连接需要仔细平衡以下变量......
1. Number of apache processes on one host
2. Total number of hosts running apache
3. wait_timout variable in mysql db server
4. max_connections variable in mysql db server
5. Number of requests served by one apache process before it is re-spawned
So, pl use persistent connection after enough deliberation. You may not want to invite complex runtime issues for a small gain that you get from persistent connection.
因此,经过充分考虑后,pl使用持久连接。您可能不希望为持久连接获得的小增益邀请复杂的运行时问题。
#3
4
Creating connections to the database is a fairly expensive operation. Persistent connections are a good idea. In the ASP.Net and Java world, we have "connection pooling", which is roughly the same thing, and also a good idea.
创建与数据库的连接是一项相当昂贵的操作。持久连接是个好主意。在ASP.Net和Java世界中,我们有“连接池”,这大致相同,也是一个好主意。
#4
3
IMO, The real answer to this question is whatever works best for you app. I would recommend you benchmark your app using both persistent and non-persistent connections.
IMO,这个问题的真正答案是最适合你的app。我建议您使用持久连接和非持久连接对您的应用进行基准测试。
Maggie Nelson @ Objectively Oriented posted about this in August and Robert Swarthout made an accompanying post with some hard numbers. Both are pretty good reads.
Maggie Nelson @ Objectively Oriented在8月份发布了这个消息,Robert Swarthout发表了一些附带一些硬数字的帖子。两者都是非常好的读物。
#5
0
In my humble opinion:
在我的愚见:
When using PHP for web development, most of your connection will only "live" for the life of the page executing. A persistant connection is going to cost you a lot of overhead as you'll have to put it in the session or some such thing.
当使用PHP进行Web开发时,大多数连接只会在页面执行期间“生效”。一个持久的连接将花费你很多开销,因为你必须把它放在会话或一些这样的事情。
99% of the time a single non-persistant connection that dies at the end of the page execution will work just fine.
99%的时间在页面执行结束时死亡的单个非持久连接将正常工作。
The other 1% of the time, you probably should not be using PHP for the app, and there is no perfect solution for you.
另外1%的时间,您可能不应该在应用程序中使用PHP,并且没有完美的解决方案。
#6
0
I was going to ask this same question but rather than ask the same question again I'll just add some information that I've found.
我打算问同样的问题,但不是再问同样的问题,我只会添加一些我发现的信息。
- Are PHP persistent connections evil ?
- Persistent Database Connections
PHP持久连接是邪恶的吗?
持久数据库连接
It is also worth noting that the newer mysqli extension does not even include the option to use persistent database connections.
值得注意的是,较新的mysqli扩展甚至不包括使用持久数据库连接的选项。
I'm still using persitent connections at the moment but plan to switch to non-persistent in the near future.
我目前仍在使用persitent连接,但计划在不久的将来切换到非持久性连接。
#7
0
In general, you'll need to use non-persistent connections sometimes, and it's nice to have a single pattern to apply to db connection design (as long as there's relatively little upside to using persistent connections in your context.)
通常,您有时需要使用非持久连接,并且将单个模式应用于数据库连接设计是很好的(只要在您的上下文中使用持久连接的优势相对较小。)
#1
60
You could use this as a rough "ruleset":
您可以将其用作粗略的“规则集”:
YES, use persistent connections, if:
是,使用持久连接,如果:
- There are only few applications/users accessing the database, i.e. you will not result in 200 open (but probably idle) connections, because there are 200 different users shared on the same host.
- The database is running on another server that you are accessing over the network
- An (one) application accesses the database very often
只有少数应用程序/用户访问数据库,即您不会导致200个打开(但可能是空闲)连接,因为在同一主机上共享200个不同的用户。
数据库正在您通过网络访问的另一台服务器上运行
(一)应用程序经常访问数据库
NO, don't use persistent connections, if:
不,不要使用持久连接,如果:
- Your application only needs to access the database 100 times an hour.
- You have many webservers accessing one database server
- You're using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly. (via @Powerlord in the comments)
您的应用程序只需要每小时访问数据库100次。
您有许多Web服务器访问一个数据库服务器
你在prefork模式下使用Apache。它为每个子进程使用一个连接,可以相当快地增加。 (通过评论中的@Powerlord)
Using persistent connections is considerable faster, especially if you are accessing the database over a network. It doesn't make so much difference if the database is running on the same machine, but it is still a little bit faster. However - as the name says - the connection is persistent, i.e. it stays open, even if it is not used.
使用持久连接的速度要快得多,尤其是在通过网络访问数据库时。如果数据库在同一台机器上运行,它没有太大的区别,但它仍然快一点。然而 - 正如名称所说 - 连接是持久的,即它保持打开,即使它没有被使用。
The problem with that is, that in "default configuration", MySQL only allows 1000 parallel "open channels". After that, new connections are refused (You can tweak this setting). So if you have - say - 20 Webservers with each 100 Clients on them, and every one of them has just one page access per hour, simple math will show you that you'll need 2000 parallel connections to the database. That won't work.
问题是,在“默认配置”中,MySQL只允许1000个并行“开放通道”。之后,拒绝新连接(您可以调整此设置)。因此,如果你有20个Web服务器,每个客户端上有100个客户端,并且每个客户端每小时只有一个页面访问权限,那么简单的数学运算将向您显示您需要与数据库建立2000个并行连接。那不行。
Ergo: Only use it for applications with lots of requests.
Ergo:仅用于有大量请求的应用程序。
#2
9
In brief, my experience says that persistent connections should be avoided as far as possible.
简而言之,我的经验表明应尽可能避免持久连接。
Note that mysql_close is a no-operation (no-op) for connections that are created using mysql_pconnect. This means persistent connection cannot be closed by client at will. Such connection will be closed by mysqldb server when no activity occurs on the connection for duration more than wait_timeout. If wait_timeout is large value (say 30 min) then mysql db server can easily reach max_connections limit. In such case, mysql db will not accept any future connection request. This is when your pager starts beeping.
请注意,对于使用mysql_pconnect创建的连接,mysql_close是无操作(无操作)。这意味着客户端无法随意关闭持久连接。当连接上没有活动发生持续时间超过wait_timeout时,mysqldb服务器将关闭此类连接。如果wait_timeout是大值(比如30分钟),则mysql数据库服务器可以轻松达到max_connections限制。在这种情况下,mysql db将不接受任何将来的连接请求。这是你的寻呼机开始发出哔哔声的时候。
In order to avoid reaching max_connections limit, use of Persistent connection need careful balancing of following variables...
为了避免达到max_connections限制,使用Persistent连接需要仔细平衡以下变量......
1. Number of apache processes on one host
2. Total number of hosts running apache
3. wait_timout variable in mysql db server
4. max_connections variable in mysql db server
5. Number of requests served by one apache process before it is re-spawned
So, pl use persistent connection after enough deliberation. You may not want to invite complex runtime issues for a small gain that you get from persistent connection.
因此,经过充分考虑后,pl使用持久连接。您可能不希望为持久连接获得的小增益邀请复杂的运行时问题。
#3
4
Creating connections to the database is a fairly expensive operation. Persistent connections are a good idea. In the ASP.Net and Java world, we have "connection pooling", which is roughly the same thing, and also a good idea.
创建与数据库的连接是一项相当昂贵的操作。持久连接是个好主意。在ASP.Net和Java世界中,我们有“连接池”,这大致相同,也是一个好主意。
#4
3
IMO, The real answer to this question is whatever works best for you app. I would recommend you benchmark your app using both persistent and non-persistent connections.
IMO,这个问题的真正答案是最适合你的app。我建议您使用持久连接和非持久连接对您的应用进行基准测试。
Maggie Nelson @ Objectively Oriented posted about this in August and Robert Swarthout made an accompanying post with some hard numbers. Both are pretty good reads.
Maggie Nelson @ Objectively Oriented在8月份发布了这个消息,Robert Swarthout发表了一些附带一些硬数字的帖子。两者都是非常好的读物。
#5
0
In my humble opinion:
在我的愚见:
When using PHP for web development, most of your connection will only "live" for the life of the page executing. A persistant connection is going to cost you a lot of overhead as you'll have to put it in the session or some such thing.
当使用PHP进行Web开发时,大多数连接只会在页面执行期间“生效”。一个持久的连接将花费你很多开销,因为你必须把它放在会话或一些这样的事情。
99% of the time a single non-persistant connection that dies at the end of the page execution will work just fine.
99%的时间在页面执行结束时死亡的单个非持久连接将正常工作。
The other 1% of the time, you probably should not be using PHP for the app, and there is no perfect solution for you.
另外1%的时间,您可能不应该在应用程序中使用PHP,并且没有完美的解决方案。
#6
0
I was going to ask this same question but rather than ask the same question again I'll just add some information that I've found.
我打算问同样的问题,但不是再问同样的问题,我只会添加一些我发现的信息。
- Are PHP persistent connections evil ?
- Persistent Database Connections
PHP持久连接是邪恶的吗?
持久数据库连接
It is also worth noting that the newer mysqli extension does not even include the option to use persistent database connections.
值得注意的是,较新的mysqli扩展甚至不包括使用持久数据库连接的选项。
I'm still using persitent connections at the moment but plan to switch to non-persistent in the near future.
我目前仍在使用persitent连接,但计划在不久的将来切换到非持久性连接。
#7
0
In general, you'll need to use non-persistent connections sometimes, and it's nice to have a single pattern to apply to db connection design (as long as there's relatively little upside to using persistent connections in your context.)
通常,您有时需要使用非持久连接,并且将单个模式应用于数据库连接设计是很好的(只要在您的上下文中使用持久连接的优势相对较小。)