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.
我对启用它的可能性,如果一些代码做了一些状态数据库连接和(可能错误地)不收拾自己,那种状态,通常会得到清理后关闭连接相反会传播到后续代码循环连接。
5 个解决方案
#1
10
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的连接池在回签时在连接上发出connector .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.
您可以快速做一些钳工加工的连接池与非SQLA应用程序通过改变池实现从默认QueuePool NullPool,这是一个没有任何实际池的池实现——它连接和断开真正获得代理连接时,随后关闭。
#2
6
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 lot of 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.
当有疑问时，benchmark—但是我大体上相信连接池库(至少是一个有信誉的库)应该正确工作并适当地重置。
#3
2
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是一种依赖于关闭线程来丢弃临时表或回滚事务的代码，它在池中会遇到很多问题。
#4
0
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。每次执行数据库查询时都要连接对象。我将Tomcat连接池用于mysql数据库，用于执行大量查询的web应用程序，在高用户负载期间，有明显的速度改进。
#5
0
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.
在局域网中，没有它，响应时间在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页面，用户可能不会注意到额外的时间，但是在我的情况下，这是不可接受的，所以我选择使用这个池。希望这能帮助你。
#1
10
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的连接池在回签时在连接上发出connector .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.
您可以快速做一些钳工加工的连接池与非SQLA应用程序通过改变池实现从默认QueuePool NullPool,这是一个没有任何实际池的池实现——它连接和断开真正获得代理连接时,随后关闭。
#2
6
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 lot of 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.
当有疑问时，benchmark—但是我大体上相信连接池库(至少是一个有信誉的库)应该正确工作并适当地重置。
#3
2
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是一种依赖于关闭线程来丢弃临时表或回滚事务的代码，它在池中会遇到很多问题。
#4
0
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。每次执行数据库查询时都要连接对象。我将Tomcat连接池用于mysql数据库，用于执行大量查询的web应用程序，在高用户负载期间，有明显的速度改进。
#5
0
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.
在局域网中，没有它，响应时间在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页面，用户可能不会注意到额外的时间，但是在我的情况下，这是不可接受的，所以我选择使用这个池。希望这能帮助你。