I have some management commands that are based on gevent. Since my management command makes thousands to requests, I can turn all socket calls into non-blocking calls using Gevent. This really speeds up my application as I can make requests simultaneously.
我有一些基于gevent的管理命令。由于我的管理命令发出数千个to请求，我可以使用Gevent将所有套接字调用转换为非阻塞调用。这确实加快了我的应用程序，因为我可以同时发出请求。
Currently the bottleneck in my application seems to be Postgres. It seems that this is because the Psycopg library that is used for connecting to Django is written in C and does not support asynchronous connections.
目前，我的应用程序中的瓶颈似乎是Postgres。这似乎是因为用于连接到Django的Psycopg库是用C编写的，不支持异步连接。
I've also read that using pgBouncer can speed up Postgres by 2X. This sounds great but it would be great if someone could explain how pgBouncer works and helps?
我也读过使用pgBouncer可以将Postgres的速度提高2X。这听起来很棒，但如果有人能解释一下pgBouncer是如何工作和帮助的，那就太棒了。
Thanks
谢谢
2 个解决方案
#1
68
Besides saving the overhead of connect & disconnect where this is otherwise done on each request, a connection pooler can funnel a large number of client connections down to a small number of actual database connections. In PostgreSQL, the optimal number of active database connections is usually somewhere around ((2 * core_count) + effective_spindle_count). Above this number, both throughput and latency get worse.
除了节省连接和断开的开销之外，连接池还可以将大量的客户端连接导入到少量的实际数据库连接中。在PostgreSQL中，活动数据库连接的最佳数量通常在((2 * core_count) +有效的spindle_count)附近。超过这个数字，吞吐量和延迟都会变得更糟。
Sometimes people will say "I want to support 2000 users, with fast response time." It is pretty much guaranteed that if you try to do that with 2000 actual database connections, performance will be horrible. If you have a machine with four quad-core processors and the active data set is fully cached, you will see much better performance for those 2000 users by funneling the requests through about 35 database connections.
有时人们会说“我想要支持2000个用户，并且有快速的响应时间。”如果您尝试使用2000个实际的数据库连接来实现这一点，那么性能将非常糟糕。如果您有一台拥有4个四核处理器的机器，并且活动数据集被完全缓存，那么通过通过35个数据库连接将请求传递给这些2000个用户，您将看到更好的性能。
To understand why that is true, this thought experiment should help. Consider a hypothetical database server machine with only one resource to share -- a single core. This core will time-slice equally among all concurrent requests with no overhead. Let's say 100 requests all come in at the same moment, each of which needs one second of CPU time. The core works on all of them, time-slicing among them until they all finish 100 seconds later. Now consider what happens if you put a connection pool in front which will accept 100 client connections but make only one request at a time to the database server, putting any requests which arrive while the connection is busy into a queue. Now when 100 requests arrive at the same time, one client gets a response in 1 second; another gets a response in 2 seconds, and the last client gets a response in 100 seconds. Nobody had to wait longer to get a response, throughput is the same, but the average latency is 50.5 seconds rather than 100 seconds.
要理解为什么这是真的，这个思想实验应该有所帮助。假设一个数据库服务器机器只有一个资源可以共享——一个核心。这个核心将在没有开销的所有并发请求中平均分配时间。假设同时有100个请求，每个请求都需要1秒的CPU时间。他们的核心工作是所有的，时间切片，直到他们全部完成100秒之后。现在考虑一下，如果在前面放置一个连接池，该连接池将接受100个客户端连接，但每次只向数据库服务器发出一个请求，将在连接繁忙时到达的任何请求放入队列。当100个请求同时到达时，一个客户在1秒内得到响应;另一个在2秒内得到响应，最后一个客户机在100秒内得到响应。没有人需要等待更长时间才能得到响应，吞吐量是相同的，但是平均延迟是50.5秒而不是100秒。
A real database server has more resources which can be used in parallel, but the same principle holds, once they are saturated, you only hurt things by adding more concurrent database requests. It is actually worse than the example, because with more tasks you have more task switches, increased contention for locks and cache, L2 and L3 cache line contention, and many other issues which cut into both throughput and latency. On top of that, while a high work_mem setting can help a query in a number of ways, that setting is the limit per plan node for each connection, so with a large number of connections you need to leave this very small to avoid flushing cache or even leading to swapping, which leads to slower plans or such things as hash tables spilling to disk.
一个真正的数据库服务器有更多的资源可以并行使用，但同样的原则是，一旦资源饱和，添加更多的并发数据库请求只会损害事情。它实际上比示例更糟糕，因为随着任务的增加，您将拥有更多的任务交换机、增加对锁和缓存的争用、L2和L3缓存线争用，以及许多其他问题，这些问题都会降低吞吐量和延迟。除此之外,而高work_mem设置可以以多种方式查询,设置限制/计划节点为每个连接,所以有大量的连接你需要离开这个非常小,以避免刷新缓存,甚至导致交换,从而导致较慢的计划或诸如哈希表溢出到磁盘。
Some database products effectively build a connection pool into the server, but the PostgreSQL community has taken the position that since the best connection pooling is done closer to the client software, they will leave it to the users to manage this. Most poolers will have some way to limit the database connections to a hard number, while allowing more concurrent client requests than that, queuing them as necessary. This is what you want, and it should be done on a transactional basis, not per statement or connection.
一些数据库产品有效地将连接池构建到服务器中，但是PostgreSQL社区采取的立场是，由于最好的连接池是在离客户端软件更近的地方进行的，因此将由用户来管理。大多数池处理程序都有一些方法来限制数据库连接到一个硬数字，同时允许更多并发客户端请求，必要时对它们进行排队。这是您想要的，并且应该在事务基础上完成，而不是每个语句或连接。
#2
9
PgBouncer reduces the latency in establishing connections by serving as a proxy which maintains a connection pool. This may help speed up your application if you're opening many short-lived connections to Postgres. If you only have a small number of connections, you won't see much of a win.
PgBouncer通过充当维护连接池的代理来减少建立连接的延迟。如果打开了许多到Postgres的短期连接，这可能有助于加快应用程序的速度。如果你只有少量的人脉，你就不会看到什么胜利。
#1
68
Besides saving the overhead of connect & disconnect where this is otherwise done on each request, a connection pooler can funnel a large number of client connections down to a small number of actual database connections. In PostgreSQL, the optimal number of active database connections is usually somewhere around ((2 * core_count) + effective_spindle_count). Above this number, both throughput and latency get worse.
除了节省连接和断开的开销之外，连接池还可以将大量的客户端连接导入到少量的实际数据库连接中。在PostgreSQL中，活动数据库连接的最佳数量通常在((2 * core_count) +有效的spindle_count)附近。超过这个数字，吞吐量和延迟都会变得更糟。
Sometimes people will say "I want to support 2000 users, with fast response time." It is pretty much guaranteed that if you try to do that with 2000 actual database connections, performance will be horrible. If you have a machine with four quad-core processors and the active data set is fully cached, you will see much better performance for those 2000 users by funneling the requests through about 35 database connections.
有时人们会说“我想要支持2000个用户，并且有快速的响应时间。”如果您尝试使用2000个实际的数据库连接来实现这一点，那么性能将非常糟糕。如果您有一台拥有4个四核处理器的机器，并且活动数据集被完全缓存，那么通过通过35个数据库连接将请求传递给这些2000个用户，您将看到更好的性能。
To understand why that is true, this thought experiment should help. Consider a hypothetical database server machine with only one resource to share -- a single core. This core will time-slice equally among all concurrent requests with no overhead. Let's say 100 requests all come in at the same moment, each of which needs one second of CPU time. The core works on all of them, time-slicing among them until they all finish 100 seconds later. Now consider what happens if you put a connection pool in front which will accept 100 client connections but make only one request at a time to the database server, putting any requests which arrive while the connection is busy into a queue. Now when 100 requests arrive at the same time, one client gets a response in 1 second; another gets a response in 2 seconds, and the last client gets a response in 100 seconds. Nobody had to wait longer to get a response, throughput is the same, but the average latency is 50.5 seconds rather than 100 seconds.
要理解为什么这是真的，这个思想实验应该有所帮助。假设一个数据库服务器机器只有一个资源可以共享——一个核心。这个核心将在没有开销的所有并发请求中平均分配时间。假设同时有100个请求，每个请求都需要1秒的CPU时间。他们的核心工作是所有的，时间切片，直到他们全部完成100秒之后。现在考虑一下，如果在前面放置一个连接池，该连接池将接受100个客户端连接，但每次只向数据库服务器发出一个请求，将在连接繁忙时到达的任何请求放入队列。当100个请求同时到达时，一个客户在1秒内得到响应;另一个在2秒内得到响应，最后一个客户机在100秒内得到响应。没有人需要等待更长时间才能得到响应，吞吐量是相同的，但是平均延迟是50.5秒而不是100秒。
A real database server has more resources which can be used in parallel, but the same principle holds, once they are saturated, you only hurt things by adding more concurrent database requests. It is actually worse than the example, because with more tasks you have more task switches, increased contention for locks and cache, L2 and L3 cache line contention, and many other issues which cut into both throughput and latency. On top of that, while a high work_mem setting can help a query in a number of ways, that setting is the limit per plan node for each connection, so with a large number of connections you need to leave this very small to avoid flushing cache or even leading to swapping, which leads to slower plans or such things as hash tables spilling to disk.
一个真正的数据库服务器有更多的资源可以并行使用，但同样的原则是，一旦资源饱和，添加更多的并发数据库请求只会损害事情。它实际上比示例更糟糕，因为随着任务的增加，您将拥有更多的任务交换机、增加对锁和缓存的争用、L2和L3缓存线争用，以及许多其他问题，这些问题都会降低吞吐量和延迟。除此之外,而高work_mem设置可以以多种方式查询,设置限制/计划节点为每个连接,所以有大量的连接你需要离开这个非常小,以避免刷新缓存,甚至导致交换,从而导致较慢的计划或诸如哈希表溢出到磁盘。
Some database products effectively build a connection pool into the server, but the PostgreSQL community has taken the position that since the best connection pooling is done closer to the client software, they will leave it to the users to manage this. Most poolers will have some way to limit the database connections to a hard number, while allowing more concurrent client requests than that, queuing them as necessary. This is what you want, and it should be done on a transactional basis, not per statement or connection.
一些数据库产品有效地将连接池构建到服务器中，但是PostgreSQL社区采取的立场是，由于最好的连接池是在离客户端软件更近的地方进行的，因此将由用户来管理。大多数池处理程序都有一些方法来限制数据库连接到一个硬数字，同时允许更多并发客户端请求，必要时对它们进行排队。这是您想要的，并且应该在事务基础上完成，而不是每个语句或连接。
#2
9
PgBouncer reduces the latency in establishing connections by serving as a proxy which maintains a connection pool. This may help speed up your application if you're opening many short-lived connections to Postgres. If you only have a small number of connections, you won't see much of a win.
PgBouncer通过充当维护连接池的代理来减少建立连接的延迟。如果打开了许多到Postgres的短期连接，这可能有助于加快应用程序的速度。如果你只有少量的人脉，你就不会看到什么胜利。