Аптымізацыя 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 Максімальная 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 года. Дададзеныя параўнальныя тэсты