Início / dba / Perguntas / 220701
Accepted
Fake Name
Asked: 2018-10-22 20:30:40 +0800 CST

VACUUM não reduziu o tamanho relatado do banco de dados?

  • 772

Eu tenho um banco de dados com uma tabela extremamente grande no postgresql.

Estou ciente de que a única maneira de reduzir o tamanho do disco é VACUUM FULL, mas não posso fazer isso, pois não tenho espaço livre suficiente (é uma tabela de 920 GB em um disco de 1 TB e não posso pagar outro ATM SSD de 1 TB).

No entanto, eu executei VACUUM VERBOSE ANALYZE web_pagese foi concluído, mas o tamanho da tabela (conforme relatado em psql) não foi reduzido.

Basicamente, existe uma maneira de reduzir uma tabela sem VACUUM FULLou completar o dump/load? Eu tenho espaço para um dump/load, mas neste momento eu espero que demore mais de uma semana.

Saída de vácuo:

webarchive=# VACUUM VERBOSE ANALYZE web_pages;
INFO:  vacuuming "public.web_pages"
INFO:  scanned index "ix_web_pages_distance_filtered" to remove 145580643 row versions
DETAIL:  CPU 4.46s/165.77u sec elapsed 324.63 sec
INFO:  scanned index "ix_web_pages_netloc" to remove 145580643 row versions
DETAIL:  CPU 40.65s/4686.88u sec elapsed 5387.13 sec
INFO:  scanned index "ix_web_pages_priority" to remove 145580643 row versions
DETAIL:  CPU 29.59s/1018.71u sec elapsed 1452.67 sec
INFO:  scanned index "ix_web_pages_state" to remove 145580643 row versions
DETAIL:  CPU 22.08s/303.12u sec elapsed 712.94 sec
INFO:  scanned index "ix_web_pages_url" to remove 145580643 row versions
DETAIL:  CPU 283.45s/673.39u sec elapsed 7583.39 sec
INFO:  scanned index "web_pages_pkey" to remove 145580643 row versions
DETAIL:  CPU 51.69s/90.19u sec elapsed 1461.37 sec
INFO:  scanned index "ix_web_pages_id" to remove 145580643 row versions
DETAIL:  CPU 63.13s/99.77u sec elapsed 1529.22 sec
INFO:  scanned index "web_pages_netloc_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 77.04s/5080.52u sec elapsed 6287.14 sec
INFO:  scanned index "id_web_pages_id_state" to remove 145580643 row versions
DETAIL:  CPU 64.52s/107.81u sec elapsed 1695.07 sec
INFO:  scanned index "web_pages_fetchtime_idx" to remove 145580643 row versions
DETAIL:  CPU 12.06s/99.66u sec elapsed 408.36 sec
INFO:  "web_pages": removed 145580643 row versions in 8584664 pages
DETAIL:  CPU 226.70s/140.17u sec elapsed 5019.28 sec
INFO:  index "ix_web_pages_distance_filtered" now contains 16007295 row versions in 814166 pages
DETAIL:  38738938 index row versions were removed.
570268 index pages have been deleted, 385915 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "ix_web_pages_netloc" now contains 27370778 row versions in 3181634 pages
DETAIL:  67244989 index row versions were removed.
2669376 index pages have been deleted, 1876620 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_priority" now contains 27370960 row versions in 2006220 pages
DETAIL:  67218177 index row versions were removed.
1056657 index pages have been deleted, 786603 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.03 sec.
INFO:  index "ix_web_pages_state" now contains 27370969 row versions in 1532024 pages
DETAIL:  67244989 index row versions were removed.
986826 index pages have been deleted, 700367 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_url" now contains 27382514 row versions in 7555366 pages
DETAIL:  78562001 index row versions were removed.
4290425 index pages have been deleted, 225461 are currently reusable.
CPU 0.02s/0.00u sec elapsed 0.04 sec.
INFO:  index "web_pages_pkey" now contains 27401242 row versions in 2421605 pages
DETAIL:  78000787 index row versions were removed.
1068399 index pages have been deleted, 373558 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "ix_web_pages_id" now contains 27411627 row versions in 2874706 pages
DETAIL:  82612172 index row versions were removed.
1290296 index pages have been deleted, 442226 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_netloc_fetchtime_idx" now contains 27556711 row versions in 4482440 pages
DETAIL:  80962513 index row versions were removed.
3373490 index pages have been deleted, 1873800 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  index "id_web_pages_id_state" now contains 27558627 row versions in 3094617 pages
DETAIL:  81497647 index row versions were removed.
1735454 index pages have been deleted, 631419 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  index "web_pages_fetchtime_idx" now contains 27559941 row versions in 656103 pages
DETAIL:  67710984 index row versions were removed.
228974 index pages have been deleted, 95938 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "web_pages": found 32297714 removable, 26459019 nonremovable row versions in 14298550 out of 14827067 pages
DETAIL:  1671 dead row versions cannot be removed yet.
There were 378926914 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 1149.21s/12598.17u sec elapsed 35893.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_38269400"
INFO:  scanned index "pg_toast_38269400_index" to remove 178956680 row versions
DETAIL:  CPU 33.85s/139.43u sec elapsed 774.95 sec
INFO:  "pg_toast_38269400": removed 178956680 row versions in 47342563 pages
DETAIL:  CPU 1267.31s/752.22u sec elapsed 22404.29 sec
INFO:  scanned index "pg_toast_38269400_index" to remove 162873580 row versions
DETAIL:  CPU 20.65s/43.54u sec elapsed 216.38 sec
INFO:  "pg_toast_38269400": removed 162873580 row versions in 39900140 pages
DETAIL:  CPU 1085.52s/716.33u sec elapsed 13775.48 sec
INFO:  index "pg_toast_38269400_index" now contains 91453965 row versions in 1622691 pages
DETAIL:  341830260 index row versions were removed.
540140 index pages have been deleted, 1626 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
INFO:  "pg_toast_38269400": found 275718152 removable, 85526893 nonremovable row versions in 102611808 out of 104048880 pages
DETAIL:  1031 dead row versions cannot be removed yet.
There were 14286891 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 4786.16s/3240.77u sec elapsed 79646.66 sec.
INFO:  analyzing "public.web_pages"
INFO:  "web_pages": scanned 90000 of 14840002 pages, containing 166193 live rows and 1769 dead rows; 90000 rows in sample, 27403383 estimated total rows
VACUUM
webarchive=#

