Re: 9.2.2 - semop hanging - Mailing list pgsql-admin

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>