Типични грешки в приложенията, които водят до bloat в postgresql. Андрей Сальников

Предлагам да се запознаете с разшифровката на доклада от началото на 2016 година на Андрей Сальников "Типични грешки в приложенията, които водят до bloat в postgresql"

В този доклад ще разгледам основните грешки в приложенията, които възникват на етапа на проектиране и написване на кода на приложението. Ще се фокусирам само на тези грешки, които водят до bloat в Postgresql. Обикновено, те са началото на края на производителността на вашата система като цяло, макар в началото да не е било видимо.

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

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

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

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

Изходните данни на таблицата: тя е достатъчно малка, 2 MB. Времето за отговор по базата и конкретно по таблицата също е много добро. И натоварването е доста добро – 2000 операции в секунда по таблицата.

През този доклад ще ви показвам графики, за да бъде ясно какво се случва. Винаги ще има 2 слайда с графики. Първият слайд – това, което се случва като цяло на сървера.

В тази ситуация виждаме, че наистина имаме таблица с малък размер. Индексът е малък - 2 MB. Това е първият график отляво.

Средното време за отговор на сървъра също е стабилно и малко. Това е горният десен график.

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

Вторият слайд винаги ще бъде посветен на тестваната таблица. В тази ситуация постоянно обновяваме оставащите суми по сметките на клиента. Виждаме, че средното време за отговор на операцията за обновление е доста добро, по-малко от милисекунда. Забелязваме, че ресурсите на процесора (това е десният горен график) също се използват равномерно и в относително малки количества.

Десният долен график показва колко оперативна и дискова памет използваме, докато търсим необходимия ред, преди да го обновим. Броят на операциите по таблицата е 2000 в секунда, както споменах по-рано.

И сега се случва трагедия. По някаква причина възниква дълга забравена транзакция. Причините обикновено са банални:

• Една от най-разпространените е, че в приложенския код започваме да се свързваме с външна услуга. И тази услуга не ни отговаря. Т.е. отворили сме транзакция, внесли сме промяна в базата и отишли сме да проверим имейла или да се свържем с друга услуга в рамките на нашата инфраструктура, и по някаква причина тя не ни отговаря. И сесията ни остава висяща в състояние – не знаем кога ще бъде разрешена.
• Втората ситуация е, когато в кода ни по някаква причина е възникнал exception. И ние не сме обработили затварянето на транзакцията в exception. Получава се висяща сесия с отворена транзакция.
• И последната – това също е доста често срещан случай. Това е некачествен код. Някои фреймове отварят транзакция. Тя остава висяща, и можете да не знаете в приложението, че тя е там.

Към какво водят такива неща?

Към това, че таблиците и индексите започват да се увеличават рязко. Това е именно ефектът bloat. За базата данни това ще означава, че времето за отговор на базата данни рязко ще се увеличи, ще се увеличи натоварването на сървъра на базата данни. В крайна сметка приложението ще пострада. Защото ако в кода сте отделяли 10 милисекунди за заявка към базата, 10 милисекунди за собствената си логика, то функцията ви е работила 20 милисекунди. А сега ситуацията ще е напълно тъжна.

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

Общата ситуация по средното време за отговор на сървъра също се е променила значително. Т.е. всички заявки към сървъра започнаха да падат драстично. Освен това се активираха вътрешните процеси на Postgres под формата на автоматични вакууми, които се опитват да правят нещо и консумират ресурси.

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

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

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

И въпросът е: „Какво се случва с базата в този момент?“. С базата се случва следното. На графика на транзакциите виждате, че тя е спряна и там наистина няма дълги транзакции. Но размерите на таблицата по времето на аварията фатално нараснаха. И оттогава не намаляха. Средното време за база се стабилизира. И отговорите изглежда работят адекватно с приемлива за нас скорост. Автоматичният вакуум стана по-активен и започна да прави нещо с таблицата, защото трябва да обработва по-голямо количество данни.

По отношение на проверяваната табло със сметки, където променяме остатъците: времето за отговор на запитването изглежда се е върнало в нормата. Но всъщност то е около 1.5 пъти по-високо.

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

В общи линии е добре, но ситуацията е по-лоша, отколкото преди. Ясна деградация на базата данни в следствие на нашето приложение, което работи с тази база данни.

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

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