Relatório de tamanho antes:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
..... 
public | web_pages                | table    | webarchuser | 920 GB     |
.....

Depois:

webarchive=# \d+
                                List of relations
 Schema |                Name      |   Type   |    Owner    |    Size    | Description
--------+--------------------------+----------+-------------+------------+-------------
 ........
 public | web_pages                | table    | webarchuser | 920 GB     |
 ........

Eu percebo que a solução "correta" aqui seria um disco maior, mas este é um projeto de hobby (embora em uma escala muito grande), e eu simplesmente não tenho dinheiro para um armazenamento SSD maior.

postgresql maintenance

3 respostas

  1. Best Answer

    Também não há como diminuir o tamanho relatado (por exemplo, o que você obtém com \d+ no psql)? Eu assumi que \d+ relatou o tamanho das tuplas usadas, em vez de apenas as primitivas de disco

    Não, \d+ informa o tamanho no disco. Se você quiser saber quanto espaço está disponível para reutilização interna, consulte a extensão principal pg_freespacemap . Se isso mostrar muito espaço interno e você quiser realmente tentar reduzir a tabela e retornar espaço para o sistema operacional, tente a extensão pg_repack não principal. Mas se um dump e um recarregamento demorarem uma semana, duvido que o pg_repack termine rapidamente.

    • 3

  2. VACUUMlibera espaço dentro das tabelas, se você quiser reduzir o tamanho do disco das tabelas você deve usarVACUUM FULL

    • 1

  3. O vácuo cortará as tabelas se os últimos registros da tabela estiverem mortos. portanto, com vácuos frequentes e churn (reescrita de registros) suficientes, a tabela diminuirá gradualmente.

    O manual: https://www.postgresql.org/docs/current/static/routine-vacuuming.html

    A forma padrão de VACUUM remove versões de linhas inativas em tabelas e índices e marca o espaço disponível para reutilização futura. No entanto, ele não retornará o espaço para o sistema operacional, exceto no caso especial em que uma ou mais páginas no final de uma tabela ficam totalmente livres e um bloqueio de tabela exclusivo pode ser facilmente obtido.

    • 0

relate perguntas