From: Scott Marlowe <scott.marlowe@gmail.com>
To: Alexy Khrabrov <deliverable@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
Date: Fri, 2 May 2008 13:53:56 -0600
Message-ID: <dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com> (raw)
In-Reply-To: <27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>
<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>
<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
On Fri, May 2, 2008 at 1:38 PM, Alexy Khrabrov <deliverable@gmail.com> wrote:
>
>
> On May 2, 2008, at 12:30 PM, Scott Marlowe wrote:
>
>
> > On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <deliverable@gmail.com>
> wrote:
> >
> > > Greetings -- I have an UPDATE query updating a 100 million row table,
> and
> > > allocate enough memory via shared_buffers=1500MB. However, I see two
> > > processes in top, the UPDATE process eating about 850 MB and the writer
> > > process eating about 750 MB. The box starts paging. Why is there the
> > > writer taking almost as much space as the UPDATE, and how can I shrink
> it?
> > >
> >
> > Shared_buffers is NOT the main memory pool for all operations in
> > pgsql, it is simply the buffer pool used to hold data being operated
> > on.
> >
> > Things like sorts etc. use other memory and can exhaust your machine.
> > However, I'd like to see the output of vmstat 1 or top while this is
> > happening.
> >
> > How much memory does this machine have?
> >
>
> It's a 2GB RAM MacBook. Here's the top for postgres
>
> Processes: 117 total, 2 running, 6 stuck, 109 sleeping... 459 threads
> 12:34:27
> Load Avg: 0.27, 0.24, 0.32 CPU usage: 8.41% user, 11.06% sys, 80.53%
> idle
> SharedLibs: num = 15, resident = 40M code, 2172K data, 3172K linkedit.
> MemRegions: num = 20719, resident = 265M + 12M private, 1054M shared.
> PhysMem: 354M wired, 1117M active, 551M inactive, 2022M used, 19M free.
> VM: 26G + 373M 1176145(160) pageins, 1446482(2) pageouts
>
> PID COMMAND %CPU TIME #TH #PRTS #MREGS RPRVT RSHRD RSIZE VSIZE
> 51775 postgres 6.8% 2:40.16 1 9 39 1504K 896M 859M+
> 1562M
> 51767 postgres 0.0% 0:39.74 1 8 28 752K 896M 752M
> 1560M
SOME snipping here.
> I randomly increased values in postgresql.conf to
>
> shared_buffers = 1500MB
> max_fsm_pages = 2000000
> max_fsm_relations = 10000
On a laptop with 2G ram, 1.5Gig shared buffers is probably WAY too high.
> Should I set the background writer parameters somehow to decrease the RAM
> consumed by the writer?
No, the background writer reads through the shared buffers for dirty
ones and writes them out. so, it's not really using MORE memory, it's
just showing that it's attached to the ginormous shared_buffer pool
you've set up.
Lower your shared_buffers to about 512M or so and see how it works.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, deliverable@gmail.com
Subject: Re: two memory-consuming postgres processes
In-Reply-To: <dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox