search

grayhemp / pgtoolkit

Tools for PostgreSQL maintenance
Other
184 stars 16 forks source link

Compaction does not work on PostgreSQL 9.6 #15

Open vladimirfx opened 7 years ago

vladimirfx commented 7 years ago

In process of migration to PG 9.6 we notice strange behavior of pgcompact - it cant clean any page on any bloated table. We are seeing logs like this:

Sat Nov 26 13:37:12 2016 oltpdb_dev, public.question_hist NOTICE Processing results: 301865 pages left (308484 pages including toasts and indexes), size reduced by 0 bytes (-288 kB including toasts and indexes) in total, approximately 37.33% (112682 pages) that is 880 MB more were expected to be compacted after this attempt.
Sat Nov 26 13:37:32 2016 oltpdb_dev, public.task_hist NOTICE Statistics: 339868 pages (422017 pages including toasts and indexes), approximately 21.21% (72098 pages) can be compacted reducing the size by 563 MB.
Sat Nov 26 13:37:41 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 13500 pages, 339868 pages left, duration 0.291 seconds.
Sat Nov 26 13:37:44 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 20100 pages, 339868 pages left, duration 0.297 seconds.
Sat Nov 26 13:37:46 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 26500 pages, 339868 pages left, duration 0.283 seconds.
Sat Nov 26 13:37:49 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 32800 pages, 339868 pages left, duration 0.279 seconds.
Sat Nov 26 13:37:52 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 39000 pages, 339868 pages left, duration 0.285 seconds.
Sat Nov 26 13:37:54 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 45100 pages, 339868 pages left, duration 0.300 seconds.
Sat Nov 26 13:37:57 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 51000 pages, 339868 pages left, duration 0.291 seconds.
Sat Nov 26 13:38:00 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 56800 pages, 339868 pages left, duration 0.280 seconds.
Sat Nov 26 13:38:02 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 62500 pages, 339868 pages left, duration 0.282 seconds.
Sat Nov 26 13:38:04 2016 oltpdb_dev, public.task_hist NOTICE Vacuum routine: can not clean 68100 pages, 339868 pages left, duration 0.289 seconds.
Sat Nov 26 13:38:07 2016 oltpdb_dev, public.task_hist NOTICE Vacuum final: can not clean 71900 pages, 339868 pages left, duration 0.802 seconds.
Sat Nov 26 13:38:32 2016 oltpdb_dev, public.task_hist WARNING Processing incomplete.
Sat Nov 26 13:38:32 2016 oltpdb_dev, public.task_hist NOTICE Processing results: 339868 pages left (422017 pages including toasts and indexes), size reduced by 0 bytes (-320 kB including toasts and indexes) in total, approximately 21.21% (72098 pages) that is 563 MB more were expected to be compacted after this attempt.

I've search extensively PG release notes from 9.4 to 9.6 but found nothing relevant. Does pgcompact conceptually broken from 9.6 or there is some options?

P.S.: I've tryed many variations of configuration on 4 clusters with very different DB schema without success...

bfgoodrich commented 7 years ago

@vladimirfx - did you ever figure out a solution or is this still an issue?

vladimirfx commented 7 years ago

No, there is no working solution yet. We are investigating alternative tool https://github.com/cybertec-postgresql/pg_squeeze , but it seems to be not stable enough.