References: 
 <CAFiWeJB30=ncjt3gHuYh61ke4x--HA_2xkA9vxxU6puLZTNmCw@mail.gmail.com>
	<15C963C4-539B-4A88-9C21-54C3221FD1D7@gmail.com>
	<CAFiWeJBWYueKF=BDyMeJAECJA39zr_YvSgf6F+Pf2=_FhKrdsg@mail.gmail.com>
	<A737B7A37273E048B164557ADEF4A58B17D2CB2C@ntex2010i.host.magwien.gv.at>
	<CAFiWeJBppWiDKBgN49WpFgGrRYw3tG9W=gsjNkZTNwmEafX1=g@mail.gmail.com>
	<CAFiWeJDUk4Ja4CRy3Qy+g5fDZYMLM7G_Lmcnq_uVz6zf8to-YA@mail.gmail.com>
	<1408981867.41595.YahooMailNeo@web122303.mail.ne1.yahoo.com>
 <CAFiWeJAn7+cSvipqoxysDPk2ny5xAEXDCL9y9if3pMEhxFsHnw@mail.gmail.com>
Message-ID: <1408989207.36764.YahooMailNeo@web122302.mail.ne1.yahoo.com>
Date: Mon, 25 Aug 2014 10:53:27 -0700
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Problems with PG 9.3
To: Dhruv Shukla <dhruvshukla82@gmail.com>
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>,
 "jayknowsunix@gmail.com" <jayknowsunix@gmail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: 
 <CAFiWeJAn7+cSvipqoxysDPk2ny5xAEXDCL9y9if3pMEhxFsHnw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Dhruv Shukla <dhruvshukla82@gmail.com> wrote:
> Kevin Grittner <kgrittn@ymail.com> wrote:
>> Dhruv Shukla <dhruvshukla82@gmail.com> wrote:

>>> Other informational details about configurations are:
>>> shared_buffers =3D 80GB
>>> temp_buffers =3D 2GB
>>> work_mem =3D 2GB
>>> maintenance_work_mem =3D 16GB

>> How much RAM is on the machine (or VM)?

> Currently we have a max connection setting for 1000 connections.

The temp_buffers setting is a limit *per connection*, and once RAM
for a temporary table is allocated to a connection it is never
released; so even when all connections are idle they could be
setting aside 2TG of RAM for possible use for caching temporary
tables.

The work_mem setting is an attempt to limit RAM per node of an
executing query; one connection can create many allocations of the
size set for work_mem.=A0 Since not all queries have nodes that
require such allocations, and not all connections are necessarily
active at the same time, a general rule of thumb is to allow for
one work_mem allocation per connection allowed by max_connections.
So these settings can easily result in another 2TB of allocations,
beyond the temp_buffers mentioned above.

A high shared_buffers setting can result in "write gluts" at the OS
level when a high percentage of that memory becomes dirty and is
dumped to the OS in a short time.=A0 This can result in the OS
appearing to "lock up" for as long as it takes it to flush all of
the dirty data to disk.=A0 I don't know what your system is capable
of, but most database servers I've seen fall between 20MB/second
and 200MB/second.=A0 That would correspond to apparent stalls lasting
between 6.8 minutes and 1.1 hours.=A0 By tweaking the bgwriter
settings and the OS dirty settings you can drastically reduce this,
but I don't think you've indicated having done that, so 80GB can be
expected to cause apparent lockups of those durations.

A high shared_buffers setting makes you more vulnerable to long
stalls because of transparent huge page operations of the OS.

> And RAM on server is 384GB RAM.

And overcommitting RAM by a factor of more than 10x is not going to
be pretty in any event.

If I could not reduce max_connections, I would set work_mem to no
more than 100MB and temp_buffers to no more than 50MB.=A0 I would
drop maintenance_work_mem to 2GB.=A0 I would probably drastically
reduce shared_buffers and would most certainly make autovacuum and
bgwriter more aggressive than the default.

If you make those changes and still see a problem, only then is it
worth looking at other possible causes.

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


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