TransWikia.com

How to increase max_connections in Postgresql-9.2

Database Administrators Asked by JineshJK on December 10, 2021

I need to increase production Postgresql-9.2 max_connections parameter from current 1000 to 3000.
Used PGTune service to get the values and updated accordingly to the postgresql.conf file and /etc/sysctl.conf.

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

# DB Version: 9.2
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 16
# Connections num: 3000
# Data Storage: ssd

max_connections = 3000
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2796kB
checkpoint_segments = 3

/etc/sysctl.conf

kernel.shmmax=17179869184
kernel.shmall=4194304

After this modification, Postgresql service failed to start with semaphore error.
As I dont have any idea on how to update this value with respect to OS/HW spec, updated with some value set from internet (kernel.sem = 250 32111 32 128). Still Postgresql service failing to start with following error messages,

FATAL: could not create semaphores: No space left on device DETAIL:
Failed system call was semget(5432129, 17, 03600). HINT: This error
does not mean that you have run out of disk space. It occurs when
either the system limit for the maximum number of semaphore sets
(SEMMNI), or the system wide maximum number of semaphores (SEMMNS),
would be exceeded. You need to raise the respective kernel parameter.
Alternatively, reduce PostgreSQL’s consumption of semaphores by
reducing its max_connections parameter.

Need help on setting semaphore values in order to enable 3000 concurrent connections to the DB.
What are the parameters to be considered for this?

System spec

  • OS: Centos-7.4
  • CPU: 16 core
  • RAM: 32GB
  • Volume: 450GB SSD
  • Swap: 16GB SSD
  • No. of DBs: 3
  • Total DB base size: 290GB

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP