Инструкция по оптимизации PostgreSQL 8.x

Опубликовано пт, 01/06/2007 - 04:00 пользователем postgresmen

Перед вами набор простых правил настройки сервера PostgreSQL. Большая часть этих идей была высказана в оригинальной статье PostgreSQL Performance Checklist Джошем Беркусом (Josh Berkus); сотрудники компании "Постгресмен" добавили к этому листу ряд важных пунктов, адаптировав некоторые места под более актуальные версии PostgreSQL. Статья написана так, чтобы администратор баз данных мог в течение получаса оптимизировать PostgreSQL, усвоив основные принципы его настройки.

Пять основных принципов настройки PostgreSQL-сервера

  1. Диски>Память>ЦПУ Если вы решили купить сервер под базу данных, лучше выбрать массив дисков с высокой производительностью, средний процессор и соответствующую память. Если ваш бюджет не позволяет вложиться в дисковый массив, лучше взять побольше памяти. Как и любая ACID-совместимая СУБД, PostgreSQL очень сильно зависит от скорости обмена данными, и редкое приложение, использующее Postgres, требует больше от процессора, чем от SCSI (хотя, бывают и такие). Это относится как к маленьким приложениям, так и к огромным, так что покупайте топовый процессор, только если кроме него хватит денег на дорогой RAID-контроллер из последней линейки хорошего производителя вроде LSI, 3ware или Areca и много дисков. (Важное замечание: в наши дни память уже не является такой дорогой, поэтому внимательно проанализируйте вашу ситуацию — если вы разрабатываете web-приложение, то вероятность того, что вам удастся добиться того, что большая часть ваших данных будет располагаться в RAM, достаточно высока. Поэтому не торопитесь, может быть, вам повезло и в вашем случае диски не являются узким местом.)
  2. Много дисков == хорошо! Имея много дисков, PostgreSQL, как и многие операционные системы, разделяет запросы на чтение и запись в базу данных. Это сразу заметно для всех приложений, база данных которых не помещается в оперативную память. При том, что минимальный размер винчестера сейчас равен 72 Гб и можно попробовать обойтись одним диском или одной RAID 1 mirrored парой, используя 2, 4, 6 или даже 14 дисков, вы значительно повысите производительность. Ну, и, конечно же, SCSI дает гораздо больший выигрыш, чем IDE, даже с Serial ATA.
  3. Отделите логи транзакций от базы данных Предположим, что вы уже потратили деньги на достойный массив дисков. Мало просто засунуть их в один RAID. Выделение для лога транзакций (pg_xlog) собственных дисковых ресурсов (массива или просто отдельного диска) дает как минимум 12% выигрыш для нагруженных систем. Это жизненно важно и для небольших серверов, особенно с медленными SCSI или IDE дисками: даже на машине с двумя дисками можно выселить лог транзакций на диск с операционной системой и получить при этом существенный выигрыш в производительности.
  4. RAID 1+0/0+1>RAID 5 RAID 5 с тремя дисками уже стал эталоном неудачных бюджетных серверов. Это, вероятно, наихудшая комбинация для PostgreSQL: вы сможете получить не более 50% от той производительности, которая была бы у вас с одним SCSI диском. Если у вас массив из 2, 4 или 6 дисков, то лучше остановиться на RAID 1, или 1+0, или 0+1. Когда дисков больше 6, RAID 5 восстанавливает свои позиции, и выбирать конкретную схему RAID нужно уже в зависимости от вашего контроллера. Имейте в виду, что зачастую использование программного RAID более предпочтительно, особенно если в сервере установлен дешевый контроллер вроде встроенного от Adaptec (Важное замечание: стоит отметить, что дешевые RAID-контроллеры зачастую проигрывают программному RAID и это относится не только к Adaptec, но и к LSI, 3ware и другим.)
  5. Приложения должны жить дружно Многие компании допускают одну и ту же ошибку: ставят на сервер вместе с PostgreSQL другие приложения, которые начинают бороться с ним за одни и те же ресурсы. Хуже того, бывает, что на одной машине стоит несколько СУБД. Они сражаются за пропусную способность винчестера и за дисковый кэш операционной системы. В итоге все СУБД работают плохо. Почти так же дело обстоит, если PostgreSQL работает на сервере документооборота или системы слежения за безопасностью, которая ведет активное журналирование. PostgreSQL может хорошо работать только вместе с приложениями, которые больше зависят от мощности процессора, чем от производительности дисков. Например, при достаточном количестве оперативной памяти, с PostgreSQL хорошо уживается веб-сервер от Apache.

