Re[2]: [PERFORM] SELECT outage in semop

Hello Albe,

Here are changes that were made on the postgresql.conf from the default configuration:

max_connections = 200

shared_buffers = 129215MB
work_mem = 256MB

maintenance_work_mem = 512MB

vacuum_cost_delay = 70
vacuum_cost_limit = 30
wal_level = hot_standby  (system sends data to 1 slave server using hot standby streaming replication. Problem still observed when there is no replication running)
wal_buffers = 2MB

commit_delay = 500
checkpoint_segments = 256
wal_keep_segments = 512
enable_seqscan = off
effective_cache_size = 258430MB
max_locks_per_transaction = 128

In general system has write queries also but this daemon runs read only queries.

It aquired near the 20-30 ACCESS SHARE locks per query so the only way to lock them would be Exclusive lock.
There is no explicit exclusive locks in the application.

During the problem LA 0.1 - 2 
No iowait. 

Also interesting point i have setted up monitoring daemon that runs select from pg_stat_activity and from pg_locks each half a second and during the time i observer the problem daemon was not able to run those queries also - only after semop timeout.

Fri, 30 May 2014 15:19:05 +0000 от Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:
>Сурен Арустамян wrote:
>
>> I'm using postgresql 9.3.4 on Red Hat Enterprise Linux Server release 6.5 (Santiago)
>>
>> Linux 193-45-142-74 2.6.32-431.17.1.el6.x86_64 #1 SMP Fri Apr 11 17:27:00 EDT 2014 x86_64 x86_64
>> x86_64 GNU/Linux
>>
>> Server specs:
>> 4x Intel(R) Xeon(R) CPU E7- 4870 @ 2.40GHz (40 physical cores in total)
>>
>>
>> 441 GB of RAM
>>
>> I have a schema when multi process daemon is setted up on the system and each process holds 1
>> postgresql session.
>>
>> Each process of this daemon run readonly queries over the database.
>> In normal situation it at most 35 ms for queries but from time to time (at a random point of time)
>> each database session hanges in some very strange semop call. Here is a part of the strace:
>
>[...]
>
>> 41733 20:15:09.682507 semop(393228, {{0, -1, 0}}, 1) = 0 <2.080439>
>
>[...]
>
>> You may see that semop took 2 seconds from the whole system call.
>> Same semops could be find in other database sessions.
>>
>> Could you point me how can i find
>
>What is your PostgreSQL configuration?
>
>Is your database workload read-only?
>If not, could these be locks?
>You could set log_lock_waits and see if anything is logged.
>
>Anything noteworthy in the database server log?
>How busy is the I/O system and the CPU when this happens?
>
>Yours,
>Laurenz Albe
>
>--
>Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>

Best Regards,
Suren Arustamyan
suren-a(at)inbox(dot)ru