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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux