Zanim autovacuum stanie sie widocznym problemem wydajnosciowym, baza juz wczesniej daje sygnaly ostrzegawcze - zbior gotowych zapytan do katalogow systemowych, ktore je wylapuja.
Z tego artykułu dowiesz się:
- jak znaleźć tabele które są czyszczone lub analizowane zbyt rzadko,
- jak oszacować najoptymalniejsze wartości dla scale factora i thresholda,
- jak sprawdzać wiek transakcyjny tabel,
- jak masowo czyścić i analizować najstarsze tabele,
- jak sprawdzać poziom bloatu w tabelach,
- jak sprawdzać poziom bloatu w indeksach.
- Progi dla autovacuuma, szacunkowa liczba wierszy w tabeli, liczba martwych i zmienionych wierszy Zapytanie zwróci informacje o tabelach, w jakich schematach się znajdują, jaką mają szacunkową liczbę wierszy (oparte na statystykach, nie jest to rzeczywista liczba), liczba martwych wierszy, które wymagają wyczyszczenia vacuumem, ile wierszy zostało zmodyfikowanych od ostatniego "vacuum analyze" oraz progi dla autovacuuma na podstawie szacunkowej liczby wierszy i globalnych ustawień autovacuuma. WAŻNE! Jeśli tabela ma indywidualnie ustawione progi dla autovacuuma, nie będą tutaj uwzględnione. Dzięki tym wynikom możemy znaleźć, które są analizowane bądź czyszczone zbyt rzadko, a dzięki liczbie martwych wierszy i modyfikacji od ostatniego autovacuuma możemy oszacować optymalniejszą wartość scale factora i thresholda.
WITH s AS (
SELECT
current_setting('autovacuum_analyze_scale_factor')::float8 - AS - analyze_factor,
current_setting('autovacuum_analyze_threshold')::float8 - AS - analyze_threshold,
current_setting('autovacuum_vacuum_scale_factor')::float8 - AS - vacuum_factor,
current_setting('autovacuum_vacuum_threshold')::float8 - AS - vacuum_threshold
)
SELECT
n.nspname,
c.relname,
c.oid AS relid,
reltuples::bigint AS estimate,
t.n_dead_tup,
t.n_mod_since_analyze,
c.reltuples * s.vacuum_factor + s.vacuum_threshold AS v_threshold,
c.reltuples * s.analyze_factor + s.analyze_threshold AS a_threshold
FROM
s,
pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_all_tables t ON c.oid = t.relid
WHERE
c.relkind = 'r'
ORDER BY c.relname;
- Ostatnie ręcznie i automatyczne wywołania vacuuma Warto wykonywać vacuum/autovacuum przynajmniej raz na tydzień lub na dwa tygodnie, minimalnie raz w miesiącu na każdej tabeli, aby mieć pewność, że jej statystyki są aktualne. Poniższym zapytaniem możemy znaleźć tabele, które nie były vacuumowane najdłużej. Jeżeli nie potrafimy dobrać dla nich odpowiednich progów, warto zaplanować ręczny vacuum w crontabie lub innym planerze zadań raz na jakiś czas w dogodnym czasie, kiedy obciążenie bazy jest najniższe.
SELECT
c.oid AS "oid"
, n.nspname AS "schema"
, c.relname AS "table"
, s.last_vacuum AS last_manual_vacuum
, now() - s.last_vacuum AS time_since_last_manual_vacuum
, s.last_autovacuum AS last_auto_vacuum
, now() - s.last_autovacuum AS time_since_last_auto_vacuum
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_stat_all_tables s ON c.oid = s.relid AND (s.last_vacuum IS NOT NULL OR s.last_autovacuum IS NOT NULL)
WHERE c.relkind IN ('r', 'm')
ORDER BY 6 DESC, time_since_last_auto_vacuum DESC;
- Monitorowanie wieku tabel Poniższe zapytanie zwróci informację o wieku w transakcjach najstarszej tabeli, oblicza procent do osiągnięcia momentu "wraparound" oraz procent do wywołania automatycznego vacuum freeze (autovacuum_max_freeze_age) w celu uwolnienia transakcji z powrotem do puli:
WITH max_age AS (
SELECT 2000000000 as max_old_xid,
setting AS autovacuum_freeze_max_age
FROM pg_catalog.pg_settings
WHERE name = 'autovacuum_freeze_max_age'
),
per_database_stats AS (
SELECT datname
, m.max_old_xid::int
, m.autovacuum_freeze_max_age::int
, age(d.datfrozenxid) AS oldest_current_xid
FROM pg_catalog.pg_database d
JOIN max_age m ON (true)
WHERE d.datallowconn
)
SELECT datname,max(oldest_current_xid) AS oldest_current_xid
, max(ROUND(100*(oldest_current_xid/max_old_xid::float))) AS percent_towards_wraparound
, max(ROUND(100*(oldest_current_xid/autovacuum_freeze_max_age::float))) AS percent_towards_emergency_autovac
FROM per_database_stats
GROUP BY datname
;
Prostsze zapytanie pokazujące tylko nazwę bazy i wiek najstarszego obiektu.
SELECT datname, age(datfrozenxid)
FROM pg_database
ORDER BY 2 DESC,1 ASC
;
- Vacuumowanie najstarszych tabel Zapytanie zwróci listę gotowych instrukcji, do wykonania vacuum freeze na najstarszych tabelach, które możemy wykonać ręcznie lub wszystkie kolejno za pomocą "\gexec".
WITH cur_vaccs AS (SELECT split_part(split_part(substring(query from 'public\..*'),'.',2),' ',1) as tab FROM pg_stat_activity WHERE query like 'autovacuum%')
select 'VACUUM FREEZE "'|| n.nspname ||'"."'|| c.relname ||'"; --"'||pg_size_pretty(pg_table_size(c.oid))||'" --"'||age(c.relfrozenxid)||'"'
from pg_class c
inner join pg_namespace n on c.relnamespace = n.oid
left join pg_class t on c.reltoastrelid = t.oid and t.relkind = 't'
where c.relkind in ('r','m')
AND NOT EXISTS (SELECT * FROM cur_vaccs WHERE tab = c.relname)
order by GREATEST(age(c.relfrozenxid),age(t.relfrozenxid)) DESC
limit 10;
\gexec
- Bloat dla tabel Zapytanie zwraca wartość "bloatu", czyli ilość wolnej przestrzeni w tabelach. Zbyt wysoki poziom wpływa na wydajność zapytań na danej tabeli.
WITH constants AS (
-- definiowanie stalych wykorzystywanych w zapytaniu
-- na podstawie parametrow postgresa
-- przechowywanych jako CTE (Common Table Expression)
SELECT current_setting('block_size')::numeric AS bs, 23 AS hdr, 8 AS ma
),
no_stats AS (
-- odfiltrowanie tabel ktore posiadajacych atrybuty
-- ktore nie posiadaja statystyk, takich jak JSON
SELECT table_schema, table_name,
n_live_tup::numeric as est_rows,
pg_table_size(relid)::numeric as table_size
FROM information_schema.columns
JOIN pg_stat_user_tables as psut
ON table_schema = psut.schemaname
AND table_name = psut.relname
LEFT OUTER JOIN pg_stats
ON table_schema = pg_stats.schemaname
AND table_name = pg_stats.tablename
AND column_name = attname
WHERE attname IS NULL
AND table_schema NOT IN ('pg_catalog', 'information_schema')
GROUP BY table_schema, table_name, relid, n_live_tup
),
null_headers AS (
-- znajdowanie pustych kolumn
-- pomijanie tabel ktore nie posiadaja kompletnych statystyk
-- oraz posiadaja atrybuty dla ktorych statystyki sa niedostepne
SELECT
hdr+1+(sum(case when null_frac <> 0 THEN 1 else 0 END)/8) as nullhdr,
SUM((1-null_frac)*avg_width) as datawidth,
MAX(null_frac) as maxfracsum,
schemaname,
tablename,
hdr, ma, bs
FROM pg_stats CROSS JOIN constants
LEFT OUTER JOIN no_stats
ON schemaname = no_stats.table_schema
AND tablename = no_stats.table_name
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
AND no_stats.table_name IS NULL
AND EXISTS ( SELECT 1
FROM information_schema.columns
WHERE schemaname = columns.table_schema
AND tablename = columns.table_name )
GROUP BY schemaname, tablename, hdr, ma, bs
),
data_headers AS (
-- szacunkowy rozmiar naglowka i wiersza w bloku danych
SELECT
ma, bs, hdr, 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 null_headers
),
table_estimates AS (
-- szacowanie rzeczywistego rozmiaru danych w tabeli
-- na podstawie rozmiaru wierzsza i bloku danych
SELECT schemaname, tablename, bs,
reltuples::numeric as est_rows, relpages * bs as table_bytes,
CEIL((reltuples*
(datahdr + nullhdr2 + 4 + ma -
(CASE WHEN datahdr%ma=0
THEN ma ELSE datahdr%ma END)
)/(bs-20))) * bs AS expected_bytes,
reltoastrelid
FROM data_headers
JOIN pg_class ON tablename = relname
JOIN pg_namespace ON relnamespace = pg_namespace.oid
AND schemaname = nspname
WHERE pg_class.relkind = 'r'
),
estimates_with_toast AS (
-- szacunkowy rozmiar tabel toast
-- szacunek oparty o zalozenie ze 4 wiersze toast znaduja sie
-- w kazdym bloku danych, nie ma tutaj dokladnej wartosci i nie
-- jestesmy w stanie jej odczytac ze statystyk
SELECT schemaname, tablename,
TRUE as can_estimate,
est_rows,
table_bytes + ( coalesce(toast.relpages, 0) * bs ) as table_bytes,
expected_bytes + ( ceil( coalesce(toast.reltuples, 0) / 4 ) * bs ) as expected_bytes
FROM table_estimates LEFT OUTER JOIN pg_class as toast
ON table_estimates.reltoastrelid = toast.oid
AND toast.relkind = 't'
),
table_estimates_plus AS (
-- dodatkowe wartosci zawierajace metadata dla tabel
-- i obliczenia wykorzystywane pozniej w zapytaniu glownym
-- oraz informacja czy mozemy oszacowac realny rozmiar danych
-- i ile miejsca bedziemy w stanie odzyskac
SELECT current_database() as databasename,
schemaname, tablename, can_estimate,
est_rows,
CASE WHEN table_bytes > 0
THEN table_bytes::NUMERIC
ELSE NULL::NUMERIC END
AS table_bytes,
CASE WHEN expected_bytes > 0
THEN expected_bytes::NUMERIC
ELSE NULL::NUMERIC END
AS expected_bytes,
CASE WHEN expected_bytes > 0 AND table_bytes > 0
AND expected_bytes <= table_bytes
THEN (table_bytes - expected_bytes)::NUMERIC
ELSE 0::NUMERIC END AS bloat_bytes
FROM estimates_with_toast
UNION ALL
SELECT current_database() as databasename,
table_schema, table_name, FALSE,
est_rows, table_size,
NULL::NUMERIC, NULL::NUMERIC
FROM no_stats
),
bloat_data AS (
-- koncowe obliczenia dla bloatu tabel
select current_database() as databasename,
schemaname, tablename, can_estimate,
table_bytes, round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
expected_bytes, round(expected_bytes/(1024^2)::NUMERIC,3) as expected_mb,
round(bloat_bytes*100/table_bytes) as pct_bloat,
round(bloat_bytes/(1024::NUMERIC^2),2) as mb_bloat,
table_bytes, expected_bytes, est_rows
FROM table_estimates_plus
)
-- zapytanie glowne i filtrowanie wynikow
SELECT databasename, schemaname, tablename,
can_estimate,
est_rows,
pct_bloat, mb_bloat,
table_mb
FROM bloat_data
-- tutaj definiujemy ktore tabele uznajemy za problematyczne
-- i maja sie pojawic w wyniku zapytania
-- przyklad ponizej zwraca tabele ktore maja bloat powyzej 50%
-- oraz rozmiar powyzej 200mb, lub bloat wiekszy niz 25%
-- i rozmiar tabeli powyzej 10GB
WHERE ( pct_bloat >= 50 AND mb_bloat >= 200 )
OR ( pct_bloat >= 25 AND mb_bloat >= 10240 )
ORDER BY pct_bloat DESC;
- Bloat dla indeksów Jak wyżej, ale dla indeksów.
-- szacowanie bloatu dla indeksow btree
-- definiowanie stalych wykorzystywanych w zapytaniu
-- na podstawie parametrow postgresa i wlasnosci indeksow
-- przechowywanych jako CTE (Common Table Expression)
WITH btree_index_atts AS (
SELECT nspname,
indexclass.relname as index_name,
indexclass.reltuples,
indexclass.relpages,
indrelid, indexrelid,
indexclass.relam,
tableclass.relname as tablename,
regexp_split_to_table(indkey::text, ' ')::smallint AS attnum,
indexrelid as index_oid
FROM pg_index
JOIN pg_class AS indexclass ON pg_index.indexrelid = indexclass.oid
JOIN pg_class AS tableclass ON pg_index.indrelid = tableclass.oid
JOIN pg_namespace ON pg_namespace.oid = indexclass.relnamespace
JOIN pg_am ON indexclass.relam = pg_am.oid
WHERE pg_am.amname = 'btree' and indexclass.relpages > 0
AND nspname NOT IN ('pg_catalog','information_schema')
),
index_item_sizes AS (
SELECT
ind_atts.nspname, ind_atts.index_name,
ind_atts.reltuples, ind_atts.relpages, ind_atts.relam,
indrelid AS table_oid, index_oid,
current_setting('block_size')::numeric AS bs,
8 AS maxalign,
24 AS pagehdr,
CASE WHEN max(coalesce(pg_stats.null_frac,0)) = 0
THEN 2
ELSE 6
END AS index_tuple_hdr,
sum( (1-coalesce(pg_stats.null_frac, 0)) * coalesce(pg_stats.avg_width, 1024) ) AS nulldatawidth
FROM pg_attribute
JOIN btree_index_atts AS ind_atts ON pg_attribute.attrelid = ind_atts.indexrelid AND pg_attribute.attnum = ind_atts.attnum
JOIN pg_stats ON pg_stats.schemaname = ind_atts.nspname
-- statystyki dla indeksow regularnych
AND ( (pg_stats.tablename = ind_atts.tablename AND pg_stats.attname = pg_catalog.pg_get_indexdef(pg_attribute.attrelid, pg_attribute.attnum, TRUE))
-- statystyki dla indeksow funkcyjnych
OR - (pg_stats.tablename = ind_atts.index_name AND pg_stats.attname = pg_attribute.attname))
WHERE pg_attribute.attnum > 0
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9
),
index_aligned_est AS (
SELECT maxalign, bs, nspname, index_name, reltuples,
relpages, relam, table_oid, index_oid,
coalesce (
ceil (
reltuples * ( 6
+ maxalign
- CASE
WHEN index_tuple_hdr%maxalign = 0 THEN maxalign
ELSE index_tuple_hdr%maxalign
END
+ nulldatawidth
+ maxalign
- CASE /* Add padding to the data to align on MAXALIGN */
WHEN nulldatawidth::integer%maxalign = 0 THEN maxalign
ELSE nulldatawidth::integer%maxalign
END
)::numeric
/ ( bs - pagehdr::NUMERIC )
+1 )
, 0 )
as expected
FROM index_item_sizes
),
raw_bloat AS (
SELECT current_database() as dbname, nspname, pg_class.relname AS table_name, index_name,
bs*(index_aligned_est.relpages)::bigint AS totalbytes, expected,
CASE
WHEN index_aligned_est.relpages <= expected
THEN 0
ELSE bs*(index_aligned_est.relpages-expected)::bigint
END AS wastedbytes,
CASE
WHEN index_aligned_est.relpages <= expected
THEN 0
ELSE bs*(index_aligned_est.relpages-expected)::bigint * 100 / (bs*(index_aligned_est.relpages)::bigint)
END AS realbloat,
pg_relation_size(index_aligned_est.table_oid) as table_bytes,
stat.idx_scan as index_scans
FROM index_aligned_est
JOIN pg_class ON pg_class.oid=index_aligned_est.table_oid
JOIN pg_stat_user_indexes AS stat ON index_aligned_est.index_oid = stat.indexrelid
),
format_bloat AS (
SELECT dbname as database_name, nspname as schema_name, table_name, index_name,
round(realbloat) as bloat_pct, round(wastedbytes/(1024^2)::NUMERIC) as bloat_mb,
round(totalbytes/(1024^2)::NUMERIC,3) as index_mb,
round(table_bytes/(1024^2)::NUMERIC,3) as table_mb,
index_scans
FROM raw_bloat
)
-- zapytanie glowne i filtrowanie wynikow
-- w klauzula where w ktorej definiujemy ktore indeksy maja zostac zwrocone
-- oraz wskazujemy klucz do sortowania w ORDER BY
SELECT *
FROM format_bloat
WHERE ( bloat_pct > 25 and bloat_mb > 10 )
ORDER BY bloat_mb DESC;
Top comments (0)