Re: High RAM usage on postgres

On Thu, Mar 14, 2013 at 12:55 PM, prashantmalik
<prashantmalikk(at)gmail(dot)com> wrote:
> Hello,
>
> We are facing very HIGH memory utilization on postgreSQL server and need
> help.
>
> Total RAM : 32GB
> Total CPU : 16cores
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> *Table Size:*
> SELECT relname, pg_size_pretty(relpages::bigint * 8 * 1024) as size,
> relkind,reltuples::bigint as rows, relpages, relfilenode FROM pg_class ORDER
> BY relpages DESC;
> relname | size | relkind |
> rows | relpages | relfilenode
> ----------------------------------------------------+------------+---------+---------+----------+-------------
> customer | 1863 MB | r |
> 8307040 | 238507 | 189335
>
>
> *Query :* "SELECT * FROM customer"
> -----------------------------------------------------------------------------------------------------------------------------------------------------------
> top
>
> top - 00:14:38 up 44 days, 12:06, 2 users, load average: 3.57, 1.34, 0.69
> Tasks: 243 total, 3 running, 240 sleeping, 0 stopped, 0 zombie
> Cpu(s): 6.5%us, 0.6%sy, 0.0%ni, 92.5%id, 0.4%wa, 0.0%hi, 0.0%si,
> 0.0%st
> Mem: 32949816k total, 31333260k used, 1616556k free, 526988k buffers
> Swap: 4192956k total, 1989136k used, 2203820k free, 9182092k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 12671 root 25 0 19.8g 19g 1612 R 100.1 62.6 4:31.78 psql
> 32546 postgres 15 0 6694m 157m 156m S 0.0 0.5 0:02.91 postmaster

You aren't showing anything running out of memory here. You mention
the OOM killer, is this killing the client side (i.e. psql / pgadmin
III etc) or the server side? This is very important.

Note that the 6694 VIRT usage includes all shared buffers touched, as
well as local process memory. Note that the shared_buffers you posted
was about 6G so that's pretty typical. It's not a sign of running out
of memory. The real memory used is the RES bit, which is 157M here,
which is no big deal.

In another part of your post you listed work_mem as 320M. That's
huge. Unless you only handle 3 or 4 connections at a time I'd lower
it.