Thread: High RAM usage on postgres

High RAM usage on postgres

From
prashantmalik
Date:
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.


Re: High RAM usage on postgres

From
Scott Marlowe
Date:
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.


Re: High RAM usage on postgres

From
"Gunnar \"Nick\" Bluth"
Date:
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



Re: High RAM usage on postgres

From
Merlin Moncure
Date:
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


Re: High RAM usage on postgres

From
"Daniel Verite"
Date:
    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