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@xxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
