Message-ID: <522F3A26.20306@dunslane.net>
Date: Tue, 10 Sep 2013 11:26:30 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130110 Thunderbird/17.0.2
MIME-Version: 1.0
To: David Whittaker <dave@iradix.com>
CC: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Intermittent hangs with 9.2
References: 
 <CABXnLXS0ez1PW6JQbPdd9rs1GnNuwjyn8Zq48XRdZpx8cdRcLQ@mail.gmail.com>
In-Reply-To: 
 <CABXnLXS0ez1PW6JQbPdd9rs1GnNuwjyn8Zq48XRdZpx8cdRcLQ@mail.gmail.com>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 09/10/2013 11:04 AM, David Whittaker wrote:
>
> Hi All,
>
> I've been seeing a strange issue with our Postgres install for about a=20
> year now, and I was hoping someone might be able to help point me at=20
> the cause. At what seem like fairly random intervals Postgres will=20
> become unresponsive to the 3 application nodes it services. These=20
> periods tend to last for 10 - 15 minutes before everything rights=20
> itself and the system goes back to normal.
>
> During these periods the server will report a spike in the outbound=20
> bandwidth (from about 1mbs to about 5mbs most recently), a huge spike=20
> in context switches / interrupts (normal peaks are around 2k/8k=20
> respectively, and during these periods they=91ve gone to 15k/22k), and a=
=20
> load average of 100+. CPU usage stays relatively low, but it=92s all=20
> system time reported, user time goes to zero. It doesn=91t seem to be=20
> disk related since we=92re running with a shared_buffers setting of 24G,=
=20
> which will fit just about our entire database into memory, and the IO=20
> transactions reported by the server, as well as the disk reads=20
> reported by Postgres stay consistently low.
>
> We=91ve recently started tracking how long statements take to execute,=20
> and we=92re seeing some really odd numbers. A simple delete by primary=20
> key, for example, from a table that contains about 280,000 rows,=20
> reportedly took 18h59m46.900s. An update by primary key in that same=20
> table was reported as 7d 17h 58m 30.415s. That table is frequently=20
> accessed, but obviously those numbers don't seem reasonable at all.
>
> Some other changes we've made to postgresql.conf:
>
> synchronous_commit =3D off
>
> maintenance_work_mem =3D 1GB
> wal_level =3D hot_standby
> wal_buffers =3D 16MB
>
> max_wal_senders =3D 10
>
> wal_keep_segments =3D 5000
>
> checkpoint_segments =3D 128
>
> checkpoint_timeout =3D 30min
>
> checkpoint_completion_target =3D 0.9
>
> max_connections =3D 500
>
> The server is a Dell Poweredge R900 with 4 Xeon E7430 processors, 48GB=20
> of RAM, running Cent OS 6.3.
>
> So far we=91ve tried disabling Transparent Huge Pages after I found a=20
> number of resources online that indicated similar interrupt/context=20
> switch issues, but it hasn=92t resolve the problem. I managed to catch=20
> it happening once and run a perf which showed:
>
> |
> +  41.40%       48154  postmaster  0x347ba9         f 0x347ba9
> +   9.55%       10956  postmaster  0x2dc820         f set_config_option
> +   8.64%        9946  postmaster  0x5a3d4          f writeListPage
> +   5.75%        6609  postmaster  0x5a2b0          f ginHeapTupleFastCol=
lect
> +   2.68%        3084  postmaster  0x192483         f build_implied_join_=
equality
> +   2.61%        2990  postmaster  0x187a55         f build_paths_for_OR
> +   1.86%        2131  postmaster  0x794aa          f get_collation_oid
> +   1.56%        1822  postmaster  0x5a67e          f ginHeapTupleFastIns=
ert
> +   1.53%        1766  postmaster  0x1929bc         f distribute_qual_to_=
rels
> +   1.33%        1558  postmaster  0x249671         f cmp_numerics|
>
> I=91m not sure what 0x347ba9 represents, or why it=92s an address rather=
=20
> than a method name.
>
> That's about the sum of it. Any help would be greatly appreciated and=20
> if you want any more information about our setup, please feel free to ask.
>
>

I have seen cases like this with very high shared_buffers settings.

24Gb for shared_buffers is quite high, especially on a 48Gb box. What=20
happens if you dial that back to, say, 12Gb?

cheers

andrew


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance