Общие положения

В документе описывается настройка PostgreSQL версий 9.2-9.4 на максимальную производительность для платформы 1С. Предполагается, что сервер, используемый для PostgreSQL является достаточно производительным и имеет приблизительно:

• 4 - 512  Gb RAM
• 2 - 256 CPU cores
• RAID 0-1 или SSD

Данный документ подразумевает хотя бы поверхностное знакомство с архитектурой PgSQL. Приведенные в документе параметры являются приблизительными и стартовыми для тонкой настройки.

Настройки сервера для PostgreSQL

• Рекомендуется отключать HyperThreading. Для программ типа систем управления базами данных от него скорее вред чем польза.
• Также рекомендуется отключать Energy Saving, поскольку в противном случае могут непредсказуемо вырастать задержки ответов БД.
• Надо запретить своппинг разделяемой памяти SYSV/posix (FreeBSD: kern.ipc.shm_use_phys=1)

Обозначения

• RAM - объем оперативной памяти сервера. Если сервер используется не только для PostgreSQL, то надо уменьшить эту величину на объем занятой памяти.
• NCores - суммарное число ядер на всех CPU сервера
• max_connections - максимальное число коннектов (или сессий) к PgSQL. Задается в конфигурационном файле.
• WAL - Write Ahead Log, опережающий лог действий с таблицами и индексами. Основная задача - целостность и отказоустойчивость  базы данных при одновременном росте производительности.
• checkpoint - точка восстановления база данных. Все WAL данные, записанные до checkpoint становятся не нужны.
• X..Y - диапазон значений от X до Y включительно

Параметры производительности

shared_buffers = RAM/4

Количество памяти, выделенной PgSQL для совместного кеша страниц. Эта память разделяется между всеми процессами PgSQL.

temp_buffers = 256MB

Максимальное количество страниц для временных таблиц. Т.е. это верхний лимит размера временных таблиц в каждой сессии.

work_mem = RAM/32..64 или 32MB..128MB

Лимит памяти для обработки одного запроса. Эта память индивидуальна для каждой сессии. Теоретически, максимально потребная память равна max_connections * work_mem, на практике такого не встречается потому что большая  часть сессий почти всегда висит в ожидании. Это рекомендательное значение используется оптимайзером: он пытается предугадать размер необходимой памяти для запроса, и, если это значение больше work_mem, то указывает экзекьютору сразу создать временную таблицу. work_mem не является в полном смысле лимитом: оптимайзер может и промахнуться, и запрос займёт больше памяти, возможно в разы. Это значение можно уменьшать, следя за количеством создаваемых временных файлов:

select maintenance_work_mem = RAM/16..32 или work_mem * 4 или 256MB..4GB

Лимит памяти для обслуживающих задач, например вакуум, автовакуум или создания индексов.

effective_cache_size = RAM - shared_buffers

Оценка размера кеша файловой системы. Увеличение параметра увеличивает склонность системы выбирать IndexScan планы. И это хорошо.

effective_io_concurrency = 2

Оценочное значение одновременных запросов к дисковой системе, которые она может обслужить единовременно. Для одиночного диска = 1, для RAID - 2 или больше.

random_page_cost = 1.5-2.0 для RAID, 1.1-1.3 для SSD

Стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше seek time дисковой системы тем меньше (но > 1.0) должен быть этот параметр. Излишне большое значение параметра увеличивает склонность PgSQL к выбору планов с сканированием всей таблицы (PgSQL считает, что дешевле последовательно читать всю таблицу, чем рандомно индекс). И это плохо.

autovacuum = on

Включение автовакуума. Не выключайте его!

autovacuum_max_workers = NCores/4..2 но не меньше 4

Количество процессов автовакуума. Общее правило - чем больше write-запросов, тем больше процессов. На read-only базе данных достаточно одного процесса.

autovacuum_naptime = 20s