Защо е нужен авто-вакуум? Авто-вакуум в един момент идва, обръща се към базата данни и я пита: 'Дай ми, моля, id на най-старата транзакция, която е отворена в момента в базата данни'. Базата данни връща този id. И авто-вакуум, опирайки се на него, преглежда редовете в таблицата. Ако види, че някои редове са били променени от много по-стари транзакции, той има право да ги обозначи като редове, които можем да преизползваме в бъдеще, записвайки нови данни там. Това е фонов процес.

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

Какво се случи по време на аварията? Как протичаше този процес?

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

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

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

Когато изпълняваме заявка, базата данни трябва да прегледа всички редове: както червени, така и зелени, за да намери необходими ред. И ефектът от надуването на таблицата с безполезни данни се нарича «bloat», който също така заема нашето дисково пространство. Спомняте ли си, че беше 2 MB, а стана 300 MB? А сега сменете мегабайтите с гигабайти и бързо ще се лишите от всички налични дискови ресурси.

Какви последствия могат да бъдат за нас?

• В примера ми таблицата и индексът нараснаха 150 пъти. Някои от нашите клиенти имат по-фатални случаи, когато просто мястото на диска започва да свършва.
• Размерът на таблиците сам по себе си никога не намалява. Автоматичният вакуум в някои случаи може да отреже края на таблицата, ако там има само мъртви редове. Но поради постоянната ротация, един зелен ред може да остане в края и да не се обновява, докато другите ще се записват някъде в началото на таблицата. Но това е толкова малко вероятно събитие, че не бива да се надявате, че таблицата сама по себе си ще намалее.
• Базата данни трябва да прегледа цялата купчина безполезни редове. И ние губим дискови ресурси, процесорни ресурси и енергия.
• И това пряко влияе на нашето приложение, защото ако в началото харчехме 10 милисекунди за запитване, 10 милисекунди за нашия код, то по време на авария започнахме да харчим секунда за запитване и 10 милисекунди за кода, т.е. производителността на приложението спадна с порядък. И когато аварията беше разрешена, започнахме да харчим 20 милисекунди за запитване, 10 милисекунди за кода. Това означава, че все пак сме спаднали в производителността с 1.5 пъти. И всичко това заради една транзакция, която се е задържала, и то вероятно по наша вина.
• И въпросът е: «Как да върнем всичко обратно?», за да стане всичко добре и запитванията да работят толкова бързо, колкото преди аварията.

За това има определен цикъл от дейности, който се провежда.

Първо трябва да намерим проблемните таблици, които са се увеличили. Разбираме, че за някои таблици запистите вървят по-активно, за други - по-малко активно. И за това се използва разширение pgstattuple. Като инсталирате това разширение, можете да напишете запитвания, които да ви помогнат да намерите таблиците, които са се увеличили достатъчно много.

След като намерите тези таблици, те трябва да бъдат компресирани. За това вече има инструменти. В нашата компания използваме три инструмента. Първият – вграден VACUUM FULL. Той е жесток, строг и безмилостен, но понякога е много полезен. Pg_repack и pgcompacttable – това е външно приложение за компресиране на таблици. И те се отнасят по-внимателно към базата данни.

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

След като всичко поправим и се уверим, че всичко е наред, трябва да знаем как да предотвратим тази ситуация в бъдеще:

• Тя се предотвратява съвсем лесно. Трябва да следите за продължителността на сесиите на основния сървър. Особено опасни сесии са тези в състояние idle in transaction. Това са тези, които отворили транзакция, направили нещо и излезли или просто се задържали, загубили се в кода.
• И за вас, като разработчици, е важно да тествате кода във времето на възникването на тези ситуации. Не е сложно да го направите. Това ще бъде полезна проверка. Ще избегнете голямо количество "детски" проблеми, свързани с дълги транзакции.

В тези графики исках да ви покажа как се промени таблицата и поведението на базата данни след като проведох VACUUM FULL върху таблицата. Това не е продуктивно.

Размерът на таблицата веднага се върна в нормално работно състояние от няколко мегабайта. Това не оказа значително влияние на средното време за отговор от сървъра.

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

С това приключва първата история. Тя е най-разпространената и се случва на всички, независимо от опита на клиента, колко квалифицирани програмисти има. Рано или късно това се случва.

Втората история, в която разпределяме натоварването и оптимизираме сървърните ресурси.

