PostgreSQL и JDBC го исцедат целиот сок. Владимир Ситников

Ви предлагам да го прочитате транскриптот од извештајот на Владимир Ситников од почетокот на 2016 година „PostgreSQL и JDBC го исцедат целиот сок“

Добар ден Јас се викам Владимир Ситников. Работам за NetCracker веќе 10 години. И јас сум најмногу по продуктивност. Сè што е поврзано со Java, сè што е поврзано со SQL е она што го сакам.

И денес ќе зборувам за она што го сретнавме во компанијата кога почнавме да го користиме PostgreSQL како сервер за бази на податоци. И најчесто работиме со Java. Но, она што ќе ви го кажам денес не е само за Java. Како што покажа практиката, ова се случува и на други јазици.

Ќе зборуваме:

• за земање примероци на податоци.
• За зачувување на податоци.
• И, исто така, за перформансите.
• А за подводните гребла што се закопани таму.

Да почнеме со едноставно прашање. Избираме еден ред од табелата врз основа на примарниот клуч.

Базата на податоци се наоѓа на истиот хост. И сето ова земјоделство трае 20 милисекунди.

Овие 20 милисекунди се многу. Ако имате 100 такви барања, тогаш трошите време во секунда прелистувајќи ги овие барања, односно губиме време.

Не сакаме да го правиме ова и гледаме што ни нуди базата за ова. Базата на податоци ни нуди две опции за извршување на барања.

Првата опција е едноставно барање. Што е добро во тоа? Тоа што го земаме и праќаме, и ништо повеќе.

https://github.com/pgjdbc/pgjdbc/pull/478

Базата на податоци има и напредно барање, кое е посложено, но пофункционално. Можете одделно да испратите барање за парсирање, извршување, врзување на променливи итн.

Супер проширеното барање е нешто што нема да го опфатиме во тековниот извештај. Ние, можеби, сакаме нешто од базата на податоци и постои список на желби што е формиран во некоја форма, т.е. тоа е она што го сакаме, но тоа е невозможно сега и во следната година. Така штотуку го снимивме и ќе одиме наоколу тресејќи ги главните луѓе.

А она што можеме да го направиме е едноставно барање и проширено барање.

Што е посебно за секој пристап?

Едноставно барање е добро за еднократно извршување. Еднаш завршена и заборавена. И проблемот е што не поддржува формат на бинарни податоци, односно не е погоден за некои системи со високи перформанси.

Проширено барање - ви овозможува да заштедите време при парсирање. Ова е она што го направивме и почнавме да го користиме. Ова навистина, навистина ни помогна. Не се заштеди само при парсирање. Има заштеди при пренос на податоци. Пренесувањето податоци во бинарен формат е многу поефикасно.

Да продолжиме да вежбаме. Вака изгледа типична апликација. Може да биде Java, итн.

Создадовме изјава. Ја изврши командата. Создаден блиску. Каде е тука грешката? Што е проблемот? Нема проблем. Така пишува во сите книги. Така треба да се пишува. Ако сакате максимални перформанси, пишете вака.

Но, практиката покажа дека тоа не функционира. Зошто? Затоа што имаме метод на „близок“. И кога го правиме ова, од гледна точка на базата на податоци излегува дека тоа е како пушач кој работи со база на податоци. Рековме „ПАРЗИРАЈ ИЗВРШИ ДЕАЛОКАТИРАЈ“.

Зошто сето ова дополнително креирање и растоварување на изјави? Никому не му требаат. Но, она што обично се случува во PreparedStatements е дека кога ги затвораме, тие затвораат сè што е во базата на податоци. Ова не е она што го сакаме.

Сакаме, како здрави луѓе, да работиме со базата. Еднаш ја зедовме и подготвивме нашата изјава, а потоа ја извршуваме многу пати. Всушност, многу пати - ова е еднаш во целиот живот на апликациите - тие се анализирани. И ние ја користиме истата изјава id на различни REST. Ова е нашата цел.

Како можеме да го постигнеме ова?