Время сна процесса автовакуума. Слишком большая величина будет приводить к тому, что таблицы не будут успевать вакуумиться и, как следствие, вырастет bloat и размер таблиц и индексов. Малая величина приведет к бесполезному нагреванию.

bgwriter_delay = 20ms

Время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на  checkpoint процесс и процессы, обслуживающие сессии (backend). Малое значение приведет к полной загрузке одного из ядер.

bgwriter_lru_multiplier = 4.0
bgwriter_lru_maxpages = 400

Параметры, управляющие интенсивностью записи фонового процесса записи. За один цикл bgwriter записывает не больше, чем было записано в прошлый цикл, умноженное на bgwriter_lru_multiplier, но не больше чемbgwriter_lru_maxpages.

synchronous_commit = off

Выключение синхронизации с диском в момент коммита. Создает риск потери последних нескольких транзакций (в течении 0.5-1 секунды), но гарантирует целостность базы данных, в цепочке коммитов гарантированно отсутствуют пропуски. Но значительно увеличивает производительность.

checkpoint_segments = 32..256   < 9.5

Максимальное количество сегментов WAL между checkpoint. Слишком частые checkpoint  приводят к значительной нагрузке на дисковую подсистему по записи. Каждый сегмент имеет размер 16MB

checkpoint_completion_target = 0.5..0.9

Степень "размазывания" checkpoint'a. Скорость записи во время checkpoint'а регулируется так, что бы время checkpoint'а было равно времени, прошедшему с прошлого, умноженному на checkpoint_completion_target.

min_wal_size = 512MB .. 4G         > =9.5
 max_wal_size = 2 * min_wal_size    > =9.5

Минимальное и максимальный объем WAL файлов. Аналогично checkpoint_segments

ssl = off

Выключение шифрования. Для защищенных ЦОД'ов шифрование бессмысленно, но приводит к увеличению загрузки CPU

fsync = on

Выключение параметра приводит к росту производительности, но появляется значительный риск потери всех данных при внезапном выключении питания. Внимание: если RAID имеет кеш и находиться в режиме write-back, проверьте наличие и функциональность батарейки кеша RAID контроллера! Иначе данные записанные в кеш RAID могут быть потеряны при выключении питания, и, как следствие, PgSQL не гарантирует целостность данных.

commit_delay = 1000
commit_siblings = 5

Групповой коммит нескольких транзакций. Имеет смысл включать, если темп транзакций превосходит 1000 TPS. Иначе эффекта не имеет.

temp_tablespaces = 'NAME_OF_TABLESPACE'

Дисковое пространство для временных таблиц/индексов. Помещение временных таблиц/индексов на отдельные диски может увеличить производительность. Предварительно надо создать tablespace командой CREATE TABLESPACE. Если характеристики дисков отличаются от основных дисков, то следует в команде указать соответствующий random_page_cost. См. статью.

row_security = off               >= 9.5

Отключение контроля разрешения уровня записи

max_files_per_process = 1000 (default)

Максимальное количество открытых файлов на один процесс PostreSQL. Один файл это как минимум либо индекс либо таблица, но таблица/может состоять из нескольких файлов. Если PostgreSQL упирается в этот лимит, он начинает открывать/закрывать файлы, что может сказываться на производительности. Диагностировать проблему под Linux можно с помощью команды lsof.

Параметры для платформы 1С:Предприятия

standard_conforming_strings = off

Разрешить использовать символ \ для экранирования

escape_string_warning = off

Не выдавать предупреждение о использовании символа \ для экранирования

max_locks_per_transaction = 256

Максимальное число блокировок индексов/таблиц в одной транзакции

max_connections = 500..1000

Количество одновременных коннектов/сессий

Параметры для PgBadger

Приводится согласно документации

При большой нагрузке может влиять на производительность по причине большого потока записи на диск. Лучше вынести на отдельный шпиндель.

