Привіт.
Мене звуть Ваня, і я Java-розробник. Так вийшло, що я багато працюю з PostgreSQL - займаюся налаштуванням БД, оптимізацією структури, продуктивністю і трохи граю в DBA у вихідні.
Останнім часом я упорядкував кілька баз даних у наших мікросервісах і написав java-бібліотеку , що полегшує цю роботу, заощаджує мій час і допомагає уникнути деяких типових помилок, які допускаються розробниками. Саме про цю бібліотеку сьогодні й йтиметься.
відмова
Основна версія PostgreSQL, з якою я працюю, це десятка. Усі SQL-запити, що використовуються мною, також перевірені на 10-й версії. Мінімальна підтримувана версія – це 11.
Передісторія
Почалося майже рік тому з дивною для мене ситуації: конкурентне створення індексу на рівному місці завершилося з помилкою. Сам індекс, як правило, у невалідному стані залишився в базі. Аналіз логів показав нестачу . І помчало ... Копнувши глибше, я виявив цілу купу проблем в конфігурації БД і, засукавши рукави, з блиском в очах почав їх лагодити.
Проблема перша – дефолтна конфігурація
Напевно, метафора про Postgres, який можна запустити на кавоварці, всім вже неабияк набридла, але... стандартна конфігурація дійсно викликає низку питань. Як мінімум, варто звернути увагу на maintenance_work_mem, temp_file_limit, оператор_тайм-аут и 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'а, що виконується за розкладом і очищає базу від тестових замовлень, почала «складати» майстер хост. CPU та IO відлітали в полицю, запити гальмували і переривалися по таймууту, сервіс п'ятисот. Швидкий аналіз показав, що зависали запити виду:
delete from <table> where id in (…)У цьому індекс по id в цільової таблиці, звісно, був, і записів видалялося за умов зовсім небагато. Здавалося, що все має працювати, але, на жаль, не працювало.
На допомогу прийшов чудовий explain analyze і розповів, що крім видалення записів у цільовій таблиці, ще йде перевірка цілісності посилання, і на одній із зв'язаних таблиць ця перевірка звалюється в sequential scan через відсутність відповідного індексу. Так народилася діагностика .
Проблема п'ята - null value в індексах
За замовчуванням Postgres включає null значення btree-індекси, але вони там, як правило, не потрібні. Тому я старанно ці null'и викидати (діагностика ), створюючи часткові індекси на nullable-стовпці за типом where <A> is not null. У такий спосіб мені вдалося скоротити розмір одного з наших індексів із 1877 Мбайт до 16 Кбайт. А в одному з сервісів обсяг БД сумарно зменшився на 16% (на 4.3 Гбайт в абсолютних цифрах) за рахунок виключення null значень з індексів. Колосальна економія дискового простору за дуже нескладних доопрацювань. 🙂
Проблема шоста – відсутність первинних ключів
В силу особливостей механізму можлива така ситуація, як , коли розмір таблиці швидко зростає за рахунок великої кількості мертвих записів. Я наївно вважав, що нам це не загрожує, і що з нашою базою такого не станеться, адже ми, ого-го!!!, нормальні ж розробники… Яким дурним і наївним я був…
Одного чудового дня одна чудова міграція взяла і оновила всі записи у великій таблиці, що активно використовується. Ми отримали +100 Гб до розміру таблиці на рівному місці. Було дуже образливо, але наші пригоди на цьому не закінчилися. Після того, як через 15 годину завершився автовакуум на цій таблиці, стало зрозуміло, що фізичне місце не повернеться. Зупинити сервіс та зробити VACUUM FULL ми не могли, тому було прийнято рішення використати . І тут з'ясувалося, що pg_repack не вміє обробляти таблиці без первинного ключа чи іншого обмеження унікальності, але в нашої таблиці первинного ключа не виявилося. Так народилася діагностика .
У версії бібліотеки 0.1.5 додалася можливість збирати дані по bloat'у таблиць та індексів та своєчасно реагувати на нього.
Проблеми сім і вісім – брак індексів та індекси, що не використовуються.
Дві наступні діагностики и - У своєму фінальному вигляді з'явилися відносно недавно. Справа в тому, що їх не можна було просто так взяти та додати.
Як я вже писав, ми використовуємо конфігурацію з кількома репліками, і навантаження, що читає, на різних хостах принципово різна. У результаті виходить ситуація, що якісь таблиці та індекси на якихось хостах практично не використовуються, і для аналізу потрібно збирати статистику з усіх хостів у кластері. теж потрібно кожному хості в кластері, не можна зробити це лише з майстра.
Такий підхід дозволив нам заощадити кілька десятків гігабайт за рахунок видалення індексів, які ніколи не використовувалися, а також додати недостатні індекси на таблиці, що рідко використовуються.
В якості висновку
Зрозуміло, що практично для всіх діагностик можна налаштувати . Таким чином можна швидко впровадити перевірки у вашому додатку, запобігши появі нових помилок, а потім поступово виправляти старі.
Частина діагностик можуть виконуватися вже у функціональних тестах одразу після накату міграцій БД. І це, мабуть, одна із найпотужніших можливостей моєї бібліотеки. Приклад використання можна переглянути в .
Перевірки на індекси, що не використовуються або відсутні, а також на bloat, має сенс виконувати тільки на реальній БД. Зібрані значення можуть бути записані в або відправлені до системи моніторингу.
Я дуже сподіваюся, що pg-index-health буде корисна та затребувана. Ви також можете сприяти розвитку бібліотеки, повідомляючи про виявлені проблеми та пропонуючи нові діагностики.
Джерело: habr.com