Postgresql занимает много места на диске
Ситуация такая: есть БД (psql), которая за месяц раздувается до 100Гб, ввиду большого количества данных (за месяц суммарно около 1млрд. строк в таблицах). Место на жестком диске ограничено. После того, как база сожрет все свободное место, что-то с ней сделать у меня не получалось - служба psql не запускается, а удалив что-то из папки data (свободного места на машине нет и ничего удалять, кроме базы, нельзя) - база не восстанавливается. Но вопрос не в этом. Как можно очистить данные в таблицах за определенный период с РЕАЛЬНЫМ освобождением места на диске? Дело в том, что я выполнил DELETE для всех таблиц, удалив записи старше недели (до этого были записи за 3 недели), после чего выполнил VACUUM для всей базы. После этих очень длительных операций, освободилось -500Мб. Именно со знаком минус. Не освободилось АБСОЛЮТНО ничего, а даже наоборот.
P.S: раньше работал с Microsoft SQL, там с такими проблемами никогда не встречался.
The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes..
Про VACUUM FULL слышал, но чтобы его выполнить, ему нужно место, равное текущему занимаемому. Т.е. в моем случае - должно быть еще 100Гб свободного места, т.к. все таблицы сначала будут скопированы. Есть еще какие-то варианты?
Алексей: вы в своём вопросе абсолютно ничего не указали что слышали про vacuum full, сами виноваты. А "ещё какие-то варианты" в огромном количестве приведены в ссылке, которую я дал. Вам даже лень было дочитать ответ.
Коллеги подскажите каким образом мне уменишить реальный размер бд на диске? Собирали статистику через заббикс теперь база разраслась до довольно больших для этого количества устройств размеров 250 гигов. Vacumm full не помогает , я бд потушил
а ты не пробовал посмотреть размер таблиц?
При использовании MySQL и кривой настройке binary logs, эти логи могут занимать раз в 10 больше самих таблиц. Про постгрес не знаю, но мб и там такое есть?
Значит нет мёртвых кортежей. Удалять старые/ненужные данные, потом vacuum
Ты оцени для начала сколько данных в БД. Возможно, что так оно и надо. Как прикидывать размер занимаемых данных где-то было в документации для администраторов.
Если часто происходила обнова данных с многих устройств, то вполне может быть и такой размер.
Если 2.2+ - housekeeping включен в UI? :)
Да housekeeping врублен
Если часто происходила обнова данных с многих устройств, то вполне может быть и такой размер.
там где то под 500 устройств , где-то до 70% хватает пинга, а вот для остальных надо уже по портам графики собирать и проц и память и температуру. еще есть пару коровских где немного больше минимально критических параметров собирать надо. Я и так собираю по минимуму .
так при таком кол-ве данных это нормально, у меня база данных с торгов за 8 месяцев с 300 гб стала 1,3 гб, и её ещё чистили 2 раза. правда у меня был мускуль 5.7
Это все хорошо, однако как это бекапить и место многовато начинает это все есть ?
Вот размер таблиц, можно ли как-то _uint порезать средставами заббикса или чего то другого?
Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в ETL-задачах.
ETL-задача выполнялась в таблице, где хранятся двоичные записи, дампы. Каждую ночь эта задача должна была удалять повторяющиеся дампы и освобождать место.
Для поиска повторяющихся дампов мы использовали этот запрос:
Запрос объединяет одинаковые дампы по BLOB-полю. С помощью функции окна мы получаем идентификатор первого появления каждого дампа. Потом этим запросом удаляем все повторяющиеся дампы.
Запрос выполнялся какое-то время, и, как видно из логов, кушал много памяти. На графике показано, как он каждую ночь забивал свободное пространство на диске:
Со временем запросу требовалось все больше памяти, провалы углублялись. И, заглянув в план выполнения, мы сразу увидели, куда все уходит:
Сортировка занимает много памяти. В плане выполнения из тестового набора данных сортировке требуется примерно 30 МБ памяти.
Что происходит внутри
Очистка должна обрабатывать и таблицу, и индексы одновременно, и делать это так, чтобы не блокировать работу остальных процессов. Как ей это удается?
Все начинается со сканирования таблицы (с учетом карты видимости, как уже отмечалось). В прочитанных страницах определяются ненужные версии строк и их идентификаторы (tid) записываются в специальный массив. Массив располагается в локальной памяти процесса очистки; для него выделяется фрагмент размером maintenance_work_mem. Значение этого параметра по умолчанию — 64 МБ. Отметим, что это память выделяется сразу в полном объеме, а не по мере необходимости. Правда, если таблица небольшая, то и фрагмент выделяется поменьше.
Дальше одно из двух: либо мы дойдем до конца таблицы, либо выделенная под массив память заканчится. В любом из двух случаев начинается фаза очистки индексов. Для этого каждый из индексов, созданных на таблице, полностью сканируется в поисках записей, которые ссылаются на запомненные версии строк. Найденные записи вычищаются из индексных страниц.
В этом месте мы получаем такую картину: в индексах уже нет ссылок на ненужные версии строк, а в таблице они еще есть. Это ничему не противоречит: выполняя запрос мы либо вообще не попадем на мертвые версии строк (при индексном доступе), либо отметем их при проверке видимости (при сканировании таблицы).
После этого начинается фаза очистки таблицы. Таблица снова сканируется, чтобы прочитать нужные страницы, вычистить из них запомненные версии строк и освободить указатели. Мы можем это сделать, поскольку ссылок из индексов уже нет.
Если на первом проходе таблица не была прочитана полностью, то массив очищается и все повторяется с того места, на котором мы остановились.
- таблица всегда сканируется два раза;
- если при очистке удаляется так много версий строк, что все они не помещаются в память размером maintenance_work_mem, то все индексы будут полностью сканироваться столько раз, сколько потребуется.
Чтобы ускорить процесс, имеет смысл либо вызывать очистку чаще (чтобы за каждый раз очищалось не очень большое количество версий строк), либо выделить больше памяти.
Замечу в скобках, что, начиная с версии 11, PostgreSQL может пропускать сканирование индексов, если в этом нет насущной необходимости. Это должно облегчить жизнь владельцев больших таблиц, в которые строки только добавляются (но не изменяются).
Соображения вдогонку
Запрос с хэшированным ключом сортировки памяти потребляет меньше, зато работает гораздо медленнее:
Хэширование задействует больше ЦП, поэтому запрос с хэшем работает медленнее. Но мы пытались решить проблему с пространством на диске, к тому же задача выполняется ночью, так что время — не проблема. Мы пошли на компромисс, чтобы сэкономить память.
Это отличный пример того, что не всегда нужно стараться ускорить запросы к базе данных. Лучше сбалансированно использовать что есть, и выжимать максимум из минимума ресурсов.
Этот вопрос был перенесен из переполнения стека, поскольку на него можно ответить в Exchange Stack Exchange для администраторов баз данных. Мигрировал 5 лет назад .
У меня есть локальная установка базы данных 9.1 с несколькими таблицами, которые имели cca. 300 миллионов записей и база данных выросли до 20 ГБ. После этого я дал delete from команду удалить все записи из него (я должен был использовать truncate , но я этого не знал). Поэтому я полностью опустошил свою базу данных, чтобы освободить место на диске, но это не помогло. Моя проблема выглядит идентично этой , но не предоставлено никакого решения. Я уже проверил этот поток и документацию по «восстановлению дискового пространства» , но все еще не могу найти решение. Я использую этот код, чтобы получить размер всех таблиц
Всего до 1 ГБ, однако
до сих пор показывает около 20 ГБ. Любой совет высоко ценится.
Ну, ваш размерный запрос исключает: индексы, таблицы в pg_catalog и таблицы в information_schema . Так что попробуйте посмотреть, есть ли это, сняв эти ограничения в WHERE предложении. Пожалуйста, покажите вашу точную версию PostgreSQL ( SELECT version() ) и что именно вы делаете для «очистки всей базы данных», то есть точную команду. Если возможно, запустите VACUUM FULL VERBOSE; (без аргументов) и вставьте куда-нибудь вывод, затем ссылку на него здесь.
Попробуйте сбросить базу данных. Вы также можете попробовать сбросить базу данных, а затем ее восстановление приведет к удалению мусора.
Хотя вы не заявили об этом, я предполагаю из ваших ссылок на документы, за которыми вы следили, что вы сделали VACUUM FULL для базы данных и / или для затронутых таблиц. Вы также не указали, какую версию postgresql вы используете - я предполагаю, что она> 9.0 (VACUUM FULL до этого вел себя иначе).
VACUUM FULL перепишет затронутые таблицы в новые файлы, затем удалит старые файлы. Однако, если какой-либо процесс все еще имеет открытый старый файл, операционная система фактически не удалит файл - пока последний процесс не закроет его.
Если это целесообразно, перезапуск базы данных обеспечит закрытие всех открытых файлов.
Если это нецелесообразно, то вы можете проверить, является ли это вашей проблемой, и выяснить, в каком процессе открыты файлы.
Если вы используете Linux (или большинство других Unix-подобных систем), вы можете использовать команду 'lsof', чтобы получить список всех файлов, открытых во всех процессах. К файлам, которые открыты, но которые с тех пор были удалены, будет добавлено «(удалено)» к имени файла. Таким образом, вы можете получить вывод lsof для поиска удаленных файлов, например так:
Если это идентифицирует процессы, у которых все еще открыты старые файлы, вы можете использовать pg_terminate_backend, чтобы завершить этот процесс:
где xxx - PID процесса, найденный в выводе lsof.
При использовании Windows может применяться тот же принцип, поскольку postgres открывает файлы с помощью флага FILE_SHARE_DELETE, который позволяет удалять файлы, открытые в другом процессе. Команда ' handle ' является грубым эквивалентом lsof, хотя я не уверен, сможете ли вы определить, удалены ли файлы или нет, поэтому может потребоваться дополнительная работа.
Другой вопрос, почему любые такие процессы будут зависеть от старых файловых дескрипторов. Однако в теме, которую вы цитировали в своем вопросе, Том Лейн, похоже, подразумевает, что это может произойти.
Мне нужно было срочно вернуть дисковое пространство, поэтому я удалил базу данных и восстановил ее из резервной копии. Однако, «как» решить эту проблему, все еще очень ценно для будущих случаев. Моя база данных 9.1, win 8 64 bit, применяется ли именование файлов (случай открытых файлов) так же, как в linux?
@arcull Хорошо, я не понял, что вы используете Windows. Я добавил некоторую информацию в ответ о том, как это относится к Windows. Если вы считаете, что это может быть полезным ответом, рассмотрите возможность голосования, поскольку другим будет легче его найти.
Мы начали с вопросов, связанных с изоляцией, сделали отступление про организацию данных на низком уровне, затем подробно поговорили о версиях строк и о том, как из версий получаются снимки данных.
В прошлый раз мы поговорили о HOT-обновлениях и внутристраничной очистке, а сегодня займемся всем известной обычной очисткой, vacuum vulgaris. Да, про нее написано уже столько всего, что вряд ли я скажу что-то новое, но полнота картины требует жертв. Терпите.
И еще раз о горизонте транзакций
Как PostgreSQL определяет, какие версии строк можно считать «мертвыми»? Мы уже рассматривали понятие горизонта транзакций, когда говорили о снимках данных, но это настолько важная тема, что не грех и повторить.
Снова начнем предыдущий опыт.
Но перед тем, как обновлять строку еще раз, пусть начнется (но не закончится) еще одна транзакция. В нашем примере она будет работать на уровне Read Committed, но должна получить настоящий (не виртуальный) номер транзакции. Например, она может изменить или даже просто заблокировать какие-то строки в любой таблице, не обязательно в vac:
Сейчас в таблице три строки, а в индексе — три ссылки. Что произойдет после очистки?
В таблице осталось две версии строки: очистка решила, что версия (0,2) еще не может быть удалена. Причина, конечно, в горизонте транзакций базы данных, который в нашем примере определяется незавершенной транзакцией:
Можно попросить очистку рассказать о том, что происходит:
- 2 nonremovable row versions — в таблице найдено 2 версии, которые нельзя удалить,
- 1 dead row versions cannot be removed yet — из них 1 «мертвая»,
- oldest xmin показывает текущий горизонт.
После завершения открытой транзакции горизонт сдвигается и ситуация исправляется:
Теперь в странице осталась только последняя актуальная версия строки:
В индексе также только одна запись:
Экономная сортировка
"Сколько сортировка съест – зависит от размера набора данных и ключа сортировки. Набор данных не уменьшишь, а вот размер ключа — можно.
За точку отсчета возьмем средний размер ключа сортировки:
Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит BLOB, хэшированный с помощью md5:
Размер ключа, хэшированного через md5, — 36 байт. Хэшированный ключ весит всего 4% от исходного варианта.
Дальше мы запустили исходный запрос с хэшированным ключом:
И план выполнения:
С хэшированным ключом запрос потребляет всего 4 лишних мегабайта, то есть чуть больше 10% от прежних 30 МБ. Значит размер ключа сортировки сильно влияет на то, сколько памяти отъедает сортировка.
Почему так?
PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem . Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.
Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.
Похожие команды
Есть несколько команд, которые тоже перестраивают таблицы и индексы полностью, и этим похожи на полную очистку. Все они полностью блокируют работу с таблицей, все они удаляют старые файлы данных и создают новые.
Команда CLUSTER во всем аналогична VACUUM FULL, но дополнительно физически упорядочивает версии строк в соответствии с одним из имеющихся индексов. Это дает планировщику возможность более эффективно использовать индексный доступ в некоторых случаях. Однако надо понимать, что кластеризация не поддерживается: при последующих изменениях таблицы физический порядок версий строк будет нарушаться.
Команда REINDEX перестраивает отдельный индекс на таблице. Фактически, VACUUM FULL и CLUSTER используют эту команду для того, чтобы перестроить индексы.
Команда TRUNCATE логически работает так же, как и DELETE — удаляет все табличные строки. Но DELETE, как уже было рассмотрено, только помечает версии строк как удаленные, что требует дальнейшей очистки. TRUNCATE же просто создает новый, чистый файл. Как правило, это работает быстрее, но надо учитывать, что TRUNCATE полностью заблокирует работу с таблицей на все время до конца транзакции.
Что делает очистка
Внутристраничная очистка выполняется быстро, но освобождает только часть места. Она работает в пределах одной табличной страницы и не затрагивает индексы.
Основная, «обычная» очистка выполняется командой VACUUM и ее мы будем называть просто очисткой (а про автоочистку мы будем говорить отдельно).
Итак, очистка обрабатывает таблицу полностью. Она вычищает не только ненужные версии строк, но и ссылки на них из всех индексов.
Обработка происходит параллельно с другой активностью в системе. Таблица и индексы при этом могут использоваться обычным образом и для чтения, и для изменения (однако одновременное выполнение таких команд, как CREATE INDEX, ALTER TABLE и некоторых других будет невозможно).
В таблице просматриваются только те страницы, в которых происходила какая-то активность. Для этого используется карта видимости (напомню, что в ней отмечены страницы, содержащие только достаточно старые версии строк, которые гарантированно видимы во всех снимках данных). Обрабатываются только страницы, не отмеченные в карте, а сама карта при этом обновляется.
В процессе работы обновляется и карта свободного пространства, чтобы отразить появившееся свободное места в страницах.
Как водится, создадим таблицу:
С помощью параметра autovacuum_enabled мы отключаем автоматическую очистку. Про нее мы будем говорить в следующий раз, а пока — для экспериментов — нам важно управлять очисткой вручную.
Сейчас в таблице три версии строки, и на каждую ведет ссылка из индекса:
После очистки «мертвые» версии пропадают и остается только одна, актуальная. И в индексе тоже остается одна ссылка:
Обратите внимание, что два первых указателя получили статус unused, а не dead, как было бы при внутристраничной очистке.
Анализ
Анализ, или, иными словами, сбор статистической информации для планировщика запросов, формально никак с очисткой не связан. Тем не менее мы можем выполнять анализ не только командой ANALYZE, но и совмещать очистку с анализом: VACUUM ANALYZE. При этом сначала выполняется очистка, а затем анализ — никакой экономии не происходит.
Но, как мы увидим позже, автоматическая очистка и автоматический анализ выполняются одним процессом и управляются схожим образом.
Как мы видели, обычная очистка освобождает больше места, чем внутристраничная, но и она не всегда решает задачу полностью.
Если таблица или индекс по каким-то причинам сильно выросли в размерах, то обычная очистка освободит место внутри существующих страниц: в них появятся «дыры», которые затем будут использованы для вставки новых версий строк. Но число страниц не изменится, и, следовательно, с точки зрения операционной системы файлы будут занимать ровно столько же места, сколько занимали и до очистки. А это плохо, потому что:
- замедляется полное сканирование таблицы (или индекса);
- может потребоваться больший буферный кэш (ведь хранятся страницы, а плотность полезной информации падает);
- в дереве индекса может появиться “лишний” уровень, который будет замедлять индексный доступ;
- файлы занимают лишнее место на диске и в резервных копиях.
Если доля полезной информации в файлах опустилась ниже некоторого разумного предела, администратор может выполнить полную очистку таблицы. При этом таблица и все ее индексы перестраиваются полностью с нуля, а данные упаковываются максимально компактно (разумеется, с учетом параметра fillfactor). При перестройке PostgreSQL последовательно перестраивает сначала таблицу, а затем и каждый из ее индексов. Для каждого объекта создаются новые файлы, а в конце перестройки старые файлы удаляются. Следует учитывать, что в процессе работы на диске потребуется дополнительное место.
Для иллюстрации снова вставим в таблицу некоторое количество строк:
Как оценить плотность информации? Для этого удобно воспользоваться специальным расширением:
Функция читает полность всю таблицу и показывает статистику по тому, сколько места какими данными занято в файлах. Основная информация, которая нам сейчас интересна — поле tuple_percent: процент, занятый полезными данными. Он меньше 100 из-за неизбежных накладных расходов на служебную информацию внутри страницы, но тем не менее довольно высок.
Для индекса выводится другая информация, но поле avg_leaf_density имеет тот же смысл: процент полезной информации (в листовых страницах).
А вот какой размер занимают таблица и индекс:
Теперь удалим 90% всех строк. Строки для удаления выбираем случайно, чтобы в каждой странице с большой вероятностью хоть одна строка, да осталась:
Какой размер будут иметь объекты после обычной очистки?
Мы видим, что размер не изменился: обычная очистка никак не может уменьшить размер файлов. Хотя плотность информации, очевидно, уменьшилась примерно в 10 раз:
Теперь проверим, что получится после полной очистки. Вот какие файлы используются сейчас таблицей и индексами:
Теперь файлы заменены на новые. Размер таблицы и индекса существенно уменьшился, а плотность информации, соответственно, увеличилась:
Обратите внимание, что плотность информации в индексе даже увеличилась по сравнению с первоначальной. Заново создать индекс (B-дерево) по имеющимся данным выгоднее, чем вставлять данные в уже имеющийся индекс строка за строкой.
Функции расширения pgstattuple, которые мы использовали, читают полностью всю таблицу. Если таблица большая, то это неудобно, и поэтому там же есть функция pgstattuple_approx, которая пропускает страницы, отмеченные в карте видимости, и показывает примерные цифры.
Еще более быстрый, но и еще менее точный способ — прикинуть отношение объема данных к размеру файла по системному каталогу. Варианты таких запросов можно найти в вики.
Полная очистка не предполагает регулярного использования, так как полностью блокирует всякую работу с таблицей (включая и выполнение запросов к ней) на все время своей работы. Понятно, что на активно используемой системе это может оказаться неприемлемым. Блокировки будут рассмотрены отдельно, а пока ограничимся упоминанием расширения pg_repack, которое блокирует таблицу только на короткое время в конце работы.
Почему так?
PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem . Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.
Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.
Мониторинг
Как понять, что очистка не справляется с работой за один проход?
Первый способ мы уже видели: можно вызывать команду VACUUM с указанием VERBOSE. Тогда на консоль будет выводиться и информация о фазах выполнения работы.
Во-вторых, начиная с версии 9.6 имеется представление pg_stat_progress_vacuum, которое также содержит всю необходимую информацию.
Вставим в таблицу побольше строк, чтобы очистка выполнялась ощутимое время, и все их обновим, чтобы очистке было чем заняться.
Уменьшим размер памяти, выделенной под массив идентификаторов:
Запускаем очистку и, пока она работает, обратимся несколько раз к представлению pg_stat_progress_vacuum:
Тут мы в частности видим:
- название текущей фазы (phase) — мы говорили о трех основных фазах, но вообще их больше;
- общее число страниц таблицы (heap_blks_total);
- число просканированных страниц (heap_blks_scanned);
- число уже очищенных страниц (heap_blks_vacuumed);
- количество проходов по индексам (index_vacuum_count).
Вывод завершившейся к этому времени команды VACUUM VERBOSE покажет общую картину:
Здесь видно, что всего было выполнено три прохода по индексам, на каждом из которых очищалось 174480 указателей на мертвые версии строк. Откуда такое число? Одна ссылка (tid) занимает 6 байтов, а 1024*1024/6 = 174762 — это число, которое мы видим в pg_stat_progress_vacuum.max_dead_tuples. Реально может использоваться чуть меньше: так гарантируется, что при чтении очередной страницы все указатели на «мертвые» версии точно поместятся в память.
Дальше — больше
В этом примере мы хэшировали BLOB с помощью md5 . Хэши, созданные с MD5, должны весить 16 байт. А у нас получилось больше:
Наш хэш был ровно в два раза больше, ведь md5 выдает хэш в виде шестнадцатеричного текста.
В PostgreSQL можно использовать MD5 для хэширования с расширением pgcrypto . pgcrypto создает MD5 типа bytea (в двоичном виде):
Хэш все равно на 4 байта больше положенного. Просто тип bytea использует эти 4 байта, чтобы хранить длину значения, но мы этого так не оставим.
Оказывается, тип uuid в PostgreSQL весит ровно 16 байт и поддерживает любое произвольное значение, так что избавляемся от оставшихся четырех байтов:
Вот и все. 32 байта с md5 превращаются в 16 с uuid .
Я проверил последствия изменения, взяв набор данных побольше. Сами данные показывать нельзя, но я поделюсь результатами:
Как видно из таблицы, исходный проблемный запрос весил 300 МБ (и будил нас среди ночи). С ключом uuid сортировке потребовалось всего 7 МБ.
Читайте также: