python Mysql 连接池问题：值得吗？

Mysql connection pooling question: is it worth it?

提问by ???u

I recall hearing that the connection process in mysql was designed to be very fast compared to other RDBMSes, and that therefore using a library that provides connection pooling(SQLAlchemy) won't actually help you that much if you enable the connection pool.

我记得听说与其他 RDBMS 相比，mysql 中的连接过程设计得非常快，因此如果启用连接池，使用提供连接池(SQLAlchemy)的库实际上不会对您有多大帮助。

Does anyone have any experience with this?

有人对这个有经验么？

I'm leery of enabling it because of the possibility that if some code does something stateful to a db connection and (perhaps mistakenly) doesn't clean up after itself, that state which would normally get cleaned up upon closing the connection will instead get propagated to subsequent code that gets a recycled connection.

我对启用它持怀疑态度，因为如果某些代码对 db 连接做了一些有状态的事情并且（可能是错误的）在它自己之后没有清理，那么通常会在关闭连接时被清理的状态将改为传播到获得回收连接的后续代码。

采纳答案by zzzeek

There's no need to worry about residual state on a connection when using SQLA's connection pool, unless your application is changing connectionwide options like transaction isolation levels (which generally is not the case). SQLA's connection pool issues a connection.rollback() on the connection when its checked back in, so that any transactional state or locks are cleared.

使用 SQLA 的连接池时无需担心连接上的剩余状态，除非您的应用程序正在更改连接范围的选项，如事务隔离级别（通常情况并非如此）。SQLA 的连接池在其重新签入时在连接上发出 connection.rollback()，以便清除任何事务状态或锁定。

It is possible that MySQL's connection time is pretty fast, especially if you're connecting over unix sockets on the same machine. If you do use a connection pool, you also want to ensure that connections are recycled after some period of time as MySQL's client library will shut down connections that are idle for more than 8 hours automatically (in SQLAlchemy this is the pool_recycle option).

MySQL 的连接时间可能非常快，尤其是当您通过同一台机器上的 unix 套接字进行连接时。如果您确实使用连接池，您还需要确保连接在一段时间后被回收，因为 MySQL 的客户端库将自动关闭空闲超过 8 小时的连接（在 SQLAlchemy 中，这是 pool_recycle 选项）。

You can quickly do some benching of connection pool vs. non with a SQLA application by changing the pool implementation from the default of QueuePool to NullPool, which is a pool implementation that doesn't actually pool anything - it connects and disconnects for real when the proxied connection is acquired and later closed.

通过将池实现从 QueuePool 的默认设置更改为 NullPool，您可以使用 SQLA 应用程序快速对连接池与非连接池进行一些基准测试，NullPool 是一个实际上不池化任何内容的池实现 - 当代理连接被获取然后关闭。

回答by Jon Skeet

Even if the connection part of MySQL itself is pretty slick, presumably there's still a network connection involved (whether that's loopback or physical). If you're making a lotof requests, that could get significantly expensive. It will depend (as is so often the case) on exactly what your application does, of course - if you're doing a lot of work per connection, then that will dominate and you won't gain a lot.

即使 MySQL 本身的连接部分非常流畅，大概仍然涉及网络连接（无论是环回还是物理连接）。如果您提出很多请求，那可能会变得非常昂贵。当然，这将取决于（通常是这种情况）您的应用程序究竟做了什么 - 如果您每个连接都做了很多工作，那么这将占主导地位，而您不会获得太多收益。

When in doubt, benchmark - but I would by-and-large trust that a connection pooling library (at least, a reputable one) should work properly and reset things appropriately.

如有疑问，请进行基准测试 - 但我总体上相信连接池库（至少是信誉良好的库）应该正常工作并适当地重置。

回答by staticsan

Short answer: you need to benchmark it.

简短回答：您需要对其进行基准测试。

Long answer: it depends. MySQL is fast for connection setup, so avoiding that cost is not a good reason to go for connection pooling. Where you win there is if the queries run are few and fast because then you will see a win with pooling.

长答案：这取决于。MySQL 的连接设置速度很快，因此避免这种成本并不是进行连接池的好理由。如果查询运行的次数少且速度快，那么您就会获胜，因为这样您将看到池化的胜利。

The other worry is how the application treats the SQL thread. If it does no SQL transactions, and makes no assumptions about the state of the thread, then pooling won't be a problem. OTOH, code that relies on the closing of the thread to discard temporary tables or to rollback transactions will have a lot of problems with pooling.

另一个担心是应用程序如何处理 SQL 线程。如果它不执行 SQL 事务，并且不对线程的状态做任何假设，那么池化就不会成为问题。OTOH，依赖关闭线程来丢弃临时表或回滚事务的代码在池化方面会有很多问题。

回答by Fabio Ceconello

I made a simple RESTful service with Django and tested it with and without connection pooling. In my case, the difference was quite noticeable.

我使用 Django 制作了一个简单的 RESTful 服务，并在使用和不使用连接池的情况下对其进行了测试。就我而言，差异非常明显。

In a LAN, without it, response time was between 1 and 5 seconds. With it, less than 20 ms. Results may vary, but the configuration I'm using for the MySQL & Apache servers is pretty standard low-end.

在 LAN 中，如果没有它，响应时间在 1 到 5 秒之间。有了它，不到20毫秒。结果可能会有所不同，但我用于 MySQL 和 Apache 服务器的配置是非常标准的低端配置。

If you're serving UI pages over the internet the extra time may not be noticeable to the user, but in my case it was unacceptable, so I opted for using the pool. Hope this helps you.

如果您通过 Internet 提供 UI 页面，则用户可能不会注意到额外的时间，但在我的情况下，这是不可接受的，因此我选择使用池。希望这对你有帮助。

回答by Mark

The connection pool speeds things up in that fact that you do not have create a java.sql.Connection object every time you do a database query. I use the Tomcat connection pool to a mysql database for web applications that do a lot of queries, during high user load there is noticeable speed improvement.

连接池加快了速度，因为您不必每次执行数据库查询时都创建 java.sql.Connection 对象。我使用 Tomcat 连接池连接到 mysql 数据库，用于执行大量查询的 Web 应用程序，在高用户负载期间有明显的速度提升。