На данный момент производительность PostgreSQL в связке с сервером 1С:Предприятия в сравнении с тем же MS SQL оставляет желать лучшего. Эта статья продолжение попыток добиться достойной производительности на PostgreSQL. Хотя на данный момент у меня не получилось добиться производительности сопоставимой MS SQL, но думаю в недалеком будущем эта проблема будет решена.

Далее в статье перечислены основные параметы и особенности, на которые следует обратить внимание при оптимизации 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 можно установить в ½ - 2/3 от объёма имеющейся в наличии оперативной памяти, если вся она отдана в распоряжение PostgreSQL.

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

fsync

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

Отрицательное влияние включенного 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.

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

Я решил провести вышеуказанный тест на своем рабочем компьютере, имеющем следующие характеристики:

• CPU: Intel Core i3-3220 @ 3.30GHz x 2
• RAM: 4GB
• HDD: Seagate ST3320418AS 320GB

Тест на Windows:

• ОС: Windows 7 Максимальная x64
• ФС: NTFS
• СУБД: PostgreSQL 9.4.2-1.1C x64

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

C:\PROGRA~1\POSTGR~1\9.4.2-1.1C\bin>pg_test_fsync
5 seconds 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                     48817.440 ops/sec      20 usecs/op
        fdatasync                                       n/a
        fsync                                79.688 ops/sec   12549 usecs/op
        fsync_writethrough                   80.072 ops/sec   12489 usecs/op
        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                     24713.634 ops/sec      40 usecs/op
        fdatasync                                       n/a
        fsync                                78.690 ops/sec   12708 usecs/op
        fsync_writethrough                   79.073 ops/sec   12646 usecs/op
        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.)
         1 * 16kB open_sync write                       n/a
         2 *  8kB open_sync writes                      n/a
         4 *  4kB open_sync writes                      n/a
         8 *  2kB open_sync writes                      n/a
        16 *  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                  76.493 ops/sec   13073 usecs/op
        write, close, fsync                  77.676 ops/sec   12874 usecs/op

Non-Sync'ed 8kB writes:
        write                              1800.319 ops/sec     555 usecs/op

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

Тест на Linux:

• ОС: Debian 8.6 Jessie
• Ядро: x86_64 Linux 3.16.0-4-amd64
• ФС: ext4
• СУБД: PostgreSQL 9.4.2-1.1C amd64

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

/usr/lib/postgresql/9.4/bin# ./pg_test_fsync
5 seconds 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                        80.215 ops/sec   12467 usecs/op
        fdatasync                            80.349 ops/sec   12446 usecs/op
        fsync                                39.384 ops/sec   25391 usecs/op
        fsync_writethrough                              n/a
        open_sync                            40.013 ops/sec   24992 usecs/op

Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync
is Linux's default)
        open_datasync                        40.033 ops/sec   24980 usecs/op
        fdatasync                            77.264 ops/sec   12943 usecs/op
        fsync                                36.325 ops/sec   27529 usecs/op
        fsync_writethrough                              n/a
        open_sync                            19.659 ops/sec   50866 usecs/op

Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB
in different write open_sync sizes.)
         1 * 16kB open_sync write            38.697 ops/sec   25842 usecs/op
         2 *  8kB open_sync writes           17.356 ops/sec   57616 usecs/op
         4 *  4kB open_sync writes            8.996 ops/sec  111156 usecs/op
         8 *  2kB open_sync writes            4.552 ops/sec  219686 usecs/op
        16 *  1kB open_sync writes            2.218 ops/sec  450930 usecs/op

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                  34.341 ops/sec   29120 usecs/op
        write, close, fsync                  35.753 ops/sec   27970 usecs/op

Non-Sync'ed 8kB writes:
        write                            484193.516 ops/sec       2 usecs/op

По результатам теста мы видим, что наилучшую скорость выдают методы fdatasync и open_datasync. Так же можно заметить, что на же оборудовании Linux выдал скорость записи почти в половину больше, чем на Windows.

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

