Ћао
Моје име је Вања и ја сам Јава програмер. Десило се да доста радим са ПостгреСКЛ-ом - постављам базу података, оптимизујем структуру, перформансе и играм мало ДБА викендом.
Недавно сам средио неколико база података у нашим микросервисима и написао јава библиотеку , што олакшава овај рад, штеди ми време и помаже ми да избегнем неке уобичајене грешке које праве програмери. Управо о овој библиотеци ћемо данас говорити.
Одрицање од одговорности
Главна верзија ПостгреСКЛ-а са којом радим је 10. Сви СКЛ упити које користим су такође тестирани на верзији 11. Минимална подржана верзија је 9.6.
praistorija
Све је почело пре скоро годину дана са ситуацијом која ми је била чудна: такмичарско креирање индекса из ведра неба се завршило грешком. Сам индекс је, као и обично, остао у бази података у неисправном стању. Анализа дневника је показала недостатак . И крећемо... Копајући дубље, открио сам гомилу проблема у конфигурацији базе података и, засукавши рукаве, почео да их поправљам са искрицом у очима.
Први проблем - подразумевана конфигурација
Вероватно су сви прилично уморни од метафоре о Постгресу, који се може покренути на апарату за кафу, али... подразумевана конфигурација заиста поставља бројна питања. У најмању руку, вреди обратити пажњу одржавање_рад_мем, темп_филе_лимит, статемент_тимеоут и лоцк_тимеоут.
У нашем случају одржавање_рад_мем била је подразумевана 64 МБ, и темп_филе_лимит нешто око 2 ГБ – једноставно нисмо имали довољно меморије да направимо индекс на великој табели.
Стога, у пг-индекс-здравље Сакупио сам серију , по мом мишљењу, параметри које треба конфигурисати за сваку базу података.
Проблем два - дупли индекси
Наше базе података живе на ССД дисковима и користимо их HA-конфигурација са више центара података, главни хост и n-број реплика. Простор на диску је веома вредан ресурс за нас; није ништа мање важно од перформанси и потрошње ЦПУ-а. Дакле, с једне стране су нам потребни индекси за брзо читање, а са друге стране, не желимо да видимо непотребне индексе у бази података, јер једу простор и успоравају ажурирање података.
А сада, обновивши све и видевши довољно , одлучио сам да организујем „велику“ чистку. Испоставило се да програмери не воле да читају документацију базе података. Не свиђа им се много. Због тога се јављају две типичне грешке - ручно креиран индекс на примарном кључу и сличан „ручни“ индекс на јединственој колони. Чињеница је да они нису потребни - Постгрес ће све урадити сам. Такви индекси се могу безбедно избрисати, а у ту сврху се појавила дијагностика .
Задатак трећи - индекси који се секу
Већина програмера почетника креира индексе на једној колони. Постепено, након што су темељно искусили овај посао, људи почињу да оптимизују своје упите и додају сложеније индексе који укључују неколико колона. Овако се појављују индекси на колонама A, А + Б, А+Б+Ц и тако даље. Прва два од ових индекса могу се безбедно избацити, јер су префикси трећег. Ово такође штеди много простора на диску и постоји дијагностика за ово .
Проблем четврти - страни кључеви без индекса
Постгрес вам омогућава да креирате ограничења страног кључа без навођења индекса подршке. У многим ситуацијама то није проблем, а можда се и не манифестује... За сада...
Исто је било и са нама: само што је у неком тренутку главни хост почео да нам „додаје“ посао који се одвија по распореду и брише базу података тестних налога. ЦПУ и ИО су пропали, захтеви су успорени и истекли су, услуга је била пет стотина. Брза анализа показао да су упити попут:
delete from <table> where id in (…)У овом случају, наравно, постојао је индекс по ИД-у у циљној табели и врло мало записа је обрисано у складу са условом. Чинило се да би све требало да функционише, али, нажалост, није.
Чудесни је притекао у помоћ објаснити анализирати и рекао да поред брисања записа у циљној табели постоји и провера референтног интегритета, а на једној од повезаних табела ова провера не успе секвенцијално скенирање због недостатка одговарајућег индекса. Тако је рођена дијагностика .
Проблем пети – нулта вредност у индексима
Постгрес подразумевано укључује нулте вредности у бтрее индексе, али тамо обично нису потребне. Стога, марљиво покушавам да избацим ове нуле (дијагностика ), креирање делимичних индекса на колонама са нултим вредностима по типу where <A> is not null. На овај начин сам успео да смањим величину једног од наших индекса са 1877 МБ на 16 КБ. А у једној од услуга, величина базе података је смањена укупно за 16% (за 4.3 ГБ у апсолутним бројевима) због искључивања нул вредности из индекса. Огромна уштеда на диску са врло једноставним модификацијама. 🙂
Шести проблем – недостатак примарних кључева
Због природе механизма могућа је оваква ситуација када величина ваше табеле брзо расте због великог броја мртвих записа. Наивно сам веровао да нам ово неће угрозити, и да се то неће десити нашој бази, јер смо ми, вау!!!, нормални програмери... Како сам био глуп и наиван...
Једног дана, једна дивна миграција је узела и ажурирала све записе у великој и активно коришћеној табели. Добили смо +100 ГБ до величине табеле из ведра неба. Била је то проклета срамота, али нашим незгодама није био крај. Након што се аутовакум на овом столу завршио 15 сати касније, постало је јасно да се физичка локација неће вратити. Нисмо могли да зауставимо услугу и направимо ВАЦУУМ ФУЛЛ, па смо одлучили да користимо . А онда се испоставило да пг_репацк не зна како да обрађује табеле без примарног кључа или другог ограничења јединствености, а наша табела није имала примарни кључ. Тако је рођена дијагностика .
У библиотечкој верзији 0.1.5 Додата је могућност прикупљања података из надувености табела и индекса и благовременог реаговања на њих.
Задаци седми и осми - недовољни индекси и неискоришћени индекси
Следеће две дијагностике су: и – појавиле су се у свом коначном облику релативно недавно. Поента је да се не могу само узети и додати.
Као што сам већ написао, користимо конфигурацију са неколико реплика, а оптерећење читања на различитим хостовима је фундаментално различито. Као резултат тога, испоставља се да се неке табеле и индекси на неким хостовима практично не користе, а за анализу морате прикупити статистику са свих хостова у кластеру. Ово је такође неопходно на сваком хосту у кластеру; ово не можете учинити само на мастеру.
Овај приступ нам је омогућио да уштедимо неколико десетина гигабајта уклањањем индекса који никада нису коришћени, као и додавањем индекса који недостају у ретко коришћене табеле.
Као закључак
Наравно, за скоро сву дијагностику можете конфигурисати . На овај начин можете брзо да примените провере у својој апликацији, спречавајући појављивање нових грешака, а затим постепено поправљате старе.
Нека дијагностика се може извршити у функционалним тестовима одмах након увођења миграција базе података. А ово је можда једна од најмоћнијих карактеристика моје библиотеке. Пример употребе може се наћи у .
Има смисла вршити провере за неискоришћене или недостајуће индексе, као и за надувавање, само на правој бази података. Прикупљене вредности се могу забележити у или послати у систем за праћење.
Заиста се томе надам пг-индекс-здравље биће корисно и тражено. Такође можете допринети развоју библиотеке тако што ћете пријавити проблеме које пронађете и предложити нову дијагностику.
Извор: ввв.хабр.цом