• Ние вече сме пораснали и сме сериозни играчи. Разбираме, че имаме реплика и би било добре да балансираме натоварването: да пишем на Мастера, а да четем от репликата. Обикновено тази ситуация възниква, когато искаме да подготвим различни отчети или ETL. Бизнесът е много доволен от това. Иска разнообразни отчети с много сложна аналитика.
• Откритите отчети отнемат много часове, защото сложната аналитика не може да се изчисли за милисекунди. Ние, като смели момчета, пишем код. Правим в приложението вставки, като записваме на Мастера, а отчетите изпълняваме на репликата.
• Разпределяме натоварването.
• Всичко работи отлично. Ние сме супер.

Как изглежда ситуацията? Конкретно на тези графики добавих и продължителността на транзакциите от репликата. Всички останали графики се отнасят само за Мастер-сервера.

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

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

Всичко е добре, докато отчетите ни не започнат да бъдат отстрелвани по конфликт с репликацията. И те се отстрелват с постоянна периодичност.

Започваме да търсим в интернет и да четем защо това се случва. И намираме решение.

Първото решение е да увеличим забавянето на репликацията. Знаем, че нашият отчет работи 3 часа. Слагаме забавяне на репликацията – 3 часа. Стартираме всичко, но все пак имаме проблеми с това, че отчетите понякога отстрелват.

Искаме всичко да е перфектно. Продължаваме да се ровим. И намираме в интернет страхотна настройка – hot_standby_feedback. Активираме я. Hot_standby_feedback ни позволява да задържим работата на автоматичния вакуум на мастера. Така изцяло се избавяме от конфликтите на репликацията. И всичко работи добре с отчетите.

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

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

Как ще изглежда това, ако не знаем за какво говоря?

• Започваме да търсим проблеми. Ако сме се сблъсквали с проблеми в първата част, знаем, че причината може да е в дългата транзакция, и отиваме при Мастера. Проблемът е при нас на Мастера. Той е натоварен. Загрява се, а Load Average е под сто.
• Запитванията там се забавят, но не виждаме никакви дълги транзакции. И не разбираме каква е причината. Не знаем къде да търсим.
• Проверяваме сървърното оборудване. Може би сме имали проблем с RAID. Може да сме изгубили планка памет. Каквото и да е, нищо не е там. Но не, сървърите са нови, всичко работи отлично.
• Всички бягат: администратори, разработчици и директорът. Нищо не помага.
• И в един момент всичко изведнъж започва да се оправя само.

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

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

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

В крайна сметка получаваме спад в производителността, както и в първия случай, с 1.5 до 2 пъти, а понякога и повече.

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

• Не включвайте hot_standby_feedback? Да, не се препоръчва да се включва без особени причини. Защото този параметър пряко влияе на главния сървър и спира работа на автозакупуването там. Ако го включите на някоя реплика и забравите за него, можете да повредите главния сървър и да получите сериозни проблеми с приложението.
• Трябва ли да увеличим max_standby_streaming_delay? Да, така е за отчетите. Ако имате тричасов отчет и не искате да се провали заради конфликти в репликацията, просто увеличете забавянето. Дългият отчет никога не изисква данни, които са пристигнали в базата в момента. Ако е тричасов, означава, че го стартирате за стар период от данни. Няма значение дали ще е три или шест часа забавяне, но вие ще получавате стабилни отчети и няма да имате проблеми с падането им.
• Естествено, трябва да контролирате дългите сесии на репликите, особено ако решите да включите hot_standby_feedback на репликата. Защото може да се случи каквото и да било. Дали сте тази реплика на разработчик, за да тествува заявки. Той е написал налудничав запит, стартира го и е отишъл да пие чай, а ние получаваме блокиран главен сървър. Или сме пуснали неправилно приложение. Ситуациите са разнообразни. Сесиите на репликите трябва да се контролират с такава същата строгост, както и на главния сървър.
• А ако имате бързи и дълги заявки по репликите, е по-добре да ги разпределите за натоварването. Това е свързано със streaming_delay. За бързите заявки имайте една реплика с малко забавяне в репликацията. За дългите отчетни заявки имайте реплика, която може да изостава с 6 часа, дори и с ден. Това е напълно нормална ситуация.

Отстраняваме последствията по същия начин:

• Намираме увеличените таблици.
• И ги компресираме с най-подходящия инструмент за нас.

