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

Здравейте.

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

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

Отказ от отговорност

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

праистория

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

Проблем първи - конфигурация по подразбиране

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

В нашия случай памет_за_работа по поддръжката беше 64 MB по подразбиране и ограничение_на_временен_файл нещо около 2 GB - просто нямахме достатъчно памет, за да създадем индекс на голяма таблица.

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

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

Нашите бази данни живеят на SSD устройства и ние използваме HA-конфигурация с множество центрове за данни, главен хост и n- брой реплики. Дисковото пространство е много ценен ресурс за нас; това е не по-малко важно от производителността и потреблението на процесора. Следователно, от една страна, имаме нужда от индекси за бързо четене, а от друга страна, не искаме да виждаме ненужни индекси в базата данни, тъй като те изяждат място и забавят актуализирането на данните.

И сега, след като възстанови всичко невалидни индекси и видя достатъчно доклади на Олег Бартунов, реших да организирам "голяма" чистка. Оказа се, че разработчиците не обичат да четат документация за бази данни. Не им харесва много. Поради това възникват две типични грешки - ръчно създаден индекс на първичен ключ и подобен „ръчен“ индекс на уникална колона. Факт е, че те не са необходими - Postgres ще направи всичко сам. Такива индекси могат безопасно да бъдат изтрити и за тази цел се появи диагностика дублирани_индекси.

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

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

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

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

Същото беше и с нас: просто в някакъв момент задача, изпълняваща се по график и изчистваща базата данни от тестови поръчки, започна да се „добавя“ към нас от главния хост. Процесорът и IO се губят, заявките се забавят и изтичат, услугата е петстотин. Бърз анализ pg_stat_activity показа, че заявки като:

delete from <table> where id in (…)

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

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

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

По подразбиране Postgres включва нулеви стойности в btree индекси, но те обикновено не са необходими там. Затова усърдно се опитвам да изхвърля тези нули (диагностика индекси_с_нулеви_стойности), създаване на частични индекси на nullable колони по тип where <A> is not null. По този начин успях да намаля размера на един от нашите индекси от 1877 MB на 16 KB. И в една от услугите размерът на базата данни намаля общо с 16% (с 4.3 GB в абсолютни числа) поради изключването на нулеви стойности от индексите. Огромни спестявания на дисково пространство с много лесни модификации. 🙂

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

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

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

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

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

Следните две диагностики са: таблици_с_липсващи_индекси и неизползвани_индекси – се появиха в окончателния си вид сравнително наскоро. Въпросът е, че те не могат просто да бъдат взети и добавени.

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

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

Като заключение

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

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

Има смисъл да се извършват проверки за неизползвани или липсващи индекси, както и за раздуване, само на реална база данни. Събраните стойности могат да бъдат записани в Щракнете върху Къща или изпратени до системата за мониторинг.

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

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