Re: semop hanging - Postgres 9.4.4

On Wed, Dec 16, 2015 at 1:04 AM, Michał Nowak <minowack(at)wp(dot)pl> wrote:

> Hello,
>
> Since couple of days on my Postgres cluster i've seen few times a hanged
> process. It looks like one of postgres workers stuck on simple INSERT query.
>
> PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
> 12173 postgres 20 0 1372M 345M run 166:18 24.47% 99.90% postgres:
> db01 db01 10.0.0.101(58713) INSERT
>
>
> It utilizes 100% of one CPU and after 1-2 hours it starts to utilize
> memory. After another 1-2 hours OOM-Killer have to take action. At the same
> time other postgres workers are properly processing queries.
>
> There's no error on syslog/dmesg. Strace:
>
>
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58851580, {{0, 1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(57802972, {{1, 1, 0}}, 1) = 0
> select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
> semop(58261738, {{12, -1, 0}}, 1) = 0
>
>
> Is this related to incorrect semaphores configuration?
>
>
>
> Here's my current configuration:
>
> sql01:~# ipcs -l
>
> ------ Shared Memory Limits --------
> max number of segments = 4096
> max seg size (kbytes) = 18014398509465599
> max total shared memory (kbytes) = 18446744073642442748
> min seg size (bytes) = 1
>
> ------ Semaphore Limits --------
> max number of arrays = 2048
> max semaphores per array = 250
> max semaphores system wide = 512000
> max ops per semop call = 100
> semaphore max value = 32767
>
> ------ Messages Limits --------
> max queues system wide = 32000
> max size of message (bytes) = 8192
> default max size of queue (bytes) = 16384
>
>
> sql01:~# ipcs -u
>
> ------ Shared Memory Status --------
> segments allocated 1
> pages allocated 1
> pages resident 1
> pages swapped 0
> Swap performance: 0 attempts 0 successes
>
> ------ Semaphore Status --------
> used arrays = 257
> allocated semaphores = 4369
>
> ------ Messages Status --------
> allocated queues = 0
> used headers = 0
> used space = 0 bytes
>
>
> Some values from postgresql.conf:
> max_connections = 4096
> effective_cache_size = 12GB
> work_mem = 16MB
> shared_buffers = 1GB
>
>
> postgres=# select version();
> version
>
> ------------------------------------------------------------------------------------------------------
> PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
> 4.8.2-19ubuntu1) 4.8.2, 64-bit
>
>
> I would appreciate any help.
>
> Best Regards,
>
> Marcin
>
>
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Hi Miachal,

May I know what is the RAM of server and how much shmmax is configured ?
You can check if any Zombi or defunct process are running on the server.
I want to know if any maintenance activity performed same time?

Regards,
Sagar Jadhav
Database Administrator
www.shreeyansh.com