История втора приключи тук. Преминаваме към трета история.

Също доста обичайна за нас, в която правим миграция.

• Всеки софтуерен продукт расте. Изискванията към него се променят. Ние искаме да се развиваме. И понякога е необходимо да обновим данните в таблицата, именно да приложим обновление в рамките на нашата миграция към новата функционалност, която внедряваме в процеса на нашето развитие.
• Старият формат на данни не ни устройва. Нека да кажем, че в момента ще се обърнем към втората таблица, в която имам операции по тези сметки. И да предположим, че те са били в рубли, а ние решаваме да повишим точността и да работим с копейки. И за това трябва да направим актуализация: полето със сумата на операцията трябва да бъде умножено по сто.
• В съвременния свят използваме автоматизирани средства за контрол на версиите на базите данни. Да предположим, Liquibase. Записваме нашата миграция там. Тестваме я на нашата тестова база. Всичко е отлично. Актуализацията преминава. Блокира работата за известно време, но получаваме обновените данни. И можем да стартираме новата функционалност на това. Всичко е тествано, проверено. Всички потвърдиха.
• Проведохме планирани дейности, проведохме миграция.

Ето миграцията с актуализацията, представена пред вас. Тъй като това са операции по сметките, таблицата беше 15 GB. И тъй като обновяваме всяко ред, ние с актуализацията удвоихме размера на таблицата, защото презаписахме всяко ред.

По време на миграцията не можехме да правим нищо с тази таблица, защото всички заявки към нея бяха в опашка и чакаха да приключи тази актуализация. Но тук искам да обърна вниманието ви на цифрите, които са на вертикалната ос. Т.е. имаме средно време за заявка преди миграцията от около 5 милисекунди и натоварване на процесора, броя на блоковите операции по четене на паметта на диска е под 7.5.

Проведохме миграцията и отново получихме проблеми.

Миграцията премина успешно, но:

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

И това отново е bloat, който отново ни създава проблеми.

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

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

И тук е важно да разберем как да извършваме такива миграции. А те са необходими. Ние доста редовно правим тези миграции.

• Тези големи миграции не се правят автоматично. Те винаги трябва да са контролирани.
• Необходим е контрол от опитен специалист. Ако имате DBA в екипа, нека той се заеме с това. Това е неговата работа. Ако няма, нека го направи най-опитният човек, който знае как да работи с бази данни.
• Новата схема на базата данни, дори и при обновяване на един столбец, винаги я подготвяме стъпка по стъпка, т.е. предварително, преди да излезе новата версия на приложението:
• Добавят се нови полета, в които ще записваме точно обновените данни.
• Преместваме данните от старото поле в новото поле на малки порции. Защо го правим? Първо, винаги контролираме процеса. Знаем, че сме преместили толкова много серии и ни остават толкова много.
• Вторият положителен ефект е, че между всяка такава порция затваряме транзакцията, отваряме нова и това дава възможност на автовакуума да работи по таблицата, за да маркира мъртвите редове за повторна употреба.
• За редовете, които ще се появят в процеса на работа на приложението (с още работи старото приложение), добавяме тригер, който записва новите стойности в новите полета. В нашия случай – това е умножение на сто от старата стойност.
• Ако сме наистина упорити и искаме същото поле, то след завършване на всички миграции и преди напускането на новата версия на приложението, просто преименуваме полетата. Старите в някакво измислено име, а новите полета преименуваме в старите.
• И само след това пускаме новата версия на приложението.

И в същото време не ще получим блоут и не ще изпаднем в производителност.

Тук завършва третата история.

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat.sql

https://github.com/dataegret/pg-utils/blob/master/sql/table_bloat_approx.sql

Сега нека да разгледаме по-подробно инструментите, които споменах в самата първа история.

Преди да търсите блоат, е задължително да инсталирате разширение. pgstattuple.

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

• Първата работи доста дълго, но затова пък ще ви покаже точните стойности на блоата в таблицата.
• Втората работи по-бързо и е много ефективна, когато трябва бързо да оцените – има ли блоат или няма блоат в таблицата. И трябва също така да разберете, че блоат в таблицата Postgres винаги съществува. Това е особеност на неговата MVCC модел.
• И 20 % блоат е нормално за таблиците в повечето случаи. Тоест, не е необходимо да се притеснявате и да компресирате тази таблица.

