From: Kevin Grittner <kgrittn@ymail.com>
To: Dhruv Shukla <dhruvshukla82@gmail.com>
Cc: Albe Laurenz <laurenz.albe@wien.gv.at>
Cc: jayknowsunix@gmail.com <jayknowsunix@gmail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Problems with PG 9.3
Date: Mon, 25 Aug 2014 10:53:27 -0700
Message-ID: <1408989207.36764.YahooMailNeo@web122302.mail.ne1.yahoo.com> (raw)
In-Reply-To: <CAFiWeJAn7+cSvipqoxysDPk2ny5xAEXDCL9y9if3pMEhxFsHnw@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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 = 80GB
>>> temp_buffers = 2GB
>>> work_mem = 2GB
>>> maintenance_work_mem = 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. 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. 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. I don't know what your system is capable
of, but most database servers I've seen fall between 20MB/second
and 200MB/second. That would correspond to apparent stalls lasting
between 6.8 minutes and 1.1 hours. 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. I would
drop maintenance_work_mem to 2GB. 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kgrittn@ymail.com, dhruvshukla82@gmail.com, laurenz.albe@wien.gv.at, jayknowsunix@gmail.com
Subject: Re: Problems with PG 9.3
In-Reply-To: <1408989207.36764.YahooMailNeo@web122302.mail.ne1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox