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@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