Многу е едноставно - нема потреба да се затвораат изјавите. Го пишуваме вака: „подготви“ „изврши“.

Ако лансираме вакво нешто, тогаш јасно е дека нешто некаде ќе се прелее. Ако не е јасно, можете да го пробате. Ајде да напишеме репер кој го користи овој едноставен метод. Направете изјава. Го лансираме на некоја верзија на драјверот и откриваме дека паѓа доста брзо со губење на сета меморија што ја имал.

Јасно е дека таквите грешки лесно се поправаат. Нема да зборувам за нив. Но, ќе кажам дека новата верзија работи многу побрзо. Методот е глупав, но сепак.

Како да се работи правилно? Што треба да направиме за ова?

Во реалноста, апликациите секогаш ги затвораат изјавите. Во сите книги велат да се затвори, инаку меморијата ќе протече.

И PostgreSQL не знае како да кешира прашања. Неопходно е секоја сесија да го креира овој кеш за себе.

И ние не сакаме да губиме време ниту на парсирање.

И како и обично имаме две опции.

Првата опција е да ја земеме и да кажеме дека ајде да завиткаме сè во PgSQL. Таму има кеш. Се кешира. Ќе испадне одлично. Го видовме ова. Имаме 100500 барања. Не функционира. Не се согласуваме барањата рачно да ги претвораме во процедури. Не не.

Имаме втора опција - земете го и пресечете сами. Ги отвораме изворите и почнуваме да сечеме. Видовме и видовме. Се испостави дека тоа не е толку тешко да се направи.

https://github.com/pgjdbc/pgjdbc/pull/319

Ова се појави во август 2015 година. Сега има помодерна верзија. И се е супер. Работи толку добро што не менуваме ништо во апликацијата. Па дури и престанавме да размислуваме во насока на PgSQL, т.е. ова беше сосема доволно за да ги намалиме сите режиски трошоци на речиси нула.

Според тоа, изјавите подготвени од серверот се активираат на 5-то извршување со цел да се избегне губење на меморијата во базата на секое еднократно барање.

Можеби ќе прашате - каде се бројките? Што добивате? И тука нема да дадам бројки, бидејќи секое барање има свое.

Нашите барања беа такви што потрошивме околу 20 милисекунди на парсирање на OLTP прашања. Имаше 0,5 милисекунди за извршување, 20 милисекунди за парсирање. Барање – 10 KiB текст, 170 линии план. Ова е барање OLTP. Бара 1, 5, 10 линии, понекогаш и повеќе.

Но, воопшто не сакавме да губиме 20 милисекунди. Го намаливме на 0. Се е супер.

Што можеш да одземеш од тука? Ако имаш Java, тогаш ја земаш модерната верзија на драјверот и се радуваш.

Ако зборувате друг јазик, тогаш размислете - можеби и ова ви треба? Затоа што од гледна точка на конечниот јазик, на пример, ако PL 8 или имате LibPQ, тогаш не ви е очигледно дека трошите време не на извршување, на парсирање и ова вреди да се провери. Како? Сè е бесплатно.

Освен што има грешки и некои особености. И ние ќе зборуваме за нив веднаш. Најмногу ќе биде за индустриска археологија, за она што го најдовме, на што наидовме.

Доколку барањето се генерира динамички. Се случува. Некој ги лепи низите заедно, што резултира со SQL барање.

Зошто е лош? Лошо е затоа што секој пат кога завршуваме со различна низа.

И хаш-кодот на оваа различна низа треба повторно да се прочита. Ова е навистина задача на процесорот - да се најде долг текст за барање дури и во постоечки хаш не е толку лесно. Затоа, заклучокот е едноставен - не генерирајте барања. Чувајте ги во една променлива. И радувај се.

Следен проблем. Видовите на податоци се важни. Има ORM кои велат дека не е важно каков вид NULL има, нека има некој вид. Ако Int, тогаш велиме setInt. И ако NULL, тогаш нека биде секогаш VARCHAR. И каква разлика има на крајот што има NULL? Самата база на податоци ќе разбере сè. И оваа слика не функционира.

