Прапаную азнаёміцца з расшыфроўкай даклада пачатку 2016 года Уладзіміра Сітнікава "PostgreSQL і JDBC выціскаем усе сокі"
Добры дзень! Мяне клічуць Уладзімір Сітнікаў. Я працую 10 гадоў у кампаніі NetCracker. І ў асноўным я займаюся прадукцыйнасцю. Усё, што звязана з Java, усё, што звязана з SQL - гэта тое, што я люблю.
І сёння я раскажу пра тое, з чым мы сутыкнуліся ў кампаніі, калі пачалі выкарыстоўваць PostgreSQL у якасці сервера баз дадзеных. І мы ў асноўным працуем з Java. Але тое, што я сёння раскажу, звязана не толькі з Java. Як практыка паказала, гэта ўзнікае і ў іншых мовах.
Мы будзем казаць:
- пра выбарку дадзеных.
- Пра захаванне даных.
- А таксама пра прадукцыйнасць.
- І пра падводныя граблі, якія там закапаны.
Давайце пачнем з простага пытання. Мы выбіраем адзін радок з табліцы па першасным ключы.
База знаходзіцца на тым жа хасце. І ўся гэтая гаспадарка займае 20 мілісекунд.
Вось гэтыя 20 мілісекунд - гэта вельмі шмат. Калі ў вас такіх 100 запытаў, тыя вы марнуеце час у секунду на тое, каб гэтыя запыты пракруціць, т. е. у пустую марнуем час.
Мы гэта не любім рабіць і глядзім, што нам прапануе база для гэтага. База прапануе нам два варыянты выканання запытаў.
Першы варыянт - гэта просты запыт. Чым ён добры? Тым, што мы яго бярэм і дасылаем, і нічога больш.
У базы ёсць яшчэ і пашыраны запыт, які хітрэйшы, але больш функцыянальны. Можна асобна дасылаць запыт на парсінг, выкананне, звязванне зменных і т. д.
Super extended query - гэта тое, што мы не будзем пакрываць у бягучым дакладзе. Мы, можа быць, нешта хочам ад базы дадзеных і ёсць такі спіс жаданак, які ў нейкім выглядзе сфарміраваны, г. зн. гэта тое, што мы хочам, але немагчыма зараз і ў найбліжэйшы год. Таму проста запісалі і будзем хадзіць, трэсці асноўных людзей.
А тое, што мы можам зрабіць, так - гэта simple query і extended query.
У чым асаблівасць кожнага падыходу?
Просты запыт добра выкарыстоўваць для аднаразовага выканання. Раз выканалі і забыліся. І праблема ў тым, што ён не падтрымлівае бінарны фармат дадзеных, т. е. для нейкіх высокапрадукцыйных сістэм ён не падыходзіць.
Extended query - дазваляе эканоміць час на парсінгу. Гэта тое, што мы зрабілі і пачалі выкарыстоўваць. Нам гэта вельмі-вельмі дапамагло. Там ёсць не толькі эканомія на парсінгу. Ёсць эканомія на перадачы даных. Перадаваць дадзеныя ў бінарным фармаце нашмат больш эфектыўна.
Пяройдзем да практыкі. Вось так выглядае тыповая праграма. Гэта можа быць Java і т. д.
Мы стварылі statement. Выканалі каманду. Стварылі close. Дзе тут памылка? У чым праблема? Няма праблем. Так ва ўсіх кнігах напісана. Так трэба пісаць. Калі вы хочаце максімальную прадукцыйнасць, пішыце так.
Але практыка паказала, што гэта не працуе. Чаму? Таму што ў нас ёсць метад "close". І калі мы так робім, то з пункту гледжання базы дадзеных атрымліваецца - гэта як праца курца з базай дадзеных. Мы сказалі "PARSE EXECUTE DEALLOCATE".
Навошта гэтыя залішнія стварэння і выгрузка statements? Яны нікому не патрэбны. Але звычайна ў PreparedStatement так і атрымліваецца, калі мы іх закрываем, яны закрываюць усё на базе дадзеных. Гэта не тое, што мы жадаем.
Мы хочам, як здаровыя людзі, працаваць з базай. Адзін раз узялі і падрыхтавалі наш statement, потым яго выконваем шмат разоў. Насамрэч шмат разоў - гэта адзін раз за ўсё жыццё прыкладання запарсілі. І на розных REST выкарыстоўваем адзін і той жа statement id. Вось гэта наша мэта.
Як нам гэтага дасягнуць?
Вельмі проста - не трэба зачыняць statements. Пішам вось так: "prepare" "execute".
Калі мы такое запусцім, то зразумела, што ў нас недзе нешта перапоўніцца. Калі не зразумела, то можна памераць. Возьмем і напішам бенчмарк, у якім такі просты метад. Ствараем statement. Запускаем на нейкай версіі драйвера і атрымліваем, што ён даволі хутка валіцца са стратай усёй памяці, якая ў на збыла.
Зразумела, такія памылкі лёгка выпраўляюцца. Я не буду пра іх казаць. Але я скажу, што ў новай версіі працуе значна хутчэй. Метад бесталковы, але тым не менш.
Як працаваць правільна? Што нам для гэтага трэба рабіць?
У рэальнасці прыкладанні заўсёды зачыняюць statements. Ва ўсіх кніжках пішуць, каб закрывалі, інакш памяць уцячэ.
І PostgreSQL не ўмее кэшаваць запыты. Трэба, каб кожная сэсія сама для сябе стварала гэты кэш.
І час марнаваць на парсінг мы таксама не жадаем.
І як звычайна ў нас ёсць два варыянты.
Першы варыянт - мы бярэм і кажам, што давайце ўсё загорнем у PgSQL. Тамака ёсць кэш. Ён усё кэшуе. Атрымаецца выдатна. Мы такое паглядзелі. У нас 100500 запытаў. Не працуе. Мы не згодныя - ручкамі ў працэдуры ператвараць запыты. Не-не.
У нас ёсць другі варыянт - узяць і самім запілаваць. Адкрываем зыходнікі, пачынаем пілаваць. Пілім-пілім. Аказалася, што не так гэта цяжка зрабіць.
Зьявілася гэта ў жніўні 2015 года. Цяпер ужо больш сучасная версія. І ўсё выдатна. Працуе настолькі добра, што мы нічога не мяняем у дадатку. І мы нават перасталі думаць у бок PgSQL, т. е. нам гэтага суцэль хапіла, каб усе накладныя выдаткі зменшыць практычна да нуля.
Адпаведна Server-prepared statements актывуецца на 5-ым выкананні для таго, каб не марнаваць памяць у базе дадзеных на кожны аднаразовы запыт.
Можна спытаць - дзе лічбы? Што вы атрымліваеце? І тут я лічбы не дам, бо ў кожнага запыту яны свае.
У нас запыты былі такія, што мы на OLTP-запытах марнавалі недзе 20 мілісекунд на парсінг. Там было 0,5 мілісекунды на выкананне, 20 мілісекунд на парсінг. Запыт - 10 КіБ тэксту, 170 радкоў плана. Гэта OLTP запыт. Ён запытвае 1, 5, 10 радкоў, часам больш.
Але нам зусім не хацелася марнаваць 20 мілісекунд. Мы ў 0 звялі. Усё выдатна.
Што вы адсюль можаце вынесці? Калі ў вас Java, тыя вы бераце сучасную версію драйвера і цешыцеся.
Калі ў вас нейкая іншая мова, то вы падумайце - можа быць вам гэта таксама трэба? Таму што з пункту гледжання канчатковай мовы, напрыклад, калі PL 8 ці ў вас LibPQ, то вам не відавочна, што вы марнуеце час не на выкананне, на парсінг і гэта варта праверыць. Як? Усё бясплатна.
За выключэннем таго, што ёсць памылкі, нейкія асаблівасці. І пра іх якраз зараз будзем казаць. Большая частка будзе аб прамысловай археалогіі, аб тым, што мы знайшлі, на што натыкнуліся.
Калі запыт генеруецца дынамічна. Такое бывае. Хтосьці радкі склейвае, атрымліваецца SQL-запыт.
Чым ён дрэнны? Ён дрэнны тым, што кожны раз у нас у выніку атрымліваецца розны радок.
І ў гэтага рознага радка трэба нанова лічыць hashCode. Гэта сапраўды CPU задача - знайсці доўгі тэкст запыту ў нават наяўнай hash'е не так проста. Таму выснова простай - не генеруйце запыты. Захоўвайце іх у нейкай адной зменнай. І радуйцеся.
Наступная праблема. Тыпы дадзеных важныя. Бываюць ORM, якія кажуць, што не важна які NULL, няхай будзе які-небудзь. Калі Int, то мы гаворым setInt. А калі NULL, то няхай VARCHAR будзе заўсёды. І якая розніца ў рэшце рэшт які там NULL? База даных сама ўсё зразумее. І такая карцінка не працуе.
На практыцы базе дадзеных зусім не ўсё роўна. Калі вы першы раз сказалі, што гэта ў вас лік, а другі раз сказалі, што гэта VARCHAR, то немагчыма перавыкарыстоўваць Server-prepared statements. І ў такім выпадку даводзіцца нанова пераствараць наш statement.
Калі вы выконваеце адзін і той жа запыт, то сачыце за тым, каб тыпы дадзеных у калонцы ў вас не блыталіся. Трэба сачыць за NULL. Гэта частая памылка, якая ў нас была пасля таго, як мы пачалі выкарыстоўваць PreparedStatements
Добра, уключылі. Узялі, можа, драйвер. І прадукцыйнасць упала. Усё стала дрэнна.
Як такое бывае? Баг гэта ці фіча? Нажаль, не атрымалася зразумець - баг гэта ці фіча. Але ёсць суцэль просты сцэнар прайгравання гэтай праблемы. Яна зусім нечакана падпільнавала нас. І заключаецца ў выбарцы літаральна з адной табліцы. У нас, канечне, такіх запытаў было больш. Яны, як правіла, дзве-тры табліцы ўключалі, але ёсць вось такі сцэнар прайгравання. Бераце на вашай базе любой версіі і прайграваеце.
Сэнс у тым, што ў нас дзве калонкі, кожная з якіх праіндэксавана. У адной калонцы па значэнні NULL ляжыць мільён радкоў. А ў другой калонцы ляжыць усяго 20 радкоў. Калі мы выконваем без сувязных зменных, тое ўсё добра працуе.
Калі мы пачнем выконваць са звязанымі зменнымі, г. зн. мы выконваем знак «?» ці "$1" для нашага запыту, тое што мы ў выніку атрымліваем?
Першае выкананне - як належыць. Другое - ледзь барзджэй. Нешта пракэшавалася. Трэцяе-чацвёртае-пятае. Потым хлоп - і неяк вось так. І самае дрэннае, што гэта адбываецца на шостым выкананні. Хто ведаў, што трэба рабіць менавіта шэсць выкананняў для таго, каб зразумець, які там рэальна план выканання?
Хто вінаваты? Што адбылося? База даных змяшчае аптымізацыю. І яна як бы аптымізавана пад generic выпадак. І, адпаведна, пачынаючы з нейкага разу яна пераходзіць на generic план, які, на жаль, можа аказацца, іншым. Ён можа аказацца такім жа, а можа і іншым. І там ёсць нейкае парогавае значэнне, якое прыводзіць да такіх паводзін.
Што з гэтым можна рабіць? Тут, вядома, больш складана нешта меркаваць. Ёсць простае рашэнне, якое мы выкарыстоўваем. Гэта +0, OFFSET 0. Напэўна, вы такія рашэнні ведаеце. Проста бяром і ў запыт дадаем «+0» і ўсё добра. Пакажу пазней.
І ёсць яшчэ варыянт - больш уважлівыя планы глядзець. Распрацоўнік павінен не толькі запыт напісаць, але і 6 разоў сказаць "explain analyze". Калі 5, то не падыдзе.
І ёсць яшчэ трэці варыянт - гэта напісаць у pgsql-hackers ліст. Я напісаў, праўда, пакуль не зразумела - баг гэта ці фіча.
Пакуль мы думаем - баг гэта ці фіча, давайце паправім. Возьмем наш запыт і дадамо "+0". Усё добра. Два знакі і нават не трэба думаць, як там і чаго там. Вельмі проста. Мы проста базе даных забаранілі выкарыстоўваць індэкс па гэтай калонцы. Няма ў нас азначніка па калонцы «+0» і ўсё, база дадзеных не выкарыстоўвае азначнік, усё добра.
Вось гэтае правіла 6-і explain'ов. Цяпер у бягучых версіях трэба рабіць 6 разоў, калі ў вас звязаныя зменныя. Калі ў вас няма звязаных пераменных, то мы вось так робім. І ў нас у рэшце рэшт менавіта гэты запыт падае. Справа не хітрая.
Здавалася б, колькі можна? Тут баг, там баг. Рэальна баг усюды.
Давайце яшчэ паглядзім. Напрыклад, у нас ёсць дзве схемы. Схема А з табліцай Ы і схема Б з табліцай Ы. Запыт - выбраць дадзеныя з табліцы. Што ў нас будзе? У нас памылка будзе. У нас будзе ўсё вышэй пералічанае. Правіла такое - баг усюды, у нас будзе ўсё вышэйпералічанае.
Цяпер пытанне: "Чаму?". Здавалася б, ёсць дакументацыя, што, калі ў нас ёсць схема, то ёсць пераменная "search_path", якая кажа аб тым, дзе трэба шукаць табліцу. Здавалася б, пераменная ёсць.
У чым праблема? Праблема ў тым, што server-prepared statements не падазраюць, што search_path можа нехта мяняць. Вось гэтае значэнне застаецца як бы канстантным для базы дадзеных. І нейкія часткі могуць не падхапіць новыя значэнні.
Вядома, гэта залежыць ад версіі, на якой вы тэстуеце. Залежыць ад таго, наколькі сур'ёзна ў вас табліцы адрозніваюцца. І версія 9.1 проста выканае старыя запыты. Новыя версіі могуць выявіць падвох і сказаць, што ў вас памылка.
Як гэта лячыць? Ёсць просты рэцэпт - не рабіце так. Не трэба мяняць search_path у працы прыкладання. Калі вы мяняеце, то лепш стварыць новае злучэнне.
Можна пагаварыць, г. зн. адкрыць, пагаварыць, дапісаць. Можа быць, і пераканаем распрацоўшчыкаў базы дадзеных, што ў выпадку, калі хтосьці мяняе значэнне, база дадзеных павінна пра гэта кліенту казаць: «Глядзіце, у вас тут значэнне абнавілася. Можа быць, вам трэба statements скінуць, перастварыць?». Цяпер база дадзеных патаемна сябе паводзіць і не паведамляе ніяк аб тым, што дзесьці ўнутры statements памяняліся.
І я зноў падкрэслю - гэта тое, што не тыпова для Java. Мы тое ж самае ўбачым у PL/ pgSQL адзін да аднаго. Але там прайграецца.
Давайце паспрабуем яшчэ абраць дадзеныя. Выбіраемы-выбіраем. У нас ёсць табліца ў мільён радкоў. Кожны радок па кілабайце. Прыкладна гігабайт дадзеных. І ў нас ёсць працоўная памяць у Java-машыне ў 128 мегабайт.
Мы, як рэкамендавана ва ўсіх кнігах, карыстаемся струменевай апрацоўкай. Т. е. мы адчыняем resultSet і чытэльны адтуль дадзеныя патроху. Ці спрацуе гэта? Ці не ўпадзе па памяці? Будзе пакрысе чытаць? Давайце паверым у базу, у Postgres паверым. Не верым. Упадзем OutOFMemory? У каго падаў OutOfMemory? А хто здолеў паправіць пасля гэтага? Хтосьці здолеў паправіць.
Калі ў вас мільён радкоў, то нельга проста так выбіраць. Трэба абавязкова OFFSET/LIMIT. Хто за такі варыянт? І хто за варыянт, што трэба autoCommit'ам гуляць?
Тут, як звычайна, самы нечаканы варыянт аказваецца правільным. І калі вы раптам выключыце autoCommit, тое яно дапаможа. Чаму так? Навуцы аб гэтым невядома.
Але па змаўчанні ўсе кліенты, якія злучаюцца з базай дадзеных Postgres, выбіраюць дадзеныя цалкам. PgJDBC у гэтым плане не выключэнне, выбірае ўсе радкі.
Ёсць варыяцыя на тэму FetchSize, т. е. можна на ўзроўні асобнага statement сказаць, што тут, калі ласка, выбірай дадзеныя па 10, 50. Але гэта не працуе датуль, пакуль вы не выключыце autoCommit. Выключылі autoCommit - пачынае працаваць.
Але хадзіць па кодзе і ўсюды ставіць setFetchSize - гэта няёмка. Таму мы зрабілі такую настройку, якая для ўсяго злучэння будзе казаць значэнне па змаўчанні.
Вось мы гэта сказалі. Настроілі параметр. І што ў нас атрымалася? Калі мы выбіраем па малым, калі, напрыклад, па 10 радкоў выбіраем, то ў нас вельмі вялікія накладныя выдаткі. Таму трэба гэтае значэнне ставіць каля сотні.
У ідэале, вядома, у байтах яшчэ навучыцца абмяжоўваць, але рэцэпт такі: ставім defaultRowFetchSize больш за сто і радуемся.
Давайце пяройдзем да ўстаўкі дадзеных. Устаўка прасцей, там ёсць розныя варыянты. Напрыклад, INSERT, VALUES. Гэта добры варыянт. Можна казаць "INSERT SELECT". На практыцы гэта адно і тое ж. Ніякай адрозненні няма па прадукцыйнасці.
Кнігі кажуць, што трэба выконваць Batch statement, кнігі кажуць, што можна выконваць больш складаныя каманды з некалькімі клямарчыкамі. І ў Postgres ёсць выдатная функцыя можна COPY рабіць, т. е. рабіць гэта хутчэй.
Калі памераць, то можна зноў некалькі цікавых адкрыццяў зрабіць. Як мы жадаем, каб гэта працавала? Жадаем не парсіць і лішніх каманд не выконваць.
На практыку TCP нам так рабіць не дае. Калі кліент заняты адпраўкай запыту, то база даных у спробах адправіць нам адказы, запыты не чытае. У выніку кліент чакае базу даных, пакуль яна прачытае запыт, а база даных чакае кліента, пакуль ён прачытае адказ.
І таму кліент змушаны адпраўляць перыядычна пакет сінхранізацыі. Лішнія сеткавыя ўзаемадзеянні, лішняя страта часу.
І чым больш мы іх дадаем, тым горш становіцца. Драйвер вельмі песімістычны і дадае іх даволі часта, прыкладна раз у 200 радкоў, у залежнасці ад памеру радкоў і т. д.
Бывае, што радок паправіш адну за ўсё і ў 10 разоў усё паскорыцца. Такое бывае. Чаму? Як звычайна, канстанта недзе такая ўжо была выкарыстаная. І значэнне 128 азначала не выкарыстоўваць batching.
Добра, што гэта не патрапіла ў афіцыйную версію. Знайшлі да таго, як пачалі выпускаць рэліз. Усе значэнні, якія я заву, засноўваюцца на сучасных версіях.
Давайце памераем. Мы мераем InsertBatch просты. Мы мераем InsertBatch шматразовы, т. е. тое ж самае, але values шмат. Хітры ход. Не ўсе так умеюць, але гэта такі просты ход значна прасцей, чым COPY.
Можна рабіць COPY.
І можна гэта рабіць на структурах. Абвясціць User default type, перадаваць масіў і INSERT напрамую ў табліцу.
Калі вы адкрыеце спасылку: pgjdbc/ubenchmsrk/InsertBatch.java, то гэты код ёсьць на GitHub. Можна паглядзець канкрэтна, якія запыты там генеруюцца. Не іста важна.
Мы запусцілі. І першае, што мы зразумелі, што не выкарыстоўваць batch гэта проста нельга. Усе варыянты batching роўныя нулю, т. е. час выканання практычна роўна нулю ў параўнанні з аднаразовым выкананнем.
Мы ўстаўляем дадзеныя. Вельмі тамака простая табліца. Тры калонкі. І што мы тут бачым? Мы бачым, што ўсе гэтыя тры варыянты прыкладна параўнальныя. І COPY, вядома, лепш.
Гэта калі мы кавалачкамі ўстаўляемы. Калі мы казалі, што адно значэнне VALUES, два значэнне VALUES, тры значэнне VALUES ці мы іх там 10 праз коску паказалі. Гэта якраз зараз па гарызанталі. 1, 2, 4, 128. Відаць, што Batch Insert, які сіненькім намаляваны, яму ад гэтага моцна палягчае. Т. е. калі вы ўстаўляеце па адным ці нават калі вы ўстаўляеце па чатыры, то становіцца ў два разы лепш, проста ад таго, што мы ў VALUES крыху пабольш запхнулі. Менш аперацый EXECUTE.
Выкарыстоўваць COPY на маленькіх аб'ёмах - гэта вельмі неперспектыўна. Я на першых двух нават не намаляваў. Яны ў нябёсы ідуць, г. зн. вось гэтыя зялёныя лічбы для COPY.
COPY трэба выкарыстоўваць, калі ў вас аб'ём дадзеных хаця б больш за сто радкоў. Накладныя выдаткі на адкрыццё гэтага злучэння вялікія. І, сапраўды скажу, у гэты бок не капаў. Batch я аптымізаваў, COPY - не.
Што мы робім далей? Памералі. Разумеем, што трэба выкарыстоўваць ці структуры, ці мудрагелісты bacth, які аб'ядноўвае некалькі значэнняў.
Што трэба вынесці з сённяшняга даклада?
- PreparedStatement - гэта наша ўсё. Гэта вельмі шмат дае для прадукцыйнасці. Яно дае вялікую бочку дзёгцю.
- І трэба рабіць EXPLAIN ANALYZE 6 разоў.
- І трэба разводзіць OFFSET 0, і трукамі тыпу +0 для таго, каб кіраваць пакінуты там адсотак ад нашых праблемных запытаў.
Крыніца: habr.com