Розшифровка доповіді 2015 року Олексія Лєсовського "Deep dive into PostgreSQL internal statistics"
Disclaimer від автора доповіді: Зауважу, що доповідь ця датована листопадом 2015 року - минуло більше 4 років і пройшло багато часу. Версія 9.4, що розглядається в доповіді, вже не підтримується. За минулі 4 роки вийшло 5 нових релізів у яких з'явилася маса нововведень, покращень та змін щодо статистики та частина матеріалу застаріла і не актуальна. У міру реву я постарався відзначити ці місця щоб не вводити тебе читач в оману. Переписувати ці місця я не став, їх дуже багато і вийде в результаті зовсім інший доповідь.
СУБД PostgreSQL - це величезний механізм, при цьому складається цей механізм з багатьох підсистем, від злагодженої роботи яких залежить продуктивність СУБД. У процесі експлуатації забезпечується збір статистики та інформації про роботу компонентів, що дозволяє оцінити ефективність PostgreSQL та вжити заходів для підвищення продуктивності. Однак цієї інформації дуже багато і представлена вона в досить спрощеному вигляді. Обробка цієї інформації та її інтерпретація часом зовсім нетривіальне завдання, а "зоопарк" інструментів і утиліт запросто поставить у глухий кут навіть просунутого DBA.
Добридень! Мене звуть Алексей. Як Ілля сказав, я розповідатиму про статистику PostgreSQL.
Статистика активності PostgreSQL. PostgreSQL має дві статистики. Статистика активності, про яку мова йде. І статистика планувальника щодо розподілу даних. Я розповідатиму саме про статистику активності PostgreSQL, яка дозволяє нам судити про продуктивність і якось її покращувати.
Розповім, як ефективно використовувати статистику для вирішення різних проблем, які у вас виникають або можуть виникнути.
Чого не буде у доповіді? У доповіді не стосуватимуся статистики планувальника, т.к. це окрема тема на окрему доповідь про те, як дані зберігаються в базі і про те, як планувальник запитів отримує уявлення про якісні та кількісні характеристики цих даних.
І не буде оглядів інструментів, я не порівнюватиму один продукт з іншим. Жодної реклами не буде. Відкинемо це.
Я хочу показати, що використовувати статистику – це корисно. Це потрібно. Використовувати її не страшно. Нам знадобиться лише звичайний SQL і базові знання про SQL.
І поговоримо, яку статистику обиратиме для вирішення проблем.
Якщо ми подивимося на PostgreSQL і в операційній системі запустимо команду для перегляду процесів, побачимо "чорну скриньку". Ми побачимо якісь процеси, які щось роблять, і ми за назвою можемо приблизно уявити, що вони роблять, чим займаються. Але, насправді, це темний ящик, всередину ми зазирнути не можемо.
Ми можемо подивитися навантаження на процесор у top, можемо подивитися утилізацію пам'яті якимись системними утилітами, але заглянути всередину PostgreSQL ми не зможемо. Для цього нам потрібні інші інструменти.
І далі я розповім, куди витрачається час. Якщо ми представимо PostgreSQL у вигляді такої схеми, то можна буде відповісти, куди витрачається час. Це дві речі: це обробка клієнтських запитів від програм та фонові завдання, які виконує PostgreSQL для підтримки своєї працездатності.
Якщо ми почнемо розглядати з лівого верхнього кута, ми можемо простежити, як обробляється клієнтські запити. Запит приходить від додатку та для подальшої роботи відкривається клієнтська сесія. Запит передається до планувальника. Планувальник будує план запиту. Відправляє його далі виконання. Відбувається якесь блокове введення-виведення даних пов'язане з таблицями та індексами. Необхідні дані читаються з дисків на згадку в спеціальну область "shared buffers". Результати запиту, якщо це updates, deletes, фіксуються у журналі транзакцій WAL. Деяка статистична інформація потрапляє в лог або колектор статистики. І результат запиту вже віддається клієнту назад. Після цього клієнт може повторити все по-новому з новим запитом.
Що у нас із фоновими завданнями та з фоновими процесами? У нас є кілька процесів, які забезпечують працездатність та підтримують базу даних у нормальному робочому режимі. Ці процеси також стосуватимуться доповіді: це autovacuum, checkpointer, процеси, пов'язані з реплікацією, background writer. Кожного з них я зачіпатиму в міру доповіді.
Які проблеми є зі статистикою?
- Інформації багато. PostgreSQL 9.4 надає 109 метриків для перегляду даних статистики. Однак, якщо в базі даних зберігається багато таблиць, схем, баз, то всі ці метрики доведеться помножити на відповідну кількість таблиць баз. Т. е. інформації стає ще більше. І потонути у ній дуже легко.
- Наступна проблема – те, що статистика представлена лічильниками. Якщо ми подивимося цю статистику, то ми побачимо лічильники, що постійно збільшуються. І якщо з моменту скидання статистики минуло багато часу, ми побачимо мільярдні значення. І вони нам нічого не кажуть.
- Нема історії. Якщо у вас стався якийсь збій, щось упало 15-30 хвилин тому, не вдасться скористатися статистикою та подивитися, що відбувалося 15-30 хвилин тому. Це проблема.
- Відсутність вбудованого в PostgreSQL інструменту – це проблема. Розробники ядра не надають жодної утиліти. Вони не мають нічого такого. Вони просто пропонують статистику в базі. Користуйтеся, робіть до неї запит, що бажаєте, те й робіть.
- Оскільки вбудованого в PostgreSQL інструменту немає, це є причиною іншої проблеми. Безліч сторонніх інструментів. Кожна компанія, яка має більш-менш прямі руки, намагається написати свою програму. І в результаті в community дуже багато інструментів, якими можна користуватися для роботи зі статистикою. І в одних інструментах є одні можливості, в інших інструментах немає інших можливостей або є якісь нові можливості. І виникає ситуація, що потрібно використовувати два-три-чотири інструменти, які один одного перекривають і мають різні функції. Це дуже неприємно.
Що з цього випливає? Важливо вміти брати статистику безпосередньо, щоб не залежати від програм або якось самому покращити ці програми: додати якісь функції, щоб отримати свою вигоду.
І потрібні базові знання SQL. Щоб отримати якісь дані зі статистики, потрібно скласти запити SQL, тобто потрібно знати, як складаються select, join.
Статистика пропонує кілька речей. Їх можна поділити на категорії.
- Перша категорія – це події, які у базі. Це коли у базі відбувається якесь подія: запит, звернення до таблиці, автовакуум, коммиты, це всі події. Відповідні події лічильники інкрементуються. І ми можемо відстежити ці події.
- Друга категорія – властивості об'єктів такі, як таблиці, бази. Вони мають властивості. Це розмір таблиць. Ми можемо відстежити зростання таблиць, зростання індексів. Можемо переглянути зміни в динаміці.
- І третя категорія - це час, витрачений на подію. Запит – це подія. Він має свій конкретний захід тривалості. Тут запустився, закінчився. Ми можемо це відстежити. Або час читання блоку з диска чи запису. Такі речі також відстежуються.
Джерела статистики представлені так:
- У пам'яті (shared buffers), що розділяється, є сегмент для розміщення там статичтичних даних, там є й ті самі лічильники, які постійно інкрементуються, коли відбувається ті чи інші події, або виникають якісь моменти в роботі бази.
- Всі ці лічильники не доступні користувачеві та навіть не доступні адміністратору. Це низькорівневі речі. Щоб звернутися до них PostgreSQL надає інтерфейс у вигляді SQL функцій. Ми можемо зробити select викиди за допомогою цих функцій і отримати якусь метрику (або набір метрик).
- Однак, використовувати ці функції не завжди зручно, тому функції є базою для уявлень (VIEWs). Це віртуальні таблиці, які надають статистику за якоюсь конкретною підсистемою, або з якогось набору подій у базі даних.
- Ці вбудовані уявлення (VIEWs) є основним інтерфейсом користувача роботи зі статистикою. Вони доступні за замовчуванням без будь-якої додаткової установки, можете відразу ними користуватися, дивитися, брати звідти інформацію. А ще є contrib'и. Contrib'и є офіційні. Ви можете встановити пакет postgresql-contrib (наприклад, postgresql94-contrib), підвантажили необхідний модуль конфігурації, вказати для нього параметри, перезапустити PostgreSQL і можна користуватися. (Примітка. Залежно від дистрибутива, в останніх версіях contrib пакет є частиною основного пакету).
- І є неофіційні contrib. Вони не постачаються у стандартній поставці PostgreSQL. Їх потрібно або скомпілювати або встановити як бібліотеку. Варіанти можуть бути різні, залежно від того, що придумав розробник цього неофіційного contrib'а.
На цьому слайді представлені всі уявлення (VIEWs) і частина тих функцій, які доступні в PostgreSQL 9.4. Як бачимо, їх дуже багато. І досить легко заплутатися, якщо ви зіткнулися з цим вперше.
Однак, якщо ми візьмемо попередню картинку Как тратится время на PostgreSQL і сумісний із цим списком, то отримаємо ось таку картинку. Кожне подання (VIEWs), або кожну функцію ми можемо використовувати з тією чи іншою метою для отримання відповідної статистики, коли у нас працює PostgreSQL. І можемо отримати вже якусь інформацію щодо роботи підсистеми.
Перше, що ми розглянемо, це pg_stat_database. Як бачимо, це уявлення. У ній дуже багато інформації. Найрізноманітніша інформація. І вона дає дуже корисне знання, що у нас відбувається у базі даних.
Що ми можемо корисне взяти звідти? Почнемо з найпростіших речей.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Перше, що ми можемо подивитись – це відсоток потрапляння до кешу. Відсоток потрапляння до кешу – це корисна метрика. Вона дозволяє оцінити, який обсяг даних береться з кешу shared buffers, який обсяг читається з диска.
Ясна річ, що чим більше у нас попадання в кеш, тим краще. Ми оцінюємо цю метрику як відсоток. І, наприклад, якщо у нас відсоткове ставлення цих попадань до кешу більше 90%, то це добре. Якщо воно опускається нижче 90%, значить у нас пам'яті недостатньо для утримання гарячої "голови" даних у пам'яті. І щоб ці дані використовувати, PostgreSQL змушений звертатися до диска і це повільніше, ніж якби дані читалися з пам'яті. І треба вже думати над збільшенням пам'яті: або shared buffers збільшувати або нарощувати залізну пам'ять (RAM).
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Що ще можна взяти з цього уявлення? Можна подивитися аномалії, що відбуваються в базі. Що тут показано? Тут є коміти, rollbacks, створення тимчасових файлів, їх об'єм, розриви і конфлікти.
Ми можемо скористатися цим запитом. Цей SQL досить простий. І можемо подивитися ці дані у себе.
І тут одразу порогові значення. Ми дивимося співвідношення commits та rollbacks. Commits – це успішне підтвердження транзакції. Rollbacks - це відкат, тобто транзакція робила якусь роботу, напружувала базу, щось вважала, а потім стався збій, і результати транзакції відкидаються. Т. е. кількість rollbacks, що постійно збільшуються, це погано. І слід якось уникати їх і правити код, щоб такого не відбувалося.
Конфлікти (conflicts) пов'язані з реплікацією. І їх також слід уникати. Якщо у вас якісь запити, які виконуються на репліці і виникають конфлікти, потрібно ці конфлікти розбирати, дивитися, що відбувається. Деталі можна знайти у логах. І усувати конфліктні ситуації, щоб запити програми працювали без помилок.
Deadlocks – це також погана ситуація. Коли запити борються за ресурси, один запит звернувся до одного ресурсу і взяв блокування, другий запит звернувся до другого ресурсу і також взяв блокування, а потім обидва запити звернулися до ресурсів один одного і заблокувалися в очікуванні, коли сусід відпустить блокування. Це також проблемна ситуація. Їх потрібно вирішувати на рівні переписування додатків та серіалізації доступу до ресурсів. І якщо ви бачите, що у вас deadlocks збільшуються постійно, потрібно дивитися деталі в логах, розбирати ситуації і дивитися в чому проблема.
Тимчасові файли (temp_files) – це погано. Коли запиту користувача не вистачає пам'яті для розміщення оперативних, тимчасових даних, він створює на диску файл. І всі операції, які б він міг виконати в тимчасовому буфері в пам'яті, починає виконувати вже на диску. Це повільно. Це підвищує час виконання запиту. І клієнт, який відправив запит до PostgreSQL, отримає відповідь трохи пізніше. Якщо всі ці операції будуть виконуватися в пам'яті, Postgres відповість набагато швидше і клієнт буде менше чекати.
Pg_stat_bgwriter – це уявлення описує роботу двох фонових підсистем PostgreSQL: це checkpointer и background writer.
Спочатку розберемо контрольні точки, т.зв. checkpoints. Що таке контрольні точки? Контрольна точка це позиція в журналі транзакцій, що повідомляє, що всі зміни даних зафіксовані в журналі успішно синхронізовані з даними на диску. Процес залежно від робочого навантаження та налаштувань може бути тривалими і здебільшого полягає у синхронізації брудних сторінок у shared buffers з датфайлами на диску. Для чого це потрібно? Якби PostgreSQL постійно звертався до диска і брав звідти дані, і записував дані при кожному зверненні, це було б повільно. Тому PostgreSQL має сегмент пам'яті, розмір якого залежить від параметрів у конфігурації. Postgres розміщує у пам'яті оперативні дані для подальшої обробки чи видачі за запитами. У разі запитів на зміну даних відбувається їхня зміна. І ми отримуємо дві версії даних. Одна в нас у пам'яті, інша на диску. І періодично слід ці дані синхронізувати. Нам потрібно те, що змінено у пам'яті, синхронізувати на диск. Для цього потрібні checkpoint.
Checkpoint проходить по shared buffers, позначає брудні сторінки, що вони потрібні для checkpoint. Потім запускає другий прохід по shared buffers. І сторінки, які позначені для checkpoint, він їх синхронізує. Таким чином виконується синхронізація даних з диском.
Є два типи контрольних точок. Один checkpoint виконується за тайм-аутом. Це checkpoint корисний та гарний – checkpoint_timed. І є checkpoints на вимогу - checkpoint required. Така контрольна точка відбувається коли ми маємо дуже великий запис даних. Ми записали багато журналів транзакцій. І PostgreSQL вважає, що йому потрібно все це якнайшвидше синхронізувати, зробити контрольну точку і жити далі.
І якщо ви подивилися статистику pg_stat_bgwriter і побачили, що у вас checkpoint_req набагато більше, ніж checkpoint_timed, це погано. Чому погано? Це означає, що PostgreSQL знаходиться у постійній стресовій ситуації, коли йому потрібно записувати дані на диск. Checkpoint по таймууту менш стресовий і виконується згідно з внутрішнім розкладом і як би розтягнутий за часом. PostgreSQL має можливість зробити паузи в роботі і не напружувати дискову підсистему. Це для PostgreSQL корисно. І запити, які виконуються під час checkpoint, не будуть відчувати стреси від того, що дискова підсистема зайнята.
І для регулювання checkpoint є три параметри:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Вони дозволяють регулювати роботу контрольних точок. Але не на них затримуватимуся. Їхній вплив – це вже окрема тема.
Увага: Версія 9.4, що розглядається в доповіді, вже неактуальна. У сучасних версіях PostgreSQL параметр checkpoint_segments замінений параметрами min_wal_size и max_wal_size.
Наступна підсистема – це фоновий письменник. background writer. Що він робить? Він працює постійно у нескінченному циклі. Сканує сторінки в shared buffers та сторінки брудні, які він знайшов, скидає на диск. Таким чином він допомагає checkpointer'у робити менше роботи в процесі виконання контрольних точок.
Навіщо він ще потрібен? Він забезпечує потребу у чистих сторінках у shared buffers якщо вони раптом знадобляться (у великій кількості та відразу) для розміщення даних. Припустимо, виникла ситуація, коли для виконання запиту були потрібні чисті сторінки і вони вже є в shared buffers. Постгресовий backend просто бере їх і використовує, йому не треба нічого чистити. Але якщо раптом таких сторінок немає, бекенд припиняє роботу і починає пошук сторінок щоб скинути їх на диск і взяти для своїх потреб - що негативно позначається на часі запиту, що виконується в даний момент. Якщо ви бачите, що у вас є параметр maxwritten_clean великий, це означає, що background writer не справляється зі своєю роботою і потрібно збільшувати параметри bgwriter_lru_maxpagesЩоб він зміг за один цикл зробити більше роботи, більше очистити сторінок.
І інший дуже корисний показник – це buffers_backend_fsync. Бекенди не роблять fsync, тому що це повільно. Вони передають fsync вище IO stack checkpointer'у. Checkpointer має свою чергу, він періодично fsync обробляє і сторінки в пам'яті синхронізує з файлами на диску. Якщо черга велика у checkpointer і заповнена, то бекенд змушений сам робити fsync і це уповільнює роботу бекенда, Тобто клієнт отримає відповідь пізніше, ніж міг би. Якщо ви бачите, що у вас це значення більше за нуль, то це вже проблема і потрібно звернути увагу на налаштування background writer'а і оцінити продуктивність дискової підсистеми.
Увага: _Наступний текст визначає статистичні уявлення пов'язані з реплікацією. Більшість імен уявлень і функцій було перейменовано на Postgres 10. Суть перейменувань зводилася до заміни xlog на wal и location на lsn в іменах функцій/уявлень тощо. Приватний приклад, функція pg_xlog_location_diff() була перейменована на pg_wal_lsn_diff()._
Тут також у нас багато всього. Але знадобляться нам лише пункти, пов'язані з location.
Якщо бачимо, що це значення рівні, це ідеальний варіант і репліка не відстає від майстра.
Ось ця шістнадцяткова позиція – це позиція в журналі транзакцій. Вона постійно збільшується, якщо в базі є якась активність: inserts, deletes тощо.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Якщо ці речі відрізняються, значить є якийсь лаг. Лаг – це відставання репліки від майстра, тобто дані відрізняються між серверами.
Є три причини відставання:
- Ця дискова підсистема не справляється із записом синхронізації файлів.
- Це можливі помилки мережі або перевантаження мережі, коли дані не встигають доїжджати до репліки і він не може їх відтворити.
- І процесор. Процесор – це дуже рідкісний випадок. І я бачив таке двічі чи тричі, але таке теж може бути.
І ось три запити, які дозволяють використовувати статистику. Ми можемо оцінити, скільки записано в журналі транзакції. Є така функція pg_xlog_location_diff і можемо оцінити лаг реплікації у байтах та секундах. Ми також використовуємо для цього значення з цього уявлення (VIEWs).
Примітка: _Замість pg_xlog_locationdiff() функції можна використовувати оператор віднімання та віднімати один location з іншого. Зручно.
З лагом, який за секунди, є один момент. Якщо на майстрі не відбувається ніякої активності, транзакція там була десь 15 хвилин тому і активності немає, і якщо ми на репліці подивимося цей лаг, то побачимо лаг в 15 хвилин. Про це варто пам'ятати. І це може вводити в ступор, коли ви переглянули цей лаг.
Pg_stat_all_tables – ще одна корисна вистава. Воно показує статистику за таблицями. Коли в базі є таблиці, з ним є якась активність, якісь дії, ми можемо цю інформацію отримати з цього уявлення.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;Перше, що ми можемо подивитися, це послідовні сканування за таблицею. Саме число після цих проходів ще не обов'язково погане і не є показником того, що нам потрібно вже щось робити.
Але є друга метрика – seq_tup_read. Це кількість рядків, повернутих у результаті послідовного сканування. Якщо середнє число перевищує 1 000, 10 000, 50 000, 100 000, то це вже показник, що можливо вам потрібно десь побудувати індекс, щоб звернення були за індексом, або можливо оптимізувати запити, які використовую такі послідовні сканування, щоб такого не було.
Простий приклад – припустимо, запит із великим OFFSET та LIMIT стоїть. Наприклад, сканується 100 000 рядків у таблиці і після цього береться 50 000 необхідних рядків, а попередні відскановані рядки відкидаються. Це теж поганий кейс. І такі запити слід оптимізувати. І тут ось такий простий SQL-запит, на якому можна це подивитися та оцінити отримані цифри.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Розміри таблиць також можна отримати за допомогою цієї таблиці та за допомогою додаткових функцій pg_total_relation_size(), pg_relation_size().
Взагалі, є метакоманди dt и di, які можна використовувати в PSQL і також переглянути розміри таблиць і індексів.
Однак використання функцій допомагає нам подивитись розміри таблиць ще з урахуванням індексів, або без урахування індексів і вже робити якісь оцінки на основі зростання бази даних, тобто як вона у нас зростає, з якою інтенсивністю і робити вже якісь висновки про оптимізацію розмірів.
Активність для запису. Що таке записування? Давайте розглянемо операцію UPDATE – операцію поновлення рядків у таблиці. По суті, update – це дві операції (а то ще більше). Це вставка нової версії рядка та позначка старої версії рядка як застарілого. Згодом прийде автовакуум і ці застарілі версії рядків вичистить, помітить це місце як доступне для повторного використання.
Крім того, update – це не лише оновлення таблиці. Це ще оновлення індексів. Якщо у вас на таблиці багато індексів, то при update всі індекси, в яких беруть участь поля, що оновлюються у запиті, потрібно також оновити. У цих індексах також будуть застарілі версії рядків, які потрібно буде почистити.
select
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;І за рахунок свого дизайну, UPDATE – це великовагові операції. Але їх можна полегшити. Є hot updates. Вони з'явилися у PostgreSQL версії 8.3. І що це таке? Це легкий update, який не викликає перебудови індексів. Т. е. ми оновили запис, але при цьому оновився тільки запис у сторінці (яка належить таблиці), а індекси, як і раніше, вказують на той самий запис у сторінці. Там трохи така цікава логіка роботи, коли приходить вакуум, то він ці ланцюжки hot перебудовує і все продовжує працювати без оновлення індексів, і відбувається все з меншою витратою ресурсів.
І коли у вас n_tup_hot_upd велике, то це дуже добре. Це означає, що легковажні updates переважають і це за ресурсами виходить нам дешевше і все чудово.
ALTER TABLE table_name SET (fillfactor = 70);Як збільшити обсяг hot updateів? Ми можемо використовувати fillfactor. Він визначає розмір резервованого вільного місця під час заповнення сторінки в таблиці за допомогою INSERT'ів. Коли таблицю йдуть inserts, вони повністю заповнюють сторінку, не залишають у ній порожнього місця. Потім виділяється нова сторінка. Знову дані заповнюються. І це поведінка за умовчанням, fillfactor = 100%.
Ми можемо зробити свійфактор в 70%. Т. е. при inserts виділилася нова сторінка, але заповнилося всього лише 70% сторінки. І 30% у нас залишилося на резерв. Коли потрібно буде зробити update, то він з високою ймовірністю відбудеться в тій самій сторінці, і нова версія рядка поміститься в ту ж сторінку. І буде зроблено hot_update. Таким чином, полегшується запис на таблицях.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Черга автовакууму. Автовакуум - це така підсистема, за якою статистиці в PostgreSQL дуже мало. Ми можемо в таблицях тільки в pg_stat_activity побачити, скільки у нас вакуумів тривають зараз. Однак зрозуміти, скільки таблиць у черзі в нього з ходу дуже складно.
Примітка: _Починаючи з версії Postgres 10 ситуація з відстеженням ватовакууму сильно покращилася - з'явилося уявлення pg_stat_progressvacuum, яке суттєво спрощує питання моніторингу автовакууму.
Ми можемо використовувати такий спрощений запит. І можемо подивитися, коли має бути зроблений вакуум. Але, як і коли має запуститися вакуум? Ось ці застарілі версії рядків, про які я говорив раніше. Update відбувся, нова версія рядка вставилася. З'явилася застаріла версія рядка. В таблиці pg_stat_user_tables є такий параметр n_dead_tup. Він показує кількість "мертвих" рядків. І як тільки кількість мертвих рядків побільшала, ніж певний поріг, до таблиці прийде автовакуум.
І як розраховується цей поріг? Це цілком конкретне відсоткове відношення від загальної кількості рядків у таблиці. Є параметр autovacuum_vacuum_scale_factor. Він і визначає відсоткове ставлення. Допустимо, 10% + там додатковий базовий поріг у 50 рядків. І що виходить? Коли у нас мертвих рядків побільшало "10 % + 50" від усіх рядків у таблиці, то ми ставимо таблицю на автовауум.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Але тут є один момент. Базові пороги параметрів av_base_thresh и av_scale_factor можуть призначатися індивідуально. І, відповідно, поріг буде глобальний, а індивідуальний для таблиці. Тому щоб розрахувати, там потрібно використовувати хитрощі та хитрощі. І якщо вам цікаво, ви можете подивитися на досвід наших колег з Avito (посилання на слайді недійсне і оновлено в тексті).
Вони написали для , що враховує ці речі. Там онуча на два аркуші. Але вважає він коректно та досить ефективно дозволяє оцінити, де у нас вакууму багато потрібно для таблиць, де мало.
Що ми можемо зробити з цим? Якщо у нас черга велика і автовакуум не справляється, то ми можемо підняти кількість воркерів вакууму або просто зробити вакуум агресивнішим.Щоб він тригерився раніше, обробляв таблицю маленькими шматочками. І тим самим черга зменшуватиметься. — Головне стежити за навантаженням на диски, т.к. Вакуум штука небезкоштовна, хоча з появою SSD/NVMe пристроїв проблема стала менш помітною.
Pg_stat_all_indexes – це статистика індексів. Вона не велика. І ми можемо по ній отримати інформацію щодо використання індексів. І, наприклад, можемо визначити які індекси у нас зайві.
Як я вже казав, update – це не тільки оновлення таблиць, це ще оновлення індексів. Відповідно, якщо у нас на таблиці багато індексів, то при оновленні рядків у таблиці, індекси проіндексованих полів також потрібно оновити, і якщо у нас є індекси, за якими немає індексних сканувань, то вони у нас висять баластом. І їх потрібно позбуватися. Для цього нам потрібне поле idx_scan. Ми просто дивимося кількість індексних сканувань. Якщо в індексів нуль сканувань за відносно довгий період зберігання статистики (не менше ніж 2-3 тижні), то найімовірніше це погані індекси, нам потрібно їх позбутися.
Примітка: При пошуку індексів, що не використовуються, у разі кластерів потокової реплікації потрібно перевіряти всі вузли кластера, т.к. статистика не глобальна, і якщо індекс не використовується на майстрі, він може використовуватися на репліках (якщо там є навантаження).
Два посилання:
Це більш просунуті приклади запитів для того, як шукати індекси, що не використовуються.
Друге посилання це досить цікавий запит. Там дуже нетривіальна логіка закладена. Рекомендую його для ознайомлення.
Що ще варто підсумувати за індексами?
-
Індекси, що не використовуються, – це погано.
-
Посідають місце.
-
Уповільнюють операції поновлення.
-
Зайва робота для вакууму.
Якщо ми видалимо індекси, що не використовуються, то ми зробимо базі тільки краще.
Наступне уявлення – це pg_stat_activity. Це аналог утиліти psтільки в PostgreSQL. Якщо psТому ви дивитеся процеси в операційній системі, то pg_stat_activity вам покаже активність усередині PostgreSQL.
Що ми можемо взяти звідти корисного?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Ми можемо переглянути загальну активність, що відбувається в базі. Можемо зробити новий деплой. У нас там все вибухнуло, коннекти нові не приймаються, помилки сиплються у додатку.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Ми можемо виконати такий запит і подивитися загальний відсоток підключень щодо максимального ліміту підключень і подивитися, хто у нас займає найбільше коннектів. І в даному наведеному випадку ми бачимо, що user cron_role відкрив 508 коннектів. І щось із ним там сталося. Потрібно з ним розбиратися та дивитися. І цілком можливо, що це якась аномальна кількість підключень.
Якщо ми маємо навантаження OLTP, запити повинні виконуватися швидко, дуже швидко і не повинно бути довгих запитів. Однак якщо виникають довгі запити, то в короткостроковій перспективі нічого страшного немає, але У довгостроковій перспективі довгі запити шкодять базі, вони збільшують bloat ефект таблиць, коли відбувається фрагментація таблиць. І від bloat, і від довгих запитів потрібно позбавлятися.
select
client_addr, usename, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Зверніть увагу: таким запитом ми можемо визначати довгі запити і транзакції. Ми використовуємо функцію clock_timestamp() визначення часу роботи. Довгі запити, які ми знайшли, ми можемо їх запам'ятати, виконати explain, подивитися плани та якось оптимізувати. Поточні довгі запити ми відстрілюємо і живемо.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Погані транзакції – це транзакції у стані idle in transaction та idle in transaction (aborted).
Що це означає? Транзакції мають кілька станів. І один із цих станів можуть приймати у будь-який момент часу. Для визначення станів є поле state у цьому поданні. І ми використовуємо його для визначення стану.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';І, як я вже сказав вище, ці два стани idle in transaction та idle in transaction (aborted) – це погано. Що це таке? Це коли програма відкрила транзакцію, зробила якісь дії і пішла у своїх справах. Транзакція залишилася відкритою. Вона висить, в ній нічого не відбувається, вона займає коннект, блокування на змінені рядки і потенційно ще збільшує bloat інших таблиць, через архітектуру транзакційного двигуна Postrges'а. І такі транзакції теж слід відстрілювати, бо вони шкідливі взагалі, за будь-якого розкладу.
Якщо ви бачите, що їх у вас у базі більше 5-10-20, то треба вже стурбуватися і починати щось робити з ними.
Тут ми також для часу обчислення використовуємо clock_timestamp(). Транзакції відстрілюємо, програму оптимізуємо.
Як я вже говорив вище, блокування – це коли дві і більше транзакцій виборюють один або групу ресурсів. Для цього ми маємо поле waiting з булевим значенням true або false.
True – це означає, що процес чекає, потрібно щось робити. Коли процес перебуває в очікуванні, значить клієнт, який ініціював цей процес, теж чекає. Клієнт у браузері сидить і теж чекає.
Увага: _Починаючи з версії Postgres 9.6 поле waiting видалено і замість нього додані два більш інформативні поля wait_event_type и wait_event._
Що робити? Якщо ви бачите true, протягом довгого часу то значить, таких запитів треба позбавлятися. Ми просто такі транзакції відстрілюємо. Розробникам пишемо, що треба якось оптимізувати, щоби не було гонки за ресурсами. І далі розробники оптимізують додаток щоб такого не виникало.
І крайній, але при цьому потенційно не фатальний випадок – це Поява клопотань. Дві транзакції оновили два ресурси, потім звертаються до них знову, вже протилежних ресурсів. PostgreSQL у разі бере і сам відстрілює транзакцію, щоб інша могла продовжити роботу. Це тупикова ситуація і вона сама не розуміється. Тому PostgreSQL змушений вживати крайніх заходів.
І ось два запити, які дозволяють відстежувати блокування. Ми використовуємо уявлення pg_locks, Що дозволяє відстежувати великовагові блокування.
І перше посилання – це сам текст запиту. Він досить довгий.
І друге посилання – це стаття з locks. Її корисно почитати, вона дуже цікава.
Отже, що бачимо? Ми бачимо два запити. Транзакція з ALTER TABLE – це блокуюча транзакція. Вона запустилася, але не завершилася і додаток, що запстигнув цю транзакцію, десь займається іншими справами. І другий запит – update. Він чекає, коли закінчиться alter table, щоб продовжити свою роботу.
Так ми можемо з'ясовувати, хто кого залочив, тримає і можемо розбиратися з цим далі.
Наступний модуль – це pg_stat_statements. Як я вже сказав, це модуль. Щоб ним скористатися, потрібно підвантажити його бібліотеку в конфігурації, перезапустити PostgreSQL, встановити модуль (одною командою) і далі у нас з'явиться нове уявлення.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Що ми можемо взяти звідти? Якщо говорити про найпростіші речі, ми можемо взяти середній час виконання запиту. Час зростає, отже, у нас PostgreSQL відповідає повільно і потрібно щось робити.
Можемо подивитися найактивніші транзакції в базі даних, які змінюють дані в shared buffers. Подивитися, хто у нас там оновлює чи видаляє дані.
І можемо просто переглянути різну статистику за цими запитами.
Ми pg_stat_statements використовуємо для побудови звітів. Раз на добу скидаємо статистику. Накопичуємо її. Перед скиданням статистики наступного разу будуємо звіт. Ось посилання на звіт. Ви можете подивитися його.
Що ми робимо? Ми підраховуємо загальну статистику з усіх запитів. Потім для кожного запиту ми вважаємо його індивідуальний внесок у цю загальну статистику.
І що ми можемо подивитись? Ми можемо подивитися сумарний час виконання всіх запитів конкретного типу на тлі всіх інших запитів. Можемо подивитися використання ресурсів процесора та введення-виводу щодо загальної картини. І вже оптимізувати ці запити. Ми будуємо топ запитів щодо цього звіту і вже отримуємо їжу для роздумів, що оптимізувати.
Що в нас лишилося за кадром? Залишилося ще кілька уявлень, які я не став розглядати, бо час обмежений.
є pgstattuple – це також додатковий модуль із стандартного пакету contribs. Він дозволяє оцінити bloat таблиці, т.зв. фрагментацію таблиці. І якщо фрагментація велика, потрібно її забирати, використовувати різні інструменти. І функція pgstattuple працює довго. І чим більше таблиць, тим довше вона працюватиме.
Наступний contrib – це pg_buffercache. Він дозволяє проводити інспекцію shared buffers: наскільки інтенсивно та під які таблиці утилізуються сторінки буфера. І просто дозволяє заглянути в shared buffers та оцінити те, що відбувається там.
Наступний модуль – це pgfincore. Він дозволяє проводити низькорівневі операції з таблицями через системний виклик mincore(), Т. е. він дозволяє завантажити таблицю в кулісні буфера, або її вивантажити. І дозволяє також проводити інспекцію сторінкового кешу операційної системи, тобто в якому обсязі у нас таблиця займаємо в page cache, в shared buffers і просто дозволяє оцінити завантаженість таблиці.
Наступний модуль – pg_stat_kcache. Він також використовує системний виклик getrusage(). І виконує його перед та після виконання запиту. І в отриманій статистиці дозволяє оцінити, скільки у нас запит витратив на виконання дискового вводу-виводу, тобто операції з файловою системою та дивиться використання процесора. Однак модуль молодий (кхе-кхе) і для своєї роботи він вимагає PostgreSQL 9.4 та pg_stat_statements, про який я говорив раніше.
-
Вміння користуватися статистикою – корисне. Вам не потрібні сторонні програми. Ви можете самі заглянути, подивитись, щось зробити, виконати.
-
Користуватися статистикою нескладно, це нормальний SQL. Ви зібрали запит, склали, відправили, подивилися.
-
Статистика допомагає відповісти на запитання. Якщо у вас виникають питання, ви звертаєтесь до статистики – дивіться, робите висновки, аналізуєте результати.
-
І експериментуйте. Запитів багато, даних багато. Завжди можна оптимізувати якийсь запит. Ви можете зробити свою версію запиту, яка підходить вам більше, ніж оригінал і використовувати його.
Посилання
Придатні посилання, які зустрічалися у статті, за матеріалами якої були у доповіді.
Автор пиши ще
(англ)
The Statistics Collector
System Administration Functions
Contrib modules
SQL utils and sql code examples
Всім дякую за увагу!
Джерело: habr.com