В последнее время я углубился в параметры памяти PostgreSQL, ища способы их тонкой настройки и повышения производительности. После изучения множества ресурсов и проведения тестов я нашел несколько ключевых настроек, которые могут существенно повлиять на производительность.
Если вы используете PostgreSQL и хотите оптимизировать его использование памяти, вот несколько важных настроек, на которые стоит обратить внимание.
Ключевые параметры памяти для настройки
Давайте разберем самые важные параметры памяти PostgreSQL, которые стоит рассмотреть для изменения.
Думайте о shared_buffers как о личном запасе памяти PostgreSQL, где хранится часто используемая информация. Идея заключается в том, чтобы хранить как можно больше данных в памяти, чтобы сократить дорогостоящие чтения с диска.
Рекомендуемая настройка:
Большинство экспертов рекомендуют устанавливать это значение примерно на 25% от общего объема ОЗУ вашей системы.
Например, если у вас 16 ГБ ОЗУ, установка на уровне 4 ГБ будет хорошей отправной точкой.
- work_mem
Когда PostgreSQL обрабатывает запросы, связанные с сортировкой, объединением или группировкой данных, он использует work_mem. Эта настройка контролирует, сколько памяти получает каждая операция, прежде чем начнет записывать временные файлы на диск.
Пример:
- Если work_mem = 50MB и ваш запрос имеет 5 задач сортировки, он может использовать 50MB x 5 = 250MB памяти.
- Теперь представьте 10 пользователей, выполняющих аналогичные запросы – это 250MB x 10 = 2.5GB памяти.
Совет:
- Установите его слишком низко, и запросы могут замедлиться из-за использования диска.
- Установите его слишком высоко, и вы можете быстро исчерпать память, особенно с большим количеством пользователей.
Иногда большие значения work_mem могут на самом деле замедлить работу, потому что PostgreSQL должен управлять большими объемами памяти.
- effective_cache_size
Эта настройка немного отличается – она не выделяет память, а сообщает PostgreSQL, сколько памяти он думает, что может использовать из кэша ОС. PostgreSQL использует это значение, чтобы решить, следует ли использовать индексы или выполнять последовательные сканирования.
Неправильная настройка:
- Слишком низко? PostgreSQL может избегать индексов, думая, что память ограничена.
- Слишком высоко? PostgreSQL может стать слишком оптимистичным и принимать неверные решения.
Хорошее правило: Установите это значение на 50-75% от общего объема ОЗУ, особенно если PostgreSQL является основной нагрузкой на машине.
- maintenance_work_mem
Эта настройка касается того, сколько памяти PostgreSQL может использовать для таких операций, как VACUUM, CREATE INDEX и других задач обслуживания. Увеличение этого значения может ускорить эти операции, не замедляя обычную деятельность базы данных.
Рекомендуемая настройка:
Если у вас есть место, увеличьте это значение до 1 ГБ или более, чтобы ускорить выполнение задач обслуживания.
Способы изменения параметров памяти
PostgreSQL позволяет изменять параметры памяти несколькими способами, в зависимости от ваших требований и готовности к риску. Вот самые эффективные подходы:
- Глобальное изменение параметров памяти
Для постоянных изменений, которые будут применяться ко всем сессиям и запросам, измените файл конфигурации PostgreSQL (postgresql.conf) или используйте ALTER SYSTEM:
ALTER SYSTEM SET work_mem = '500MB';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '20GB';
SELECT pg_reload_conf(); -- Применить изменения без перезагрузки- Изменение настроек на уровне сессии
Для временных корректировок, которые влияют только на текущее соединение:
SET work_mem = '100MB';
SET effective_cache_size = '16GB';Мой реальный пример настройки памяти PostgreSQL
Сценарий ##o
База данных PostgreSQL 10, работающая на сервере с 30 ГБ ОЗУ, с часто доступными 15 ГБ ОЗУ, испытывала медленную производительность запросов на экспорт CSV при обработке финансовых транзакций. Сложный запрос, объединяющий четыре большие таблицы, занимал 33 секунды для получения результатов:
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;Размеры таблиц:
- finance_transaction (~1 миллион строк)
- transaction_log (~1 миллион строк)
- transaction_audit_history (~4 миллиона строк)
- billing_record (~1 миллион строк)
Настройки памяти по умолчанию:
| Настройки | Значение по умолчанию |
|---|---|
| shared_buffers | 128MB |
| work_mem | 4MB |
| maintenance_work_mem | 64MB |
| effective_cache_size | 4GB |
План выполнения запроса (EXPLAIN ANALYZE)
Gather (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=26603.645..33465.648 rows=4847517 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=26817.703..29038.177 rows=969503 loops=5)
Hash Cond: (ft.id = tah.finance_transaction)
-> Hash Join (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=18471.362..19081.998 rows=259931 loops=5)
Hash Cond: (br.id = ft.billing_record)
-> Parallel Seq Scan on billing_record br (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..42.297 rows=268393 loops=5)
-> Hash (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=18468.587..18468.589 rows=1299656 loops=5)
Buckets: 4096 Batches: 512 Memory Usage: 708kB
-> Hash Join (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=9094.933..15042.241 rows=1300948 loops=5)
Hash Cond: (tl.id = ft.transaction_log)
-> Seq Scan on transaction_log tl (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.019..133.507 rows=1564453 loops=5)
-> Hash (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=9092.767..9092.768 rows=1300948 loops=5)
Buckets: 16384 Batches: 128 Memory Usage: 2199kB
-> Seq Scan on finance_transaction ft (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.005..143.568 rows=1300948 loops=5)
-> Hash (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=8267.360..8267.360 rows=4852654 loops=5)
Buckets: 32768 Batches: 512 Memory Usage: 2059kB
-> Seq Scan on transaction_audit_history tah (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.020..789.242 rows=4852654 loops=5)
Planning time: 0.346 ms
Execution time: 33586.541 msАнализ плана выполнения запроса:
| Проблема | Наблюдение из EXPLAIN ANALYZE | Возможная причина | Решение |
|---|---|---|---|
| Дисковые переполнения в Hash Joins | Buckets: 4096 → Batches: 512 Memory Usage: 708kB | work_mem слишком низкий (по умолчанию 4MB) | Увеличьте work_mem до 300MB ALTER SYSTEM SET work_mem = '300MB'; |
| Высокий ввод-вывод от последовательных сканирований | Параллельные последовательные сканирования на billing_record, finance_transaction и transaction_log | shared_buffers слишком низкий (по умолчанию 128MB) | Увеличьте shared_buffers до 8GB ALTER SYSTEM SET shared_buffers = '8GB'; |
| Планировщик запросов недооценил доступную ОЗУ | Использует больше последовательных сканирований вместо индексных сканирований | effective_cache_size слишком низкий (по умолчанию 4GB) | Увеличьте effective_cache_size до 20GB ALTER SYSTEM SET effective_cache_size = '20GB'; |
После увеличения параметров памяти, вот результат EXPLAIN ANALYZE:
Gather (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=16228.246..23884.145 rows=4847517 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=17226.338..20072.376 rows=969503 loops=5)
Hash Cond: (ft.id = tah.finance_transaction)
-> Hash Join (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=14289.482..15561.150 rows=259931 loops=5)
Hash Cond: (br.id = ft.billing_record)
-> Parallel Seq Scan on billing_record br (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..37.096 rows=268393 loops=5)
-> Hash (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=14287.210..14287.212 rows=1299656 loops=5)
Buckets: 524288 Batches: 8 Memory Usage: 46914kB
-> Hash Join (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=3728.706..6986.704 rows=1300948 loops=5)
Hash Cond: (tl.id = ft.transaction_log)
-> Seq Scan on transaction_log tl (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.018..123.814 rows=1564453 loops=5)
-> Hash (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=3704.309..3704.309 rows=1300948 loops=5)
Buckets: 2097152 Batches: 2 Memory Usage: 151889kB
-> Seq Scan on finance_transaction ft (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.018..139.211 rows=1300948 loops=5)
-> Hash (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=2927.466..2927.466 rows=4852654 loops=5)
Buckets: 2097152 Batches: 4 Memory Usage: 246002kB
-> Seq Scan on transaction_audit_history tah (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.025..630.852 rows=4852654 loops=5)
Planning time: 0.341 ms
Execution time: 24013.565 msСравнение производительности: До и После увеличения параметров памяти
| Метрика | До (Настройки по умолчанию) | После (Оптимизированные параметры памяти) | Улучшение |
|---|---|---|---|
| Общее время выполнения | 33.58с | 24.01с | ⬇ ~28.5% быстрее (~9.5с сэкономлено) |
| Hash Join (ft.id = tah.finance_transaction) | 29.03с | 20.07с | ⬇ ~9с быстрее |
| Hash Join (br.id = ft.billing_record) | 19.08с | 15.56с | ⬇ ~3.5с быстрее |
| Hash Batches (ft.transaction_log) | 128 Batches | 2 Batches | ✅ Огромное сокращение дисковых переполнений |
| Hash Batches (transaction_audit_history) | 512 Batches | 4 Batches | ✅ Сниженное использование диска |
Выбор правильных параметров памяти: Индивидуальный подход
Настройка параметров памяти PostgreSQL – это не универсальное решение. Это зависит от размера вашей базы данных, количества одновременно работающих пользователей и шаблонов запросов. Найдите правильные work_mem, shared_buffers и effective_cache_size.
- Если у вашего сервера достаточно свободной ОЗУ, вы можете выделить больше памяти для PostgreSQL.
- Если у вас много одновременно работающих пользователей, выделение слишком большого объема памяти на запрос может вызывать нестабильность системы.
- Запустите EXPLAIN ANALYZE на медленных запросах, чтобы выявить дисковые переполнения и неэффективности объединений.
Если вы хотите безопасно протестировать изменения, не затрагивая всю систему, применяйте настройки для каждого запроса вместо глобальных:
✅ Измените настройки для одного тяжелого запроса:
SET LOCAL work_mem = '300MB';
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;Заключение
Настройка параметров памяти PostgreSQL может существенно повлиять на производительность, помогая вашим запросам выполняться быстрее и снижая ненужный дисковый ввод-вывод. Главное – найти правильный баланс в зависимости от нагрузки на базу данных, доступной ОЗУ и поведения ваших запросов.
Ключевые выводы:
- Знайте свою нагрузку – Не увеличивайте параметры памяти без разбора; сначала проанализируйте, как выполняются ваши запросы.
- Используйте
EXPLAIN ANALYZE– Это поможет вам выявить проблемы, такие как дисковые переполнения и неэффективные объединения. - Тщательно настраивайте
work_mem– Более высокое значение может ускорить объединения и сортировку, но установка его слишком высоко может исчерпать всю вашу ОЗУ. - Изменяйте настройки на правильном уровне – Используйте глобальные настройки для общих улучшений, настройки сессии для временных корректировок и настройки для запросов для действительно тяжелых операций.
- Следите за воздействием – Проверяйте логи, отслеживайте производительность и корректируйте по мере необходимости.
В конечном итоге, настройка PostgreSQL – это часть науки, часть эксперимента. Начинайте с малого, отслеживайте результаты и корректируйте по мере необходимости. С правильными настройками ваша база данных будет работать более плавно, быстрее и гораздо эффективнее. 🚀