В Postgresql вы можете настроить всё! часть 2
Глава 2. Обнаружение и перестройка фрагментированных таблиц.
Настройка автоочистки – процесс интерактивный. Подходить к настройке автоочистки следует из соображений баланса нагрузки на В/В и разрастанием таблиц. Неправильные настройки автоочистки приводят к разрастанию таблиц. Просто оценивать размеры таблиц не показательно, они могут расти по оправданным причинам, т.к. прибавляются новые данные. Так как же определить степень «фрагментированности» таблиц – тот самый bloat таблицы?
Первый подход – обращение к статистикам, собираемым самой автоочисткой для планировщика в представлении pg_stats. PG хранит и обновляет информацию о среднем размере значений каждого поля таблицы в pg_stats. avg_width. Планировщик запросов использует эту информацию для оценки требуемой памяти для различных узлов плана. Просуммировав среднюю ширину каждого поля строки, прибавив заголовок строки и умножив на число строк в таблице, мы получим «эффективный размер» таблицы. Разделив это значение на общий размер таблицы на диске, можно получить долю полезной информации в таблице. Для примера создадим таблицу с миллионом строк, и посмотрим её размер.
Оценить долю полезной информации в таблице можно при помощи такой функции:
create or replace function bloat_table (in tab_name text)
returns table (tuple_width int,effective_table_size text,table_size text, bloat_percent float )
language sql
as $body$
select
tuple_width.a,
pg_size_pretty ( tuple_width.a::bigint * (select reltuples from pg_class where relname = tab_name )::bigint ) as effective_table_size,
pg_size_pretty(pg_relation_size(tab_name)),
(tuple_width.a::bigint * (select reltuples from pg_class where relname = tab_name ))::real / pg_relation_size(tab_name)::real as bloat
from (select sum(avg_width) + '27'::int from pg_stats where tablename = tab_name) as tuple_width(a) ;
$body$ ;
Оценим «плотность» только-что созданной таблицы:
Получаем, что ~0.86 это эталонное значение для новой таблицы с fillfactor=100% , в которой пока нет мёртвых строк. Не 100 из-за неучтённых накладных расходов на заголовки страниц, битовая карта неопределенных значений — отмечает те столбцы данной версии, которые содержат неопределенное значение NULL и тд. (NULL не является одним из обычных значений типов данных, поэтому признак приходится хранить отдельно).
Выполним обновление 20% строк таблицы и сравним результаты:
Таблица выросла на 11Мб, чтобы таблица попала в очередь на очистку нужно обновить ещё 50 строк (autovacuum_vacuum_threshold=50).
После очистки размер таблицы не уменьшится, но последующие обновления смогут повторно использовать освобождённое пространство и на какое-то время таблица перестанет расти до следующей очистки.
Получается, максимальная «плотность» таблицы такой структуры составляет ~ 70% при исправно работающих очистках, большего значения можно добиться, снизив значение scale_factor. Когда значение плотности или «bloat percent» снизиться, данная таблица будет выступать кандидатом на дефрагментацию «перестройку таблицы» и показателем, что автоочистка не справляется со своей задачей и требует настройки.
Этот подход к анализу разрастания таблиц через системные представления хорош тем, что не требует фактического чтения содержимого таблиц и может выполняться часто. Но плох тем, что зависит от точности и актуальности статистик. Для таблиц с типами данных нефиксированной длины, в которых хранятся значения разного размера, данный метод будет давать приблизительную оценку, т.к. опирается на pg_stats. avg_width , где храниться усреднённое значение размера поля строки по всем записям, которые были отобраны для анализа, а обычно это default_statistics_target=100 * 300 = 30000 строк.
Второй подход к оценке фрагментированности таблиц заключается в использовании ф-ции pgstattuple() из одноимённого расширения, которое является штатным для PostgreSQL и входит в поставку.
Выполним обновление 20% строк, аналогично предыдущему разу.
tuple_percent сократилось до 67% - это и есть доля полезной информации, а free_percent измениться после очистки мёртвых строк.
Из-за различий в расчётах «плотности таблицы» в первом и во втором методе значения отличаются, но сопоставимы. Отличие второго подхода от первого в том, что данная статистика собирается полным сканированием содержимого таблицы. Для больших таблиц это может быть долго, поэтому в составе расширения есть и вторая функция, выполняющая сбор статистик по выборке, а не по всей таблице.
Существенное различие в значениях оценки доли живых строк в pgstattuple('t') и pgstattuple_approx('t') вызвано тем, что обновления по таблице распределены неравномерно, первые 200000 строк все мёртвые. В остальных случаях, статистика получается точнее.
