Рассмотрю некоторые параметры, которые влияют на производительность самого сервера (без учета оптимизации структуры самой БД)
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 ГБ.
Объем может быть выше, если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров.
И, конечно же, не забываем об остальных приложениях. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности.
На всех форумах и в самом файле конфигурации пишут что значение для этого параметра следует устанавливать в два раза больше, чем максимальное количество подключений max_connections*2, причем каждый буфер занимает 8K.
Вот несколько примеров, полученных на личном опыте и при тестировании:
- 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 нужно еще понижать, разумнее в этом случае менять настройки статистики планировщика.
Использование дополнительного ПО.
pgBouncer и другие.
pgBouncer (или его альтернатива) — первое, что должно быть установлено на сервере с базой данных. Я уже насмотрелся на cacti-графики показывающие десятикратное падение нагрузки от простой установки менеджера соединений. Если пулер соединений у вас не установлен, то на каждое соединение с базой у вас запускается отдельный процесс, далее этот процесс отъедает, как минимум, work_mem оперативки и начинает бороться за CPU и жесткий диск с себе подобными выполняя SQL запрос. Всё бы хорошо, но вот когда количество таких процессов зашкаливает за 200-500 серверу, даже очень мощному, становится туго. Очень туго. pgBouncer нас от этого спасает. Также список полезных или даже незаменимых приложений для работы с PostgreSQL можно найти на сайте postgresqlrussia.org.
pgFouine
pgFouine как раз одна из таких незаменимых программ. Это очень продвинутый аналог mysqlsla на php. В комплекте с Playr (реплэйер продакшн логов) она позволяет проводить оптимизацию запросов на staging серверах в практически «боевых» условиях.
Документация по PostgreSQL 8.4.2
Тюнинг postgresql
Сервер на стероидах: FreeBSD, nginx, MySQL, PostgreSQL, PHP и многое другое
Инструкция по оптимизации PostgreSQL 8.x
guc_tutorial
Проекты-спутники PostgreSQL
Горизонтальное масштабирование PostgreSQL с помощью PL/Proxy.
postgresql-performance.pdf
Справочное пособие postgresql.pdf
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 ГБ.
Объем может быть выше, если у вас большие активные порции базы данных, сложные запросы, большое число одновременных соединений, длительные транзакции, вам доступен большой объем оперативной памяти или большее количество процессоров.
И, конечно же, не забываем об остальных приложениях. Выделив слишком много памяти для базы данных, мы можем получить ухудшение производительности.
На всех форумах и в самом файле конфигурации пишут что значение для этого параметра следует устанавливать в два раза больше, чем максимальное количество подключений max_connections*2, причем каждый буфер занимает 8K.
Вот несколько примеров, полученных на личном опыте и при тестировании:
- 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 нужно еще понижать, разумнее в этом случае менять настройки статистики планировщика.
Использование дополнительного ПО.
pgBouncer и другие.
pgBouncer (или его альтернатива) — первое, что должно быть установлено на сервере с базой данных. Я уже насмотрелся на cacti-графики показывающие десятикратное падение нагрузки от простой установки менеджера соединений. Если пулер соединений у вас не установлен, то на каждое соединение с базой у вас запускается отдельный процесс, далее этот процесс отъедает, как минимум, work_mem оперативки и начинает бороться за CPU и жесткий диск с себе подобными выполняя SQL запрос. Всё бы хорошо, но вот когда количество таких процессов зашкаливает за 200-500 серверу, даже очень мощному, становится туго. Очень туго. pgBouncer нас от этого спасает. Также список полезных или даже незаменимых приложений для работы с PostgreSQL можно найти на сайте postgresqlrussia.org.
pgFouine
pgFouine как раз одна из таких незаменимых программ. Это очень продвинутый аналог mysqlsla на php. В комплекте с Playr (реплэйер продакшн логов) она позволяет проводить оптимизацию запросов на staging серверах в практически «боевых» условиях.
Документация по PostgreSQL 8.4.2
Тюнинг postgresql
Сервер на стероидах: FreeBSD, nginx, MySQL, PostgreSQL, PHP и многое другое
Инструкция по оптимизации PostgreSQL 8.x
guc_tutorial
Проекты-спутники PostgreSQL
Горизонтальное масштабирование PostgreSQL с помощью PL/Proxy.
postgresql-performance.pdf
Справочное пособие postgresql.pdf
Комментариев нет:
Отправить комментарий