Обсуждение: High RAM usage on postgres
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.
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.
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
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
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