log_min_duration_statement = 0
 log_line_prefix = '%t [%p]: [%l-1] ' или '%t [%p]: [%l-1] user=%u,db=%d,client=%h '
 log_checkpoints = on
 log_connections = on
 log_disconnections = on
 log_lock_waits = on
 log_temp_files = 0
 log_autovacuum_min_duration = 0
 lc_messages='C'
 log_duration = on
 log_statement = all
 log_destination = stderr

Примечание. Здесь пока никак не рассматриваются вопросы ротации логов и использования самого PgBadger'a.

Параметры дополнительных модулей

plantuner

Исходники git clone

Документация

plantuner.fix_empty_table = 'on'

Исправляет чрезмерную пессимистичность оптимизатора посгтреса на пустых, недавно созданных таблицах

online_analyze

Исходники git clone

Документация

online_analyze.table_type = 'temporary'

Автоматически анализировать временные таблицы при их изменении. Фоновый analyze может заметно отставать, и, как результат, планер ошибается.

online_analyze.verbose = 'off'

Отключение излишней болтливости автоматического analyze

 

Проблема

Обновлено: 20.03.2008

При использовании PostgreSQL в качестве СУБД при работе 1С:Предприятия 8, возможно появление сообщения об ошибке:

• Ошибка СУБД: could not send data to server: No buffer space available (0x00002747/10055)

Причина

При использовании операционной системы Windows, максимальное стандартное число временных TCP-портов равно 5000. При попытке установить TCP-соединение через порты, номера которых превышают 5000, выдается сообщение об ошибке.

Решение

Для решения этой проблемы необходимо увеличить максимальное число временных портов. Для этого необходимо выполнить следующие действия:

1. Открыть редактор реестра.
2. Найти следующий раздел реестра и выберите Parameters (HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters)
3. Добавьте следующий параметр реестра:
   • Имя: MaxUserPort
   • Тип: DWORD
   • Значение: 65534(Допустимые значения: 5000-65534)
     Данный параметр определяет максимальный номер порта, который используется, когда программа запрашивает у системы доступный пользовательский порт. Как правило, временные порты имеют номер в диапазоне от 1024 до 5000 включительно.
4. Закрыть редактор реестра.

 

Оптимизация производительности PostgreSQL

Ниже перечислены основные параметры, на  которые следует обратить внимание при оптимизации производительности PostgreSQL.

shared_buffers

Объём совместно используемой памяти, выделяемой PostgreSQL для кэширования данных, определяется числом страниц (shared_buffers) по 8 килобайт каждая. Следует учитывать, что операционная система сама кеширует данные, поэтому нет необходимости отводить под кэш всю наличную оперативную память. Размер shared_buffers зависит от многих факторов, для начала можно принять следующие значения:

• 8–16 Мб  – Обычный настольный компьютер с 512 Мб и небольшой базой данных
• 80–160 Мб – Небольшой > сервер, предназначенный для обслуживания базы данных с объёмом оперативной памяти 1 Гб и базой данных около 10 Гб.
• 400 Мб – Сервер с несколькими процессорами, с объёмом памяти в 8 Гб и базой данных занимающей свыше 100 Гб обслуживающий несколько сотен активных соединений одновременно .

work_mem

Под каждый запрос выделяется ограниченный объём памяти для работы. Этот объём используется для сортировки, объединения и других подобных операций. При превышении этого объёма сервер начинает использовать временные файлы на диске, что может существенно снизить производительность. Оценить необходимое значение для work_mem можно разделив объём доступной памяти (физическая память минус объём занятый под другие программы и под совместно используемые страницы shared_buffers) на максимальное число одновременно используемых активных соединений.

maintenance_work_mem

Эта память используется для выполнения операций по сбору статистики (ANALYZE), сборке мусора (VACUUM), создания индексов (CREATE INDEX) и добавления внешних ключей. Размер выделяемой под эти операции памяти должен быть сравним с физическим размером самого большого индекса на диске.

effective_cache_size

