Здравјето на индексите во PostgreSQL низ очите на развивачот на Java

Еј.

Моето име е Вања и јас сум развивач на Java. Се случува да работам многу со PostgreSQL - поставување на базата на податоци, оптимизирање на структурата, перформансите и играње малку DBA за време на викендите.

Неодамна средив неколку бази на податоци во нашите микросервиси и напишав java библиотека стр-индекс-здравје, што ја олеснува оваа работа, ми заштедува време и ми помага да избегнам некои вообичаени грешки направени од програмерите. Токму за оваа библиотека ќе зборуваме денес.

Општи услови

Главната верзија на PostgreSQL со која работам е 10. Сите SQL прашања што ги користам се тестираат и на верзијата 11. Минималната поддржана верзија е 9.6.

праисторијата

Сè започна пред речиси една година со ситуација која ми беше чудна: конкурентното креирање на индекс од ведро небо заврши со грешка. Самиот индекс, како и обично, остана во базата на податоци во неважечка состојба. Анализата на дневниците покажа недостиг temp_file_limit. И тргнуваме... Копајќи подлабоко, открив цел куп проблеми во конфигурацијата на базата на податоци и, засукајќи ги ракавите, почнав да ги поправам со сјај во очите.

Проблем еден - стандардна конфигурација

Веројатно сите се прилично уморни од метафората за Postgres, која може да се користи на кафемат, но... стандардната конфигурација навистина покренува голем број прашања. Како минимум, вреди да се обрне внимание одржување_работа_мем, temp_file_limit, изјава_време и lock_timeout.

Во нашиот случај одржување_работа_мем беше стандардно 64 MB, и temp_file_limit нешто околу 2 GB - едноставно немавме доволно меморија за да создадеме индекс на голема маса.

Затоа, во стр-индекс-здравје Собрав серија клуч, според мене, параметрите што треба да се конфигурираат за секоја база на податоци.

Проблем два - дупликат индекси

Нашите бази на податоци живеат на SSD-дискови, а ние ги користиме HA-конфигурација со повеќе центри за податоци, главен хост и n-број на реплики. Просторот на дискот е многу вреден ресурс за нас; тоа не е помалку важно од перформансите и потрошувачката на процесорот. Затоа, од една страна, ни требаат индекси за брзо читање, а од друга страна, не сакаме да гледаме непотребни индекси во базата на податоци, бидејќи тие јадат простор и го забавуваат ажурирањето на податоците.

И сега, откако обнови сè неважечки индекси и имајќи доволно видено извештаи од Олег Бартунов, решив да организирам „одлична“ чистка. Се испостави дека програмерите не сакаат да читаат документација за базата на податоци. Не им се допаѓа многу. Поради ова, се појавуваат две типични грешки - рачно креиран индекс на примарен клуч и сличен „рачен“ индекс на единствена колона. Факт е дека тие не се потребни - Postgres ќе направи сè сам. Таквите индекси можат безбедно да се избришат, а за оваа намена се појавија дијагностика дуплирани_индекси.

Задача трет - индекси кои се вкрстуваат

Повеќето програмери почетници создаваат индекси на една колона. Постепено, откако темелно го искусиле овој бизнис, луѓето почнуваат да ги оптимизираат своите прашања и да додаваат посложени индекси кои вклучуваат неколку колони. Вака се појавуваат индексите на колоните A, А + Б, А+Б+Ц и така натаму. Првите два од овие индекси можат безбедно да се исфрлат, бидејќи тие се префикси на третиот. Ова исто така заштедува многу простор на дискот и има дијагностика за ова пресечени_индекси.

Проблем четири - странски клучеви без индекси

Postgres ви овозможува да креирате ограничувања за странски клучеви без да наведете индекс за поддршка. Во многу ситуации тоа не е проблем, а можеби и не се манифестира... Засега...

Така беше и со нас: едноставно во одреден момент од времето, работата, која работи според распоредот и ја чисти базата на нарачки за тестирање, почна да ни „додава“ од главниот домаќин. Процесорот и IO отидоа на отпад, барањата забавија и беа истечени, услугата беше петстотини. Брза анализа pg_stat_activity покажа дека прашањата како:

delete from <table> where id in (…)

