Vacuum and Reindex hangs - Mailing list pgsql-general
From | Jason Long |
---|---|
Subject | Vacuum and Reindex hangs |
Date | |
Msg-id | 496F975D.4000307@supernovasoftware.com Whole thread Raw |
Responses |
Re: Vacuum and Reindex hangs
Re: Vacuum and Reindex hangs Re: Vacuum and Reindex hangs |
List | pgsql-general |
I am attempting to vacuum and reindex my database. It keeps timing out. See commands and last part of output below. The vacuum or reindex only takes a short time to complete normally because the database it less than 50 mb. I have the query timeout set to 2 minutes, but I do not know if this can affect vacuumdb or reindexdb and the time should take much less than 2 minutes anyway.
I am using 8.3.3 on Centos 5.
Any advice would be greatly appreciated.
vacuumdb -f -v -z -e -d pipetracker-live
INFO: analyzing "general.t_access"
INFO: "t_access": scanned 3000 of 20186 pages, containing 100449 live rows and 0 dead rows; 3000 rows in sample, 675888 estimated total rows
INFO: vacuuming "information_schema.sql_parts"
INFO: "sql_parts": found 0 removable, 9 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 56 to 96 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7500 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7500 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sql_parts": moved 0 row versions, truncated 1 to 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_11449"
INFO: "pg_toast_11449": 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_11449_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 "information_schema.sql_parts"
INFO: "sql_parts": scanned 1 of 1 pages, containing 9 live rows and 0 dead rows; 9 rows in sample, 9 estimated total rows
vacuumdb: vacuuming of database "pipetracker-live" failed: ERROR: canceling statement due to statement timeout
reindexdb -d pipetracker-live
NOTICE: table "pg_class" was reindexed
NOTICE: table "pg_type" was reindexed
NOTICE: table "sql_features" was reindexed
NOTICE: table "sql_implementation_info" was reindexed
NOTICE: table "sql_languages" was reindexed
NOTICE: table "pg_statistic" was reindexed
NOTICE: table "sql_packages" was reindexed
NOTICE: table "t_access" was reindexed
NOTICE: table "sql_parts" was reindexed
reindexdb: reindexing of database "pipetracker-live" failed: ERROR: canceling statement due to statement timeout
I am using 8.3.3 on Centos 5.
Any advice would be greatly appreciated.
vacuumdb -f -v -z -e -d pipetracker-live
INFO: analyzing "general.t_access"
INFO: "t_access": scanned 3000 of 20186 pages, containing 100449 live rows and 0 dead rows; 3000 rows in sample, 675888 estimated total rows
INFO: vacuuming "information_schema.sql_parts"
INFO: "sql_parts": found 0 removable, 9 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 56 to 96 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 7500 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 7500 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sql_parts": moved 0 row versions, truncated 1 to 1 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_11449"
INFO: "pg_toast_11449": 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_11449_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 "information_schema.sql_parts"
INFO: "sql_parts": scanned 1 of 1 pages, containing 9 live rows and 0 dead rows; 9 rows in sample, 9 estimated total rows
vacuumdb: vacuuming of database "pipetracker-live" failed: ERROR: canceling statement due to statement timeout
reindexdb -d pipetracker-live
NOTICE: table "pg_class" was reindexed
NOTICE: table "pg_type" was reindexed
NOTICE: table "sql_features" was reindexed
NOTICE: table "sql_implementation_info" was reindexed
NOTICE: table "sql_languages" was reindexed
NOTICE: table "pg_statistic" was reindexed
NOTICE: table "sql_packages" was reindexed
NOTICE: table "t_access" was reindexed
NOTICE: table "sql_parts" was reindexed
reindexdb: reindexing of database "pipetracker-live" failed: ERROR: canceling statement due to statement timeout
pgsql-general by date: