I add some log I collected for this problem. My them be helpful.
The attachements are two logs and one picture.
sds_20170311_1124.bug.log
It's log I collected every 5 minutes, it contains postgres processes list, ipcs -s output and ipcs -s -u output. I delete most of the log, just keep necessary pieces.
ipcs.log
It's the output of every semaphores set used by postgresql displayed with ipcs -s -i command, you can find out many process were waited for a semapthore. The number of lines which ncount == 1 is 469
The pstack screenshot
It shows two process's stack, one is pid 186397 who were in authentication, and the other is 188832 who where in INSERT.
Forgot to mention, I can only recovery from this situation by restart postgresql service. If I killed any process blocked with kill -9 command, the service will restart.
The following bug has been logged on the website:
Bug reference: 14588
Logged by: Chenhong Liu
Email address: diabloneo@gmail.com
PostgreSQL version: 9.2.16
Operating system: CentOS 7.2 1511
Description:
We run a distributed application which storing data in pgsql. This
application is designed to do some route works during 00:00 to 6:00 every
day. I found that, postgres processes are all blocked on semop function,
which means they are waiting for sysv semaphores. I'm not sure if this is a
bug or we misconfigured pgsql and OS.
Techinque information:
30 hosts running the application.
3 hosts running the postgresql servers, one master and two host-stanby
servers.
pgsql config:
max_connections = 1000
shared_buffers = 512MB
OS kernel.sem
250 32000 32 128
During the daytime, there are about 300 postgres processes running, work as
expected. On 00:00 each day, there will be about 200 more postgresql
processes created, now totally about 500. And from that time, thoese working
processes are all blocked on semop function. I check this using pstack
command, and also check the output of ipcs -s [-i]. After this has happened,
all new connection will stay in state authentication.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
