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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Когато извършваме запитване, базата данни трябва да прегледа всички редове: както активните, така и неактивните, за да намери необходимия ред. Ефектът от разширяване на таблицата с излишни данни се нарича "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. И бизнесът много се радва на това. Искат разнообразни отчети с много сложна аналитика.
• Отчетите отнемат часове, тъй като сложната аналитика не може да се изчисли за милисекунди. Ние, като добри служители, пишем код. Правим в приложението insert-и, че записът вървим на Мастера, а отчетите се изпълняват на репликата.
• Разпределяме натоварването.
• Всичко работи отлично. Ние сме велики.

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

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

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

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

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

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

Искаме всичко да бъде идеално. Продължаваме да търсим. Намираме страхотна настройка в интернет - hot_standby_feedback. Включваме я. Hot_standby_feedback ни позволява да забавим работата на авто-вакуума на Майстора. По този начин напълно се избавяме от конфликтите на репликацията. И всичко работи добре с отчетите.

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

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

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

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

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

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

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

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

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

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

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

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

История второ тук завърши. Преминаваме към третата история.

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

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

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

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

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

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

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

И това отново е блат, който ни разваля живота.

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

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

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

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

И при това няма да получим bloat и производителността няма да спадне.

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

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

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

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

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

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

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

Сега за това как да коригираме bloat:

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

Спасибо за доклада! Някъде приемливо ли е да се използва statement timeout за ограничаване на продължителността на запитванията?

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

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

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

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

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

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

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

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

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

А ако достигне изцяло нула?

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

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

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

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

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

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

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

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

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

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

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

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

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

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