Оптимізація PostgreSQL під сервер 1С: Підприємства

1. Основні параметри PostgreSQL.
2. work_mem
3. maintenance_work_mem
4. effective_cache_size
5. fsync
6. wal_buffers
7. checkpoint_segments
8. full_page_writes
9. synchronous_commit
10. Особливості та обмеження в 1С: Підприємство при роботі з PostgreSQL.
11. Оптимізація використання віртуальної таблиці СрезПоследніх при роботі з PostgreSQL.
12. Рішення проблеми з зависанням PostgreSQL.
13. Післямова.

На даний момент продуктивність 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 Ultimate 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, clos e 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

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

full_page_writes

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

synchronous_commit

Вмикає / вимикає синхронну запис в лог-файли після кожної транзакції. Включення синхронного запису захищає від можливої ​​втрати даних. Але, накладає обмеження на пропускну здатність сервера. Ви можете відключити синхронну запис, якщо вам необхідно забезпечити більш високу продуктивність за кількістю транзакцій. А потенційно низька можливість втрати невеликої кількості змін при краху системи не критична. Для відключення синхронного запису встановіть значення off в цьому параметрі.

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

У Linux це робиться за допомогою створення символьного посилання на нове положення каталогу з журналом транзакцій.

У Windows можна використовувати для цих цілей утиліту Junction . Для цього треба:

1. Зупинити PostgreSQL.
2. Зробити бекап C: \ Program Files \ PostgreSQL \ XXX \ data \ pg_xlog.
3. Скопіювати C: \ Program Files \ PostgreSQL \ XXX \ data \ pg_xlog в D: \ pg_xlog і видалити C: \ Program Files \ PostgreSQL \ XXX \ data \ pg_xlog.
4. Розпакувати програму Junction в C: \ Program Files \ PostgreSQL \ XXX \ data.
5. Відкрити вікно CMD, перейти в C: \ Program Files \ PostgreSQL \ XXX \ data і виконати junction -s pg_xlog D: \ pg_xlog.
6. Встановити права на папку D: \ pg_xlog користувачеві postgres.
7. Запустити PostgreSQL.
   Де XXX - версія використовуваної 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 року. Додані порівняльні тести