Во пракса, базата на податоци воопшто не се грижи. Ако првиот пат кажавте дека ова е бројка, а вториот пат дека тоа е VARCHAR, тогаш е невозможно повторно да се користат изјави подготвени од серверот. И во овој случај, мораме повторно да ја креираме нашата изјава.

Ако го извршувате истото барање, проверете дали типовите на податоци во вашата колона не се збунети. Треба да внимавате на NULL. Ова е вообичаена грешка што ја имавме откако почнавме да користиме PreparedStatements

Добро, вклучено. Можеби го зеле возачот. И продуктивноста падна. Работите станаа лоши.

Како се случува ова? Дали е ова грешка или функција? За жал, не беше можно да се разбере дали ова е грешка или функција. Но, постои многу едноставно сценарио за репродукција на овој проблем. Сосема неочекувано ни направи заседа. И се состои од земање примероци буквално од една табела. Ние, се разбира, имавме повеќе такви барања. Како по правило, тие вклучуваа две или три табели, но постои такво сценарио за репродукција. Земете која било верзија од вашата база на податоци и пуштете ја.

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Поентата е дека имаме две колони, од кои секоја е индексирана. Има милион редови во една NULL колона. А втората колона содржи само 20 линии. Кога извршуваме без врзани променливи, сè работи добро.

Ако почнеме да извршуваме со врзани променливи, т.е. го извршуваме "?" или „1$“ за нашето барање, што добиваме на крајот?

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Првото извршување е како што се очекуваше. Вториот е малку побрз. Нешто беше зачувано. Трето, четврто, петто. Потоа тресне - и нешто слично. И најлошото е што ова се случува на шестото погубување. Кој знаеше дека е неопходно да се направат точно шест егзекуции за да се разбере каков е вистинскиот план за извршување?

Кој е виновен? Што се случи? Базата на податоци содржи оптимизација. И се чини дека е оптимизиран за генеричкиот случај. И, соодветно, почнувајќи од одреден момент, таа се префрла на генерички план, кој, за жал, може да испадне различен. Може да испадне дека е исто, или може да биде различно. И постои некаква вредност на прагот што води до ова однесување.

Што можете да направите за тоа? Тука, се разбира, потешко е да се претпостави нешто. Постои едноставно решение што го користиме. Ова е +0, ОФСЕТ 0. Сигурно знаете такви решенија. Само го земаме и додаваме „+0“ на барањето и сè е во ред. Ќе ти покажам подоцна.

И има уште една опција - погледнете ги плановите повнимателно. Инвеститорот не само што мора да напише барање, туку и да каже „објасни анализирај“ 6 пати. Ако е 5, нема да работи.

И постои трета опција - напишете писмо до pgsql-хакерите. Напишав, сепак, сè уште не е јасно дали ова е грешка или функција.

https://gist.github.com/vlsi/df08cbef370b2e86a5c1

Додека размислуваме дали ова е грешка или функција, ајде да го поправиме. Ајде да го земеме нашето барање и да додадеме „+0“. Се е во ред. Два симболи и не треба ни да размислувате како е или што е. Многу едноставно. Ние едноставно забранивме базата на податоци да користи индекс на оваа колона. Немаме индекс на колоната „+0“ и тоа е тоа, базата на податоци не го користи индексот, сè е во ред.

Ова е правило на 6 објаснување. Сега во тековните верзии треба да го направите тоа 6 пати ако имате врзани променливи. Ако немате врзани променливи, ова е она што го правиме. И на крајот токму ова барање пропаѓа. Тоа не е незгодна работа.

Се чини, колку е можно? Бубачка овде, бубачка таму. Всушност, бубачката е насекаде.

Ајде да погледнеме подетално. На пример, имаме две шеми. Шема А со табела S и дијаграм Б со табела S. Барање – изберете податоци од табела. Што ќе имаме во овој случај? Ќе имаме грешка. Ќе ги имаме сите горенаведени. Правилото е - бубачка е насекаде, ќе ги имаме сите горенаведени.

