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