Как да идентифицираме таблиците, които са се надули, вече разбрахме, когато са се надули с безполезни данни.

Сега за това как да коригирате блоата:

• Ако имаме малка таблица и добри дискове, т.е. в таблицата до един гигабайт, напълно е възможно да се използва VACUUM FULL. Той ще вземе от вас изключителна блокировка на таблицата за няколко секунди и е добре, но бързо и строго ще свърши работата си. Какво прави VACUUM FULL? Той взима изключителна блокировка на таблицата и от старите таблици пренаписва живите редове в новата таблица. И в края подменя местата им. Стари файлове се изтриват, новите се поставят вместо старите. Но за времето на работа си взима изключителна блокировка на таблицата. Това означава, че с тази таблица няма какво да правите: нито да записвате в нея, нито да четете от нея, нито да я модифицирате. И VACUUM FULL изисква допълнително пространство на диска, за да запише данните.
• Следващият инструмент pg_repack. По принцип той е много подобен на VACUUM FULL, защото също пренаписва данните от старите файлове в новите и ги подменя в таблицата. Но в същото време не взема изключителна блокировка на таблицата в самото начало на работата си, а я взема само в момента, когато вече има готови данни за подмяна на файловете. Изискванията за дискови ресурси са аналогични на VACUUM FULL. Нуждаете се от допълнително пространство на диска, а това понякога може да бъде критично, ако имате таблици с терабайти. И той е доста ресурсен, тъй като извършва активна работа с вход-изход.
• Третият инструмент е pgcompacttable. Тя по-щадящ начин се отнася към ресурсите, тъй като работи малко по-различни принципи. Основната същност на pgcompacttable е, че при актуализации в таблицата преместваме всички активни редове в началото на таблицата. След това стартираме вакуум на тази таблица, защото знаем, че в началото имаме активни, а в края - мъртви редове. Вакуумът вече сам отрязва това

С инструментите всичко.

Ако темата за bloat ви се стори интересна за по-подробно проучване, ето някои полезни линкове:

• https://www.slideshare.net/alexius2Mb/where-is-the-space-postgres – това е доклад на колега. Той обхваща общо къде отива мястото в Postgres по време на работата и живота му. И там има много подробен технически раздел за администратори на бази данни относно bloat.
• https://github.com/dataegret/pg-utils – това е линк към нашето хранилище, където съхраняваме куп полезни скриптове за проверка на състоянието на базата данни. Там можете да намерите скриптове за откриване на bloat.
• Трети и четвърта линкове към инструменти, които ще ви помогнат да оптимизирате таблиците.
• http://blog.dataegret.com/2Mb018/03/postgresql-bloatbusters.html – това е пост на колега. Там той доста сериозно и подробно технически разглежда bloat на ниво, близко до това на администраторите.

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

Въпроси

Благодаря за доклада! Говорехте за това как могат да се идентифицират проблеми. А как могат да се предотвратят? Тоест, имах ситуация, когато заявките забавяха не само заради достъп до определени външни услуги. Имаха и много сложни joins. Имаше и малки безобидни запитвания, които дни наред висяха, а след това започваха да причиняват проблеми. Тоест, много прилича на описаното от вас. Как може да се следи това? Да се седи и постоянно да се следи коя заявка е забавена? Как може да се предотврати това?

В този случай - това е задача за администраторите на вашата компания, не непременно за DBA.

Аз съм администратор.

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

Трябва ли да влизам и да гледам на всеки 5 минути?

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

Има ли ясни причини защо това се случва?

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

Благодаря за доклада! Исках да уточня за инструмента pg_repack. Ако той не прави ексклузивна блокировка, тогава...

Той прави ексклузивна блокировка.

… то потенциално мога да загубя данни. Моето приложение не трябва ли да записва нищо през това време?

Не, то спокойно работи с таблицата, тоест pg_repack преди всичко прехвърля живите редове, които има. Разбира се, там има някакво записване в таблицата. Той просто добавя този край.

Тоест, той накрая все пак го прави?

В края той взема ексклузивна блокировка, за да замени тези файлове.

Това ще бъде по-бързо от VACUUM FULL?

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

Здравейте! Разказвахте за работата на автоклинирането. Имаше графика с червени, жълти и зелени клетки за записване. Тоест жълтите – той ги е маркирал като изтрити. И следователно в тях можете ли да запишете нещо ново?

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

