High RAM usage on postgres - Mailing list pgsql-general

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.

Please suggest







--
View this message in context: http://postgresql.1045698.n5.nabble.com/High-RAM-usage-on-postgres-tp5748487.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.