Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
От | Robin Iddon |
---|---|
Тема | Re: 8.0.3 pg_autovacuum doesn't clear out stats table? |
Дата | |
Msg-id | 43FE4A1A.7050903@edesix.com обсуждение исходный текст |
Ответ на | Re: 8.0.3 pg_autovacuum doesn't clear out stats table? ("Jim C. Nasby" <jnasby@pervasive.com>) |
Ответы |
Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
("Jim C. Nasby" <jnasby@pervasive.com>)
|
Список | pgsql-admin |
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
В списке pgsql-admin по дате отправления:
Предыдущее
От: "Jim C. Nasby"Дата:
Сообщение: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?
Следующее
От: "Jim C. Nasby"Дата:
Сообщение: Re: 8.0.3 pg_autovacuum doesn't clear out stats table?