Во овој случај, се разбира, имаше индекс по id во целната табела и многу малку записи беа избришани според условот. Се чинеше дека сè треба да функционира, но, за жал, тоа не беше.

На помош дојде прекрасниот објасни анализирај и рече дека покрај бришењето на записите во целната табела, постои и референцијална проверка на интегритетот, а на една од поврзаните табели оваа проверка не успева секвенцијално скенирање поради немање соодветен индекс. Така се роди дијагностика странски_клучеви_без_индекс.

Задача пет – нулта вредност во индексите

Стандардно, Postgres вклучува нула вредности во индексите btree, но тие обично не се потребни таму. Затоа, вредно се обидувам да ги исфрлам овие нула (дијагностика индекси_со_нула_вредности), создавајќи делумни индекси на колони со поништување по тип where <A> is not null. На овој начин успеав да ја намалам големината на еден од нашите индекси од 1877 MB на 16 KB. И во една од услугите, големината на базата на податоци се намали вкупно за 16% (за 4.3 GB во апсолутни бројки) поради исклучувањето на нула вредности од индексите. Огромни заштеди во простор на дискот со многу едноставни модификации. 🙂

Проблем шест – недостаток на примарни клучеви

Поради природата на механизмот MVCC во Postgres можна е ваква ситуација надуеносткога големината на вашата маса рапидно расте поради голем број мртви записи. Наивно верував дека ова нема да ни се закани, и дека тоа нема да се случи со нашата база, бидејќи, леле!!!, ние сме нормални програмери... Колку бев глуп и наивен...

Еден ден, една прекрасна миграција ги зеде и ги ажурираше сите записи во голема и активно користена табела. Добивме +100 GB до големината на табелата од ведро небо. Беше проклето срам, но нашите несреќи не завршија тука. Откако автовакуумот на оваа маса заврши 15 часа подоцна, стана јасно дека физичката локација нема да се врати. Не можевме да ја прекинеме услугата и да го направиме ВАКУУМОТ ПОЛН, па решивме да користиме pg_repack. И тогаш се покажа дека pg_repack не знае како да обработува табели без примарен клуч или друго ограничување за уникатност, а нашата табела немаше примарен клуч. Така се роди дијагностика табели_без_примарен_клуч.

Во верзијата на библиотеката 0.1.5 Способноста да се собираат податоци од надуеноста на табелите и индексите и навремено да се одговори на нив е додадена.

Задачи седми и осми - недоволни индекси и неискористени индекси

Следниве две дијагностика се: табели_со_испуштени_индекси и неискористени_индекси – се појавија во конечна форма релативно неодамна. Поентата е дека тие не може само да се земат и додадат.

Како што веќе напишав, користиме конфигурација со неколку реплики, а оптоварувањето за читање на различни хостови е фундаментално различно. Како резултат на тоа, ситуацијата излегува дека некои табели и индекси на некои хостови практично не се користат, а за анализа треба да соберете статистика од сите домаќини во кластерот. Ресетирај статистика Ова е исто така неопходно за секој домаќин во кластерот; не можете да го направите ова само на главниот.

Овој пристап ни овозможи да заштедиме неколку десетици гигабајти со отстранување на индекси кои никогаш не биле користени, како и додавање на индекси што недостасуваат на ретко користените табели.

Како заклучок

Се разбира, за речиси сите дијагностика можете да конфигурирате листа за исклучување. На овој начин, можете брзо да спроведете проверки во вашата апликација, спречувајќи да се појавуваат нови грешки, а потоа постепено да ги поправате старите.

Некои дијагностика може да се изведат во функционални тестови веднаш по објавувањето на миграциите на базата на податоци. И ова е можеби една од најмоќните карактеристики на мојата библиотека. Пример за употреба може да се најде во демо.

Има смисла да се вршат проверки за неискористени или исчезнати индекси, како и за надуеност, само на вистинска база на податоци. Собраните вредности може да се евидентираат во Кликни куќа или испратени до системот за следење.

Навистина се надевам на тоа стр-индекс-здравје ќе биде корисно и барано. Можете исто така да придонесете за развојот на библиотеката со пријавување на проблеми што ги наоѓате и предлагање нова дијагностика.

Извор: www.habr.com