PostgreSQL в своих планах опирается на кэширование файлов, осуществляемое операционной системой. Этот параметр соответствует максимальному размеру объекта, который может поместиться в системный кэш. Это значение используется только для оценки. effective_cache_size можно установить в 1/2 - 2/3 от объёма имеющейся в наличии оперативной памяти, если вся она отдана в распоряжение PostgreSQL.

Следующие параметры могут существенно увеличить производительность работы PostgreSQL. Однако их рекомендуется использовать только если имеются надежные ИБП и программное обеспечение, завершающее работу системы при низком заряде батарей.

fsync

Данный параметр отвечает за сброс данных из кэша на диск при завершении транзакций. Если установить его значение fsync=off то данные не будут записываться на дисковые накопители сразу после завершения операций. Это может существенно повысить скорость операций insert и update, но есть риск повредить базу, если произойдет сбой (неожиданное отключение питания, сбой ОС, сбой дисковой подсистемы).

synchronous_commit

Включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера.

Если вашей системе не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы, но необходимо обеспечить в несколько раз большую производительность по количеству транзакций в секунду. В этом случае можно установить этот параметр в off (отключение синхронной записи).

 

Настройка Write Ahead Log для PostgreSQL

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

fsync

По умолчанию, параметр fsync включен. Это означает, что при выполнении операции COMMIT данные сразу переписываются из кеша операционной системы на диск, тем самым гарантируется консистентность при возможном аппаратном сбое. Обратной стороной этого является снижение производительности операций записи на диск, поскольку при этом не используются возможности отложенной записи данных операционной системы.
Отрицательное влияние включенного fsync можно уменьшить, отключив его, положившись на надежность вашего оборудования, или правильно подобрав параметр wal_sync_method - метод, который используется для принудительной записи данных на диск.

Возможные значения:

• open_datasync – запись данных методом open() с параметром O_DSYNC,
• fdatasync – вызов метода fdatasync() после каждого commit,
• fsync_writethrough – вызывать fsync() после каждого commit игнорирую паралельные процессы,
• fsync – вызов fsync() после каждого commit,
• open_sync – запись данных методом open() с параметром O_SYNC.

Не все методы доступны на определенных платформах. Выбор метода зависит от операционной системы под управлением, которой работает PostgreSQL.

В состав PostgreSQL входит утилита pg_test_fsync, с помощью которой можно определить оптимальное значение параметра wal_sync_method.

Она выполняет серию дисковых тестов с использованием различных методов синхронизации. В результате этого теста получаются оценки производительности дисковой системы, по которым можно определить оптимальный метод синхронизации для данной опереционной системы

Пример результатов теста для Windows

Compare file sync methods using one 8kB write:

(in wal_sync_method preference order, except  fdatasync is Linux's default)

        open_datasync                   133333.333 ops/sec

        fdatasync                                     n/a

        fsync                              24.124 ops/sec

        fsync_writethrough                 25.109 ops/sec

        open_sync                                     n/a

 

Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except  fdatasync is Linux's default)

        open_datasync                   64516.129 ops/sec

        fdatasync                                     n/a

        fsync                              25.266 ops/sec

        fsync_writethrough                 26.440 ops/sec

        open_sync                                     n/a

 

Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB in different write open_sync sizes.)

        16kB open_sync write                          n/a

         8kB open_sync writes                         n/a

         4kB open_sync writes                         n/a

         2kB open_sync writes                         n/a

         1kB open_sync writes                         n/a

 Test if fsync on non-write file descriptor is honored:

(If the times are similar, fsync() can sync data written on a different descriptor.)

        write, fsync, close                26.643 ops/sec

        write, close, fsync                26.484 ops/sec

 

Non-Sync'ed 8kB writes:

        write                             305.623 ops/sec

Из результатов теста можно определить, что для Windows оптимальным решением будет использование open_datasync.

Пример результатов теста для Linux

2000 operations per test

O_DIRECT supported on this platform for open_datasync and open_sync.

 Compare file sync methods using one 8kB write:

