Odp: Re: semop hanging - Postgres 9.4.4

Hi Sagar,

> 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?

Thanks for you reply.

Server has 16GB of RAM. Kernel shmmax has strange value, i think it's default on Ubuntu - i haven't changed that.

kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096

I changed kermel.sem from defaults to: 250 512000 100 2048

There is no zombi nor defunct process. Now i have 3 stucked processes.

First:

psql01:~# strace -ffp 14135
Process 14135 attached
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
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
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
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
semop(58261738, {{12, -1, 0}}, 1) = 0
semop(58261738, {{12, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)

Second:

psql01:~# strace -ffp 12712
Process 12712 attached
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
semop(57737434, {{6, 1, 0}}, 1) = 0
semop(57934048, {{2, -1, 0}}, 1) = 0
semop(57802972, {{7, 1, 0}}, 1) = 0
semop(57934048, {{2, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
semop(57934048, {{2, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
semop(57934048, {{2, -1, 0}}, 1) = 0
semop(57934048, {{2, -1, 0}}, 1) = 0

Third:

psql02:~# strace -ffp 18283
Process 18283 attached
semop(58523890, {{11, 1, 0}}, 1) = 0
semop(57802972, {{9, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
semop(57802972, {{9, -1, 0}}, 1) = 0
semop(58818811, {{13, 1, 0}}, 1) = 0
semop(57802972, {{9, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
semop(57802972, {{9, -1, 0}}, 1) = 0
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)

perf top:

17.04% postgres [.] _bt_moveright
13.39% postgres [.] LWLockAcquire
8.82% postgres [.] LWLockRelease
8.11% postgres [.] _bt_checkpage
6.10% postgres [.] hash_search_with_hash_value
4.35% libc-2.19.so [.] __strcoll_l
3.16% postgres [.] 0x00000000002989ec
2.06% postgres [.] s_lock
1.99% postgres [.] _bt_compare
1.49% postgres [.] hash_any
1.41% postgres [.] 0x0000000000298a10
1.14% postgres [.] varstr_cmp
1.07% [kernel] [k] _raw_spin_unlock_irqrestore
1.00% libc-2.19.so [.] strlen
0.99% postgres [.] 0x0000000000298fdb
0.96% postgres [.] 0x00000000002989fc
0.80% postgres [.] LockBuffer
0.73% libc-2.19.so [.] 0x000000000009478e
0.60% postgres [.] ReadBufferExtended
0.49% postgres [.] bttextcmp
0.47% libc-2.19.so [.] 0x0000000000094787
0.44% [kernel] [k] finish_task_switch
0.40% postgres [.] _bt_relandgetbuf
0.40% postgres [.] ResourceOwnerForgetBuffer
0.40% libc-2.19.so [.] 0x0000000000094782
0.39% postgres [.] FunctionCall2Coll
0.38% postgres [.] ReleaseAndReadBuffer
0.31% postgres [.] pg_detoast_datum_packed

Best regards,
Michal