On Fri, May 2, 2008 at 1:38 PM, Alexy Khrabrov <deliverable@[EMAIL PROTECTED]
>
wrote:
>
>
> On May 2, 2008, at 12:30 PM, Scott Marlowe wrote:
>
>
> > On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <deliverable@[EMAIL PROTECTED]
>
> 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.
--
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
|