(in wal_sync_method preference order, except fdatasync is Linux's default)

        open_datasync                    5617.741 ops/sec

        fdatasync                        5266.734 ops/sec

        fsync                            5301.412 ops/sec

        fsync_writethrough                            n/a

        open_sync                        5983.080 ops/sec

 Compare file sync methods using two 8kB writes:

(in wal_sync_method preference order, except fdatasync is Linux's default)

        open_datasync                    2632.597 ops/sec

        fdatasync                        3674.546 ops/sec

        fsync                            3767.003 ops/sec

        fsync_writethrough                            n/a

        open_sync                        2640.051 ops/sec

 Compare open_sync with different write sizes:

(This is designed to compare the cost of writing 16kB in different write open_sync sizes.)

        16kB open_sync write             4400.682 ops/sec

         8kB open_sync writes            2349.649 ops/sec

         4kB open_sync writes            1466.995 ops/sec

         2kB open_sync writes             730.509 ops/sec

         1kB open_sync writes             374.540 ops/sec

 Test if fsync on non-write file descriptor is honored:

(If the times are similar, fsync() can sync data written on a different descriptor.)

        write, fsync, close              5048.108 ops/sec

        write, close, fsync              5198.964 ops/sec

 Non-Sync'ed 8kB writes:

        write                           115154.307 ops/sec

Из результатов теста можно определить, что для Linux все варианты синхронизации примерно равноценны и проигрывают варианту с отключенной синхронизацией.

Следует учитывать, что в данном тесте использовалась дисковая система, состоящая из одного диска. При использовании RAID массива с большим количеством дисков картина может быть другой.

wal_buffers

Количество памяти используемое в SHARED MEMORY для ведения транзакционных логов. При доступной памяти 1-4GB рекомендуется устанавливать 256-1024kb. Этот параметр стоит увеличивать в системах с большим количеством модификаций таблиц базы данных.

checkpoint_segments

Oпределяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками.  Для баз данных со множеством модифицирующих данные транзакций рекомендуется увеличение этого параметра. Критерием достаточности количества сегментов является отсутствие в логе  предупреждений (warning) о том, что контрольные точки происходят слишком часто.

full_page_writes

Включение этого параметра гарантирует корректное восстановление, ценой увелечения записываемых данных в журнал транзакций. Отключение этого параметра ускоряет работу, но может привести  к повреждению базы данных в случае системного сбоя или отключения питания.

synchronous_commit

Включает/выключает синхронную запись в лог файлы после каждой транзакции. Это защищает от возможной потери данных. Но это накладывает ограничение на пропускную способность сервера. Если не критична потенциально низкая возможность потери небольшого количества изменений при крахе системы, но важно обеспечить большую производительность по количеству транзакций в секунду. В этом случае устанавливайте этот параметр в off (отключение синхронной записи).

Еще одним способом увеличения производительности работы PostgreSQL является отделение перенос журнала транзакций(pg_xlog) на другой диск. Выделение для журнала транзакций  отдельного дискового ресурса позволяет получить получить при этом существенный выигрыш в производительности  10%-12% для нагруженных OLTP систем.

Для Linux это делается с помощью создания символической ссылки на новое положение каталога с журналом транзакций.

Для Windows можно использовать для этих целей утилиту junction.

 

Последовательность действий:

Для этого надо:

1. Остановить postgresql.
2. Сделать бэкап C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog.
3. Скопировать C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog в D:\pg_xlog и удалить C:\Program Files\PostgreSQL\X.X.X\data\pg_xlog.
4. Распаковать программу junction в C:\Program Files\PostgreSQL\X.X.X\data.
5. Открыть окно cmd, перейти в C:\Program Files\PostgreSQL\X.X.X\data и выполнить junction -s pg_xlog D:\pg_xlog.
6. Установить права на папку D:\pg_xlog пользователю postgres.
7. Запустить postgresql.

 

Статья предоставлена 1С Рарус.