科学地增加postgresql最大连接数

本文详细介绍了如何查看和调整PostgreSQL的最大连接数,包括修改配置文件、调整内核参数以及考虑增加shared_buffers大小的方法。同时,文章强调了盲目增加连接数可能带来的负面影响,并建议使用如pg_bouncer等工具来优化连接池。
摘要由CSDN通过智能技术生成

PG配置文件路径

/etc/postgresql/9.3/main/postgresql.conf

首先如何查看最大连接数

This SQL will help you

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) t1,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) t2,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) t3

Result:

max_conn | used | res_for_super | res_for_normal 
---------+------+---------------+----------------
  100    |    2 |             3 |             95
(1 row)

You can put this in shell:

#!/bin/bash
for (( c=1; c<=3600; c++ ))
do
     gsql -U pgdba -W pgdba -p 6432 -c "sql" >> /home/pgdba/res_data.log
     sleep 1  # once per second
done

or you can record the results into a table, then execute

postgres=# copy restbl to '/home/pgdba/res.csv' csv header;

to get result csv file.

其次如何科学设置

https://stackoverflow.com/a/32584211/2803340

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


Considerations

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.

Each PostgreSQL connection consumes RAM for managing the connection or the client using it. The more connections you have, the more RAM you will be using that could instead be used to run the database.

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.


How to increase max connections

1. Increase max_connection and shared_buffers

in /var/lib/pgsql/data/postgresql.conf

change

max_connections = 100
shared_buffers = 24MB

to

max_connections = 300
shared_buffers = 80MB

The shared_buffers configuration parameter determines how much memory is dedicated to PostgreSQL to use for caching data.

  • 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. Change kernel.shmmax

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

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

References

How to increase max connections in postgres

Postgres Max Connections And Shared Buffers

Tuning Your PostgreSQL Server

转载于:https://www.cnblogs.com/xiangnan/p/10051240.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值