Following up.
I don't see any rolled back transactions in the logs.
The part that troubles me really is that vacuum full doesn't actually fix the problem. If there were bad data that had been corrected via mass updates, I'd expect the bloat issue to be fixed by a vacuum full.
When I run the vacuum back to back, this is what I get:
db_name=# VACUUM FULL VERBOSE table_schema.table_name;
INFO: vacuuming "table_schema.table_name"
INFO: "table_name": found 2 removable, 29663 nonremovable row versions in 1754 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.07s/0.10u sec elapsed 0.30 sec.
VACUUM
db_name=# VACUUM FULL VERBOSE table_schema.table_name;
INFO: vacuuming "table_schema.table_name"
INFO: "table_name": found 0 removable, 29663 nonremovable row versions in 1754 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.09s/0.09u sec elapsed 0.32 sec.
VACUUM
I think the question to address may be: "Why does the check_postgres query think there should only be 334 pages instead of 1754?"
The tbloat and wastedbytes calculations provided in the query from this page: http://wiki.postgresql.org/wiki/Show_database_bloat seems to correlate with the wasted bytes reported by nagios though.
Greg Haase
On Tue, Oct 29, 2013 at 7:06 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Tue, Oct 29, 2013 at 5:38 AM, Chris Travers <chris.travers@xxxxxxxxx> wrote:well, or by rolled back transactions. we we have to wonder if OP has
>
>
>
> On Mon, Oct 28, 2013 at 4:17 PM, Gregory Haase <haaseg@xxxxxxxxxxxxxxxx>
> wrote:
>>
>> I have a table that is triggering my nagios database bloat alert
>> regularly. Usually, I have to give it the vacuum full more than once to get
>> it under the threshold. Today I tried repeatedly and cannot get the alert to
>> resolve.
>>
>> I had a discussion with one of the primary developers about how the table
>> is utilized, and it turns out they are basically only ever inserting into
>> it. This sort of flies in the face of conventional wisdom about bloat being
>> caused by frequent updates and deletes.
>
>
> As I understand it, vacuuming only removes the tuples removed by delete and
> update operations.
a lot of queries trying to insert and failing. maybe check the log?
merlin
