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
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.
max_connections is bad idea. You need to increase
kernel.shmmax as well.
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
max_connections = 100 shared_buffers = 24MB
max_connections = 300 shared_buffers = 80MB
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
/etc/sysctl.conf set the parameter as shown below. It will take effect when
postgresql reboots (The following line makes the kernel max to
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 email@example.com 举报，一经查实，本站将立刻删除。