Десять параметров, которые нужно менять в postgresql.conf

Количество параметров, которые появились в основном конфигурационном файле PostgreSQL, postgresql.conf, просто пугает. Даже те функции, которые были знакомы по предыдущим версиям, сменили свое название. Все это сделано для того, чтобы обеспечить администратору базы данных большую свободу, но к нововведениям нужно привыкнуть.

Ниже перечислены основные параметры, которые меняют опытные администраторы БД в стремлении улучшить производительность. Конечно, есть еще и другие настройки, в которые пользователи боятся вмешиваться, но изменение которых может оказаться просто необходимым -- они остаются за рамками данного мини-обзора.

Замечание: если вы комментируете какой-либо параметр в postgresql.conf, это совсем не значит, что он принимает первоначальное значение по умолчанию. PostgreSQL будет помнить значение его последней настройки.

Соединения

listen_adresses: заменяет tcp_ip и virtual_hosts из PostgreSQL 7.4. По умолчанию, как правило, устанавливается разрешение только на соединение в пределах сервера через локальный сокет ("localhost"). Многие администраторы БД предпочитают указывать "*", чтобы PostgreSQL слушал все доступные сетевые интерфейсы для возможности (после добавления соответствующих настроек в файл pg_hba.conf) устанавливать соединение по сети.

max_connections: как и в более ранних версиях, нужно указать максимально количество возможных одновременных соединений, которое вам может понадобиться. Если задать большие значения, вам может понадобиться больше разделяемой памяти (shared_buffers). Так как ограничение на число соединений, как у ОС, так и у PostgreSQL может быть довольно большим, то имеет смысл группировать соединения. Например, для среднего однопроцессорного 32-битного linux-сервера уже 150 соединений будут вполне ощутимы, а 600 соединений будут пределом возможностей. Для более мощных серверов эти числа, конечно, больше.

Память

shared_buffers: напомним, это НЕ вся память, которая нужна для работы PostgreSQL, это только размер разделяемой между процессами PostgreSQL памяти, которая нужна для выполнения активных операций. Она должна занимать меньшую часть оперативной памяти вашего компьютера, так как PostgreSQL использует также дисковый кэш операционной системы. К сожалению, чтобы знать точное число shared buffers, нужно учесть количество оперативной памяти компьютера, размер базы данных, число соединений и сложность запросов, так что лучше воспользуемся несколькими простыми правилами настройки.

На выделенных серверах полезным объемом будет значение от 8 МБ до 2 ГБ. Объем может быть выше, если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров. И, конечно же, не забываем об остальных приложениях. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности. Вот несколько примеров, полученных на личном опыте и при тестировании:

  • Laptop, Celeron processor, 384MB RAM, база данных 25MB: shared_buffers 12 MB
  • Athlon server, 1GB RAM, база данных поддержки принятия решений 10GB: 200 MB
  • Quad PIII server, 4GB RAM, 40GB, 150 соединений, "тяжелые" транзакции: 1 GB
  • Quad Xeon server, 8GB RAM, 200GB, 300 соединений, "тяжелые" транзакции: 2 GB

Заметим, что увеличение числа shared_buffers и других параметров памяти потребует изменения настроек System V memory вашей операционной системы. Подробнее об этом можно прочитать в документации по PostgreSQL.

work_mem: ранее известное как sort_mem, было переименовано, так как сейчас определяет максимальное количество оперативной памяти, которое может выделить одна операция сортировки, агрегации и др. Это не разделяемая память, work_mem выделяется отдельно на каждую операцию (от одного до нескольких раз за один запрос). Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.

Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ. С другой стороны, приложения для поддержки принятия решений с сотнями строк в каждом запросе и десятками миллионов столбцов  в таблицах фактов часто требуют work_mem порядка 500 МБ. Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии.

