Skip site navigation (1) Skip section navigation (2)

Huge memory consumption during vacuum (v.8.0)

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Huge memory consumption during vacuum (v.8.0)
Date: 2005-01-30 06:14:06
Message-ID: Pine.GSO.4.62.0501300846470.6701@ra.sai.msu.su (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi there,

seems I have a serious problem with vacuuming of rather big table
(500,000,000 rows) on dual Intel(R) Xeon(TM) CPU 2.40GHz, 1Gb RAM,
running Linux 2.6.7.  I have PostgreSQL 8.0 release installed with
slightly changed postgresql.conf:

shared_buffers = 24576       # min 16, at least max_connections*2, 8KB each
maintenance_work_mem = 65536 # 16384    # min 1024, size in KB
checkpoint_segments = 12 #3     # in logfile segments, min 1, 16MB each

I tried run 'vacuumdb -v -z -f wsdb > vacuum-wsdb.log 2>&1&' 
with default value of maintenance_work_mem but it was
too small for big table and I increased its value as Tom recommended.
But this change causes huge memory consumption - rather quickly memory
grew to 1Gb and after almost 42 hours of running (yes, it's still running) 
postmaster eats more than 2Gb of RAM

20458 postgres  15   0 2462m 646m 204m D 37.5 63.9 744:38.74 postmaster

There are no messages in log file since start (just pg_* tables),  so it's 
difficult to say if there is some useful activity :)

The only non-standard action was installing 8.0 in neighbour with running
7.4.6 version. I run configure with different prefix and pgport specified
and use PGPORT, PGLIB, PGDATA, PATH modified to work with new postmaster.
I don't see any problem here.

Does anybody have experience vacuuming large database with 8.0 ?


table is very simple:

      Table "public.usno"
  Column |  Type  | Modifiers 
--------+--------+-----------
  ra     | real   |
  dec    | real   |
  bmag   | real   |
  rmag   | real   |
  ipix   | bigint | 
Indexes:
     "ipix_ind" btree (ipix)
     "radec_idx1" btree (ra, "dec")

 	Regards,
 		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2005-01-30 06:46:19
Subject: Re: [BUGS] Bug in create operator and/or initdb
Previous:From: Kevin BrownDate: 2005-01-30 04:36:06
Subject: Re: IBM patent

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group