1

Temat: VACUUM FULL zacina się?

Witam,

Mam problem z dużą tabelą, nie działa na niej VACUUM FULL a dokładniej zacina się po vacuumowaniu indexa pg_toast

db1=# reindex index pg_toast.pg_toast_17929229_index; reindex table pg_toast.pg_toast_17929229; vacuum full verbose sch1.myobjects;
REINDEX
REINDEX
VACUUM
INFO:  vacuuming "sch1.myobjects"
INFO:  "myobjects": found 8359 removable, 265619 nonremovable row versions in 25899 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 96 to 2032 bytes long.
There were 286682 unused item pointers.
Total free space (including removable row versions) is 81949164 bytes.
5540 pages are or will become empty, including 1 at the end of the table.
18640 pages containing 81678752 free bytes are potential move destinations.
CPU 0.47s/0.06u sec elapsed 1.69 sec.
INFO:  index "myobjects_pkey" now contains 265619 row versions in 1503 pages
DETAIL:  8359 index row versions were removed.
70 index pages have been deleted, 70 are currently reusable.
CPU 0.05s/0.10u sec elapsed 1.55 sec.
INFO:  index "mob" now contains 265619 row versions in 4855 pages
DETAIL:  8359 index row versions were removed.
358 index pages have been deleted, 358 are currently reusable.
CPU 0.20s/0.12u sec elapsed 9.42 sec.
INFO:  index "so_idt" now contains 265619 row versions in 1506 pages
DETAIL:  8359 index row versions were removed.
75 index pages have been deleted, 75 are currently reusable.
CPU 0.07s/0.12u sec elapsed 1.44 sec.
INFO:  index "so_obclass" now contains 265619 row versions in 1938 pages
DETAIL:  8359 index row versions were removed.
10 index pages have been deleted, 10 are currently reusable.
CPU 0.04s/0.10u sec elapsed 3.74 sec.
INFO:  index "so_obstring" now contains 265619 row versions in 3240 pages
DETAIL:  8359 index row versions were removed.
218 index pages have been deleted, 218 are currently reusable.
CPU 0.09s/0.15u sec elapsed 5.03 sec.
INFO:  "myobjects": moved 8244 row versions, truncated 25899 to 15998 pages
DETAIL:  CPU 1.98s/2.05u sec elapsed 25.51 sec.
INFO:  index "myobjects_pkey" now contains 265619 row versions in 1503 pages
DETAIL:  8244 index row versions were removed.
76 index pages have been deleted, 76 are currently reusable.
CPU 0.04s/0.05u sec elapsed 0.09 sec.
INFO:  index "mob" now contains 265619 row versions in 4855 pages
DETAIL:  8244 index row versions were removed.
397 index pages have been deleted, 397 are currently reusable.
CPU 0.13s/0.05u sec elapsed 0.19 sec.
INFO:  index "so_idt" now contains 265619 row versions in 1506 pages
DETAIL:  8244 index row versions were removed.
81 index pages have been deleted, 81 are currently reusable.
CPU 0.04s/0.05u sec elapsed 0.09 sec.
INFO:  index "so_obclass" now contains 265619 row versions in 1964 pages
DETAIL:  8244 index row versions were removed.
690 index pages have been deleted, 690 are currently reusable.
CPU 0.07s/0.04u sec elapsed 0.12 sec.
INFO:  index "so_obstring" now contains 265619 row versions in 3240 pages
DETAIL:  8244 index row versions were removed.
240 index pages have been deleted, 240 are currently reusable.
CPU 0.08s/0.06u sec elapsed 0.14 sec.
INFO:  vacuuming "pg_toast.pg_toast_17929229"
INFO:  "pg_toast_17929229": found 6203 removable, 257509 nonremovable row versions in 20509016 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 45 to 2030 bytes long.
There were 87521183 unused item pointers.
Total free space (including removable row versions) is 166910212580 bytes.
20384082 pages are or will become empty, including 3 at the end of the table.
20507826 pages containing 166910155528 free bytes are potential move destinations.
CPU 412.14s/58.08u sec elapsed 3632.16 sec.
INFO:  index "pg_toast_17929229_index" now contains 257509 row versions in 709 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.02s/0.15u sec elapsed 0.22 sec.

i tak stoi...
Po wyświetleniu ostatniego komunikatu status vacuuma wygląda tak:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25948 postgres  18   0 3543m 3.2g 1.9g D    2 20.6  20:09.29 postgres: postgres db1 [local] VACUUM

Około 8 godzin później żaden nowy komunikat się nie pojawia, a proces wygląda tak:

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25948 postgres  17   0 3544m 3.2g 1.9g D    2 20.7  27:55.63 postgres: postgres db1 [local] VACUUM

Na tej tabeli jedynie vacuum FULL się "zawiesza", pozostałe kończą się prawidłowo.
Czy ten vacuum się zakończy? Dodam, że żadne transakcje wtedy nie są przeprowadzane (nie ma w procesach "idle in transaction").

Vacuum analyze kończy się bez problemu:

(...)
INFO:  vacuuming "pg_toast.pg_toast_17929229"
INFO:  index "pg_toast_17929229_index" now contains 260391 row versions in 1279 pages
DETAIL:  194099 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.16u sec elapsed 0.18 sec.
INFO:  "pg_toast_17929229": removed 196567 row versions in 42846 pages
DETAIL:  CPU 0.05s/0.32u sec elapsed 1.24 sec.
INFO:  "pg_toast_17929229": found 196567 removable, 257570 nonremovable row versions in 20510280 pages
DETAIL:  321 dead row versions cannot be removed yet.
There were 87339448 unused item pointers.
0 pages are entirely empty.
CPU 266.24s/51.49u sec elapsed 1920.18 sec.
INFO:  analyzing "sch1.myobjects"
INFO:  "myobjects": scanned 3000 of 16620 pages, containing 48341 live rows and 1107 dead rows; 3000 rows in sample, 267809 estimated total rows
STATYSTYKA TABELI               WARTOŚĆ
Sekwencyjne Skanowanie          447
Sekwencyjne czytanie krotek     112970571
Skanowanie Indeksu              134224142
Otrzymane krotki z indeksu      150731640
Krotki Wstawione                2264237
Krotki Zmodyfikowane            66627716
Krotki Usunięte                 2251604
Czytane Bloki Sterty            449134812
Trafione Bloki Sterty           2961264210
Czytane Bloki Indeksu           171414088
Trafione Bloki Indeksu          2925322632
Czytane Bloki Toast             7023198390
Trafione Bloki Toast            149901760
Czytane Bloki Indeks Toast      59609445
Trafione Bloki Indeks Toast     674523766
Rozmiar Tabeli                  129 MB
Rozmiar Tabeli Toast            156 GB
Rozmiar Indeksu                 102 MB

PostgreSQL 8.1.18

Ostatnio edytowany przez Krowax (2009-10-13 08:40:46)

2

Odp: VACUUM FULL zacina się?

usuń index, zrób vacuum, załóż index, zrób jeszcze raz vacuum
miałem ostatnio podobny problem z tabelką w której mam 70tys wierszy
ale pomogło dopiero gdy index zmieniłem z hash na btree