Re: [GENERAL] core system is getting unresponsive because over 300cpu load - Mailing list pgsql-general

On 10/10/2017 10:40 PM, pinker wrote:
> Hi to all!
> 
> We've got problem with a very serious repetitive incident on our core
> system. Namely, cpu load spikes to 300-400 and the whole db becomes

What is "CPU load"? Perhaps you mean "load average"?

Also, what are the basic system parameters (number of cores, RAM), it's
difficult to help without knowing that.

> unresponsive. From db point of view nothing special is happening, memory
> looks fine, disks io's are ok and the only problem is huge cpu load. Kernel
> parameters that are increasing with load are always the same:
> 
> * page tables size
> * Committed_AS
> * Active anon
> 
> <http://www.postgresql-archive.org/file/t342733/pagetables.png> 
> 
> and the total number of connections are increasing very fast (but I suppose
> it's the symptom not the root cause of cpu load) and exceed max_connections
> (1000).
> 

I doubt you have 1000 cores in your system, so 1000 connections active
at the same time is guaranteed to cause issues. What we see quite often
is a minor hiccup (occasional slow query) snowballing into much more
serious trouble exactly because of this.

Queries get slower for some reason, application starts opening more
connections (through a built-in connection pool) to run more queries,
that further increases pressure, slows the queries even more, ...

As Scott suggested, you should consider using a connection pool.

> System:
> * CentOS Linux release 7.2.1511 (Core) 
> * Linux 3.10.0-327.36.3.el7.x86_64 #1 SMP Mon Oct 24 16:09:20 UTC 2016
> x86_64 x86_64 x86_64 GNU/Linux
> * postgresql95-9.5.5-1PGDG.rhel7.x86_64
> * postgresql95-contrib-9.5.5-1PGDG.rhel7.x86_64
> * postgresql95-docs-9.5.5-1PGDG.rhel7.x86_64
> * postgresql95-libs-9.5.5-1PGDG.rhel7.x86_64
> * postgresql95-server-9.5.5-1PGDG.rhel7.x86_64
> 
> * 4 sockets/80 cores
> * vm.dirty_background_bytes = 0
> * vm.dirty_background_ratio = 2
> * vm.dirty_bytes = 0
> * vm.dirty_expire_centisecs = 3000
> * vm.dirty_ratio = 20
> * vm.dirty_writeback_centisecs = 500
> 
> after the first incident we have changed:
> * increased shared_buffers to 16GB (completely on huge pages. previously
> 2GB)
> * adjusted vm.nr_hugepages to 8000 (we've got 2mb pages)
> * changed vm.overcommit_memory = 2 and vm.overcommit_ratio = 99
> * disabled transparent huge pages (they were set before unfortunately to
> 'always')
> 
> 
> It's a highly transactional db. Today I've run:
> select now(), txid_current();
> and the results:
> 3 339 351 transactions between 2017-10-10 14:42 and 2017-10-10 16:24
> 

Well, 3M transactions over ~2h period is just ~450tps, so nothing
extreme. Not sure how large the transactions are, of course.

> ... snip ...
> 
> So, from the kernel stats we know that the failure happens when db is trying
> to alocate some huge amount of pages (page tables size, anons, commited_as).
> But what is triggering this situation? 
>

Something gets executed on the database. We have no idea what it is, but
it should be in the system logs. And you should see the process in 'top'
with large amounts of virtual memory ...

> I suppose it could be lazy autovacuum (just standard settings). So
> autovacuum had to read whole 369gb yesterday to clean xids. today did the
> same on some other tables.

Possible, but it shouldn't allocate more than maintenance_work_mem. So I
don't why it would allocate so much virtual memory.

Another possibility is a run-away query that consumes a lot of work_mem.

> Another idea is too small shared buffers setting. 

... snip ...

> bgwriter stats:
> <http://www.postgresql-archive.org/file/t342733/bgwriter.png> 
> 

Yes, this suggests you probably have shared_buffers set too low, but
it's impossible to say if increasing the size will help - perhaps your
active set (part of DB you regularly access) is way too big.

Measure cache hit ratio (see pg_stat_database.blks_hit and blks_read),
and then you can decide.

You may also make the bgwriter more aggressive - that won't really
improve the hit ratio, it will only make enough room for the backends.

But I don't quite see how this could cause the severe problems you have,
as I assume this is kinda regular behavior on that system. Hard to say
without more data.


regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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