Здравейте.
Мен казват Ваня и съм 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, A+B+C и т.н. Първите два от тези индекси могат смело да се изхвърлят, тъй като те са префикси на третия. Това също така значително спестява място на диска и за това има диагностика. .
Четвъртият проблем – външни ключове без индекси.
Postgres позволява създаването на ограничения на външните ключове без указване на поддържащ индекс. В много ситуации това не е проблем и дори може да не се проявява... До определен момент...
Така беше и при нас: просто в някакъв момент работата на job’a, изпълняваща се по график и почистване на базата от тестови поръчки, започна да „слага“ нашия майстор хост. CPU и IO отлетяха в небето, заявките се забавяха и спираха по таймаут, услугата предизвика грешка 500. Бързият анализ показа, че заявките от тип:
изтрий от <table> където id в (…)В същото време индексът по id в целевата таблица, естествено, съществуваше и записите се изтриваха по условие съвсем малко. Изглеждаше, че всичко трябва да работи, но, за съжаление, не работеше.
На помощ дойде чудото explain analyze и показа, че освен изтриването на записи в целевата таблица, все още се извършва проверка на референтната цялост и на една от свързаните таблици тази проверка се провали в sequential scan поради отсъствието на подходящ индекс. Така се роди диагностиката .
Петият проблем – null стойности в индексите.
По подразбиране Postgres включва null стойности в btree-индексите, но те там, обикновено, не са нужни. Затова усърдно се опитвам да изхвърлям тези null стойности (диагностика ), създавайки частични индекси на nullable-стълбове по тип where is not null. По този начин успях да намаля размера на един от нашите индекси от 1877 МВ до 16 КВ. А в един от сървърите размерът на БД намаля сумарно с 16% (с 4.3 ГБ в абсолютни цифри) благодарение на изключването на null стойности от индексите. Огромна икономия на дисково пространство при съвсем несложни корекции. 🙂
Проблема шеста – отсъствие на първични ключове
Поради характеристиките на механизма може да възникне такава ситуация, като , когато размерът на вашата таблица бързо нараства заради голямо количество мъртви записи. Наивно предполагах, че не ни засяга, и че с нашата база такова нещо няма да се случи, понеже ние, ого-го!!!, сме нормални разработчици... Какъв глупак и наивник бях...
В един прекрасен ден една чудесна миграция обнови всички записи в голяма и активно използвана таблица. Получихме +100 ГБ размер на таблицата изневиделица. Беше изключително обидно, но нашите злоключения не свършиха тук. След като след 15 часа автопочистването на таблицата приключи, стана ясно, че физическото място няма да се върне. Не можехме да спрем услугата и да направим VACUUM FULL, затова беше взето решение да използваме . И тук се оказа, че pg_repack не може да обработва таблици без първичен ключ или друго ограничение за уникалност, а на нашата таблица не се оказа първичен ключ. Така се роди диагностиката .
В версията на библиотеката 0.1.5 беше добавена възможност за събиране на данни относно блоата на таблици и индекси и своевременно реагиране на него.
Проблеми седем и осем – липса на индекси и неизползвани индекси
Две следващи диагностики – и – в окончателния си вид се появиха относително наскоро. Работата е там, че не можеше просто така да бъдат добавени.
Както вече споменах, използваме конфигурация с многобройни реплики, и натоварването на четене на различни хостове е принципно различно. В крайна сметка получаваме ситуация, в която някои таблици и индекси на някои хостове практически не се използват, и за анализ е необходимо да се събира статистика от всички хостове в клъстера. също е необходимо на всеки хост в клъстера, не може да се направи само на майстора.
Този подход ни позволи да спестим десетки гигабайти, като премахнахме индекси, които никога не са използвани, и добавихме липсващи индекси към рядко използвани таблици.
В заключение
Разбира се, почти за всички диагностики може да се настрои . Така бързо можете да внедрите проверки в приложението си, предотвратявайки появата на нови грешки, и след това постепенно да коригирате старите.
Част от диагностиките могат да се извършват веднага в функционалните тестове след внедряване на миграциите на БД. И това, вероятно, е една от най-силните възможности на моята библиотека. Пример за използване може да се види в .
Проверки за неизползвани или липсващи индекси, както и за bloat, е разумно да се извършват само на реална БД. Събраните стойности могат да бъдат записани в или изпратени в системата за мониторинг.
Силно се надявам, че pg-index-health ще бъде полезна и търсена. Можете също така да допринесете за развитието на библиотеката, като докладвате за открити проблеми и предлагате нови диагностики.
Източник: habr.com
