Vacuum databáze nevrací volné místo

[jhonzatko]

Ahoj,
nepotkal jste se nekdo s tim, ze i kdyz poustim vacuum (at uz autovacuum nebo vacuum analyze) horem dolem, tak se mi proste misto nevraci?

Kdyz tabulku vydumpuju, je vyrazne mensi nez kolik zabira ... ale export/drop/import mi neprijde jako prilis elegantni reseni. Vacuum se tvari jako ze bezi, pri verbose rezimu vypisuje, kolik to uvolnilo dead tuples, ale vysledkem je stejne nula ...

Nejak mi uz dochazi napady, co jeste muzu delat spatne.
Diky za pripadne zkusenosti!

[Reklama]

[ja.]

Z dokumentácie:

Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. However, extra space is not returned to the operating system (in most cases); it's just kept available for re-use within the same table. VACUUM FULL rewrites the entire contents of the table into a new disk file with no extra space, allowing unused space to be returned to the operating system. This form is much slower and requires an exclusive lock on each table while it is being processed.

[Ondrej Nemecek]

Jojo, zkuste VACUUM FULL. Ještě mohou hrát roli indexy - pokud je jich hodně a jsou netriviální, mohou zabírat dost místa.

[Pavel Rauš]

VACUUM FULL + REINDEX

Nicméně pokud to bobtná hodně, tak je to buď chyba v aplikaci a/nebo nemáte správně nastavené autovacuum. Zkuste zjistit kde máte největší bloat

Kód: [Vybrat]

SELECT
  current_database(), schemaname, tablename, /*reltuples::bigint, relpages::bigint, otta,*/
  ROUND((CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages::float/otta END)::numeric,1) AS tbloat,
  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::BIGINT END AS wastedbytes,
  iname, /*ituples::bigint, ipages::bigint, iotta,*/
  ROUND((CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages::float/iotta END)::numeric,1) AS ibloat,
  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes
FROM (
  SELECT
    schemaname, tablename, cc.reltuples, cc.relpages, bs,
    CEIL((cc.reltuples*((datahdr+ma-
      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta,
    COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
    COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
  FROM (
    SELECT
      ma,bs,schemaname,tablename,
      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
    FROM (
      SELECT
        schemaname, tablename, hdr, ma, bs,
        SUM((1-null_frac)*avg_width) AS datawidth,
        MAX(null_frac) AS maxfracsum,
        hdr+(
          SELECT 1+count(*)/8
          FROM pg_stats s2
          WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename
        ) AS nullhdr
      FROM pg_stats s, (
        SELECT
          (SELECT current_setting('block_size')::numeric) AS bs,
          CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
          CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
        FROM (SELECT version() AS v) AS foo
      ) AS constants
      GROUP BY 1,2,3,4,5
    ) AS foo
  ) AS rs
  JOIN pg_class cc ON cc.relname = rs.tablename
  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema'
  LEFT JOIN pg_index i ON indrelid = cc.oid
  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
) AS sml
ORDER BY wastedbytes DESC

 - to Vás navede kde je problém. Pak lze případně udělat změnu parametrů pro autovacuum pro konkrétní tabulky přes ALTER TABLE.

Mimochodem jaký je ten výrazný rozdíl, o kterém píšete ? Pokud je to třeba dvojnásobek, tak to nemusí být velký problém. Obecně lze říci, že výkon začne degradovat až když je tabulka mnohonásobě větší než množství dat uvnitř.