From: Mindaugas Riauba <mind@bi.lt>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: How to avoid database bloat
Date: Thu, 2 Jun 2005 10:28:03 +0300
Message-ID: <018601c56744$9d5aae50$f20214ac@bite.lt> (raw)
References: <00d501c5667d$8cc12320$f20214ac@bite.lt>
<13878.1117636799@sss.pgh.pa.us>
> > Our database increases in size 2.5 times during the day.
> > What to do to avoid this? Autovacuum running with quite
> > aggressive settings, FSM settings are high enough.
>
> 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 full/reindex
it was 20MB in size now (after 6 hours) it is already 70MB and counting.
vacuum verbose output below. msg_id is integer, next_retry - timestamp,
recipient - varchar(20). max_fsm_pages = 200000. Another table has foregn
key which referenced msg_id in this one.
Thanks,
Mindaugas
$ vacuumdb -v -z -U postgres -t queue database
INFO: vacuuming "queue"
INFO: index "queue_msg_id_pk" now contains 110531 row versions in 5304
pages
DETAIL: 31454 index row versions were removed.
95 index pages have been deleted, 63 are currently reusable.
CPU 0.03s/0.07u sec elapsed 2.50 sec.
INFO: index "queue_next_retry" now contains 110743 row versions in 3551
pages
DETAIL: 31454 index row versions were removed.
1163 index pages have been deleted, 560 are currently reusable.
CPU 0.04s/0.06u sec elapsed 4.93 sec.
INFO: index "queue_recipient_idx" now contains 111596 row versions in 1802
pages
DETAIL: 31454 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.05u sec elapsed 0.16 sec.
INFO: "queue": removed 31454 row versions in 1832 pages
DETAIL: CPU 0.00s/0.01u sec elapsed 0.27 sec.
INFO: "queue": found 31454 removable, 110096 nonremovable row versions in
9133 pages
DETAIL: 119 dead row versions cannot be removed yet.
There were 258407 unused item pointers.
0 pages are entirely empty.
CPU 0.12s/0.25u sec elapsed 8.20 sec.
INFO: analyzing "queue"
INFO: "queue": scanned 3000 of 9133 pages, containing 34585 live rows and
1808 dead rows; 3000 rows in sample, 105288 estimated total rows
VACUUM
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mind@bi.lt, tgl@sss.pgh.pa.us
Subject: Re: How to avoid database bloat
In-Reply-To: <018601c56744$9d5aae50$f20214ac@bite.lt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox