Re: two memory-consuming postgres processes - Mailing list pgsql-performance

From Scott Marlowe
Subject Re: two memory-consuming postgres processes
Date
Msg-id dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com
Whole thread Raw
In response to Re: two memory-consuming postgres processes  (Alexy Khrabrov <deliverable@gmail.com>)
Responses Re: two memory-consuming postgres processes
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: Alexy Khrabrov
Date:
Subject: Re: two memory-consuming postgres processes
Next
From: Tom Lane
Date:
Subject: Re: two memory-consuming postgres processes