Re: 8.0.3 pg_autovacuum doesn't clear out stats table?

Jim C. Nasby wrote:

>Please upgrade.
>
>Are you running with the default FSM settings? I'm guessing that
>pg_statistics has just gotten so large that it's blowing out the FSM.
>The last few lines from vacuumdb -av would verify that...
>
>
Jim,

I am running this in a critical production environment, so I would like
to understand whether upgrading is my only option before I do it (I'll
need to do some testing first, then organise downtime, etc. - I will do
it if required, but would rather a workaround!). If I do upgrade, I
assume you mean upgrade to the current 8.1.x release?

Yes, I am running with the default FSM settings.

Here is the output from vacuumdb -v gems (I ran it against my tiny test
database, not the production database right now, but remember that even
this tiny test database had an enormous pg_statistic table):

<snip> this section relates to vacuuming pg_statistic
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: index "pg_statistic_relid_att_index" now contains 293 row
versions in 15421 pages
DETAIL: 2265351 index row versions were removed.
15072 index pages have been deleted, 7480 are currently reusable.
CPU 3.25s/1.54u sec elapsed 304.19 sec.
<snip>

<snip> this is the last few lines as requested
INFO: free space map: 182 relations, 25120 pages stored; 98272 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
<snip>

Here is the output from vacuumdb -av on my standby server which has a
recent copy of the main production data (via pg_dump/pg_restore) but has
very few (if any) transactions executed on it since the copy was made.

<snip> the section relates to the copy of the production database on the
backup server
INFO: vacuuming "pg_catalog.pg_statistic"
INFO: index "pg_statistic_relid_att_index" now contains 3821225 row
versions in 35676 pages
DETAIL: 2795920 index row versions were removed.
19879 index pages have been deleted, 11950 are currently reusable.
CPU 6.58s/7.35u sec elapsed 321.85 sec.
INFO: "pg_statistic": removed 2795920 row versions in 81619 pages
DETAIL: CPU 17.89s/4.45u sec elapsed 191.72 sec.
INFO: index "pg_statistic_relid_att_index" now contains 1025283 row
versions in 35676 pages
DETAIL: 2795942 index row versions were removed.
28947 index pages have been deleted, 11950 are currently reusable.
CPU 6.15s/6.77u sec elapsed 120.23 sec.
INFO: "pg_statistic": removed 2795942 row versions in 82363 pages
DETAIL: CPU 18.23s/4.48u sec elapsed 161.45 sec.
INFO: index "pg_statistic_relid_att_index" now contains 1554 row
versions in 35676 pages
DETAIL: 1023729 index row versions were removed.
34045 index pages have been deleted, 11950 are currently reusable.
CPU 3.01s/2.31u sec elapsed 46.31 sec.
INFO: "pg_statistic": removed 1023729 row versions in 30288 pages
DETAIL: CPU 6.96s/1.50u sec elapsed 69.42 sec.
INFO: "pg_statistic": found 6615591 removable, 1554 nonremovable row
versions in 395203 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 7545029 unused item pointers.
0 pages are entirely empty.
CPU 64.16s/28.04u sec elapsed 1017.92 sec.
<snip>

<snip> All of the FSM sections from the whole cluster during the
vacuumdb -av run.
INFO: free space map: 6 relations, 19936 pages stored; 21632 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
INFO: free space map: 6 relations, 19936 pages stored; 21632 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
INFO: free space map: 6 relations, 19939 pages stored; 21648 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
INFO: free space map: 6 relations, 19939 pages stored; 21648 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
INFO: free space map: 6 relations, 19975 pages stored; 29600 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB
shared memory.
<snip>

I don't actually understand exactly what this is telling me though!

Thanks,
Robin