Re: Poor performance after update from SLES11 SP1 to SP2

On Thu, Feb 21, 2013 at 1:59 AM, Mark Smith <smithmark662(at)gmail(dot)com> wrote:
> Software: SLES 11 SP2 3.0.58-0.6.2-default x86_64, PostgreSQL 9.0.4.

[skipped]

> Problem: We have been running PostgreSQL 9.0.4 on SLES11 SP1, last kernel in
> use was 2.6.32-43-0.4, performance has always been great. Since updating
> from SLES11 SP1 to SP2 we now experience many database 'stalls' (e.g.
> normally 'instant' queries taking many seconds, any query will be slow, just
> connecting to the database will be slow).

It reminds me a transparent huge pages defragmentation issue that was
found in recent kernels.

Transparent huge pages defragmentation could lead to unpredictable
database stalls on some Linux kernels. The recommended settings for
this are below.

db1: ~ # echo always > /sys/kernel/mm/transparent_hugepage/enabled
db1: ~ # echo madvise > /sys/kernel/mm/transparent_hugepage/defrag

I am collecting recommendations for DB server configuration by the
link below. Try to look at it also if the above wont help.

http://code.google.com/p/pgcookbook/wiki/Database_Server_Configuration

> We have trialled PostgreSQL 9.2.3
> under SLES11 SP2 with the exact same results. During these periods the
> machine is completely responsive but anything accessing the database is
> extremely slow.
>
> I have tried increasing sched_migration_cost from 500000 to 5000000 and also
> tried setting sched_compat_yield to 1, neither of these appeared to make a
> difference. I don't have the parameter 'sched_autogroup_enabled'. Nothing
> jumps out from top/iostat/sar/pg_stat_activity however I am very far from
> expert in interpreting their output
>
> We have work underway to reduce our number of connections as although it has
> always worked ok, perhaps it makes us particularly vulnerable to
> kernel/scheduler changes.
>
> I would be very grateful for any suggestions as to the best way to diagnose
> the source of this problem and/or general recommendations?

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com