Re: two memory-consuming postgres processes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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

On Fri, May 2, 2008 at 1:24 PM, Alexy Khrabrov <deliverable@xxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux