AndryDLДоброго всем времени суток. Кто нибудь сталкивался с проблемой течи счетчика транзакций в 9.5 ? Вставка интенсивная несколько десятков миллионов записей в сутки. Таблиц порядка 1500 (количество не постоянно , данные секционированы по датам и храняться 3 месяца т.е. 4-й от текущего удаляется ) БД большая опрядка 12Тб. Процессов автовакуума 4-е не прекращают работу никогда. Что самое замечательное счетчик течет и в служебных БД postgres, template0, template1 хотя туда вставки нет. Приходится раз в неделю запускать vacuum . Хотя суточные секции ежедневно ночью пакуются pg_repack (индексы перестраивает) плюс vacuum freeze.

Для начала скажите что вы под счетчиком транзакций понимаете :).
И что именно вас не устраивает?

--
Maxim Boguk
www.postgresql-consulting.ru

AndryDLqwwq,
Вставка записей и так идет пачкой командой copy, проверки форейнкеи и прочии радости упразднены в угоду производительности записи. Но данные в секции перекладываются триггерами на родительских таблицах. Одно время вроде жило нормально,пока размер данных не перевалил за 10Тб.vacuum по БД идет приблизительно 36 часов и это напрягает его постоянно гонять. Может увеличить кол-во процессов autovacuum -а ?

10Tb это уже много.
А какой у вас autovacuum_vacuum_cost_delay стоит? Зачастую его проще уменьшить сильно чем больше процессов делать которым память надо.
Диски то у вас SSD под такой обьем я надеюсь?


PS: а зачем вы вообще руками vacuum делаете??? Это закат солнца вручную и смысла в нем в 99% случаев - ноль.
Единственный случай когда это может быть полезно - сразу после создания новой таблицы и заливки туда БОЛЬШОЙ пачки данных полезно сделать vacuum freeze analyze на нее.

--
Maxim Boguk
www.postgresql-consulting.ru

AndryDLMaxim Boguk,
Если бы не помогал закат солнца в ручную, то я его не делал бы. После вакуума в ручную
WITH tbl AS (
SELECT
age(relfrozenxid),
current_setting('autovacuum_freeze_max_age')::integer fma
FROM
pg_class
WHERE
relkind IN ('r', 't')
)
SELECT
extract(epoch from now())::integer ts,
(
SELECT
((1 - max(age)::double precision / current_setting('autovacuum_freeze_max_age')::integer) * 100)::numeric(9,6)
FROM
tbl
WHERE
age < fma
) prc_before_av,
(
SELECT
((1 - max(age)::double precision / -((1 << 31) + 1)) * 100)::numeric(9,6)
FROM
tbl
) prc_before_stop;

такой не хитрый запрос показывает что до остановки БД у меня в районе 90%, а через неделю работы 40% если, его не делать то через 2 недели БД встанет колом . И восстановить ее работоспособность можно будет только в single mode vacuum. А это на моем объеме больше суток. Так что лучше я его в фоне погоняю раз в неделю, чем умрет БД на сутки.
autovacuum_vacuum_cost_delay по дефолту т.е равен vacuum_cost_delay = 0 меньше некуда. Вообще все параметры автовакуума стоят по дефолту раньше хватало, единственное что поменял это воркер еще один добавил.
Повторюсь дневные секции ночью обрабатываются pg_repack после него делается vacuum freeze таблицы и все равно autovacuum по ним бежит. Зачем для меня загадка. Данные не меняются счетчик у них всегда в прошлом, зачем по ним еще раз бежать? И главное почему течет счетчик в служебных БД в них данные не пишутся.

Когда подходит время autovacuum freeze будет по всем таблицам и всем строкам проходить в которых хоть 1 строку поменяли.
По дефолту autovacuum_vacuum_cost_delay равен не 0 а 20 вообще то.
Т.е. если после vacuum freeze - в таблице поменяли ХОТЬ 1 строку или добавили хоть одну - по ней будет autovacuum freeze, более того если подошло время - то даже если ничего не меняли всеравно заново фризить надо все.
Я бы сказал вам надо для начала на 9.6 обновится там с этим проще (не фризятся те страницы что не менялись).

Про служебные таблицы/базы я бы порекомендовал бы посмотреть на то для каких именно таблиц то age(relfrozenxid) меняется - тогда станет понятнее в чем сложность.
Что то вида
select c.oid, relname, n.nspname, age(relfrozenxid), ((1 - age(relfrozenxid)::double precision / -((1 << 31) + 1)) * 100)::numeric(9,6) FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind IN ('r', 't') order by 4 desc limit 10;

AndryDLЕсли бы не помогал закат солнца в ручную, то я его не делал бы. После вакуума в ручную
такой не хитрый запрос показывает что до остановки БД у меня в районе 90%, а через неделю работы 40% если, его не делать то через 2 недели БД встанет колом . И восстановить ее работоспособность можно будет только в single mode vacuum. А это на моем объеме больше суток. Так что лучше я его в фоне погоняю раз в неделю, чем умрет БД на сутки.
autovacuum_vacuum_cost_delay по дефолту т.е равен vacuum_cost_delay = 0 меньше некуда. Вообще все параметры автовакуума стоят по дефолту раньше хватало, единственное что поменял это воркер еще один добавил.
Повторюсь дневные секции ночью обрабатываются pg_repack после него делается vacuum freeze таблицы и все равно autovacuum по ним бежит. Зачем для меня загадка. Данные не меняются счетчик у них всегда в прошлом, зачем по ним еще раз бежать? И главное почему течет счетчик в служебных БД в них данные не пишутся.

