PostgreSQL High Memory consumption

haproxypostgresql

We have been getting a lot of issues pertaining memory consumption in Postgres. I am thinking of deploying PgBouncer to resolve this issue, but wanted to know what could be the cause for this.

ps aux --sort=-%mem | head -30

USER        PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
postgres  23438 41.0  6.2 78075200 8258220 ?    Ssl  Aug09 525:40 postgres: xxxxx: xxxxx(60028) idle
postgres   4225 31.7  5.9 77797888 7903220 ?    Ssl  Aug09 438:58 postgres: xxxxx: xxxxx(45012) idle
postgres 118999 39.3  4.8 76290020 6395136 ?    Ssl  Aug09 306:52 postgres: xxxxx: xxxxx(50026) idle

free -g
              total        used        free      shared  buff/cache   available

Mem:            125          36          35           3          54          84
Swap:            19           0          19



System RAM : 128 GB
CPU(s) : 32
Shared_buffer : 32 GB
DB Size : 24 GB
Max Connections : 200
Average Connections: 50
Effective_Cache_Size : 64 GB
Work_mem : 41MB
PG Version : 11
OS : Ubuntu

A lot of the idle connections are getting reused despite it being closed from the app side. I have tuned the database by changing Shared_buffer, work_mem to values as per PostgreSQL expectations, but still getting these issues.

PS : We have setup HAProxy between App and DB.

Best Answer

This is probably shared memory that is common to all PostgreSQL processes.

ps is not smart about distinguishing private memory and shared memory.

Don't worry.

Related Solutions

PostgreSQL 8.3 – How to Limit Resource Consumption Per Query

To limit memory consumption, the main configuration parameter is work_mem. Since this applies per operation, not per query, you cannot simply set it to N if you want to spend N amount of memory, but you have to tweak and tune it a bit to get the desired result.

This doesn't help, however, in case of bugs and other memory leaks in the server code or extensions. You could control that with the process-specific resource limits controlled by ulimit. But if you reach the limit and memory allocation fails, what do you want to have happen? It probably won't behave too nicely. Better fix those bugs or use a different version.

The stop queries whose execution time passed a threshold, use the parameter `statement_timeout', e.g.,

SET statement_timeout TO '10min';

Postgresql – High availability in postgresql

This is pretty broad. Let's answer (2) first:

http://www.postgresql.org/support/professional_support/

Now, on to (1):

Is there any good conceptual design that could offer reliable pgsql cluster - reliability, data corruption prevention, high availability, no SPOF, load balancing and access to DB through one (virtual?) IP address?

The question is not "if" but "which".

You are describing a heartbeat/failover cluster. There aren't really fully canned solutions for this, you'll need your own heartbeat and STONITH using appropriate non-PostgreSQL-specific tools.

You can use PostgreSQL 9.1 streaming replication and log shipping for the replication. The replication manager repmgr can help automate much of the management.

Other replication options are described on the PostgreSQL wiki. All will require added heartbeat and STONITH for a fully transparent cluster with virtual IP, etc.

You might also want to look at pgbouncer. It's a useful tool not only for connection pooling, but also for failover and load balancing.

See the PostgreSQL manual on high availability, load balancing, and replication for more information.

Related Question