У меня есть небольшое (~ 200 ГБ) хранилище данных, работающее на Postgres 9.5.15 в экземпляре AWS RDS.
Для надежности я вставляю новые данные в аналитическую схему (результат ELT) следующим образом:
- вставить новый фрагмент
- удалить старый фрагмент с помощью команды удаления
- вакуум
Я знаю, что Postgres soft удаляет кортежи, когда вы выполняете команды удаления или обновления. Это не проблема, связанная с размерами таблиц. Проблема в том, что мертвые кортежи не удаляются ни при явной очистке по (3), ни при обычной автоочистке. Итак, если конвейер выполняется много раз, я получаю ОГРОМНОЕ раздувание таблицы, которое сильно влияет на производительность таблицы, не говоря уже о дополнительной трате памяти.
Более того, когда я начал расследование, я обнаружил, что даже системные таблицы имеют эту проблему:
schemaname | relname | n_live_tup | n_dead_tup | ratio%
pg_catalog | pg_attribute | 46081 | 8339587 | 18097
pg_catalog | pg_depend | 27375 | 2490507 | 9097
pg_catalog | pg_statistic20094 | 1208474 | 6013
Это может ухудшить общую производительность экземпляра настолько, что я даже представить себе не могу. Когда я пытаюсь сделать VACUUM FULL VERBOSE pg_catalog.pg_attribute
, это дает мне это:
"pg_attribute": found 0 removable, 8387117 nonremovable row versions in 152494 pages
Я читал статьи типа "3 причины раздувания таблицы" но ни одна не относится (я не делаю репликацию, у меня нет зависших транзакций и т.д.). Я могу использовать что-то вроде pg_repack
, чтобы избавиться от раздувания по какому-то расписанию, но я хотел бы понять причину, по которой это происходит. Также я точно не хочу перепаковывать системные таблицы.
Моя единственная гипотеза заключается в том, что вакуум требует, чтобы все мертвые кортежи помещались в память, которая ограничена настройкой maintenance_work_mem
(127 МБ для нашего примера), и нам нужно увеличить это значение, но сначала мне нужно стороннее мнение.
pg_attribute
илиpg_depend
. Вы очень часто создаете и удаляете (временные) таблицы? Обычно это одна из причин раздувания системных каталогов. И еслиvacuum full
ничего не удаляет, это означает, что у вас есть хотя бы одна открытая транзакция, которая использовала таблицы, которым принадлежали атрибуты. - person a_horse_with_no_name   schedule 23.12.2019pg_stat_activity
, указателей на эти таблицы нет. - person AlexYes   schedule 23.12.2019select count(*) from pg_attribute
? - person jjanes   schedule 23.12.2019maintenance_work_mem
для одной команды, а затем вернуться к значению по умолчанию, см.SET LOCAL
, хотя не уверен, что это поможет. - person Christophe Roussy   schedule 23.12.2019pg_stat_activity
не будет содержать указателей на эти таблицы, но будет отображать незафиксированные транзакции (соединения, которые простаивают в транзакции). Вы постоянно удаляете и заново создаете промежуточные таблицы? Если да, то почему? - person a_horse_with_no_name   schedule 23.12.2019... 8387117 nonremovable row versions
- person AlexYes   schedule 24.12.2019pg_locks
для выявления возможных блокировок и взаимоблокировок в любых таблицах, и они ничего не вернули. - person AlexYes   schedule 24.12.2019