Прывітанне.
Мяне клічуць Ваня, і я Java-распрацоўшчык. Так атрымалася, што я шмат працую з PostgreSQL – займаюся наладай БД, аптымізацыяй структуры, прадукцыйнасцю і крыху гуляю ў DBA па выходных.
За апошні час я давёў да ладу некалькі баз дадзеных у нашых мікрасэрвісах і напісаў java-бібліятэку , якая палягчае гэтую працу, эканоміць мой час і дапамагае пазбегнуць некаторых тыпавых памылак, якія дапускаюцца распрацоўшчыкамі. Менавіта пра гэтую бібліятэку сёння і пойдзе размова.
адмова
Асноўная версія PostgreSQL, з якой я працую, гэта 10-ка. Усе выкарыстоўваныя мною SQL-запыты таксама правераны на 11-й версіі. Мінімальная падтрымліваецца версія - гэта 9.6.
перадгісторыя
Пачалося ўсё амаль год таму з дзіўнай для мяне сітуацыі: канкурэнтнае стварэнне азначніка на роўным месцы завяршылася з памылкай. Сам індэкс, як водзіцца, у невалідным стане застаўся ў базе. Аналіз логаў паказаў недахоп . І панеслася… Капнуўшы глыбей, я выявіў цэлую кучу праблем у канфігурацыі БД і, закасаўшы рукавы, з бляскам у вачах прыняўся іх правіць.
Праблема першая - дэфолтная канфігурацыя
Мусіць, метафара пра Postgres, які можна запусціць на кававарцы, усім ужо ладна надакучыла, але… канфігурацыя па змаўчанні сапраўды выклікае шэраг пытанняў. Як мінімум, варта звярнуць увагу на maintenance_work_mem, temp_file_limit, statement_timeout и lock_timeout.
У нашым выпадку maintenance_work_mem быў па змаўчанні 64 Мбайта, а temp_file_limit нешта каля 2 Гбайт - нам банальна не хапала памяці для стварэння індэкса на вялікай табліцы.
Таму ў pg-index-health я сабраў шэраг , на мой погляд, параметраў, якія варта наладзіць пад кожную БД.
Праблема другая - дублюючыя індэксы
Нашы базы жывуць на SSD дысках, і мы выкарыстоўваем HA-канфігурацыю з некалькімі дата-цэнтрамі, майстар-хастом і n-най колькасцю рэплік. Месца на дыску - вельмі каштоўны рэсурс для нас; яно не менш важна, чым прадукцыйнасць і спажыванне CPU. Таму, з аднаго боку, нам патрэбныя індэксы для хуткага чытання, а з другога боку, мы не жадаем бачыць у БД лішніх азначнікаў, бо яны пажыраюць месца і запавольваюць абнаўленне дадзеных.
І вось, аднавіўшы ўсё і наглядзеўшыся , я вырашыў задаволіць «вялікую» чыстку. Аказалася, што распрацоўшчыкі не любяць чытаць дакументацыю да БД. Вельмі не кахаюць. З-за гэтага ўзнікаюць дзве тыпавыя памылкі - уручную створаны індэкс на першасны ключ і аналагічны "ручны" індэкс на унікальны слупок. Справа ў тым, што яны не патрэбныя – Postgres усё зробіць сам. Такія індэксы можна смела выдаляць, і для гэтага з'явілася дыягностыка. .
Праблема трэцяя - перасякальныя індэксы
Большасць пачаткоўцаў распрацоўшчыкаў стварае індэксы на адзін слупок. Паступова, як след паспрабаваўшы гэтую справу на густ, людзі пачынаюць аптымізаваць свае запыты і дадаваць больш складаныя індэксы, якія ўключаюць у сябе некалькі слупкоў. Так з'яўляюцца азначнікі на слупкі A, А + У, A + B + C і да т.п. Першыя два з гэтых азначнікаў можна адважна выкінуць, бо яны з'яўляюцца прэфіксамі трэцяга. Гэта таксама прыстойна эканоміць месца на дыску і для гэтага ёсць дыягностыка .
Праблема чацвёртая - знешнія ключы без індэксаў
Postgres дазваляе ствараць абмежаванні вонкавага ключа без указання які падтрымлівае азначніка. У многіх сітуацыях гэта не з'яўляецца праблемай, і нават можа сябе ніяк не праяўляць… Да пары да часу…
Так было і ў нас: проста ў нейкі момант часу job'а, якая выконваецца па раскладзе і ачышчальная базу ад тэставых замоў, стала "складаць" нам майстар хост. CPU і IO ляцелі ў паліцу, запыты тармазілі і перарываліся па таймаўце, сэрвіс пяцісот. Хуткі аналіз паказаў, што завісалі запыты віду:
delete from <table> where id in (…)Пры гэтым індэкс па id у мэтавай табліцы, натуральна, быў, і запісаў выдалялася па ўмове зусім няшмат. Здавалася, што ўсё павінна працаваць, але, нажаль, не працавала.
На дапамогу прыйшоў цудоўны explain analyze і распавёў, што апроч выдалення запісаў у мэтавай табліцы, яшчэ ідзе праверка спасылачнай цэласнасці, і на адной з злучаных табліц гэтая праверка звальваецца ў sequential scan з-за адсутнасці падыходнага азначніка. Так нарадзілася дыягностыка .
Праблема пятая - null value у індэксах
Па змаўчанні Postgres уключае null значэння ў btree-індэксы, але яны там, як правіла, не патрэбныя. Таму я старанна імкнуся гэтыя null'ы выкідваць (дыягностыка ), ствараючы частковыя індэксы на nullable-слупкі па тыпе where <A> is not null. Такім спосабам мне ўдалося скараціць памер аднаго з нашых індэксаў з 1877 Мбайт да 16 Кбайт. А ў адным з сэрвісаў памер БД сумарна паменшыўся на 16% (на 4.3/XNUMX ГБайт у абсалютных лічбах) за кошт выключэння null значэнняў з індэксаў. Каласальная эканомія дыскавай прасторы пры вельмі нескладаных дапрацоўках. 🙂
Праблема шостая - адсутнасць першасных ключоў
У сілу асаблівасцяў механізму магчыма такая сітуацыя, як , калі памер вашай табліцы хутка расце за кошт вялікай колькасці мёртвых запісаў. Я наіўна меркаваў, што нам гэта не пагражае, і што з нашай базай такога не здарыцца, бо мы, ого-го!!!, нармальныя ж распрацоўшчыкі… Якім дурным і наіўным я быў…
Аднаго дня адна цудоўная міграцыя ўзяла і абнавіла ўсе запісы ў вялікай табліцы, якая актыўна выкарыстоўваецца. Мы атрымалі 100 Гбайт да памеру табліцы на роўным месцы. Было страшэнна крыўдна, але нашы ліхтугі на гэтым не скончыліся. Пасля таго, як праз 15 гадзін завяршыўся аўтавакуум на гэтай табліцы, стала зразумела, што фізічнае месца не вернецца. Спыніць сэрвіс і зрабіць VACUUM FULL мы не маглі, таму было прынятае рашэнне выкарыстоўваць . І тут высветлілася, што pg_repack не ўмее апрацоўваць табліцы без першаснага ключа ці іншага абмежавання ўнікальнасці, а на нашай табліцы першаснага ключа не апынулася. Так нарадзілася дыягностыка .
У версіі бібліятэкі 0.1.5 дадалася магчымасць збіраць дадзеныя па bloat'у табліц і азначнікаў і своечасова рэагаваць на яго.
Праблемы сем і восем - недахоп індэксаў і невыкарыстоўваныя індэксы
Дзве наступныя дыягностыкі - и – у сваім фінальным выглядзе з'явіліся адносна нядаўна. Справа ў тым, што іх нельга было проста так узяць і дадаць.
Як я ўжо пісаў, мы выкарыстоўваем канфігурацыю з некалькімі рэплікамі, і якая чытае нагрузка на розных хастах прынцыпова розная. У выніку атрымліваецца сітуацыя, што нейкія табліцы і азначнікі на нейкіх хастах практычна не выкарыстоўваюцца, і для аналізу трэба збіраць статыстыку са ўсіх хастоў у кластары. таксама трэба на кожным хасце ў кластары, нельга зрабіць гэта толькі на майстру.
Такі падыход дазволіў нам зэканоміць некалькі дзясяткаў гігабайт за кошт выдалення індэксаў, якія ніколі не выкарыстоўваліся, а таксама дадаць адсутнічаюць індэксы на рэдка выкарыстоўваюцца табліцы.
У якасці заключэння
Зразумела, практычна для ўсіх дыягностык можна наладзіць . Такім чынам можна хутка ўкараніць праверкі ў вашым дадатку, прадухіліўшы з'яўленне новых памылак, і затым паступова выпраўляць старыя.
Частка дыягностык могуць выконвацца ўжо ў функцыянальных тэстах адразу пасля накату міграцый БД. І гэта, мабыць, адна з самых магутных магчымасцей маёй бібліятэкі. Прыклад выкарыстання можна паглядзець у .
Праверкі на якія не выкарыстоўваюцца або адсутныя індэксы, а таксама на bloat, мае сэнс выконваць толькі на рэальнай БД. Сабраныя значэння могуць быць запісаны ў або адпраўлены ў сістэму маніторынгу.
Я вельмі спадзяюся, што pg-index-health будзе карысная і запатрабаваная. Вы таксама можаце паспрыяць развіццю бібліятэкі, паведамляючы аб выяўленых праблемах і прапаноўваючы новыя дыягностыкі.
Крыніца: habr.com