Сега прашањето е: „Зошто? Се чини дека постои документација дека ако имаме шема, тогаш постои променлива „search_path“ која ни кажува каде да ја бараме табелата. Се чини дека постои променлива.

Што е проблемот? Проблемот е што изјавите подготвени од серверот не се сомневаат дека search_path може некој да го промени. Оваа вредност останува, како што беше, константна за базата на податоци. И некои делови можеби нема да добијат нови значења.

Се разбира, ова зависи од верзијата на која ја тестирате. Зависи од тоа колку сериозно се разликуваат вашите табели. И верзијата 9.1 едноставно ќе ги изврши старите барања. Новите верзии може да ја фатат грешката и да ви кажат дека имате бубачка.

Поставете search_path + изјави подготвени од серверот =
кешираниот план не смее да го менува типот на резултат

Како да се лекува? Постои едноставен рецепт - не го правете тоа. Нема потреба да се менува search_path додека апликацијата работи. Ако промените, подобро е да создадете нова врска.

Можете да дискутирате, т.е. отворате, дискутирате, додавате. Можеби можеме да ги убедиме развивачите на базата на податоци дека кога некој менува вредност, базата треба да му каже на клиентот за ова: „Гледај, вашата вредност е ажурирана овде. Можеби треба да ги ресетирате изјавите и повторно да ги создадете?“ Сега базата се однесува тајно и никако не известува дека изјавите се сменети некаде внатре.

И пак ќе нагласам - ова е нешто што не е типично за Java. Ќе го видиме истото во PL/pgSQL еден до еден. Но, таму ќе се репродуцира.

Ајде да пробаме уште некој избор на податоци. Ние избираме и избираме. Имаме табела со милион редови. Секоја линија е килобајт. Приближно еден гигабајт податоци. И имаме работна меморија во машината Јава од 128 мегабајти.

Ние, како што е препорачано во сите книги, користиме обработка на поток. Односно, отвораме resultSet и ги читаме податоците од таму малку по малку. Дали ќе работи? Дали ќе падне од меморија? Ќе прочиташ ли малку? Да веруваме во базата на податоци, да веруваме во Postgres. Не веруваме. Дали ќе испаднеме од меморијата? Кој го доживеа OutOfMemory? Кој успеа да го поправи после тоа? Некој успеа да го поправи.

Ако имате милион редови, не можете само да изберете и да изберете. Потребен е OFFSET/LIMIT. Кој е за оваа опција? А кој е за играње со autoCommit?

Овде, како и обично, најнеочекуваната опција се покажува како точна. И ако одеднаш го исклучите autoCommit, тоа ќе ви помогне. Зошто е тоа? Науката не знае за ова.

Но, стандардно, сите клиенти кои се поврзуваат со базата на податоци на Postgres ги преземаат сите податоци. PgJDBC не е исклучок во овој поглед; ги избира сите редови.

Постои варијација на темата FetchSize, т.е. можете да кажете на ниво на посебна изјава дека овде, ве молиме изберете податоци со 10, 50. Но, ова не функционира додека не го исклучите autoCommit. Исклучен autoCommit - почнува да работи.

Но, поминувањето низ кодот и поставувањето setFetchSize насекаде е незгодно. Затоа, направивме поставка што ќе ја каже стандардната вредност за целата врска.

Тоа е она што го кажавме. Параметарот е конфигуриран. И што добивме? Ако избереме мали количини, ако, на пример, избираме 10 редови одеднаш, тогаш имаме многу големи режиски трошоци. Затоа, оваа вредност треба да се постави на околу сто.

Идеално, се разбира, сè уште треба да научите како да го ограничите во бајти, но рецептот е овој: поставете го defaultRowFetchSize на повеќе од сто и бидете среќни.

Ајде да продолжиме со вметнување податоци. Вметнувањето е полесно, има различни опции. На пример, INSERT, VALUES. Ова е добра опција. Може да кажете „INSERT SELECT“. Во пракса тоа е истото. Нема разлика во перформансите.