maintenance_work_mem: предыдущее название в PostgreSQL 7.x vacuum_mem. Это объем памяти, который требуется PostgreSQL для VACUUM, ANALYZE, CREATE INDEX, и добавления внешних ключей. Чтобы операции выполнялись максимально быстро, нужно устанавливать этот параметр тем выше, чем больше размер таблиц в вашей базе данных. Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ.

Диск и журнал транзакций (WAL)

checkpoint_segments: определяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками. Этот параметр не имеет особого значения для базы данных, предназначенной преимущественно для чтения, но для баз данных со множеством транзакций увеличение этого параметра может оказаться жизненно необходимым. В зависимости от объема данных установите этот параметр в диапазоне от 12 до 256 сегментов и, если в логе появляются предупреждения (warning) о том, что контрольные точки происходят слишком часто, постепенно увеличивайте его. Место, требуемое на диске, вычисляется по формуле (checkpoint_segments * 2 + 1) * 16 МБ, так что убедитесь, что у вас достаточно свободного места. Например, если вы выставите значение 32, вам потребуется больше 1 ГБ дискового пространства.

max_fsm_pages: определяет размер реестра, в котором хранится информация о частично освобождённых страницах данных, готовых к заполнению новыми данными. При правильной настройке ускоряет VACUUM и избавляет от необходимости делать VACUUM FULL или REINDEX. Значение этого параметра нужно установить чуть больше, чем полное число страниц, которые могут быть затронуты операциями обновления или удаления между выполнением VACUUM. Чтобы определить это число, можно запустить VACUUM VERBOSE ANALYZE и выяснить общее число страниц, используемых базой данных. max_fsm_pages обычно требует немного памяти, так что на этом параметре лучше не экономить.

vacuum_cost_delay: Если у вас большие таблицы, и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягиваяя его по времени. Чтобы включить эту функциональность, нужно поднять значение vacuum_cost_delay выше 0. Используйте разумную задержку от 50 до 200 мс. Для более тонкой настройки повышайте vacuum_cost_page_hit и понижайте vacuum_cost_page_limit. Это ослабит влияние VACUUM, увеличив время его выполнения. В тестах с параллельными транзакциями Ян Вик (Jan Wieck) получил, что при значениях delay - 200, page_hit - 6 и предел - 100 вляние VACUUM уменьшилось более чем на 80%, но его длительность увеличилась втрое. 

Планировщик запросов

Следующие настройки помогают планировщику запросов правильно оценивать стоимости различных операций и выбирать оптимальный план выполнения запроса. Существуют 2 глобальные настройки планировщика, на которые стоит обратить внимание:

effective_cache_size: указывает планировщику на размер самого большого объекта в базе данных, который теоретически может быть закеширован. На выделенном сервере имеет смысл выставлять effective_cache_size в 2/3 от всей оперативной памяти; на сервере с другими приложениями сначала нужно вычесть из всего объема RAM размер дискового кэша ОС и память, занятую остальными процессами.

random_page_cost: переменная, указывающая на условную стоимость индексного доступа к страницам данных. На серверах с быстрыми дисковыми массивами имеет смысл уменьшать изначальную настройку до 3.0, 2.5 или даже до 2.0. Если же активная часть вашей базы данных много больше размеров оперативной памяти, попробуйте поднять значение параметра. Можно подойти к выбору оптимального значения и со стороны производительности запросов. Если планировщик запросов чаще, чем необходимо, предпочитает последовательные просмотры (sequential scans) просмотрам с использованием индекса (index scans), понижайте значение. И наоборот, если планировщик выбирает просмотр по медленному индексу, когда не должен этого делать, настройку имеет смысл увеличить. После изменения тщательно тестируйте результаты на максимально широком наборе запросов. Никогда не опускайте значение random_page_cost ниже 2.0; если вам кажется, что random_page_cost нужно еще понижать, разумнее в этом случае менять настройки статистики планировщика.