Это только значит что autovacuum у вас настроен не верно и нет графиков и мониторинга его занятости.
Если все autovacuum_max_workers заняты дольше часа - это КРИТИЧЕСКАЯ проблема которую надо немедленно решать.
А у вас походу оно сутками в таком состоянии стоит.
Отсюда и начинаются закаты солнца в ручную вместо решения проблемы с настройкой.

AndryDLqwwq,
Забить на фриз пустых баз это классно. Только они колом ставят весь сервер когда у них кончается счетчик и БД становиться доступной только для чтения , очень веселая история. Для того чтоб оживить сервер, надо его остановить , зайти stand-alone и повакуумить все БД в ручную. Видимо у Вас данных 1,5 Мб раз такие советы даете, какой врапэраунд - не слышали.

С задачей фриза должен справляться AUTOVACUUM !!!! и только он.
Если вы занимаетесь закатом солнца вручную - надо это исправлять.
За мои 15 лет работы с Pg ни разу не приходилось freeze руками делать (потому что нормально все настроено и есть мониторинг и занятости autovacuums и сколько TS до wraparound осталось).

--
Maxim Boguk
www.postgresql-consulting.ru

AndryDLMaxim Boguk,
Если бы автовакуум справлялся не было бы вопросов. Поставлю вопрос по другому .после плотного погружения в данный вопрос . Я выяснил что процессы автовакуума работают сильно тормозно (несколько часов на таблицу причем не большую 40-50 мл записей). Увеличение количество процессов автовакуума не приводит к желаемому результату, так как они тупо висят и ни чем не занимаются. Что нужно смотреть в каком направлении копать. Конфиг никто не трогал, кроме меня когда я пытался увеличить число автовакуумов. Таблицы которые автовакуумятся никто кроме него самого не держит. Но почти 9 часов делать вакуум на таблице из 43 Мл. записей это как то очень долго. Отсюда "течь" счетчика. Если туже таблицу руками фризить займет порядка 1 минуты , при этом он будет фризить и toast таблицу , а автовакуум бежит исключитель но по искомой, т.е. получается он основную таблицу 9 часов фризил еще столько же на тоаст потратит. Нигде информации по подобной ситуации не нашел, все исходят из того что автовакуум работает нормально. Что делать если он тупит? Вставка интенсивная и объемы тоже по этому счетчик течет только в путь. За ранее благодарен всем кто посоветует в каком направлении копать. Автовакуум должен справляться, но увы.

1)show autovacuum_vacuum_cost_delay ;
2)show vacuum_cost_delay ;
3)show show autovacuum_vacuum_scale_factor;
4)pg_dumpall -s | grep autovacuum (проверка что никто несуразные autovacuum_vacuum_cost_delay per table не прописал или еще какие то милые настройки для autovacuum per table)

Далее по результатам.
Простейший метод (жестокий конечно но):
1)autovacuum_vacuum_cost_delay=0
2)autovacuum_max_workers=10
3)рестарт базы (иначе 3 не применится)
(по итогам может поплохеть конечно дискам временно)

Но лучше покажите что дает 1-4 из начального запроса.

AndryDLMaxim Boguk,
Это запрос настройки
"autovacuum";"on";"on"
"autovacuum_analyze_scale_factor";"0.1";"0.1"
"autovacuum_analyze_threshold";"50";"50"
"autovacuum_freeze_max_age";"200000000";"200000000"
"autovacuum_max_workers";"3";"3"
"autovacuum_multixact_freeze_max_age";"400000000";"400000000"
"autovacuum_naptime";"60";"60"
"autovacuum_vacuum_cost_delay";"20";"20"
"autovacuum_vacuum_cost_limit";"-1";"-1"
"autovacuum_vacuum_scale_factor";"0.2";"0.2"
"autovacuum_vacuum_threshold";"50";"50"
"autovacuum_work_mem";"-1";"-1"
"log_autovacuum_min_duration";"-1";"-1"
"vacuum_cost_delay";"0";"0"
"vacuum_cost_limit";"200";"200"
"vacuum_cost_page_dirty";"20";"20"
"vacuum_cost_page_hit";"1";"1"
"vacuum_cost_page_miss";"10";"10"
"vacuum_defer_cleanup_age";"0";"0"
"vacuum_freeze_min_age";"50000000";"50000000"
"vacuum_freeze_table_age";"150000000";"150000000"
"vacuum_multixact_freeze_min_age";"5000000";"5000000"
"vacuum_multixact_freeze_table_age";"150000000";"150000000"

Ну вот как я уже написал у вас autovacuum потециально в бесконечное количество раз медленнее чем ручной vacuum
(скорее всего раз в 100-1000 на практике в зависимости от скорости дисков).

Как и ожидалось.
Я бы ставил что то вида для начала
autovacuum_vacuum_cost_delay=1
autovacuum_max_workers=20 (а то и 30 учитывая размер базы)

у вас настройки autovacuum стоят по умолчанию
а это значит настройки для базы на програмируемом калькуляторе
а не для базы в 12Tb что уже очень много для postgres и требует КРАЙНЕ грамотного обслуживания и мониторинга чтобы не иметь проблем.