Здравето на индексите в PostgreSQL през погледа на Java-разработчика

Здравей.

Казвам се Ваня и съм Java-разработчик. Случи се така, че работя много с PostgreSQL – занимавам се с настройка на бази данни, оптимизация на структура, производителност и малко играя на DBA през уикендите.

През последното време приведох в ред няколко бази данни в нашите микросервизи и написах java-библиотека pg-index-health, която улеснява тази работа, спестява времето ми и помага да избегна някои типови грешки, допускани от разработчиците. Именно за тази библиотека ще става дума днес.

Отказ

Основната версия на PostgreSQL, с която работя, е 10. Всички SQL запитвания, които използвам, са проверени и на 11-та версия. Минималната поддържана версия е 9.6.

Предистория

Всичко започна преди почти година със странна за мен ситуация: конкурентното създаване на индекс на празно място приключи с грешка. Самият индекс, както обикновено, остана в невалидно състояние в базата. Анализът на логовете показа недостиг на temp_file_limit. И започнаха проблемите… Копавайки по-дълбоко, открих цял куп проблеми в конфигурацията на базата данни и, засучвайки ръкави, с блясък в очите, започнах да ги поправям.

Проблем номер едно – дефолтната конфигурация

Вероятно метафората за 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 всичко ще направи сам. Такива индекси могат спокойно да бъдат премахнати, и за това има диагностика. duplicated_indexes.

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

Повечето начинаещи разработчици създават индекси за една колона. Постепенно, след като разберат как върви работата, хората започват да оптимизират заявките си и добавят по-сложни индекси, включващи няколко колони. Така се появяват индекси за колоните A, A+B, A+B+C и т.н. Първите два от тези индекси могат смело да бъдат изхвърлени, тъй като те са префикси на третия. Това също спестява доста място на диска и за това има диагностика. intersected_indexes.

Проблем четвърти – външни ключове без индекси

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

Така беше и при нас: просто в един момент job’а, изпълняваща се по график и почистваща базата от тестови поръчки, започна да „събира“ нашия master host. CPU и IO летяха нагоре, заявките забавяха и прекъсваха по таймаут, услугата не работеше. Бързият анализ pg_stat_activity показа, че зависат заявки от вида:

изтриване от <table> където id в (…)

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

На помощ дойде чудесното explain analyze и разказа, че освен изтриването на записи в целевата таблица, още става проверка за референтна цялост, и на една от свързаните таблици тази проверка пада в sequential scan поради липса на подходящ индекс. Така се роди диагностиката foreign_keys_without_index.

Проблем пети – null стойност в индекси

По подразбиране Postgres включва null стойности в btree-индекси, но те там, като правило, не са нужни. Затова усърдно се опитвам да изхвърлям тези null-ове (диагностика indexes_with_null_values), създавайки частични индекси на nullable-колони по тип where is not null. По този начин успях да намаля размера на един от нашите индекси от 1877 Мбайта на 16 Кбайта. А в един от сервисите размерът на БД намаля общо с 16% (с 4.3 Гбайта в абсолютни цифри) благодарение на изключването на null стойности от индексите. Огромна икономия на дисково пространство при сравнително несложни поправки. 🙂

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

В резултат на особеностите на механизма MVCC в Postgres’е може да възникне такава ситуация, като bloat, когато размерът на вашата таблица бързо расте заради голямото количество мъртви записи. Наивно си мислех, че ние сме застраховани от това, и че с нашата база такова нещо няма да се случи, защото ние, охо-хо!!!, сме нормални разработчици… Колко глупав и наивен бях…

В един прекрасен ден, една чудесна миграция просто обнови всички записи в голямата и активно използвана таблица. Получихме +100 Гбайта размер на таблицата от нищото. Беше ужасно разочароващо, но нашите злоключения не приключиха тук. След 15 часа, когато автопочистването на тази таблица приключи, стана ясно, че физическото пространство няма да се върне. Не можахме да спрем услугата и да направим VACUUM FULL, затова беше взето решение да се използва pg_repack. И тук се оказа, че pg_repack не може да обработва таблици без първичен ключ или друга уникална ограниченост, а в нашата таблица нямаше първичен ключ. Така се роди диагностиката tables_without_primary_key.

В версията на библиотеката 0.1.5 бе добавена възможност за събиране на данни по bloat на таблиците и индексите и своевременно реагиране на него.

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

Две следващи диагностики — tables_with_missing_indexes и unused_indexes – в тяхната окончателна форма се появиха сравнително скоро. Фактът е, че не можеше просто така да ги добавим.

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

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

В заключение

Разбира се, практически за всички диагностики може да бъде настроен списък с изключения. По този начин бързо можете да внедрите проверки в приложението си, предотвратявайки появата на нови грешки, а след това постепенно да коригирате старите.

Част от диагностиците могат да се изпълняват вече във функционалните тестове веднага след внедряване на миграции на БД. И това, може би, е една от най-силните функции на моята библиотека. Можете да видите пример за използване в демо.

Проверки за неиспользвани или отсъстващи индекси, а също така за блоат, има смисъл да се извършват само на реална БД. Събраните стойности могат да бъдат записани в ClickHouse или изпратени в система за мониторинг.

Много се надявам, че pg-index-health ще бъде полезна и търсена. Можете също така да допринесете за развитието на библиотеката, като докладвате за открити проблеми и предлагате нови диагностики.

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