Re: semop hanging - Postgres 9.4.4

Hi Michal,

Sometimes terminating a query via normal methods of pg_cancel_backend() and
pg_terminate_backend() fail and additional steps need to be taken.

ps -eflyCpostgres |grep PID(stuck pid)

OS level kill is not recommended but we don't have alternate solution in
some scenarios we need to from OS
kill -11 'stuck pid'

You could decide shmmax depending upon the size of DB. We recommend to set
shmmax to 6 GB and increase shared_buffers size to 2 GB.

Best regards,
Sagar(DBA)
Shreeyansh Technologies.

On Wed, Dec 16, 2015 at 4:44 PM, Michał Nowak <minowack(at)wp(dot)pl> wrote:

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