如何增加postgres中的最大连接数? [英] How to increase the max connections in postgres?

问题描述

我正在为我的产品使用Postgres DB.使用slick 3进行批量插入时，出现一条错误消息:

I am using Postgres DB for my product. While doing the batch insert using slick 3, I am getting an error message:

org.postgresql.util.PSQLException:FATAL:对不起，已经有太多客户端了.

org.postgresql.util.PSQLException: FATAL: sorry, too many clients already.

我的批量插入操作将超过数千条记录. 我的postgres的最大连接数为100.

My batch insert operation will be more than thousands of records. Max connection for my postgres is 100.

如何增加最大连接数?

推荐答案

仅增加max_connections是个坏主意.您还需要增加shared_buffers和kernel.shmmax.

Just increasing max_connections is bad idea. You need to increase shared_buffers and kernel.shmmax as well.

注意事项

max_connections确定与数据库服务器的并发连接的最大数目.默认值通常为100个连接.

max_connections determines the maximum number of concurrent connections to the database server. The default is typically 100 connections.

在增加连接数之前，您可能需要扩展部署.但是在此之前，您应该考虑是否真的需要增加连接限制.

Before increasing your connection count you might need to scale up your deployment. But before that, you should consider whether you really need an increased connection limit.

每个PostgreSQL连接都会消耗RAM来管理连接或使用该连接的客户端.您拥有的连接越多，将使用的RAM就会更多，而可以用来运行数据库.

一个编写良好的应用程序通常不需要大量的连接.如果您的应用程序确实需要大量连接，请考虑使用诸如 pg_bouncer 之类的工具.可以为您合并连接.由于每个连接都消耗RAM，因此您应该尽量减少它们的使用.

A well-written app typically doesn't need a large number of connections. If you have an app that does need a large number of connections then consider using a tool such as pg_bouncer which can pool connections for you. As each connection consumes RAM, you should be looking to minimize their use.

如何增加最大连接数

1.增加max_connection和shared_buffers

1. Increase max_connection and shared_buffers

在/var/lib/pgsql/{version_number}/data/postgresql.conf

更改

max_connections = 100
shared_buffers = 24MB

到

max_connections = 300
shared_buffers = 80MB

shared_buffers配置参数确定专用于PostgreSQL的内存 用于缓存数据.

• 如果您的系统具有1GB或更大的RAM，那么合理的启动时间 shared_buffers的值是系统内存的1/4.
• 您不太可能会发现使用40％以上的RAM来更好地工作 比少量(例如25％)
• 请注意，如果您的系统或PostgreSQL构建是32位的，则可能 将shared_buffers设置为2〜2.5GB以上是不现实的.
• 请注意，在Windows上，shared_buffers的较大值不如 有效，您可能会发现将其保持在较低水平的更好结果 并更多地使用操作系统缓存.在Windows上，有用范围是 64MB至512MB .

• If you have a system with 1GB or more of RAM, a reasonable starting value for shared_buffers is 1/4 of the memory in your system.
• it's unlikely you'll find using more than 40% of RAM to work better than a smaller amount (like 25%)
• Be aware that if your system or PostgreSQL build is 32-bit, it might not be practical to set shared_buffers above 2 ~ 2.5GB.
• Note that on Windows, large values for shared_buffers aren't as effective, and you may find better results keeping it relatively low and using the OS cache more instead. On Windows the useful range is 64MB to 512MB.

2.更改kernel.shmmax

您需要将内核最大段大小增加到稍大 比shared_buffers.

You would need to increase kernel max segment size to be slightly larger than the shared_buffers.

在文件/etc/sysctl.conf中，如下所示设置参数.重新启动postgresql后，该命令才会生效(以下行将内核的最大值设置为96Mb)

In file /etc/sysctl.conf set the parameter as shown below. It will take effect when postgresql reboots (The following line makes the kernel max to 96Mb)

kernel.shmmax=100663296

---

参考

Postgres最大连接数和共享缓冲区

调整PostgreSQL服务器

这篇关于如何增加postgres中的最大连接数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！