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

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

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

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

Ще говорим:

• относно вземането на проби от данни.
• Относно запазването на данни.
• А също и относно изпълнението.
• И за подводните гребла, които са заровени там.

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

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

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

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

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

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

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

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

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

Какво е специалното за всеки подход?

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

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

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

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

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

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

Искаме като здрави хора да работим с базата. Взехме и подготвихме нашето изявление веднъж, след което го изпълняваме много пъти. Всъщност много пъти - това е веднъж в целия живот на приложенията - те са анализирани. И ние използваме един и същ идентификатор на израз на различни 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, OFFSET 0. Със сигурност знаете такива решения. Просто го вземаме и добавяме „+0“ към заявката и всичко е наред. Ще ти покажа по-късно.

А има и друг вариант - разгледайте по-внимателно плановете. Разработчикът трябва не само да напише заявка, но и да каже „обяснете анализ“ 6 пъти. Ако е 5, няма да работи.

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

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

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

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

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

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

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

Какъв е проблемът? Проблемът е, че изявленията, подготвени от сървъра, не подозират, че search_path може да бъде променен от някого. Тази стойност остава така да се каже постоянна за базата данни. И някои части може да не възприемат нови значения.

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

Задайте search_path + подготвени от сървъра оператори =
кешираният план не трябва да променя типа резултат

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

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

И пак ще подчертая - това е нещо нетипично за Java. Ще видим едно към едно едно и също нещо в PL/pgSQL. Но там ще се възпроизведе.

Нека опитаме да изберем още малко данни. Ние избираме и избираме. Имаме таблица с милион реда. Всеки ред е килобайт. Приблизително гигабайт данни. И имаме работна памет в Java машината от 128 мегабайта.

Ние, както се препоръчва във всички книги, използваме поточна обработка. Тоест отваряме resultSet и малко по малко четем данните от там. ще работи ли Ще падне ли от паметта? Ще почетеш ли малко? Нека се доверим на базата данни, нека се доверим на Postgres. Ние не го вярваме. Ще изпаднем ли от паметта? Кой е изпитал OutOfMemory? Кой успя да го оправи след това? Някой успя да го поправи.

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

Тук, както обикновено, най-неочакваният вариант се оказва правилен. И ако внезапно изключите autoCommit, това ще помогне. Защо така? Науката не знае за това.

Но по подразбиране всички клиенти, свързващи се с база данни на Postgres, извличат всички данни. PgJDBC не е изключение в това отношение; той избира всички редове.

Има вариант на темата FetchSize, т.е. можете да кажете на ниво отделно изявление, че тук, моля, изберете данни с 10, 50. Но това не работи, докато не изключите autoCommit. Изключих autoCommit - започва да работи.

Но преминаването през кода и настройването на setFetchSize навсякъде е неудобно. Затова направихме настройка, която ще казва стойността по подразбиране за цялата връзка.

Това казахме. Параметърът е конфигуриран. И какво получихме? Ако избираме малки суми, ако например избираме 10 реда наведнъж, тогава имаме много големи режийни разходи. Следователно тази стойност трябва да бъде зададена на около сто.

В идеалния случай, разбира се, все още трябва да се научите как да го ограничавате в байтове, но рецептата е следната: задайте defaultRowFetchSize на повече от сто и бъдете щастливи.

Нека да преминем към вмъкване на данни. Поставянето е по-лесно, има различни варианти. Например INSERT, VALUES. Това е добър вариант. Можете да кажете „INSERT SELECT“. На практика е същото. Няма разлика в производителността.

Книгите казват, че трябва да изпълните пакетен оператор, книгите казват, че можете да изпълните по-сложни команди с няколко скоби. А Postgres има чудесна функция - можете да направите COPY, т.е. да го направите по-бързо.

Ако го измерите, отново можете да направите някои интересни открития. Как искаме да работи това? Искаме да не анализираме и да не изпълняваме ненужни команди.

На практика TCP не ни позволява да правим това. Ако клиентът е зает с изпращане на заявка, тогава базата данни не чете заявките в опити да ни изпрати отговори. Крайният резултат е, че клиентът чака базата данни да прочете заявката, а базата данни чака клиентът да прочете отговора.

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

И колкото повече ги добавяме, толкова по-лошо става. Драйверът е доста песимистичен и ги добавя доста често, около веднъж на всеки 200 реда, в зависимост от размера на редовете и т.н.

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

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

Съоръжение за микробенчмарк на Java

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

Нека го пробваме. Ние измерваме InsertBatch просто. Ние измерваме InsertBatch няколко пъти, т.е. едно и също нещо, но има много стойности. Сложен ход. Не всеки може да направи това, но това е толкова прост ход, много по-лесен от КОПИРАНЕ.

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

И можете да направите това върху структури. Декларирайте потребителския тип по подразбиране, подайте масив и INSERT директно в таблицата.

Ако отворите връзката: pgjdbc/ubenchmsrk/InsertBatch.java, тогава този код е в GitHub. Можете да видите конкретно какви заявки се генерират там. Няма значение.

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

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

Това е, когато вмъкваме парчета. Когато казахме, че една стойност VALUES, две стойности VALUES, три стойности VALUES или посочихме 10 от тях, разделени със запетая. Това вече е хоризонтално. 1, 2, 4, 128. Вижда се, че партидата, която е нарисувана в синьо, го кара да се чувства много по-добре. Тоест, когато вмъквате едно по едно или дори когато вмъквате четири наведнъж, става два пъти по-добро, просто защото сме натъпкали малко повече в VALUES. По-малко операции EXECUTE.

Използването на COPY на малки обеми е изключително необещаващо. Дори не съм рисувал на първите две. Те отиват в рая, тоест тези зелени числа за КОПИРАНЕ.

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

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

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

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

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