Обсуждение: pg_class reltuples/relpages not updated by autovacuum/vacuum

Hi.

I'm running a production database with PostgreSQL 9.0.3 (64-bit) on
Debian 5.0.4 and have an issue with a TOAST table and far to frequent
autovacuum runs.

I think I've pinned the problem down to the values pg_class holds for
the affected TOAST table:

relpages  | 433596
reltuples | 1868538

These values are significantly too low. Interestingly, the autovacuum
logout reports the correct values:

    pages: 0 removed, 34788136 remain
    tuples: 932487 removed, 69599038 remain

but these aren't stored in pg_class after each run.

Currently, there are no long running transactions and/or dumps running.

I've confirmed that PostgreSQL is using the values stored in pg_class
for it's calculations, it starts autovacuum for the table at around
375k dead rows (threshold is 50, scale_factor 0.2 (both default)).

Additionally I've done manual VACUUM ANALYZE of both the parent table
and the TOAST table, which didn't help either.

Other databases with the same hardware, PostgreSQL and OS versions don't
have this issue.

Currently I've worked around the issue by disabling autovacuum for the
TOAST table and doing manual VACUUM ANALYZE once a week.

Any clue how to get PostgreSQL to store the correct values?

Side note: while trying to debug this I've noticed, that the TOAST
chunks on 32-bit systems have the documented size of 2000 bytes, on
64-bit systems they have 1996 bytes. Is this normal/on purpose?

Regards,
Florian Helmberger

--

Florian Helmberger --------------------

25th-floor - Operating Custom Solutions
de Pretis & Helmberger KG

Gluckgasse 2/6, 1010 Wien, Austria

Mail: fh@25th-floor.com
Web : http://www.25th-floor.com
Tel.: +43 1 / 512 82 89 - 60
Fax : +43 1 / 512 82 89 - 76
Mob.: +43 699 / 109 24 24 5
---------------------------------------

Florian Helmberger <fh@25th-floor.com> writes:
> I'm running a production database with PostgreSQL 9.0.3 (64-bit) on
> Debian 5.0.4 and have an issue with a TOAST table and far to frequent
> autovacuum runs.

> I think I've pinned the problem down to the values pg_class holds for
> the affected TOAST table:

> relpages  | 433596
> reltuples | 1868538

> These values are significantly too low. Interestingly, the autovacuum
> logout reports the correct values:

>     pages: 0 removed, 34788136 remain
>     tuples: 932487 removed, 69599038 remain

> but these aren't stored in pg_class after each run.

That's exceedingly weird.  Do the pg_stat_all_tables columns update
after autovacuums on that table?

            regards, tom lane

On 25.05.11 04:47, Tom Lane wrote:

> Florian Helmberger<fh@25th-floor.com>  writes:
>> I'm running a production database with PostgreSQL 9.0.3 (64-bit) on
>> Debian 5.0.4 and have an issue with a TOAST table and far to frequent
>> autovacuum runs.
>
>> I think I've pinned the problem down to the values pg_class holds for
>> the affected TOAST table:
>
>> relpages  | 433596
>> reltuples | 1868538
>
>> These values are significantly too low. Interestingly, the autovacuum
>> logout reports the correct values:
>
>>     pages: 0 removed, 34788136 remain
>>     tuples: 932487 removed, 69599038 remain
>
>> but these aren't stored in pg_class after each run.
>
> That's exceedingly weird.  Do the pg_stat_all_tables columns update
> after autovacuums on that table?

Hi Tom,

Yes they do:

-[ RECORD 1 ]----+------------------------------
relid            | 16391
schemaname       | pg_toast
relname          | pg_toast_16386
seq_scan         | 0
seq_tup_read     | 0
idx_scan         | 298820512
idx_tup_fetch    | 1812697121
n_tup_ins        | 60907628
n_tup_upd        | 0
n_tup_del        | 56710637
n_tup_hot_upd    | 0
n_live_tup       | 4196999
n_dead_tup       | 20746580
last_vacuum      | 2011-05-21 06:33:49.869459+02
last_autovacuum  | 2011-05-15 18:40:49.746234+02
last_analyze     | NULL
last_autoanalyze | NULL

That was the last autovacuum run before I disabled it (via storage
parameter on the main table) and switched to manual vacuum's once per week.

I've also rechecked the "sister" database (same Hareware, OS/PostgreSQL
Version and database schema) which is working as intended.

Regards,
Florian

--

Florian Helmberger --------------------

25th-floor - Operating Custom Solutions
de Pretis & Helmberger KG

Gluckgasse 2/6, 1010 Wien, Austria

Mail: fh@25th-floor.com
Web : http://www.25th-floor.com
Tel.: +43 1 / 512 82 89 - 60
Fax : +43 1 / 512 82 89 - 76
Mob.: +43 699 / 109 24 24 5
---------------------------------------

Florian Helmberger <fh@25th-floor.com> writes:
> I think I've pinned the problem down to the values pg_class holds for
> the affected TOAST table:
> relpages  | 433596
> reltuples | 1868538
> These values are significantly too low. Interestingly, the autovacuum
> logout reports the correct values:
>     pages: 0 removed, 34788136 remain
>     tuples: 932487 removed, 69599038 remain
> but these aren't stored in pg_class after each run.

I've moved discussion of this to pgsql-hackers, since this appears to be
an actual bug.


> Side note: while trying to debug this I've noticed, that the TOAST
> chunks on 32-bit systems have the documented size of 2000 bytes, on
> 64-bit systems they have 1996 bytes. Is this normal/on purpose?

I don't have the exact numbers in my head, but the TOAST chunk size does
depend on a MAXALIGN calculation, so it being different between 32- and
64-bit isn't surprising.

            regards, tom lane