Postgresql занимает много места на диске
Обновлено: 29.06.2024
У меня есть таблица в моей базе данных, которая занимает 161 ГБ места на жестком диске. Осталось всего 5 Гб свободного места на жестком диске 200 ГБ.
Следующая команда показывает, что моя таблица потребляет 161 ГБ места на жестком диске,
select pg_size_pretty(pg_total_relation_size('Employee'));
В таблице около 527 строк. Теперь я удалил 250 строк. Снова я проверил pg_total_relation_size Employee. Тем не менее размер составляет 161 ГБ.
После просмотра вывода вышеуказанного запроса я выполнил команду вакуума:
VACUUM VERBOSE ANALYZE Employee;
Я подтвердил, действительно ли ВАКУУМ или нет,
SELECT relname, last_vacuum, last_autovacuum FROM pg_stat_user_tables; Я вижу последнее время вакуума, соответствующее времени, когда я запускал команду VACUUM.
Я также выполнил команду ниже, чтобы увидеть, есть ли мертвые кортежи,
SELECT relname, n_dead_tup FROM pg_stat_user_tables ; Счет n_dead_tup для таблицы Employee равен 0.
Все еще после всех этих команд, если я забегаю,
select pg_size_pretty(pg_total_relation_size('Employee')); он по-прежнему показывает 161 ГБ.
Позвольте мне, пожалуйста, знать причину этого. Также, пожалуйста, поправьте меня, как освободить интерфейс.
Стандартная форма VACUUM удаляет версии мертвой строки в таблицах и индексах и обозначает пространство, доступное для последующего повторного использования. Тем не менее, он не вернет пространство операционной системе, за исключением специального случая, когда одна или несколько страниц в конце таблицы становятся полностью бесплатными и можно легко получить эксклюзивную блокировку таблицы
Если вы повторно вставьте 250 удаленных строк, вы увидите, что таблица не будет расти снова, так как вновь вставленные строки просто используют пространство, которое было выделено свободным vacuum .
Если вы действительно хотите физически уменьшить размер таблицы до требуемого размера, вам необходимо запустить vacuum full .
VACUUM FULL активно объединяет таблицы, записывая полную новую версию файла таблицы без мертвого пространства. Это минимизирует размер таблицы, но может занять много времени. Это также требует дополнительного места на диске для новой копии таблицы, пока операция не завершится
Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в ETL-задачах.
ETL-задача выполнялась в таблице, где хранятся двоичные записи, дампы. Каждую ночь эта задача должна была удалять повторяющиеся дампы и освобождать место.
Для поиска повторяющихся дампов мы использовали этот запрос:
Запрос объединяет одинаковые дампы по BLOB-полю. С помощью функции окна мы получаем идентификатор первого появления каждого дампа. Потом этим запросом удаляем все повторяющиеся дампы.
Запрос выполнялся какое-то время, и, как видно из логов, кушал много памяти. На графике показано, как он каждую ночь забивал свободное пространство на диске:
Со временем запросу требовалось все больше памяти, провалы углублялись. И, заглянув в план выполнения, мы сразу увидели, куда все уходит:
Сортировка занимает много памяти. В плане выполнения из тестового набора данных сортировке требуется примерно 30 МБ памяти.
Почему так?
PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem . Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.
Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.
Экономная сортировка
"Сколько сортировка съест – зависит от размера набора данных и ключа сортировки. Набор данных не уменьшишь, а вот размер ключа — можно.
За точку отсчета возьмем средний размер ключа сортировки:
Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит BLOB, хэшированный с помощью md5:
Размер ключа, хэшированного через md5, — 36 байт. Хэшированный ключ весит всего 4% от исходного варианта.
Дальше мы запустили исходный запрос с хэшированным ключом:
И план выполнения:
С хэшированным ключом запрос потребляет всего 4 лишних мегабайта, то есть чуть больше 10% от прежних 30 МБ. Значит размер ключа сортировки сильно влияет на то, сколько памяти отъедает сортировка.
Дальше — больше
В этом примере мы хэшировали BLOB с помощью md5 . Хэши, созданные с MD5, должны весить 16 байт. А у нас получилось больше:
Наш хэш был ровно в два раза больше, ведь md5 выдает хэш в виде шестнадцатеричного текста.
В PostgreSQL можно использовать MD5 для хэширования с расширением pgcrypto . pgcrypto создает MD5 типа bytea (в двоичном виде):
Хэш все равно на 4 байта больше положенного. Просто тип bytea использует эти 4 байта, чтобы хранить длину значения, но мы этого так не оставим.
Оказывается, тип uuid в PostgreSQL весит ровно 16 байт и поддерживает любое произвольное значение, так что избавляемся от оставшихся четырех байтов:
Вот и все. 32 байта с md5 превращаются в 16 с uuid .
Я проверил последствия изменения, взяв набор данных побольше. Сами данные показывать нельзя, но я поделюсь результатами:
Как видно из таблицы, исходный проблемный запрос весил 300 МБ (и будил нас среди ночи). С ключом uuid сортировке потребовалось всего 7 МБ.
Соображения вдогонку
Запрос с хэшированным ключом сортировки памяти потребляет меньше, зато работает гораздо медленнее:
Хэширование задействует больше ЦП, поэтому запрос с хэшем работает медленнее. Но мы пытались решить проблему с пространством на диске, к тому же задача выполняется ночью, так что время — не проблема. Мы пошли на компромисс, чтобы сэкономить память.
Это отличный пример того, что не всегда нужно стараться ускорить запросы к базе данных. Лучше сбалансированно использовать что есть, и выжимать максимум из минимума ресурсов.
Я должен быть уверен, что у меня есть по крайней мере 1 ГБ свободного места на диске, прежде чем начать выполнять некоторую работу в моей базе данных. Я ищу что-то вроде этого:
это возможно? (Я ничего не нашел об этом в docs).
PG: 9.3 & OS: Linux / Windows
PostgreSQL в настоящее время не имеет функций для прямого предоставления дискового пространства.
во-первых, какой диск? Производственный экземпляр PostgreSQL часто выглядит следующим образом:
- /pg/pg94/ : RAID6 быстрого надежного хранения на контроллере BBU RAID в режиме WB, для каталогов и наиболее важных данных
- /pg/pg94/pg_xlog : быстрый надежный RAID1, для журналов транзакций
- /pg/tablespace-lowredundancy : RAID10 быстрого дешевого хранения для таких вещей, как индексы и UNLOGGED таблицы, которые вы не заботитесь о потере, поэтому вы можете использовать хранилище с меньшим резервированием
- /pg/tablespace-bulkdata : RAID6 или аналогичное медленное магнитное хранилище, используемое для старых журналов аудита, исторических данных, данных записи и других вещей, которые могут быть медленнее для доступа.
- журналы postgreSQL обычно находятся где-то еще, но если это заполнится, система все равно может остановиться. Где зависит от ряда параметров, некоторые из которых вы не можете видеть PostgreSQL вообще, как параметры системного журнала.
тогда есть тот факт, что" свободное " пространство не обязательно означает, что PostgreSQL может его использовать (подумайте: дисковые квоты, зарезервированное системой дисковое пространство), и тот факт, что free блоки/байт не единственным ограничением, так как многие файловые системы также имеют ограничения на количество файлов (дескрипторы).
как SELECT pg_get_free_disk_space() сообщить об этом?
знание свободного места на диске может быть проблемой безопасности. Если поддерживается, это то, что будет доступно только суперпользователю, по крайней мере.
что ты можете do использует ненадежный процедурный язык, такой как plpythonu чтобы сделать вызовы операционной системы для опроса хост-ОС для информации о дисковом пространстве, используя запросы против pg_catalog.pg_tablespace и с помощью data_directory параметр pg_settings чтобы узнать, где PostgreSQL хранит материал на хост-ОС. Вы также должны проверить точки монтирования (unix / Mac) / точки соединения (Windows), чтобы узнайте, если pg_xlog и т. д. находятся на отдельном хранилище. Однако это все равно не поможет вам с пространством для журналов.
я бы очень хотел иметь SELECT * FROM pg_get_free_diskspace это сообщило основное пространство datadir и любые точки монтирования или точки соединения внутри него, как для pg_xlog или pg_clog , а также сообщил о каждом табличном пространстве и любых точках монтирования внутри него. Это будет функция возврата набора. Кто-то, кто достаточно заботится, должен был бы потрудиться, чтобы реализовать его для всех целевых платформ хотя, и прямо сейчас, никто не хочет этого достаточно, чтобы сделать работу.
в то же время, если вы готовы упростить ваши потребности:
- одна файловая система
- целевая ОС совместима с UNIX / POSIX, как Linux
- система квот не включена
- нет корневого зарезервированного процента блока
- inode истощение не является проблемой
тут вы можете CREATE LANGUAGE plpython3u; и CREATE FUNCTION a LANGUAGE plpython3u функция, которая делает что-то вроде:
в функции, которая returns bigint и либо принимает datadir_path в качестве аргумента или обнаруживает его, выполняя запрос SPI, например SELECT setting FROM pg_settings WHERE name = 'data_directory' изнутри PL / Python.
если вы также хотите поддерживать Windows, см. кросс-платформенное пространство, оставшееся на Томе с помощью python . Я бы использовал запросы интерфейса управления Windows (WMI), а не использовать ctypes для вызова Windows API.
или вы могли бы используйте эту функцию кто-то написал в PL / Perlu сделать это с помощью df и mount синтаксический анализ вывода команды, который, вероятно, будет работать только на Linux, но эй, он предварительно написан.
Недавно перешел на Postgresql и еще не силен в нем. Столкнулся с проблемой (не критичной, но все же).
Сайт на джанго использует дерево treebear. При импорте пары сотен тысяч объектов база данных раздувается до 300 ГБ. Запускал VACUUM - не помогает. А вот после запуска VACUUM (FULL) база сжимается до адеватных 3ГБ, т.е. в 100 раз. Я читал, что Postgres при изменении данных не удаляет сразу старые версии данных, а помечает, поэтому требуется очистка. Но меня удивляют объемы - все же 300Гб как-то многовато. Сами импортируемые тексты имеют объем менее 8ГБ (несжатыми). В процессе импорта вроде ничего необычного не происходит: идет вставка объектов, а потом еще раз перезапись. Ну, раздувание раз в 10 я бы еще понял, но откуда 300ГБ? Это нормально или я что-то не так делаю?
На каждую операцию update тоже создается копия, индексы также занимают место. Зачем многократная перезапись? Автовакуум отключен, небось? Размер страницы какой? Транзакция закрывается после работы импорта?
Оптимизайка:Зачем многократная перезапись?
После вставки один раз каждый объект перезаписывается. Так логика сильно упрощается, т.к. только после вставки объекта в дерево можно на полную мощь пользоваться функционалом, предоставляемым деревом. Т.е. после вставки в дерево (insert), производятся некоторые вычисления и апдейт.
Оптимизайка:Автовакуум отключен, небось?
Всё что касается атовакуума осталось в настройках по дефолту (postgres версии 9.4), я их не трогал. Хотя в процессе импорта (он долго идет) вручную в консоли postgres VACUUM запускал несколько раз - база продолжала расти, только VACUUM (FULL) помогал.
Средний размер - несколько килобайт или десятков килобайт, иногда до 1-8 мегабайт текста.
Оптимизайка:Транзакция закрывается после работы импорта?
Надеюсь :) В джанго нет команды явного закрытия транзакции, в документации написано, что она автоматически закрывается по окончании атомарного блока кода.
И еще пара вопросов:
1) Я заметил, скорость импорта заметно падает после середины импорта. Может это быть связано с разрастанием базы? Или в сторону индексов смотреть?
2) Vacuum (FULL) можно запускать при импорте? Кроме временной задержки (из-за полной блокировки) ничего деструктивного для импорта не должно произойти?
Читайте также: