High RAM usage on postgres

On Thu, Mar 14, 2013 at 12: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

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.

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

Am 17.03.2013 09:31, schrieb Scott Marlowe:

On Thu, Mar 14, 2013 at 12:55 PM, prashantmalik
<prashantmalikk@gmail.com> wrote:

*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

What eats up your memory is "psql", which indeed allocates a whopping
19G physical memory, not the server process.
- Are you sure that is _your_ "psql" selecting "* from customers" and
not somebody else's, doing a cross-join?
- Is there potentially anything that gets TOASTed in your "customer"
table? I'm not sure if that would show up in pg_relation_size and
friends, but it would get sent to psql of course.

Regards,

--
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
__________________________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX. Ten years later
they are choosing Windows over UNIX. What part of that message aren't you
getting? - Tom Payne

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

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

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

Merlin Moncure wrote:

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

Also there's psql's FETCH_COUNT that is specifically meant to avoid the
buffering problem.

From the 9.1 manpage:

FETCH_COUNT
If this variable is set to an integer value > 0, the results
of
SELECT queries are fetched and displayed in groups of that
many
rows, rather than the default behavior of collecting the
entire
result set before display. Therefore only a limited amount of
memory is used, regardless of the size of the result set.
Settings of 100 to 1000 are commonly used when enabling this
feature. Keep in mind that when using this feature, a query
might fail after having already displayed some rows.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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