db.dobo.sk

Zaujímavý problém, ktorý riešil v práci môj kolega.

Podstata problému tkvela v tom, že veľkosť foldru s datovými fajlami (du -sh /var/lib/pgsql/13/data/base) bola omnoho väčšia, než dotaz na veľkosť všetkých databází v clusteru. Taký dotaz vypadá takto:

SELECT database_name, pg_size_pretty(size)

FROM

(SELECT pg_database.datname as “database_name”, pg_database_size(pg_database.datname) AS size FROM pg_database

ORDER BY size DESC) as zarovnane;

Da sa to vyextrahovať i dotazovaním na tabuľky v jednotlivých databázach:

SELECT pg_size_pretty(sum(pg_total_relation_size(oid)))

FROM pg_class pc

WHERE pc.relkind = ‘r’;

Ako je to možné? No tak, že pg_class (evidencia všetkých relačných objektov v DB) “nemal info” o všetkých objektoch v datovom foldri – proste sa tam nachádzali siroty, o ktorých databáza nemala povedomie (a nechýbali pri vacuume a iných operáciách), ktoré ovšem zaberali diskový priestor.

Tento dotaz vylistuje názvy datových fajlov, ktoré sú sirotami a môžu sa natvrdo z basedir zmazať:

SELECT * FROM pg_ls_dir ( (SELECT setting || ‘/base/’ || oid FROM pg_settings, pg_database WHERE pg_settings.name = ‘data_directory’ AND pg_database.datname = CURRENT_CATALOG) ) AS file

WHERE file ~ ‘^[0-9]*$’ AND file::OID NOT IN (SELECT pg_relation_filenode(pg_class.oid)

FROM pg_class

WHERE relkind in (‘i’,’r’,’t’,’S’,’m’) AND reltablespace=0 );

relation_size, table_size, total_relation_size

Pri doptávání sa na veľkosť tabuliek sa človek často dostane do situcie, kedy nevie, čo z tohoto použiť. Obrázok (ukradnutý tu) za všetky slová…

• funkcia pg_relation_size = veľkosť holej tabuľky
• funkcia pg_table_size = veľkosť tabuľky a TOASTu
• funkcia pg_total_relation_size = veľkosť tabuľky, TOASTu a indexov k tabuľke

Prehľad všetkých “veľkostných” funkcií tu. Dá sa pýtať i na veľkosťjednotlivých zložiek tauľky (“fsm“, “vm“, “init“).