Книгите велат дека треба да извршите Batch изјава, книгите велат дека можете да извршите посложени команди со неколку загради. А Postgres има прекрасна карактеристика - можете да направите КОПИРАЊЕ, т.е. да го направите побрзо.

Ако го измерите, можете повторно да направите некои интересни откритија. Како сакаме ова да функционира? Сакаме да не анализираме и да не извршуваме непотребни команди.

Во пракса, TCP не ни дозволува да го правиме тоа. Ако клиентот е зафатен со испраќање барање, тогаш базата на податоци не ги чита барањата во обид да ни испрати одговори. Крајниот резултат е дека клиентот чека базата на податоци да го прочита барањето, а базата чека клиентот да го прочита одговорот.

И затоа клиентот е принуден периодично да испраќа пакет за синхронизација. Дополнителни мрежни интеракции, дополнително губење време.

И колку повеќе ги додаваме, толку е полошо. Возачот е доста песимист и ги додава доста често, околу еднаш на 200 линии, во зависност од големината на линиите итн.

https://github.com/pgjdbc/pgjdbc/pull/380

Се случува да поправите само една линија и сè да се забрза 10 пати. Се случува. Зошто? Како и обично, ваква константа веќе некаде е искористена. И вредноста „128“ значеше да не се користи серија.

Јава микробенчмарк темперамент

Добро е што ова не беше вклучено во официјалната верзија. Откриен пред да започне објавувањето. Сите значења што ги давам се засноваат на модерни верзии.

Ајде да го пробаме. Ние го мериме InsertBatch едноставно. Ние го мериме InsertBatch повеќе пати, односно истото, но има многу вредности. Слабо потег. Не секој може да го направи ова, но тоа е толку едноставен потег, многу полесен од КОПИРАЊЕ.

Можете да направите КОПИРАЊЕ.

И можете да го направите ова на структури. Објавете го стандардниот тип на корисник, поминете низа и Вметнете директно во табелата.

Ако ја отворите врската: pgjdbc/ubenchmsrk/InsertBatch.java, тогаш овој код е на GitHub. Можете да видите конкретно какви барања се генерираат таму. Не е важно.

Лансиравме. И првото нешто што сфативме беше дека некористењето серија е едноставно невозможно. Сите опции за серии се нула, односно времето на извршување е практично нула во споредба со еднократното извршување.

Внесуваме податоци. Тоа е многу едноставна табела. Три колони. И што гледаме овде? Гледаме дека сите три од овие опции се приближно споредливи. И COPY е, се разбира, подобро.

Ова е кога вметнуваме парчиња. Кога кажавме дека една вредност на VALUES, две вредности на VALUES, три вредности на VALUES, или наведовме 10 од нив одделени со запирка. Ова сега е само хоризонтално. 1, 2, 4, 128. Се гледа дека Batch Insert, кој е нацртан во сина боја, прави да се чувствува многу подобро. Односно, кога вметнувате едно по едно или дури и кога вметнувате четири одеднаш, станува двојно подобро, едноставно затоа што се натрупавме малку повеќе во ВРЕДНОСТИ. Помалку EXECUTE операции.

Користењето COPY на мали томови е крајно неперспективно. Јас дури и не цртав на првите две. Тие одат во рајот, односно овие зелени бројки за COPY.

COPY треба да се користи кога имате најмалку сто редови податоци. Режимот за отворање на оваа врска е голем. И, да бидам искрен, не копав во оваа насока. Ја оптимизирав Batch, но не и COPY.

Што правиме следно? Го пробавме. Разбираме дека треба да користиме или структури или паметна бања која комбинира неколку значења.

Што треба да земете од денешниот извештај?

• Подготвената изјава е наше сè. Ова дава многу за продуктивноста. Произведува голема пропаст во маста.
• И треба да направите EXPLAIN ANALYZE 6 пати.
• И ние треба да го разредиме OFFSET 0 и трикови како +0 за да го поправиме преостанатиот процент од нашите проблематични прашања.

Извор: www.habr.com