From: Tom Lane <tgl@sss.pgh.pa.us>
To: eudald_v <reaven.galaeindael@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sudden connection and load average spikes with postgresql 9.3
Date: Tue, 30 Jun 2015 11:37:20 -0400
Message-ID: <28249.1435678640@sss.pgh.pa.us> (raw)
In-Reply-To: <1435675936590-5855895.post@n5.nabble.com>
References: <1435675936590-5855895.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
eudald_v <reaven.galaeindael@gmail.com> writes:
> This is my very first message to the Postgresql community, and I really hope
> you can help me solve the trouble I'm facing.
> I've an 80 core server (multithread) with close to 500GB RAM.
> My configuration is:
> MaxConn: 1500 (was 850)
> Shared buffers: 188Gb
> work_mem: 110Mb (was 220Mb)
> maintenance_work_mem: 256Mb
> effective_cache_size: 340Gb
> The database is running under postgresql 9.3.9 on an Ubuntu Server 14.04 LTS
> (build 3.13.0-55-generic)
> Two days from now, I've been experiencing that, randomly, the connections
> rise up till they reach max connections, and the load average of the server
> goes arround 300~400, making every command issued on the server take
> forever. When this happens, ram is relatively low (70Gb used), cores
> activity is lower than usual and sometimes swap happens (I've swappiness
> configured to 10%)
You haven't described why you would suddenly be getting more connections,
but if that's just an expected byproduct of transactions taking too long
to finish, then a plausible theory is that something is acquiring a strong
lock on some heavily-used table, causing other transactions to back up
behind it. The pg_stat_activity and pg_locks views may help identify
the culprit.
In any case, a good mitigation plan would be to back off your
max_connections setting, and instead use a connection pooler to manage
large numbers of clients. The usual rule of thumb is that you don't want
number of backends much more than the number of cores. 1500 active
sessions is WAY more than your hardware can support, and even approaching
that will exacerbate whatever the root performance problem is.
I'm also a bit worried by your focus on CPU capacity without any mention
of the disk subsystem. It may well be that the actual bottleneck is disk
throughput, and that you can't even sustain 80 sessions unless they're
read-mostly. It would be a good idea to watch the i/o stats the next
time you see one of these high-load episodes.
It might also be a good idea to decrease the shared_buffers setting and
rely more on OS-level disk caching. Some have found that very large
shared buffer pools tend to increase contention without much payback.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, reaven.galaeindael@gmail.com
Subject: Re: Sudden connection and load average spikes with postgresql 9.3
In-Reply-To: <28249.1435678640@sss.pgh.pa.us>
* 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