PG 8.2.3, FSM warning & VACUUM
От | Sergey E. Koposov |
---|---|
Тема | PG 8.2.3, FSM warning & VACUUM |
Дата | |
Msg-id | Pine.LNX.4.64.0704050220350.6741@lnfm1.sai.msu.ru обсуждение исходный текст |
Список | pgsql-general |
Hello All, Recently I experienced the strange PG behaviour with the large tables in PG 8.2.3 which I didn't have in 8.1: I have the large table: around 1 million records and around 400 columns. When I try to run VACUUM ANALYZE on it, I'm getting the message: WARNING: relation "public.xx" contains more than "max_fsm_pages" pages with useful free space HINT: Consider compacting this relation or increasing the configuration parameter "max_fsm_pages". Which was strange to me , because I never updated or deleted from that table. I tried to run VACUUM FULL -- but it didn't change anything ... The plain VACUUM still was producing the warning about the FSM... I even tried to recreate the table doing CREATE new_table as SELECT * FROM old_table But it didn't change anything ... Still the warning is produced by VACUUM. Which is strange for me, because I thought that the recreation of table should garantee the absence of the free space ... Below I inserted the output of VACUUM FULL VERBOSE ANALYZE and VACUUM FULL VERBOSE ANALYZE The value of max_fsm_pages is 204800 So. Is the observed behaviour expected (especially the warning after executing VACUUM FULL and recreation of the table CREATE TABLE AS SELECT * FROM ) ? Or is this a bug ? BTW, it is strange that VACUUM FULL doesn't produce the warning ... , while plain VACUUM does. Thanks, Sergey Output of VACUUM and VACUUM FULL... cas=# VACUUM FULL VERBOSE ANALYZE xx; INFO: vacuuming "public.xx" INFO: "xx": found 0 removable, 1647599 nonremovable row versions in 554353 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 292 to 3092 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 268302952 bytes. 0 pages are or will become empty, including 0 at the end of the table. 327022 pages containing 224924296 free bytes are potential move destinations. CPU 2.51s/1.90u sec elapsed 4.42 sec. INFO: "xx": moved 0 row versions, truncated 554353 to 554353 pages DETAIL: CPU 0.00s/0.01u sec elapsed 0.01 sec. INFO: vacuuming "pg_toast.pg_toast_845776" INFO: "pg_toast_845776": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 0 to 0 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 0 bytes. 0 pages are or will become empty, including 0 at the end of the table. 0 pages containing 0 free bytes are potential move destinations. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_845776_index" now contains 0 row versions in 1 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.xx" INFO: "xx": scanned 3000 of 554353 pages, containing 8918 live rows and 0 dead rows; 3000 rows in sample, 1647907 estimated total rows VACUUM cas=# VACUUM VERBOSE ANALYZE xx; INFO: vacuuming "public.xx" INFO: "xx": found 0 removable, 1647599 nonremovable row versions in 554353 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 364358 pages contain useful free space. 0 pages are entirely empty. CPU 4.29s/2.32u sec elapsed 39.88 sec. WARNING: relation "public.xx" contains more than "max_fsm_pages" pages with useful free space HINT: Consider compacting this relation or increasing the configuration parameter "max_fsm_pages". INFO: vacuuming "pg_toast.pg_toast_845776" INFO: index "pg_toast_845776_index" now contains 0 row versions in 1 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: "pg_toast_845776": found 0 removable, 0 nonremovable row versions in 0 pages DETAIL: 0 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages contain useful free space. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.xx" INFO: "xx": scanned 3000 of 554353 pages, containing 8907 live rows and 0 dead rows; 3000 rows in sample, 1645874 estimated total rows VACUUM ******************************************************************* Sergey E. Koposov Max Planck Institute for Astronomy/Cambridge Institute for Astronomy/Sternberg Astronomical Institute Tel: +49-6221-528-349 Web: http://lnfm1.sai.msu.ru/~math E-mail: math@sai.msu.ru
В списке pgsql-general по дате отправления: