Аудит прироста базы данных. Узнать за счёт чего растёт база на уровне сегментов

Версия Postgresql 9.1

Необходимо узнать за счёт чего так быстро прирастает база?
смотрю сегменты в pg_class, но это мало помогает.

Сегодня утром база 100Гб, в обед уже 110Гб.
auto_vacuum включен, из pg_stat_user_tables видно, что последний раз отрабатывал сегодня

Хочется понять какая именно таблица или индекс выросли на 10 Гб.
спасибо!

pg_newbie_user,

Собирайте данные по размеру всех объектов в отдельную таблицу с установленной периодичностью (можно в другую БД и другой сервер). Потом анализируйте статистику.

autovacuum в общем случае не сжимает таблицу. То есть объем таблицы уменьшается в редких случаях, когда зачищается хвост таблицы.

Если отработал "сегодня" - есть подозрение на неоптимальную его настройку. Значение обычно ближе к "отработал x раз за последний час".

/\/\/\/\/\/\

pg_newbie_user, Собирайте данные по размеру всех объектов в отдельную таблицу с установленной периодичностью (можно в другую БД и другой сервер). Потом анализируйте статистику. autovacuum в общем случае не сжимает таблицу. То есть объем таблицы уменьшается в редких случаях, когда зачищается хвост таблицы. Если отработал "сегодня" - есть подозрение на неоптимальную его настройку. Значение обычно ближе к "отработал x раз за последний час".

За последний час база выросла на 6 Гб.

Могли бы вы любезно подсказать, какими запросами осуществить данный сбор данных?
приведёт ли это к полному сканированию таблиц или брать значения уже собранной ранее статистики?
как это можно сделать?

спасибо!

pg_newbie_user,

Для начала проделайте элементарное:

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

Повторять раз в 5 минут до понимания тенденции.

pg_newbie_user,

Затем прочитайте тут и тут и собирайте более подробную статистику через pg_total_relation_size.

У вас нет долгих транзакций? От них база может сильно распухать.

SELECT (now() - query_start) as TTT_______,query, * FROM pg_stat_activity WHERE state <> 'idle' and   datname = current_database() order by 1 desc

pg_newbie_user,

А производные ловить как-то так:

create table pagestat ( moment timestamptz, relname name, relpages integer );

insert into pagestat select now(), relname , relpages from pg_class; -- регулярно

select moment,relname, dr/ds as diff from (
  select 
    moment, relname, 
    (lead(relpages) over (partition by relname order by moment) - relpages) as dr, 
    (extract(epoch from (lead(moment) over  (partition by relname order by moment) - moment))) as ds 
  from pagestat 
) q where dr notnull and ds notnull and ds > 0 order by diff desc;

pg_newbie_user,

А производные ловить как-то так:

create table pagestat ( moment timestamptz, relname name, relpages integer );

insert into pagestat select now(), relname , relpages from pg_class; -- регулярно

select moment,relname, dr/ds as diff from (
  select 
    moment, relname, 
    (lead(relpages) over (partition by relname order by moment) - relpages) as dr, 
    (extract(epoch from (lead(moment) over  (partition by relname order by moment) - moment))) as ds 
  from pagestat 
) q where dr notnull and ds notnull and ds > 0 order by diff desc;

hattifattener
pg_newbie_user, Для начала проделайте элементарное: SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; Повторять раз в 5 минут до понимания тенденции.

Кстати, еще раз более внимательно прочитал RTFM про relpages.
Оказывается, это ОЖИДАЕМОЕ значение. Нужно быть осторожным.

автор
Size of the on-disk representation of this table in pages (of size BLCKSZ). This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX.

Поэтому я бы опирался на pg_total_relation_size .

/\/\/\/\/\/\

hattifattener pg_newbie_user, Для начала проделайте элементарное: SELECT relname, relpages FROM pg_class ORDER BY relpages DESC; Повторять раз в 5 минут до понимания тенденции. Кстати, еще раз более внимательно прочитал RTFM про relpages. Оказывается, это ОЖИДАЕМОЕ значение. Нужно быть осторожным. автор Size of the on-disk representation of this table in pages (of size BLCKSZ). This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. Поэтому я бы опирался на pg_total_relation_size .

Всем добрый день!

Я выяснил, что у меня прирост идёт за счёт pg_largeobject. Прирост 1 Гб за 1 час.
last_autovacuum для pg_largeobject отсутствует...

что можно сделать в данной ситуации с pg_largeobject? я так понял там хранятся BLOB, их можно как-то ужать?