Разбрах. Но въпросът не е точно за това. Не завърших. Да предположим, че имаме таблица. В нея има полета с променлив размер. И ако се опитам да вмъкна нещо ново, то може просто да не влезе в старото поле.

Не, там в какъвто и да е случай цялата редица се обновява. В Postgres има два модела за съхранение на данни. Той избира в зависимост от типа данни. Има данни, които се съхраняват директно в таблицата, а има и tos-данни. Това са големи обеми данни: текст, json. Те се съхраняват в отделни таблици. И по тези таблици се прави същото с bloat, т. е. всичко същото. Просто те са преместени отделно.

Благодаря за доклада! В каква степен е приемливо да се използват ограничени времеви заявки с statement timeout?

Много е приемливо. Ние го използваме навсякъде. И тъй като нямаме свои услуги, а предлагаме отдалечена поддръжка, имаме доста разнообразни клиенти. Всички са напълно удовлетворени от това. Т. е. имаме задачи в cron, които проверяват. Просто с клиента се уточнява максималната продължителност на сесиите, под която не се прекъсват. Това може да бъде минута, може да бъде 10 минути. Зависи от натоварването на базата и нейната цел. Но при всички използваме pg_stat_activity.

Благодаря за доклада! Опитвам се да приложа вашия доклад в моите приложения. И сякаш навсякъде започваме транзакция, навсякъде я завършваме явно. Ако възникне някакъв exception, rollback все пак се случва. И тук се замислих. В крайна сметка, може ли транзакцията да стартира неявно. Това е подслон за дамата, вероятно. Ако просто правя обновление на записа, транзакцията ще стартира в PostgreSQL и ще приключи само когато връзката бъде прекъсната?

Ако говорите на ниво приложение, то зависи от драйвера, който използвате, от ORM, който се използва. Има много настройки. Ако auto commit on е включен, транзакцията стартира и веднага се затваря.

Т. е. тя се затваря веднага след обновлението?

Това зависи от настройките. Една настройка, която споменах, е auto commit on. Тя е доста разпространена. Ако е включена, транзакцията стартира и приключва. Ако не сте казали явно „start transaction“ и „end transaction“, а просто сте стартирали заявка в сесията.

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

Мястото на сървъра наистина трябва да се наблюдава.

Например, DBA отиде да пие чай, беше на курорт и т. н.

Когато се създава файлова система, понеже се създава известно резервно пространство, където не се записват данни.

А ако е съвсем нула?

То там се нарича резервно пространство, т.е. може да бъде освободено и в зависимост от това колко голямо е създадено, получавате свободно място. По подразбиране не знам колко е там. А в друг случай – да се доставят дискове, за да имате място за възстановителна операция. Може да се изтрие някаква таблица, която наистина не ви е необходима.

Няма ли други инструменти?

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

Благодаря за доклада! Имам два въпроса. Първо, вие демонстрирахте слайдове, където показахте, че в случай на зависнали транзакции нараства както обемът на табличното пространство, така и размерът на индекса. А какво става с индекса?

Те също се пакетират.

Но вакуумът не засяга индекса?

Някои работят с индекса. Например, pg_rapack, pgcompacttable. Вакуума пресъздава индексите, засяга ги. Същността на VACUUM FULL е да презапише всичко, т.е. той работи с всички.

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

В какво се състои конфликтът в репликацията? Имаме Мастър, на който протичат процеси. При нас протича автовакуум. Автовакуумът по същество какво прави? Изтрива стари редове. Ако по това време на репликата има заявка, която чете тези стари редове, а на Мастъра е възникнала ситуация, че автовакуумът е маркирал тези редове като възможни за презапис, то ние ги презаписваме. И при нас е дошъл пакет данни, когато трябва да презапишем тези редове, които са необходими за заявката на репликата, то процесът на репликация ще изчака указаното време, което сте настроили. И след това PostgreSQL ще реши кое е по-важно за него. А репликацията за него е по-важна от заявката и той ще отстрани заявката, за да извърши тези промени на репликата.

Андрей, имам въпрос. Тези прекрасни графики, които показа по време на презентацията, резултат ли са от работата на някакъв ваш инструмент? С какво са изградени графиците?

Това е услуга Okmeter.

Това търговски продукт ли е?

Да. Това е търговски продукт.

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