wal_buffers

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

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.
   Где X.X.X - версия используемой PostgreSQL.

Особенности и ограничения в 1С:Предприятие при работе с PostgreSQL.

Использование конструкции ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ.

В СУБД PostgreSQL реализована только частичная поддержка FULL OUTER JOIN (ERROR: “FULL JOIN is only supported with mergejoinable join conditions”). Для реализации полной поддержки FULL OUTER JOIN при работе 1С:Предприятия 8 с PostgreSQL подобный запрос трансформируется в другую форму с эквивалентным результатом, однако эффективность использования конструкции ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ снижается.

В связи с этим не рекомендуется использовать ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ при работе с PostgreSQL. В большинстве случаев без использования этой конструкции можно обойтись, переписав исходный запрос.

Оптимизация использования виртуальной таблицы СрезПоследних при работе с PostgreSQL.

Проблема: При работе с PostgreSQL использование соединения с виртуальной таблицей СрезПоследних может приводить к существенному снижению производительности. Из-за ошибки оптимизатора может быть выбран неоптимальный план выполнения запроса.

Решение: Если в запросе используется соединение с виртуальной таблицей языка запросов 1С:Предприятия СрезПоследних и запрос работает с неудовлетворительной производительностью, то рекомендуется вынести обращение к виртуальной таблице в отдельный запрос с сохранением результатов во временной таблице.

Решение проблемы с зависанием PostgreSQL.

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

Варианты решения проблемы:

• Увеличить количество записей, просматриваемых при сборе статистики по таблицам. Большие значения могут повысить время выполнения команды ANALYZE, но улучшат построение плана запроса:
  • Файл postgresql.conf - default_statistics_target = 1000 -10000.
• Отключение оптимизатору возможности использования NESTED LOOP при выборе плана выполнения запроса в конфигурации PostgreSQL:
  • Файл postgresql.conf - enable_nestloop = off.
  • Отрицательным эффектом этого способа является возможное замедление некоторых запросов, поскольку при их выполении будут использоваться другие, более затратные, методы соединения (HASH JOIN).
• Отключение оптимизатору возможности изменения порядка соединений таблиц в запросе:
  • Файл postgresql.conf - join_collapse_limit=1.
  • Следует использовать этот метод, если вы уверены в правильности порядка соединений таблиц в проблемном запросе.
• Изменение параметров настройки оптимизатора:
  • Файл postgresql.conf:
    • seq_page_cost = 0.1
    • random_page_cost = 0.4
    • cpu_operator_cost = 0.00025
• Использование версии PostgreSQL 9.1.2-1.1.C и выше, в которой реализован независимый от AUTOVACUUM сбор статистики, на основе информации об изменении данных в таблице. По умолчанию включен сбор статистики только для временных таблиц и во многих ситуациях этого достаточно. При возникновении проблем с производительностью выполнения регламентных операций, можно включить сбор статистики для всех или отдельных проблемных таблиц изменив значение параметра конфигурации PostgreSQL (файл postgresql.conf) online_analyze.table_type = "temporary" на online_analyze.table_type = "all".

После изменения этих параметров, следует оценить возможное влияние этих изменений на работу системы и выбрать наиболее приемлимый вариант для ваших задач.

Послесловие.

Ко всему вышеперечисленному можно так же добавить:

• Необходимость использования управляемых блокировок при разработке прикладного решения. Если же у вас типовая конфигурация, то по возможности ее так же необходимо переводить на управляемые блокировки.
• Рекомендацию реализовать для PostgreSQL кэширование на SSD-накопитель. Сделать это можно с помощью Flashcache или Bcache. Подробнее вопрос организации системы кэширования я рассмотрю в другой статье.
• Довольно удобный веб-сервис для начальной настройки PostgreSQL. Его порекомендовал мне товарищ Vasiliy P. Melnik. Несмотря на то, что интерфейс на английском, он простой и интуитивно понятный. Думаю каждый желающий сможет с ним разобраться.

Статья обновлена 8 октября 2016 года. Добавлены сравнительные тесты