Re: Error when clustering a table
| От | Benjamin Krajmalnik |
|---|---|
| Тема | Re: Error when clustering a table |
| Дата | |
| Msg-id | F4E6A2751A2823418A21D4A160B68988613A43@fletch.stackdump.local обсуждение исходный текст |
| Ответ на | Re: Error when clustering a table ("Benjamin Krajmalnik" <kraj@illumen.com>) |
| Список | pgsql-admin |
Strange – seems to have sorted itself out.
I turned off a scheduled vacuum of the table and within a few minutes I sorted itself out.
I have reduced the frequency of the vacuuming task which is being carried out via pgagent.
From: Benjamin Krajmalnik
Sent: Monday, December 14, 2009 12:28 PM
To: Benjamin Krajmalnik; 'pgsql-admin@postgresql.org'
Subject: RE: Error when clustering a table
Additional info:
When I perform a vacuum full, this is the information I am getting:
INFO: vacuuming "public.tblksaura"
INFO: "tblksaura": found 89 removable, 238907 nonremovable row versions in 59808 pages
DETAIL: 200792 dead row versions cannot be removed yet.
Nonremovable row versions range from 1776 to 2032 bytes long.
There were 391587 unused item pointers.
Total free space (including removable row versions) is 19992676 bytes.
3 pages are or will become empty, including 0 at the end of the table.
616 pages containing 945300 free bytes are potential move destinations.
CPU 0.00s/0.22u sec elapsed 0.23 sec.
INFO: index "tblksaura_kstestssysid_key" now contains 58562 row versions in 211 pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
WARNING: index "tblksaura_kstestssysid_key" contains 58562 row versions, but table contains 229083 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_pkey" now contains 58562 row versions in 211 pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
WARNING: index "tblksaura_pkey" contains 58562 row versions, but table contains 229083 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_idx_time" now contains 58562 row versions in 183 pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
WARNING: index "tblksaura_idx_time" contains 58562 row versions, but table contains 229083 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_idx_kstestssysid" now contains 58562 row versions in 298 pages
DETAIL: 13 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.
WARNING: index "tblksaura_idx_kstestssysid" contains 58562 row versions, but table contains 229083 row versions
HINT: Rebuild the index with REINDEX.INFO: "tblksaura": moved 168 row versions, truncated 59808 to 59803 pages
DETAIL: CPU 0.00s/0.13u sec elapsed 0.13 sec.INFO: index "tblksaura_kstestssysid_key" now contains 58714 row versions in 212 pages
DETAIL: 16 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.
WARNING: index "tblksaura_kstestssysid_key" contains 58714 row versions, but table contains 229235 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_pkey" now contains 58714 row versions in 212 pages
DETAIL: 16 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.
WARNING: index "tblksaura_pkey" contains 58714 row versions, but table contains 229235 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_idx_time" now contains 58714 row versions in 183 pages
DETAIL: 16 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.
WARNING: index "tblksaura_idx_time" contains 58714 row versions, but table contains 229235 row versions
HINT: Rebuild the index with REINDEX.
INFO: index "tblksaura_idx_kstestssysid" now contains 58714 row versions in 299 pages
DETAIL: 16 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.
WARNING: index "tblksaura_idx_kstestssysid" contains 58714 row versions, but table contains 229235 row versions
HINT: Rebuild the index with REINDEX.INFO: vacuuming "pg_toast.pg_toast_145099"INFO: "pg_toast_145099": found 245 removable, 117973 nonremovable row versions in 4479 pages
DETAIL: 101666 dead row versions cannot be removed yet.
Nonremovable row versions range from 178 to 312 bytes long.
There were 31907 unused item pointers.
Total free space (including removable row versions) is 726932 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1411 pages containing 481984 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.02 sec.INFO: index "pg_toast_145099_index" now contains 117973 row versions in 342 pages
DETAIL: 245 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.01u sec elapsed 0.01 sec.INFO: "pg_toast_145099": moved 495 row versions, truncated 4479 to 4457 pages
DETAIL: CPU 0.00s/0.02u sec elapsed 0.02 sec.
INFO: index "pg_toast_145099_index" now contains 117973 row versions in 343 pages
DETAIL: 495 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.Total query runtime: 719 ms.
What can be causing such a high number of rows to be nonremovable?
From: Benjamin Krajmalnik
Sent: Monday, December 14, 2009 12:17 PM
To: pgsql-admin@postgresql.org
Subject: Error when clustering a table
I am trying to cluster a table for which vacuum full is not reducing its size.
When I do so, I am getting the following error:
ERROR: missing chunk number 0 for toast value 207869115 in pg_toast_145099
********** Error **********
ERROR: missing chunk number 0 for toast value 207869115 in pg_toast_145099
SQL state: XX000
Any idea how this can be remediated?
В списке pgsql-admin по дате отправления: