Re: two memory-consuming postgres processes

On May 2, 2008, at 12:30 PM, Scott Marlowe wrote:

> On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov
> <deliverable(at)gmail(dot)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

the first is the UPDATE, the second is the writer.

The query is very simple,

netflix=> create index movs_mid_idx on movs(mid);
CREATE INDEX
netflix=> update ratings set offset1=avg-rating from movs where
mid=movie_id;

where the table ratings has about 100 million rows, movs has about
20,000.

I randomly increased values in postgresql.conf to

shared_buffers = 1500MB
max_fsm_pages = 2000000
max_fsm_relations = 10000

Should I set the background writer parameters somehow to decrease the
RAM consumed by the writer?

Cheers,
Alexy