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

Предлагам да се запознаете с разшифровката на доклада от началото на 2016 година на Владимир Ситников "PostgreSQL и JDBC изцеждаме всичко"

Добър ден! Аз съм Владимир Ситников. Работя в компанията NetCracker от 10 години. Основно се занимавам с производителност. Всичко, свързано с Java, всичко, свързано с SQL – това е, което обичам.

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

Ще говорим за:

• извличане на данни.
• съхранение на данни.
• както и за производителността.
• И за подводните камъни, които са там заровени.

Нека започнем с прост въпрос. Извличаме една редица от таблица по първичния ключ.

Базата данни се намира на същия хост. И всичко това отнема 20 милисекунди.

Тези 20 милисекунди са много. Ако имате 100 такива заявки, тогава губите време на секундата, за да преминете през тези заявки, т.е. хабите време напразно.

Не обичаме да правим това и гледаме какво ни предлага базата данни за това. Базата ни предлага два варианта за изпълнение на заявки.

Първият вариант – това е простата заявка. Какво е хубавото в нея? Това, че я взимаме и я пращаме, и нищо повече.

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

Базата има и разширена заявка, която е по-умна, но с повече функционалности. Можем отделно да изпращаме заявка за парсинг, изпълнение, свързване на променливи и т.н.

Super extended query – това е нещо, което няма да покриваме в настоящия доклад. Може би искаме нещо от базата данни и имаме такъв списък с желания, който е по някакъв начин формулиран, т.е. това е нещо, което искаме, но в момента не е възможно и в най-скоро време. Затова просто го записахме и ще ходим да настояваме основните хора.

А онова, което можем да направим, е простата заявка и разширената заявка.

Каква е особеността на всеки подход?

Простата заявка е добра за еднократно изпълнение. Изпълняваме я веднъж и забравяме. Проблемът е, че тя не поддържа бинарен формат данни, т.е. за някои високо производителни системи не е подходяща.

Разширеното заявка – спестява време при парсинга. Това е нещо, което направихме и започнахме да използваме. Изключително ни помогна. Там не става въпрос само за спестяване при парсинга. Има спестяване и при предаването на данни. Предаването на данни в бинарен формат е много по-ефективно.

Преминаваме към практиката. Ето как изглежда типичното приложение. Може да бъде Java и т.н.

Създадохме заявка. Изпълнихме команда. Създадохме close. Къде е грешката тук? В какво е проблема? Няма проблеми. Така пише във всички книги. Така трябва да се пише. Ако искате максимална производителност, пишете така.

Но практиката показа, че това не работи. Защо? Защото имаме метод "close". И когато правим така, от гледна точка на базата данни, това е като работа на пушач с база данни. Казахме "PARSE EXECUTE DEALLOCATE".

Зачем тези излишни създавания и изтегляния на заявки? Те на никого не са нужни. Но обикновено в PreparedStatement така и става, когато ги затворим, те затварят всичко на базата данни. Това не е това, което искаме.

Искаме, като здрави хора, да работим с базата. Взимаме един път и подготвяме нашата заявка, след това я изпълняваме много пъти. Всъщност много пъти – това е един път за целия живот на приложението, когато парсим. И на различни REST използваме същия идентификатор на заявката. Това е нашата цел.

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

Много е просто – не е нужно да затваряме заявките. Пишем така: "prepare" "execute".

Ако стартираме това, ясно е, че някъде нещо ще се прелее. Ако не е ясно, може да измерим. Вземаме и пишем бенчмарк, в който има такъв прост метод. Създаваме заявка. Стартираме на някаква версия на драйвера и получаваме, че пада доста бързо с загуба на цялата памет, която имаме.

Ясно е, че такива грешки лесно се коригират. Няма да говоря за тях. Но ще кажа, че в новата версия работи много по-бързо. Методът е безсмислен, но въпреки това.

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

В реалността приложенията винаги затварят заявките. Във всичките книги пише, че трябва да се затварят, иначе паметта ще изтече.

И PostgreSQL не може да кешира запитвания. Необходимо е всяка сесия сама да създава този кеш.

И време за парсинг също не искаме да губим.

И както обикновено, имаме два варианта.

Първият вариант – взимаме и казваме, че нека всичко да го направим в PgSQL. Там има кеш. Той всичко кешира. Ще стане прекрасно. Ние така го разгледахме. Имаме 100500 запитвания. Не работи. Не сме съгласни да трансформираме запитванията ръчно в процедури. Не-не.

Имаме втори вариант – да вземем и сами да го направим. Отворихме изходния код, започваме да работим. Работим, работим. Оказа се, че не е толкова сложно да се направи.

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

Появи се това през август 2015 година. Сега вече има по-съвременна версия. И всичко е супер. Работи толкова добре, че не променяме приложението. Дори спряхме да мислим в посока PgSQL, т.е. това ни беше напълно достатъчно, за да намалим практически всички разходи до нула.

Съответно, Server-prepared statements се активират на петото изпълнение, за да не харчим памет в базата данни за всяко еднократно запитване.

Може да питате – къде са цифрите? Какво получавате? И тук няма да дам цифри, защото за всяко запитване те са различни.

Имахме запитвания, при които за 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.

