From: Suren Arustamyan <suren-a@inbox.ru>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re[2]: [PERFORM] SELECT outage in semop
Date: Sat, 31 May 2014 13:03:30 +0400
Message-ID: <1401527010.152645996@f205.i.mail.ru> (raw)
In-Reply-To: <A737B7A37273E048B164557ADEF4A58B17CFF4FE@ntex2010i.host.magwien.gv.at>
References: <1401461713.227724134@f426.i.mail.ru>
<A737B7A37273E048B164557ADEF4A58B17CFF4FE@ntex2010i.host.magwien.gv.at>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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.albe@wien.gv.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@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-performance
>
Best Regards,
Suren Arustamyan
suren-a@inbox.ru
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: suren-a@inbox.ru, laurenz.albe@wien.gv.at
Subject: Re: Re[2]: [PERFORM] SELECT outage in semop
In-Reply-To: <1401527010.152645996@f205.i.mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox