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

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

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

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

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

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

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

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

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

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

Първият вариант е обикновената заявка. Какво води до положителен резултат? Че взимаме и изпращаме искането, и нищо повече.

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

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

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

Това, което можем да направим, е simple query и extended query.

Каква е специфичността на всеки подход?

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

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

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

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

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

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

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

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

Много просто – не трябва да затваряме statements. Пишем така: ‘prepare’ ‘execute’.

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

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

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

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

И 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. И в такъв случай трябва отново да създадем нашето заявление.

Ако изпълнявате една и съща заявка, наблюдавайте типовете данни в колоната да не се смесват. Трябва да внимавате с 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 пъти, ако имате свързани променливи. Ако нямате свързани променливи, правим така. И в крайна сметка точно това запитване пада. Не е нещо сложно.

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

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

Сега въпросът е: „Защо?“. Изглежда, че има документация, която казва, че ако имаме схема, то има променлива „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 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 – не.

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

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

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

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