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

Success report (WAS: postgresql 'eats' my whole data partition)

From: Tomas Szepe <szepe(at)pinerecords(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>,Javier Carlos <fjcarlos(at)correo(dot)insp(dot)mx>, pgsql-bugs(at)postgresql(dot)org
Subject: Success report (WAS: postgresql 'eats' my whole data partition)
Date: 2003-10-04 09:10:11
Message-ID: 20031004091011.GC2276@louise.pinerecords.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sep-26 2003, Fri, 16:28 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Okay, so a pkey index entry will take 32 bytes counting overhead ...
> you've got about 10:1 bloat on the stats_min indexes and 2:1 in stats_hr.
> Definitely bad :-(
> 
> I expect the bloat is coming from the fact that the interesting range of
> "start" changes over time.  7.4 should be able to recycle index space
> in that situation, but 7.3 and before can't.

This is how things look with 7.4beta3:

INFO:  vacuuming "public.stats_min"
INFO:  index "stats_min_start" now contains 1963394 tuples in 12086 pages
DETAIL:  736088 index tuples were removed.
5029 index pages have been deleted, 2411 are currently reusable.
CPU 1.07s/2.13u sec elapsed 8.15 sec.
INFO:  index "stats_min_pkey" now contains 1963520 tuples in 27945 pages
DETAIL:  736088 index tuples were removed.
11722 index pages have been deleted, 5603 are currently reusable.
CPU 4.18s/5.55u sec elapsed 113.04 sec.
INFO:  "stats_min": removed 736088 tuples in 9483 pages
DETAIL:  CPU 1.00s/1.12u sec elapsed 13.68 sec.
INFO:  "stats_min": found 736088 removable, 1963394 nonremovable tuples in 23890 pages
DETAIL:  0 dead tuples cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 7.20s/9.16u sec elapsed 146.28 sec.

...

INFO:  vacuuming "public.stats_hr"
INFO:  index "stats_hr_start" now contains 7850557 tuples in 28059 pages
DETAIL:  728706 index tuples were removed.
4337 index pages have been deleted, 2035 are currently reusable.
CPU 2.66s/4.72u sec elapsed 41.18 sec.
INFO:  index "stats_hr_pkey" now contains 7852826 tuples in 57657 pages
DETAIL:  728706 index tuples were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 5.26s/5.66u sec elapsed 123.91 sec.
INFO:  "stats_hr": removed 728706 tuples in 16033 pages
DETAIL:  CPU 1.51s/1.68u sec elapsed 25.43 sec.
INFO:  "stats_hr": found 728706 removable, 7848770 nonremovable tuples in 161840 pages
DETAIL:  0 dead tuples cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 15.71s/12.88u sec elapsed 213.28 sec.

Definitely a tremendous improvement.  The "working base" on-disk
db size seems to have stabilised at around 2.4 GiB and is only
increasing by ~100 MiB a day, which, by my calculations, is roughly
proportional to the frequency of data added.  (There is still
some minor overhead all right, but it's far from serious.)

So again it's a big thank-you to the whole postgres development
group for yet another nice step forward, especially to you Tom
for the "allow btree index compaction and empty page reuse"
changeset.

Best regards,
-- 
Tomas Szepe <szepe(at)pinerecords(dot)com>

In response to

pgsql-bugs by date

Next:From: Christoph BeckerDate: 2003-10-04 11:27:29
Subject: 7.4beta4: make check broken?
Previous:From: Hiroshi SaitoDate: 2003-10-04 01:03:33
Subject: Re: [BUGS] bug reporting

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