Добре, включихме. Взехме, може би, драйвер. И производителността падна. Всичко стана лошо.

Как така? Това бъг ли е или функция? За съжаление, не успяхме да разберем – бъг ли е или функция. Но има напълно прост сценарий за възпроизвеждане на проблема. Той ни изненада напълно. И става дума за избор от буквално една таблица. Ние, разбира се, имахме повече такива заявки. Те обикновено включваха две-три таблици, но има такъв сценарий за възпроизвеждане. Вземете на вашата база с каквато и да е версия и го възпроизведете.

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

Смисълът е, че имаме две колони, всяка от които е индексирана. В една колона по стойността NULL има милион реда. А във втората колона има само 20 реда. Когато изпълняваме без свързани променливи, всичко работи добре.

Ако започнем да изпълняваме със свързани променливи, т.е. изпълняваме знак «?» или «$1» за нашата заявка, какво всъщност получаваме?

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

Първото изпълнение – както трябва. Второто – малко по-бързо. Нещо е кеширано. Третото-четвъртото-петото. После хоп – и по някакъв начин така. И най-лошото е, че това се случва при шестото изпълнение. Кой знаеше, че трябва да се направят точно шест изпълнения, за да разберем какъв е наистина планът за изпълнение?

Кой е виновен? Какво се е случило? Базата данни съдържа оптимизация. И тя е оптимизирана за общия случай. И, съответно, от някакъв момент нататък преминава на общ план, който, за съжаление, може да се окаже различен. Може да бъде същия, а може и да е различен. И там има някаква праговата стойност, която води до такова поведение.

Какво можем да направим с това? Тук, разбира се, е по-сложно да се предполага нещо. Има простото решение, което използваме. Това е +0, OFFSET 0. Сигурно знаете такива решения. Просто добавяме «+0» в заявката и всичко е наред. Ще покажа по-късно.

И има още един вариант – внимателно да разгледате плановете. Разработчикът трябва не само да напише заявката, но и 6 пъти да каже «explain analyze». Ако казва само 5, няма да свърши работа.

И има и третият вариант – да напишете имейл в pgsql-hackers. Написах, но все още не е ясно – дали е бъг или функция.

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

Докато мислим – бъг ли е или функция, да го поправим. Вземаме нашата заявка и добавяме «+0». Всичко е наред. Два символа и дори не е нужно да мислим какво се случва. Много просто. Просто забранихме на базата данни да използва индекс по тази колона. Нямаме индекс по колоната «+0» и всичко, базата данни не използва индекс, всичко е наред.

Ето го правилото за 6 explain-ка. В текущите версии трябва да го правите 6 пъти, ако имате свързани променливи. Ако нямате свързани променливи, тогава правим точно така. И накрая именно тази заявка се проваля. Нищо сложно.

Изглежда, колко много може? Тук е бъг, там е бъг. Всъщност бъг навсякъде.

Нека да погледнем отново. Например, имаме две схеми. Схема А с таблица Ы и схема Б с таблица Ы. Заявката – да изберем данни от таблицата. Какво ще получим в този случай? Ще получим грешка. Всичко изброено по-горе ще се случи. Правилото е такова – бъг навсякъде, ще получим всичко изброено по-горе.

Сега въпросът е: «Защо?». Изглежда, има документация, че, ако имаме схема, то има променлива «search_path», която указва къде да търсим таблицата. Изглежда, че променливата съществува.

В чем е проблемът? Проблемът е, че server-prepared statements не подозират, че search_path може да бъде променян. Това значение остава константно за базата данни. И някои части може да не подхванат новите стойности.

Разбира се, това зависи от версията, на която тествате. Зависи също и от това колко сериозно се различават таблиците ви. А версия 9.1 просто ще изпълни старите заявки. Новите версии могат да засекат уловката и да кажат, че имате грешка.

Set search_path + server-prepared statements =
кешираният план не трябва да променя типа на резултата

Как да го поправим? Има проста рецепта – не го правете. Не променяйте 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 реда, в зависимост от размера на редовете и т.н.

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

Понякога, ако поправиш само един ред, всичко се ускори десетократно. Става така. Защо? Както обикновено, константа беше използвана някъде. А стойността '128' означаваше – не използвай batching.

Java microbenchmark harness

Добре, че това не попадна в официалната версия. Открихме го преди да започнем да издаваме релиза. Всички стойности, които споменавам, се основават на съвременни версии.

Нека измерим. Мериим InsertBatch просто. Мерим InsertBatch многократно, т.е. същото, но с много values. Хитър ход. Не всеки го знае, но това е такъв прост ход, много по-лесно, отколкото COPY.

Може да се прави COPY.

Можете да направите това със структури. Обявете тип User default, предайте масив и 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 - не.

Какво правим по-нататък? Измерваме. Разбираме, че трябва да използваме или структури, или сложен batch, обединяващ няколко стойности.

Какво трябва да запомним от днешната лекция?

• PreparedStatement - това е нашето всичко. То дава много за производителността. Обаче дава и голяма бочка с катран.
• И трябва да направим EXPLAIN ANALYZE 6 пъти.
• И трябва да разреждаме OFFSET 0 и трикове като +0, за да коригираме останалия процент от нашите проблемни заявки.

Източник: habr.com