Re: High RAM usage on postgres

On Thu, Mar 14, 2013 at 1:55 PM, prashantmalik <prashantmalikk@gmail.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
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------
> postgresql.conf
> shared_buffers = 6400MB    # min 128kB
>                                         # (change requires restart)
> temp_buffers = 286720    # min 800kB
>
> work_mem = 320MB    # min 64kB
> maintenance_work_mem = 960MB
>
> checkpoint_segments = 32    # in logfile segments, min 1, 16MB each
> checkpoint_timeout = 1h    # range 30s-1h
> checkpoint_completion_target = 0.9    # checkpoint target duration, 0.0 -
> 1.0
> checkpoint_warning = 10min    # 0 disables
>
> effective_cache_size = 16000MB
>
> 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
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------------------------
> More over, the query is throwing all the data on the screen at once without
> any keyboard interrupt for this table.
> For all other tables, output is shown in parts when keys are pressed from
> keyboard.
>
> Is a query from another server with less memory(16GB) is made to this
> postgres, oomkiller kills the postgres thread due to out of memory.

what are you intending to do with the 19gb+ data you are querying out?

problem is psql buffering whole result set in memory before outputting
result.   note this is core problem with libpq client library until
very recently.  there are several easy workarounds:

*) use cursor
*) don't select entire table, page it out using index (I can suggest
some methods )
*) if you are outputting to file, consider using COPY

merlin