Huge memory consumption during vacuum (v.8.0)

Started by Oleg Bartunovalmost 21 years ago10 messages

oleg@sai.msu.su

almost 21 years ago

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:

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

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Oleg Bartunov (#1)

Re: Huge memory consumption during vacuum (v.8.0)

Oleg Bartunov <oleg@sai.msu.su> writes:

I tried run 'vacuumdb -v -z -f wsdb > vacuum-wsdb.log 2>&1&'

I'm confused. The log trace you showed us before appeared to be from
a non-FULL vacuum, but here you're saying it's VACUUM FULL. Which is
it ... or did you change?

regards, tom lane

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Tom Lane (#2)

Re: Huge memory consumption during vacuum (v.8.0)

On Sun, 30 Jan 2005, Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

I tried run 'vacuumdb -v -z -f wsdb > vacuum-wsdb.log 2>&1&'

I'm confused. The log trace you showed us before appeared to be from
a non-FULL vacuum, but here you're saying it's VACUUM FULL. Which is
it ... or did you change?

Yes, first time I tried vacuum from withing psql, next time I decided
to run vacuumdb and seems changed option.

regards, tom lane

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Oleg Bartunov (#3)

Re: Huge memory consumption during vacuum (v.8.0)

Oleg Bartunov <oleg@sai.msu.su> writes:

On Sun, 30 Jan 2005, Tom Lane wrote:

I'm confused. The log trace you showed us before appeared to be from
a non-FULL vacuum, but here you're saying it's VACUUM FULL. Which is
it ... or did you change?

Yes, first time I tried vacuum from withing psql, next time I decided
to run vacuumdb and seems changed option.

Um. Well, a VACUUM FULL is going to build in-memory data structures
that represent *all* of the usable free space in a table. I don't
actually think that VACUUM FULL is useful on an enormous table ... you
want to keep after it with routine plain VACUUMs, instead.

Another possibility is to use CLUSTER or a rewriting ALTER TABLE to
shrink the space, but be aware that this requires a transient second
copy of the table and indexes.

regards, tom lane

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Tom Lane (#4)

Re: Huge memory consumption during vacuum (v.8.0)

On Sun, 30 Jan 2005, Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

On Sun, 30 Jan 2005, Tom Lane wrote:

I'm confused. The log trace you showed us before appeared to be from
a non-FULL vacuum, but here you're saying it's VACUUM FULL. Which is
it ... or did you change?

Yes, first time I tried vacuum from withing psql, next time I decided
to run vacuumdb and seems changed option.

Um. Well, a VACUUM FULL is going to build in-memory data structures
that represent *all* of the usable free space in a table. I don't
actually think that VACUUM FULL is useful on an enormous table ... you
want to keep after it with routine plain VACUUMs, instead.

ok. I'll try without FULL, but if memory does not fail me postmaster was
also greedy. Let's see

Another possibility is to use CLUSTER or a rewriting ALTER TABLE to
shrink the space, but be aware that this requires a transient second
copy of the table and indexes.

regards, tom lane

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Oleg Bartunov (#5)

Re: Huge memory consumption during vacuum (v.8.0)

On Sun, 30 Jan 2005, Oleg Bartunov wrote:

On Sun, 30 Jan 2005, Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

On Sun, 30 Jan 2005, Tom Lane wrote:

I'm confused. The log trace you showed us before appeared to be from
a non-FULL vacuum, but here you're saying it's VACUUM FULL. Which is
it ... or did you change?

Yes, first time I tried vacuum from withing psql, next time I decided
to run vacuumdb and seems changed option.

Um. Well, a VACUUM FULL is going to build in-memory data structures
that represent *all* of the usable free space in a table. I don't
actually think that VACUUM FULL is useful on an enormous table ... you
want to keep after it with routine plain VACUUMs, instead.

ok. I'll try without FULL, but if memory does not fail me postmaster was
also greedy. Let's see

Seems, postmaster eats expected amount of memory now ! Will see how long
it will proceeded. Probably, my case should be documented somewhere.

Another possibility is to use CLUSTER or a rewriting ALTER TABLE to
shrink the space, but be aware that this requires a transient second
copy of the table and indexes.

I aware, but I don't so much free space :)

Is there TODO for scaling VACUUM FULL ?

regards, tom lane

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 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Oleg Bartunov (#6)

Re: Huge memory consumption during vacuum (v.8.0)

On Sun, 30 Jan 2005, Oleg Bartunov wrote:

Seems, postmaster eats expected amount of memory now ! Will see how long
it will proceeded. Probably, my case should be documented somewhere.

just to inform - vacuum took almost 48 hours !

Another possibility is to use CLUSTER or a rewriting ALTER TABLE to
shrink the space, but be aware that this requires a transient second
copy of the table and indexes.

I aware, but I don't so much free space :)

I run
wsdb=# create table c_usno as select * from usno order by ipix;
to cluster table and notice that disk usage changing in discontinuous manner.
After some reduction I see no changes. postmaster is doing something

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
12723 postgres 18 0 207m 196m 204m D 21.6 19.4 52:17.12 postmaster

I see that pgsql_tmp/ contains files, looks like clustered table.
What postmaster is doing if disk usage doesn't changed ?

Is there TODO for scaling VACUUM FULL ?

regards, tom lane

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 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Tom Lane

tgl@sss.pgh.pa.us

almost 21 years ago

In reply to: Oleg Bartunov (#7)

Re: Huge memory consumption during vacuum (v.8.0)

Oleg Bartunov <oleg@sai.msu.su> writes:

I see that pgsql_tmp/ contains files, looks like clustered table.
What postmaster is doing if disk usage doesn't changed ?

Most likely doing a disk-based merge sort ...

regards, tom lane

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Tom Lane (#8)

Re: Huge memory consumption during vacuum (v.8.0)

On Tue, 1 Feb 2005, Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

I see that pgsql_tmp/ contains files, looks like clustered table.
What postmaster is doing if disk usage doesn't changed ?

Most likely doing a disk-based merge sort ...

just interesting - multiway, in-place or just place merge sort ?

regards, tom lane

#10

Oleg Bartunov

oleg@sai.msu.su

almost 21 years ago

In reply to: Tom Lane (#8)

Re: Huge memory consumption during vacuum (v.8.0)

On Tue, 1 Feb 2005, Tom Lane wrote:

Oleg Bartunov <oleg@sai.msu.su> writes:

I see that pgsql_tmp/ contains files, looks like clustered table.
What postmaster is doing if disk usage doesn't changed ?

Most likely doing a disk-based merge sort ...

so, it uses 'work_mem' as a buffer ?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match