> >> First thing I'd suggest is to get a more detailed idea of exactly
> >> what is bloating --- which tables/indexes are the problem?
> >   I think the most problematic table is this one. After vacuum
> > it was 20MB in size now (after 6 hours) it is already 70MB and counting.
> AFAICT the vacuum is doing what it is supposed to, and the problem has
> to be just that it's not being done often enough.  Which suggests either
> an autovacuum bug or your autovacuum settings aren't aggressive enough.

  -D -d 1 -v 1000 -V 0.5 -a 1000 -A 0.1 -s 10

  That is autovacuum settings. Should be aggressive enough I think?

> Which PG version is this exactly?  Some of the earlier autovacuum
> releases do have known bugs, so it'd be worth your while to update
> if you're not on the latest point release of your series.


> I don't know much about autovacuum settings, but if you'll show what
> you're using someone can probably comment on them.

  And what in vacuum verbose output suggests that vacuum is not done
often enough? Current output (table is 100MB already) is below.



$ vacuumdb -v -z -U postgres -t queue database
INFO:  vacuuming "queue"
INFO:  index "queue_msg_id_pk" now contains 302993 row versions in 18129
DETAIL:  102763 index row versions were removed.
1 index pages have been deleted, 1 are currently reusable.
CPU 0.87s/0.46u sec elapsed 76.40 sec.
INFO:  index "queue_next_retry" now contains 310080 row versions in 9092
DETAIL:  102763 index row versions were removed.
675 index pages have been deleted, 658 are currently reusable.
CPU 0.38s/0.31u sec elapsed 79.47 sec.
INFO:  index "queue_recipient_idx" now contains 323740 row versions in 2900
DETAIL:  102763 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.27u sec elapsed 9.06 sec.
INFO:  "queue": removed 102763 row versions in 9623 pages
DETAIL:  CPU 0.16s/0.39u sec elapsed 29.26 sec.
INFO:  "queue": found 102763 removable, 292342 nonremovable row versions in
12452 pages
DETAIL:  14 dead row versions cannot be removed yet.
There were 183945 unused item pointers.
0 pages are entirely empty.
CPU 1.56s/1.51u sec elapsed 194.39 sec.
INFO:  analyzing "queue"
INFO:  "queue": scanned 3000 of 12452 pages, containing 72850 live rows and
7537 dead rows; 3000 rows in sample, 302376 estimated total rows

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to