Обсуждение: Huge memory consumption during vacuum (v.8.0)

Поиск
Список
Период
Сортировка

Huge memory consumption during vacuum (v.8.0)

От
Oleg Bartunov
Дата:
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

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

От
Tom Lane
Дата:
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

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

От
Oleg Bartunov
Дата:
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
>
    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

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

От
Tom Lane
Дата:
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

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

От
Oleg Bartunov
Дата:
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
>
    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

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

От
Oleg Bartunov
Дата:
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
>
    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

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

От
Oleg Bartunov
Дата:
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
>
    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

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

От
Tom Lane
Дата:
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

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

От
Oleg Bartunov
Дата:
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
>
    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

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

От
Oleg Bartunov
Дата:
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
>
    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