Обсуждение: 9.2.2 - semop hanging

On Mon, 15 Jul 2013 10:16:19 -0300
Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

I'm not a Linux expert, I'm a BSD man, but

a) do you have an interrupt storm?
b) what does postgres do before the hang?
c) do you have anyother software running? Including contrib modules. It may be a dns lookup timeout (watch port 53
tcp/udp)
d) what filesystem? Needs filesystem some kind of "maintenance window"? I mean flush dirty caches, metadata, wake up
barriers...


---   ---
Eduardo Morras <emorrasg@yahoo.es>

On Mon, 15 Jul 2013 10:16:19 -0300
Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

I'm not a Linux expert, I'm a BSD man, but 

a) do you have an interrupt storm?
b) what does postgres do before the hang?
c) do you have anyother software running? Including contrib modules. It may be a dns lookup timeout (watch port 53 tcp/udp)
d) what filesystem? Needs filesystem some kind of "maintenance window"? I mean flush dirty caches, metadata, wake up barriers...


---   ---
Eduardo Morras <emorrasg@yahoo.es>

Max seg size, max total shared memory  represents SHMMAX and SHMALL respectively.

Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

> PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
> (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit

> CentOS release 6.3 (Final)

> Since 2 weeks I'm get stucked in a very strange situation: from
> time to time (sometimes with intervals less than 10 minutes), the
> server get "stucked"/"hang" (I dont know how to call it) and
> every connections on postgres (dont matter if it's SELECT,
> UPDATE, DELETE, INSERT, startup, authentication...) seems like
> get "paused"; after some seconds (say ~10 or ~15 sec, sometimes
> less) everything "goes OK".

During these episodes, do you see high system CPU time?  If so, try
disabling transparent huge page support, and see whether it affects
the frequency or severity of the episodes.

> So, my first trial was to check disks. Running "iostat"
> apparently showed that disks was OK.

Did you run iostat during an episode of slowness?  What did it
show?  Giving an interpretation that it as "apparently OK" doesn't
provide much useful information.

> It's a Raid10, 4 600GB SAS, IBM Storage DS3512, over FC. IBM DS
> Storage Manager says that disks is OK.

Are there any reports to show you when writing was saturated?

>              total       used       free     shared    buffers    cached
> Mem:        145182     130977      14204          0         43    121407
> -/+ buffers/cache:       9526     135655
> Swap:         6143         65       6078

> Following is what I've tried:
> 1) Emre Hasegeli has suggested to reduce my shared buffers, but
> it's already low:
>   total server memory: 141 GB
>   shared_buffers: 16 GB

On a machine with nearly twice that RAM, I've had to decrease
shared_buffers to 2GB to avoid the symptoms you describe.  That is
in conjunction with making the background writer more aggressive
and making sure the checkpoint completion target is set to 0.9.

> Maybe it's too low? I've been thinking to increase to 32 GB.

Well, you could try that; if the symptoms get worse, then you might
be willing to go the other direction....

> max_connections = 500 and ~400 connections average

How many cores (not "hardware threads") does the machine have?  You
will probably have better throughput and latency if you use
connection pooling to limit the number of active database
transactions to somewhere arount two times the number of cores, or
slightly above that.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

> Yeah, disabling THP seens to lower the severity of the situation.
> Thanks. Right now is about 1 hour without any episode.

> Googling I've found that others had the same problem, and
> resolved disabling THP. Is it the right way?

That is the only way to correct this problem when it appears that I
am aware of.  I have seen recommendations to disable THP defrag
instead, but where I have seen people do that, they wound up
entirely disabling THP support later.

Huge pages should benefit performance in general, but some
implementations seem to have problems.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, 16 Jul 2013 10:08:49 -0300
Rafael Domiciano <rafael.domiciano@gmail.com> wrote:

> First of all, Thanks for response, answers below.
>
>
> On Mon, Jul 15, 2013 at 4:12 PM, Kevin Grittner <kgrittn@ymail.com> wrote:
>
> > Rafael Domiciano <rafael.domiciano@gmail.com> wrote:
> >
> > > PostgreSQL 9.2.2 on x86_64-unknown-linux-gnu, compiled by gcc
> > > (GCC) 4.4.6 20120305 (Red Hat 4.4.6-4), 64-bit
> >
> > > CentOS release 6.3 (Final)
> >
> > > Since 2 weeks I'm get stucked in a very strange situation: from
> > > time to time (sometimes with intervals less than 10 minutes), the
> > > server get "stucked"/"hang" (I dont know how to call it) and
> > > every connections on postgres (dont matter if it's SELECT,
> > > UPDATE, DELETE, INSERT, startup, authentication...) seems like
> > > get "paused"; after some seconds (say ~10 or ~15 sec, sometimes
> > > less) everything "goes OK".
> >
> > During these episodes, do you see high system CPU time?  If so, try
> > disabling transparent huge page support, and see whether it affects
> > the frequency or severity of the episodes.
> >
> Yeah, disabling THP seens to lower the severity of the situation. Thanks.
> Right now is about 1 hour without any episode.
> Same problem here and same resolution:
> http://dba.stackexchange.com/questions/32890/postgresql-pg-stat-activity-shows-commit
> .
>
> Googling I've found that others had the same problem, and resolved
> disabling THP. Is it the right way?

Why don't try to configure THP for your needs? It looks like the transparent manager tries to defrag memory when it's
toolate to do it fast because there's not enough free memory, and that it may have a default configuration bad for you.
Afast search in postgresql source code gets no MADV_HUGEPAGE, so THP can't be set to process those pages only and must
beconfigured for all system. 

Set THP on, #echo always >/sys/kernel/mm/transparent_hugepage/enabled

get the values from and try to find and set better values:

/sys/kernel/mm/transparent_hugepage/khugepaged/pages_to_scan (how many pages should scan at each pass)
/sys/kernel/mm/transparent_hugepage/khugepaged/scan_sleep_millisecs (how many milisecs spend each pass)
/sys/kernel/mm/transparent_hugepage/khugepaged/alloc_sleep_millisecs (how many milisecs between pass)

khugepaged daemon is similar to our autovaccum, it should be "easy" find a local optimun values

alternatively you can disable the defrag, but this way you will end losing a lot of memory in due to fragmentation:

#echo never >/sys/kernel/mm/transparent_hugepage/defrag

Once the values are setted, Postgres must be restarted.

> About the disks activity, my parameter is the test that was did when the
> storage was installed/configured. At that test iostat was around ~600 tps.
> In my episodes tps was around ~300 tps.
>
> The Processors is 2x Intel Xeon E5-2690, giving a total of 32 threads.
>
> About shared_buffers, I going to try different values and test.
>
> Thanks,
> Rafael Domiciano

--
Eduardo <emorrasg@yahoo.es>