Postgresql не использует память
Этот вопрос был перенесен из переполнения стека, поскольку на него можно ответить в Exchange Stack Exchange для администраторов баз данных. Мигрировал 7 лет назад .
У меня есть два идентичных сервера (с точки зрения аппаратного обеспечения), они оба являются стандартными установками Windows Server 2008 r2, с минимальным количеством установленного программного обеспечения (в основном мой код и необходимые вещи, такие как jvm и т. Д.).
На одном сервере я запускаю sql server 2005, на втором сервере postgresql 9.1. Разница в производительности между этими двумя серверами ошеломляет, это настолько плохо на postgresql, что я сожалею о своей первоначальной речи «давайте использовать postgresql вместо того, чтобы платить за лицензию sql server» моему боссу. Мы говорим о разнице в 30 секунд и 15 минут для одной и той же команды, и это не просто одна команда, это любой запрос или команда, которую я бросаю на нее. Они оба имеют практически одинаковые данные (записи были вставлены в разном порядке), и обе базы данных имеют одинаковую структуру / индексы и т. Д.
Но я надеюсь, что это просто вопрос настройки производительности. Дело в том, что сервер sql в значительной степени использует все 32 гигабайта оперативной памяти на сервере, в то время как postgresl ничего не использует, определенно меньше, чем концерт, хотя на самом деле я не разобрался в деталях.
Как мне заставить postgresql использовать более 20 гигабайт оперативной памяти? Эти серверы были созданы специально для этой базы данных, поэтому, по моему мнению, все оперативные памяти, не используемые базой данных и вспомогательными процессами, теряются.
Вы поменяли что-нибудь на первоначальную настройку? Шаг 1: SET effective_cache_size=18G; (настройка по умолчанию очень низкая) Кстати: если предположить, что это 64-битный компьютер (без PTE)
Ты действительно не даешь нам достаточно, чтобы сильно помочь. Помимо «Это медленно», мы мало что знаем о вашем наборе данных, о том, как вы к нему обращаетесь, какие типы запросов обычно выполняются медленно, что вы уже сделали, чтобы настроить (и, возможно, неправильно) настроить ваш сервер. Черт возьми, на Linux-машине с большим количеством ядер и каналов памяти вы можете получить хреновую производительность задолго до того, как вы установили postgresql. Вы процессор или IO связаны? Какие нестандартные настройки у вас уже есть? Какие типы запросов медленные?
Postgres не «использует оперативную память», как вы говорите об этом. Он основывается на кеше страниц файловой системы ОС для большей части своего кэширования, поэтому, когда вы наблюдаете использование оперативной памяти в системе, работающей с postgres, вы обычно видите много ГБ, используемых буферами / кешем ОС, и отдельные процессы бэкэнда postgres, использующие лишь несколько несколько десятков МБ каждый.
Посмотрите эту ссылку: tekadempiere.blogspot.ae/2014/09/… И найдите значения конф, установленные для вашего ресурса, здесь: pgtune.leopard.in.ua
По умолчанию конфигурация PostgreSQL не настроена для рабочей нагрузки. Значения по умолчанию установлены для обеспечения работоспособности PostgreSQL везде с наименьшим количеством ресурсов. Имеются настройки по умолчанию для всех параметров базы данных. Главной обязанностью администратора базы данных или разработчика является настройка PostgreSQL в соответствии с нагрузкой их системы. В этом блоге мы изложим основные рекомендации по настройке параметров базы данных PostgreSQL для повышения производительности базы данных в соответствии с рабочей нагрузкой.
Имейте в виду, что, хотя оптимизация конфигурации сервера PostgreSQL повышает производительность, разработчик базы данных также должен быть внимательным при написании запросов. Если запросы выполняют полное сканирование таблицы, где можно использовать индекс, или выполнют тяжелые объединения или дорогостоящие операции агрегирования, тогда система все равно может работать плохо, даже если параметры базы данных настроены корректно. При написании запросов к базе данных важно обращать внимание на производительность.
Тем не менее, параметры базы данных тоже очень важны, поэтому давайте посмотрим на восемь, которые имеют наибольший потенциал для повышения производительности
Настраиваемые параметры PostgreSQL
PostgreSQL использует свой собственный буфер, а также использует буферизованный IO ядра. Это означает, что данные хранятся в памяти дважды, сначала в буфере PostgreSQL, а затем в буфере ядра. В отличие от других баз данных, PostgreSQL не обеспечивает прямой ввод-вывод. Это называется двойной буферизацией. Буфер PostgreSQL называется shared_buffer, который является наиболее эффективным настраиваемым параметром для большинства операционных систем. Этот параметр устанавливает, сколько выделенной памяти будет использоваться PostgreSQL для кеширования.
Значение по умолчанию для shared_buffer установлено очень низким, и вы не получите большой выгоды от него. Сделано это потому, что некоторые машины и операционные системы не поддерживают более высокие значения. Но в большинстве современных машин вам необходимо увеличить это значение для оптимальной производительности.
Рекомендуемое значение составляет 25% от общего объема оперативной памяти компьютера. Вам следует попробовать некоторые более низкие и более высокие значения, потому что в некоторых случаях можно получить хорошую производительность с настройкой более 25%. Но реальная конфигурация зависит от вашей машины и рабочего набора данных. Если ваш рабочий набор данных может легко поместиться в вашу оперативную память, вы можете увеличить значение shared_buffer, чтобы оно содержало всю вашу базу данных и чтобы весь рабочий набор данных мог находиться в кеше. Тем не менее, вы, очевидно, не хотите резервировать всю оперативную память для PostgreSQL.
Замечено, что в производственных средах большое значение для shared_buffer действительно дает хорошую производительность, хотя для достижения правильного баланса всегда следует проводить тесты.
Проверка значения shared_buffer
Примечание: Будьте осторожны, так как некоторые ядра не поддерживают большее значение, особенно в Windows.
Настройка автоочистки (autovacuum)
Сложность: средняя.
Потенциальное влияние: низкое-среднее.
Управление параллельным доступом посредством многоверсионности (Multi-Version Concurrency Control) — один из основополагающих принципов, делающих PostgreSQL таким популярным решением среди СУБД. Однако одним из неприятных моментов является то, что для каждой измененной или удаленной записи создаются неиспользуемые копии, от которых в конечном итоге надо избавляться. Неправильно настроенный процесс автоочистки (autovacuum) может снижать производительность. При этом чем сервер загруженнее, тем сильнее проявляется проблема.
Для управления демоном автоочистки используются следующие параметры:
- Параметр autovacuum_max_workers. При наличии большого количества огромных таблиц стоит увеличить количество одновременно работающих процессов автоочистки (по умолчанию три). В идеале должен быть один рабочий процесс на один процессор, но не больше количества процессоров. Слишком большое количество может увеличить нагрузку на процессор. Обычно берется значение между двумя этими числами. Это баланс между максимальной эффективностью автоочистки и общей производительностью системы.
- Параметр maintenance_work_mem. Чем больше значение, тем эффективнее процесс очистки. Имейте в виду, что есть закон убывающей отдачи. Слишком большое значение в лучшем случае станет пустой тратой оперативной памяти, а в худшем может исчерпать всю доступную память.
- Параметр autovacuum_freeze_max_age уменьшает вероятность TXID WRAPAROUND. Чем значение больше, тем реже он запускается, что снижает нагрузку на систему. Но, как и со всеми параметрами автоочистки, упомянутыми выше, есть нюанс. Если сделать задержку слишком большой, то и вы рискуете исчерпать txid, что приведет к принудительному завершению работы сервера в целях защиты целостности данных. Для определения правильного значения необходимо сопоставлять самый большой/старый txid с процессом автоочистки используя pg_stat_activity на предмет WRAPAROUND.
Аналогично вычислению work_mem, это значение можно посчитать арифметически или выполнить бенчмарки для получения оптимальных значений.
checkpoint_timeout, checkpoint_completion_target
PostgreSQL записывает изменения в WAL. Процесс контрольной точки сбрасывает данные в файлы. Это действие выполняется, когда возникает контрольная точка (CHECKPOINT). Это дорогостоящая операция и может вызвать огромное количество операций IO. Весь этот процесс включает в себя дорогостоящие операции чтения/записи на диск. Пользователи могут всегда запустить задание контрольной точки (CHECKPOINT), когда это необходимо, или автоматизировать запуск с помощью параметров checkpoint_timeout и checkpoint_completion_target.
Параметр checkpoint_timeout используется для установки времени между контрольными точками WAL. Установка слишком низкого значения уменьшает время восстановления после сбоя, поскольку на диск записывается больше данных, но это также снижает производительность, поскольку каждая контрольная точка в конечном итоге потребляет ценные системные ресурсы.
checkpoint_completion_target — это доля времени между контрольными точками для завершения контрольной точки. Высокая частота контрольных точек может повлиять на производительность. Для плавного выполнения задания контрольной точки, checkpoint_timeout должен иметь низкое значение. В противном случае ОС будет накапливать все грязные страницы до тех пор, пока соотношение не будет соблюдено, а затем производить большой сброс.
Почему так?
PostgreSQL выделяет память для хэширования и сортировки. Объем памяти управляется параметром work_mem . Размер work_mem по умолчанию — 4 МБ. Если для хэширования или сортировки нужно больше 4 МБ, PostgreSQL временно задействует пространство на диске.
Наш запрос потребляет явно больше 4 МБ, поэтому база данных использует столько памяти. Мы решили: спешить не будем, — и не стали увеличивать параметр и расширять хранилище. Лучше поискать другой способ урезать память для сортировки.
work_mem
Эта настройка используется для сложной сортировки. Если вам нужно выполнить сложную сортировку, увеличьте значение work_mem для получения хороших результатов. Сортировка в памяти происходит намного быстрее, чем сортировка данных на диске. Установка очень высокого значения может стать причиной узкого места в памяти для вашей среды, поскольку этот параметр относится к операции сортировки пользователя. Поэтому, если у вас много пользователей, пытающихся выполнить операции сортировки, тогда система выделит
для всех пользователей. Установка этого параметра глобально может привести к очень высокому использованию памяти. Поэтому настоятельно рекомендуется изменить его на уровне сеанса.
Первоначальный узел сортировки запроса оценивается в 514431,86. Стоимость — это произвольная вычисляемая единица. Для приведенного выше запроса у нас work_mem всего 2 МБ. В целях тестирования давайте увеличим это значение до 256 МБ и посмотрим, повлияет ли это на стоимость.
Стоимость запроса снижена с 514431,86 до 360617,36, то есть уменьшилась на 30%.
Заключение
Есть больше параметров, которые можно настроить, чтобы получить лучшую производительность, но они оказывают меньшее влияние, чем те, которые выделены здесь. В конце концов, мы всегда должны помнить, что не все параметры актуальны для всех типов приложений. Некоторые приложения работают лучше, при настройке параметров, а некоторые нет. Настройка параметров базы данных PostgreSQL должна выполняться в соответствии с конкретными потребностями приложения и операционной системы, в которой оно работает.
Сервера приложений
Сложность: низкая.
Потенциальное влияние: высокое.
Избегайте запуска приложений (PHP, Java и Python) и postgres на одном хосте. Относитесь внимательно к приложениям на этих языках, так как они могут потреблять большие объемы оперативной памяти, особенно сборщик мусора, что влечет за собой конкуренцию с системами баз данных за ресурсы и снижение общей производительности.
Экономная сортировка
"Сколько сортировка съест – зависит от размера набора данных и ключа сортировки. Набор данных не уменьшишь, а вот размер ключа — можно.
За точку отсчета возьмем средний размер ключа сортировки:
Каждый ключ весит 780. Чтобы уменьшить двоичный ключ, его можно хэшировать. В PostgreSQL для этого есть md5 (да, не секьюрно, но для нашей цели сойдет). Посмотрим, сколько весит BLOB, хэшированный с помощью md5:
Размер ключа, хэшированного через md5, — 36 байт. Хэшированный ключ весит всего 4% от исходного варианта.
Дальше мы запустили исходный запрос с хэшированным ключом:
И план выполнения:
С хэшированным ключом запрос потребляет всего 4 лишних мегабайта, то есть чуть больше 10% от прежних 30 МБ. Значит размер ключа сортировки сильно влияет на то, сколько памяти отъедает сортировка.
maintenance_work_mem
maintenance_work_mem — это параметр памяти, используемый для задач обслуживания. Значение по умолчанию составляет 64 МБ. Установка большого значения помогает в таких задачах, как VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY и ALTER TABLE.
Время создания индекса составляет 170091,371 мс, если для параметра maintenance_work_mem установлено значение только 10 МБ, но оно уменьшается до 111274,903 мс, когда мы увеличиваем значение параметра maintenance_work_mem до 256 МБ.
«Горячие точки» в данных
Сложность: высокая.
Потенциальное влияние: низкое-среднее-высокое.
Как и в случае с MySQL, избавление PostgreSQL от «горячих точек» зависит от ваших потоков данных и может даже повлечь за собой изменение архитектуры системы.
В первую очередь следует обращать внимание на следующее:
- Индексы. Убедитесь, что для столбцов, по которым осуществляется поиск есть индексы. Можно использовать системные каталоги и представления для мониторинга и проверки, что запросы используют индексы. Для анализа производительности запросов используйте расширения pg_stat_statement и pgbadger.
- Heap Only Tuples (HOT). Индексов может быть и слишком много. Снизить потенциальное раздувание и уменьшить размер таблицы можно, удалив неиспользуемые индексы.
- Секционирование таблиц. Ничто так не влияет на производительность, как огромная таблица, размер которой в несколько раз превышает средний размер других таблиц. Разбивка большой таблицы на более мелкие секции поможет повысить производительность запросов, например, при запросе данных, секционированных по дате. И так как таблица может обрабатываться только одним процессом автоочистки, то разбивка его на множество меньших таблиц позволяет более чем одному процессу автоочистки выполнять автоматическое удаление. Еще одно преимущество секционирования в том, что удаление большого количества строк намного эффективнее и быстрее, чем из единой огромной таблицы.
- Параллельные запросы. Появились в последних версиях postgres. Теперь для выполнения одного запроса может использоваться несколько процессоров, тогда как раньше запрос обрабатывался только одним.
- Денормализация. Можно повысить производительность, объединив столбцы из нескольких таблиц в одну таблицу. Повышение производительности достигается за счет увеличения избыточности данных. Тщательно обдумайте этот вариант, прежде чем его использовать!
effective_cache_size
effective_cache_size предоставляет оценку памяти, доступной для кэширования диска. Это всего лишь ориентир, а не точный объем выделенной памяти или кеша. Он не выделяет фактическую память, но сообщает оптимизатору объем кеша, доступный в ядре. Если значение этого параметра установлено слишком низким, планировщик запросов может принять решение не использовать некоторые индексы, даже если они будут полезны. Поэтому установка большого значения всегда имеет смысл.
Заключение
Есть больше параметров, которые можно настроить, чтобы получить лучшую производительность, но они оказывают меньшее влияние, чем те, которые выделены здесь. В конце концов, мы всегда должны помнить, что не все параметры актуальны для всех типов приложений. Некоторые приложения работают лучше, при настройке параметров, а некоторые нет. Настройка параметров базы данных PostgreSQL должна выполняться в соответствии с конкретными потребностями приложения и операционной системы, в которой оно работает.
Недавно нас по ночам стали будить алерты: на диске не хватает места. Мы быстро разобрались, что проблема в ETL-задачах.
ETL-задача выполнялась в таблице, где хранятся двоичные записи, дампы. Каждую ночь эта задача должна была удалять повторяющиеся дампы и освобождать место.
Для поиска повторяющихся дампов мы использовали этот запрос:
Запрос объединяет одинаковые дампы по BLOB-полю. С помощью функции окна мы получаем идентификатор первого появления каждого дампа. Потом этим запросом удаляем все повторяющиеся дампы.
Запрос выполнялся какое-то время, и, как видно из логов, кушал много памяти. На графике показано, как он каждую ночь забивал свободное пространство на диске:
Со временем запросу требовалось все больше памяти, провалы углублялись. И, заглянув в план выполнения, мы сразу увидели, куда все уходит:
Сортировка занимает много памяти. В плане выполнения из тестового набора данных сортировке требуется примерно 30 МБ памяти.
Управление подключениями
Сложность: низкая.
Потенциальное влияние: низкое-среднее-высокое
Высокая нагрузка обычно связана с увеличением сессий клиентов в единицу времени. Слишком большое их количество может блокировать процессы, вызывать задержки или даже приводить к ошибкам.
Простое решение — увеличить максимальное количество одновременных подключений:
Но более эффективный подход — пул соединений. Существует множество решений, но наиболее популярное — pgbouncer. PgBouncer может управлять соединениями, используя один из трёх режимов:
- Пул сеансов (session pooling). Наиболее корректный подход. При подключении клиента ему выдается соединение и остается за ним, пока он не отключится. Когда клиент отключается, подключение возвращается в пул. Это метод по умолчанию.
- Пул транзакций (transaction pooling). Подключение назначается клиенту только на время транзакции. Когда PgBouncer замечает, что транзакция завершена, подключение возвращается в пул.
- Пул операторов (statement pooling). Наиболее агрессивный метод. Подключение к серверу будет возвращаться в пул сразу после завершения запроса. Транзакции с несколькими операторами в этом режиме запрещены, так как они будут прерываться.
synchronous_commit
effective_cache_size
effective_cache_size предоставляет оценку памяти, доступной для кэширования диска. Это всего лишь ориентир, а не точный объем выделенной памяти или кеша. Он не выделяет фактическую память, но сообщает оптимизатору объем кеша, доступный в ядре. Если значение этого параметра установлено слишком низким, планировщик запросов может принять решение не использовать некоторые индексы, даже если они будут полезны. Поэтому установка большого значения всегда имеет смысл.
synchronous_commit
Соображения вдогонку
Запрос с хэшированным ключом сортировки памяти потребляет меньше, зато работает гораздо медленнее:
Хэширование задействует больше ЦП, поэтому запрос с хэшем работает медленнее. Но мы пытались решить проблему с пространством на диске, к тому же задача выполняется ночью, так что время — не проблема. Мы пошли на компромисс, чтобы сэкономить память.
Это отличный пример того, что не всегда нужно стараться ускорить запросы к базе данных. Лучше сбалансированно использовать что есть, и выжимать максимум из минимума ресурсов.
Взяв за основу статью Петра Зайцева об узких местах в производительности MySQL (MySQL Performance Bottlenecks), я хочу немного рассказать о PostgreSQL.
В наши дни для работы с PostgreSQL часто используются ORM-фреймворки. Обычно они работают хорошо, но со временем нагрузка увеличивается и возникает необходимость тюнить сервер базы данных. Каким бы надежным ни был PostgreSQL, но и он может тормозить при увеличении трафика.
Есть много способов устранения узких мест в производительности, но в этой статье мы обратим внимание на следующее:
- Параметры сервера
- Управление подключениями
- Настройка автоочистки (autovacuum)
- Дополнительная настройка автоочистки
- Раздувание таблиц (bloat)
- «Горячие точки» в данных
- Сервера приложений
- Репликация
- Серверное окружение
Дальше — больше
В этом примере мы хэшировали BLOB с помощью md5 . Хэши, созданные с MD5, должны весить 16 байт. А у нас получилось больше:
Наш хэш был ровно в два раза больше, ведь md5 выдает хэш в виде шестнадцатеричного текста.
В PostgreSQL можно использовать MD5 для хэширования с расширением pgcrypto . pgcrypto создает MD5 типа bytea (в двоичном виде):
Хэш все равно на 4 байта больше положенного. Просто тип bytea использует эти 4 байта, чтобы хранить длину значения, но мы этого так не оставим.
Оказывается, тип uuid в PostgreSQL весит ровно 16 байт и поддерживает любое произвольное значение, так что избавляемся от оставшихся четырех байтов:
Вот и все. 32 байта с md5 превращаются в 16 с uuid .
Я проверил последствия изменения, взяв набор данных побольше. Сами данные показывать нельзя, но я поделюсь результатами:
Как видно из таблицы, исходный проблемный запрос весил 300 МБ (и будил нас среди ночи). С ключом uuid сортировке потребовалось всего 7 МБ.
Дополнительная настройка автоочистки
Сложность: высокая.
Потенциальное влияние: высокое.
Этот метод, из-за его сложности, следует использовать только когда производительность системы уже на грани физических пределов хоста и это действительно стало проблемой.
Runtime-параметры автоочистки настраиваются в postgresql.conf . К сожалению, нет единого универсального решения, которое будет работать в любой высоконагруженной системе.
Параметры хранения для таблиц. Часто в базе данных значительная часть нагрузки ложится только на несколько таблиц. Настройка индивидуальных параметров автоочистки для таблицы — отличный способ не прибегать к ручному запуску VACUUM, который может существенно влиять на систему.
Настроить таблицы можно с помощью команды:
checkpoint_timeout, checkpoint_completion_target
PostgreSQL записывает изменения в WAL. Процесс контрольной точки сбрасывает данные в файлы. Это действие выполняется, когда возникает контрольная точка (CHECKPOINT). Это дорогостоящая операция и может вызвать огромное количество операций IO. Весь этот процесс включает в себя дорогостоящие операции чтения/записи на диск. Пользователи могут всегда запустить задание контрольной точки (CHECKPOINT), когда это необходимо, или автоматизировать запуск с помощью параметров checkpoint_timeout и checkpoint_completion_target.
Параметр checkpoint_timeout используется для установки времени между контрольными точками WAL. Установка слишком низкого значения уменьшает время восстановления после сбоя, поскольку на диск записывается больше данных, но это также снижает производительность, поскольку каждая контрольная точка в конечном итоге потребляет ценные системные ресурсы. checkpoint_completion_target — это доля времени между контрольными точками для завершения контрольной точки. Высокая частота контрольных точек может повлиять на производительность. Для плавного выполнения задания контрольной точки, checkpoint_timeout должен иметь низкое значение. В противном случае ОС будет накапливать все грязные страницы до тех пор, пока соотношение не будет соблюдено, а затем производить большой сброс.
wal_buffers
PostgreSQL сначала записывает записи в WAL (журнал предзаписи) в буферы, а затем эти буферы сбрасываются на диск. Размер буфера по умолчанию, определенный wal_buffers, составляет 16 МБ. Но если у вас много одновременных подключений, то более высокое значение может повысить производительность.
work_mem
Эта настройка используется для сложной сортировки. Если вам нужно выполнить сложную сортировку, увеличьте значение work_mem для получения хороших результатов. Сортировка в памяти происходит намного быстрее, чем сортировка данных на диске. Установка очень высокого значения может стать причиной узкого места в памяти для вашей среды, поскольку этот параметр относится к операции сортировки пользователя. Поэтому, если у вас много пользователей, пытающихся выполнить операции сортировки, тогда система выделит:
для всех пользователей. Установка этого параметра глобально может привести к очень высокому использованию памяти. Поэтому настоятельно рекомендуется изменить его на уровне сеанса.
Первоначальный узел сортировки запроса оценивается в 514431,86. Стоимость — это произвольная вычисляемая единица. Для приведенного выше запроса у нас work_mem всего 2 МБ. В целях тестирования давайте увеличим это значение до 256 МБ и посмотрим, повлияет ли это на стоимость.
Стоимость запроса снижена с 514431,86 до 360617,36, то есть уменьшилась на 30%.
Настраиваемые параметры PostgreSQL
PostgreSQL использует свой собственный буфер, а также использует буферизованный IO ядра. Это означает, что данные хранятся в памяти дважды, сначала в буфере PostgreSQL, а затем в буфере ядра. В отличие от других баз данных, PostgreSQL не обеспечивает прямой ввод-вывод. Это называется двойной буферизацией. Буфер PostgreSQL называется shared_buffer, который является наиболее эффективным настраиваемым параметром для большинства операционных систем. Этот параметр устанавливает, сколько выделенной памяти будет использоваться PostgreSQL для кеширования.
Значение по умолчанию для shared_buffer установлено очень низким, и вы не получите большой выгоды от него. Сделано это потому, что некоторые машины и операционные системы не поддерживают более высокие значения. Но в большинстве современных машин вам необходимо увеличить это значение для оптимальной производительности.
Рекомендуемое значение составляет 25% от общего объема оперативной памяти компьютера. Вам следует попробовать некоторые более низкие и более высокие значения, потому что в некоторых случаях можно получить хорошую производительность с настройкой более 25%. Но реальная конфигурация зависит от вашей машины и рабочего набора данных. Если ваш рабочий набор данных может легко поместиться в вашу оперативную память, вы можете увеличить значение shared_buffer, чтобы оно содержало всю вашу базу данных и чтобы весь рабочий набор данных мог находиться в кеше. Тем не менее, вы, очевидно, не хотите резервировать всю оперативную память для PostgreSQL.
Замечено, что в производственных средах большое значение для shared_buffer действительно дает хорошую производительность, хотя для достижения правильного баланса всегда следует проводить тесты.
Проверка значения shared_buffer
Примечание: Будьте осторожны, так как некоторые ядра не поддерживают большее значение, особенно в Windows.
maintenance_work_mem
maintenance_work_mem — это параметр памяти, используемый для задач обслуживания. Значение по умолчанию составляет 64 МБ. Установка большого значения помогает в таких задачах, как VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY и ALTER TABLE.
Время создания индекса составляет 170091,371 мс, если для параметра maintenance_work_mem установлено значение только 10 МБ, но оно уменьшается до 111274,903 мс, когда мы увеличиваем значение параметра maintenance_work_mem до 256 МБ.
Раздувание таблиц (bloat)
Сложность: низкая.
Потенциальное влияние: среднее-высокое.
Со временем производительность системы может ухудшаться из-за неправильных политик очистки, вследствие чрезмерного раздувания (bloat) таблиц. Так что даже настройка демона автоочистки и ручной запуск VACUUM не решает проблему. В этих случаях на помощь приходит расширение pg_repack.
С помощью расширения pg_repack можно перестроить и реорганизовать таблицы и индексы в условиях продакшена
wal_buffers
PostgreSQL сначала записывает записи в WAL (журнал предзаписи) в буферы, а затем эти буферы сбрасываются на диск. Размер буфера по умолчанию, определенный wal_buffers, составляет 16 МБ. Но если у вас много одновременных подключений, то более высокое значение может повысить производительность.
Параметры сервера
Сложность: низкая.
Потенциальное влияние: высокое.
Еще не так давно были времена, когда актуальные версии postgres могли работать на i386. С тех пор настройки по умолчанию были изменены, но они по-прежнему сконфигурированы на использование наименьшего количества ресурсов.
Эти параметры изменить очень просто и обычно они настраиваются при первоначальной установке. Неправильные значения этих параметров могут привести к высокой загрузке процессора и ввода-вывода:
- Параметр effective_cache_size ~ от 50 до 75%
- Параметр shared_buffers ~ 1/4 – 1/3 объема оперативной памяти
- Параметр work_mem ~ 10МБ
Вычисление значения shared_buffers — интересная головоломка. На нее можно смотреть с двух сторон: если у вас небольшая база данных, то можно установить значение shared_buffers достаточно большим, чтобы вся база данных поместилась в оперативной памяти. С другой стороны, можно настроить загрузку в память только часто используемых таблиц и индексов (вспоминайте правило 80/20). Раньше рекомендовалось устанавливать значение в 1/3 объема оперативной памяти, но со временем, так как объемы памяти росли, оно было уменьшено до 1/4. Если памяти выделено мало, то будет увеличиваться ввод-вывод и нагрузка на процессор. О слишком большом выделении памяти будет свидетельствовать выход на плато загрузки процессора и ввода-вывода.
Еще один фактор, который следует учитывать — это кэш ОС. При достаточном объеме оперативной памяти Linux будет кэшировать таблицы и индексы в памяти и, в зависимости от настроек, может заставить PostgreSQL поверить в то, что он читает данные с диска, а не из оперативной памяти. Одна и та же страница находится и в буфере postgres и в кэше ОС, и это одна из причин не делать shared_buffers очень большим. С помощью расширения pg_buffercache можно посмотреть использование кэша в реальном времени.
Параметр work_mem задает объем памяти, используемый для операций сортировки. Установка слишком низкого значения гарантирует плохую производительность, так как сортировка будет выполняться с использованием временных файлов на диске. С другой стороны, хотя установка большого значения и не влияет на производительность, но при большом количестве подключений есть риск нехватки оперативной памяти. Проанализировав память, используемую всеми запросами и сеансами, можно посчитать необходимое значение.
Используя EXPLAIN ANALYZE можно увидеть, как выполняются операции сортировки, и, изменяя значение для сеанса, определить момент, когда начинается слив на диск.
Можно также использовать бенчмарки системы.
О «категориях» и «потенциальном влиянии»
«Сложность» говорит о том насколько просто реализовать предлагаемое решение. А «потенциальное влияние» дает представление о степени улучшений в производительности системы. Однако в силу возраста системы, ее типа, технического долга и т.п. точное описание сложности и влияния может быть проблематичным. В конце концов, в сложных ситуациях окончательный выбор всегда за вами.
- Сложность
- Низкая
- Средняя
- Высокая
- Низкая-средняя-высокая
Репликация
Сложность: низкая.
Потенциальное влияние: высокое.Синхронная и асинхронная репликация. Последние версии postgres поддерживают логическую и потоковую репликацию как в синхронном, так и в асинхронном режимах. Хотя по умолчанию используется асинхронный режим репликации, но необходимо учитывать последствия использования синхронной репликации, особенно в сетях с существенной задержкой.
Серверное окружение
И последнее, но не менее важное — это простое увеличение мощности хоста. Давайте рассмотрим, на что влияет каждый из ресурсов в плане производительности PostgreSQL:
По умолчанию конфигурация PostgreSQL не настроена для рабочей нагрузки. Значения по умолчанию установлены для обеспечения работоспособности PostgreSQL везде с наименьшим количеством ресурсов. Имеются настройки по умолчанию для всех параметров базы данных. Главной обязанностью администратора базы данных или разработчика является настройка PostgreSQL в соответствии с нагрузкой их системы. В этом блоге мы изложим основные рекомендации по настройке параметров базы данных PostgreSQL для повышения производительности базы данных в соответствии с рабочей нагрузкой.
Имейте в виду, что, хотя оптимизация конфигурации сервера PostgreSQL повышает производительность, разработчик базы данных также должен быть внимательным при написании запросов. Если запросы выполняют полное сканирование таблицы, где можно использовать индекс, или выполнют тяжелые объединения или дорогостоящие операции агрегирования, тогда система все равно может работать плохо, даже если параметры базы данных настроены корректно. При написании запросов к базе данных важно обращать внимание на производительность.
Тем не менее, параметры базы данных тоже очень важны, поэтому давайте посмотрим на восемь, которые имеют наибольший потенциал для повышения производительности
Читайте также:
- Сохранить в кривых в coreldraw
- Часто ли ломаются моноблоки
- Для перемещения одного или нескольких файлов можно использовать предложение with move
- Точка движется по закону s t 2t3 3t чему равна скорость в момент t0 1c
- Какое понятие на компьютерном жаргоне означает единицу измерения количества информации