🥇Deep dive into PostgreSQL internal statistics. Алексей Лесовски

Разшифроване на доклада през 2015 година на Алексей Лесовски "Дълбочинен анализ на вътрешната статистика на PostgreSQL"

Отказ от отговорност от автора на доклада: Ще отбележа, че този доклад е датиран от ноември 2015 година — изминали са повече от 4 години и много време. Версията на която става въпрос в доклада 9.4 вече не се поддържа. През последните 4 години излязоха 5 нови версии, в които се появиха много нововъведения, подобрения и промени относно статистиката и част от материалите са остарели и неактуални. По време на прегледа се опитах да отбележа тези места, за да не заблуждавам читателя. Не се заех да пренапиша тези части, тъй като са много и в крайна сметка ще се получи съвсем различен доклад.

СУБД PostgreSQL е огромен механизъм, състоящ се от множество подсистеми, чието координирано функциониране пряко влияе на производителността на СУБД. В процеса на експлоатация се събира статистика и информация за работата на компонентите, което позволява да оценим ефективността на PostgreSQL и да предприемем мерки за повишаване на производителността. Въпреки това, информацията е много и е представена в доста опростен вид. Обработката на тази информация и нейното тълкуване понякога е съвсем нетривиална задача, а "зоопаркът" от инструменти и утилити лесно би затруднил дори напреднал DBA.

Здравейте! Аз съм Алексей. Както каза Иля, аз ще говоря за статистиката на PostgreSQL.

Статистика на активността на PostgreSQL. PostgreSQL има две статистики. Статистиката на активността, за която ще става дума. И статистиката на планировчика относно разпределението на данните. Аз ще говоря именно за статистиката на активността на PostgreSQL, която ни позволява да оценяваме производителността и как да я подобряваме.

Ще разкажа как ефективно да използвате статистиката за решаване на най-различни проблеми, които възникват или могат да възникнат.

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

Няма да има прегледи на инструменти, няма да сравнявам един продукт с друг. Никаква реклама няма да има. Да оставим това настрана.

Искам да ви покажа, че използването на статистика е полезно. Това е необходимо. Не е страшно да я използваме. Нужно е само обикновен SQL и основни познания за SQL.

И ще говорим за това каква статистика да изберем за решаване на проблеми.

Ако погледнем PostgreSQL и в операционната система стартираме команда за преглед на процесите, ще видим "черна кутия". Ще видим някакви процеси, които нещо правят, и от името можем да си представим какво правят, с какво се занимават. Но по същество, това е черна кутия, не можем да погледнем вътре.

Можем да видим натоварването на процесора в top, можем да проверим използването на паметта чрез някои системни инструменти, но не можем да погледнем вътре в PostgreSQL. За това ни трябват други инструменти.

И продължавайки, ще разкажа къде се губи времето. Ако си представим PostgreSQL под формата на схема, можем да отговорим на въпроса къде се губи времето. Има две неща: обработка на клиентските заявки от приложенията и фонови задачи, които PostgreSQL изпълнява, за да поддържа своята работоспособност.

Ако започнем да разглеждаме от горния ляв ъгъл, можем да проследим как се обработват клиентските заявки. Заявката идва от приложението и за по-нататъшна работа се отваря клиентска сесия. Заявката се прехвърля в планировчика. Планировчикът изгражда план за заявката. Изпраща го нататък за изпълнение. Процесът включва блоково входно-изходно действие, свързано с таблиците и индексите. Необходимите данни се четат от дисковете в паметта в специална област "shared buffers". Резултатите от заявката, ако са актуализации, изтривания, се фиксират в журнала на транзакциите в WAL. Някаква статистическа информация попада в лог или в колектор за статистика. И резултатът от заявката се връща обратно на клиента. След това клиентът може да повтори всичко отново с нова заявка.

Какво имаме с фоновите задачи и фоновите процеси? Има няколко процеса, които осигуряват работоспособността и поддържат базата данни в нормален работен режим. Тези процеси също ще бъдат засегнати в доклада: autovacuum, checkpointer, процеси, свързани с репликацията, background writer. Всеки един от тях ще разгледам по време на доклада.

Какви проблеми има със статистиката?

Има много информация. PostgreSQL 9.4 предоставя 109 метрики за преглед на статистическите данни. Въпреки това, ако в базата данни се съхраняват много таблици, схеми или бази, всички тези метрики ще трябва да бъдат умножени по съответния брой таблици или бази. Тоест, информацията става още повече. И е много лесно да се изгубите в нея.
Следващият проблем е, че статистиката е представена чрез броячи. Ако погледнем тази статистика, ще видим постоянно нарастващи стойности. И ако от момента на нулиране на статистиката е минало много време, можем да видим милиарди стойности. А те не ни казват нищо.
Липсва история. Ако имате някакъв срив, не можете да се възползвате от статистиката, за да видите какво е било случило 15-30 минути назад. Това е проблем.
Липсата на вграден инструмент в PostgreSQL е проблем. Разработчиците на ядрото не предоставят никаква утилита. Нямат нищо такова. Те просто дават статистиката в базата. Ползвайте я, правете запитване, каквото искате.
Тъй като няма вграден инструмент в PostgreSQL, това е причина за друг проблем. Множество външни инструменти. Всяка компания с по-приемливи умения се опитва да напише собствена програма. И в крайна сметка в общността има много инструменти, с които можем да работим със статистика. В някои инструменти има определени възможности, в други няма, или пък има нови функции. Възниква ситуация, в която се налага да ползвате две, три или четири инструмента, които частично се припокриват и имат различни функции. Това е много неприятно.

Какво следва от това? Важно е да можете да вземате статистика директно, за да не зависите от програмите, или да подобрите тези програми сами: да добавите функции, за да получите свои ползи.

И са необходими основни познания по SQL. За да получите някакви данни от статистиката, трябва да съставите SQL запитвания, тоест трябва да знаете как да съставяте select, join.

Статистиката ни предлага няколко неща. Те могат да бъдат разделени на категории.

Първата категория – това са събития, които се случват в базата. Това е, когато в базата настъпи някое събитие: заявка, достъп до таблица, авто-вакуум, комити – всичко това са събития. Свързаните с тези събития броячи се инкрементират. И можем да проследим тези събития.
Втората категория – това са свойства на обектите като таблици и бази. Те имат свойства. Това е размер на таблиците. Можем да проследим растежа на таблиците, растежа на индексите. Можем да видим промените в динамиката.
И третата категория – това е времето, прекарано за събитие. Заявката е събитие. Тя има своя конкретна мярка за продължителност. Тук започна, тук приключи. Можем да проследим това. Или времето за четене на блок от диска или запис. И такива неща също се проследяват.

Източниците на статистиката са представени по следния начин:

В споделената памет (shared buffers) има сегмент за разполагане на статични данни, там има и същите тези броячи, които постоянно се инкрементират, когато настъпят определени събития или възникнат моменти в работата на базата.
Всички тези броячи не са достъпни за потребителя и дори не са достъпни за администратора. Това са нискоуровневи неща. За да се достигне до тях, PostgreSQL предоставя интерфейс под формата на SQL функции. Можем да направим селекти чрез тези функции и да получим някаква метрика (или набор от метрики).
Обаче използването на тези функции не винаги е удобно, затова функциите служат като основа за представления (VIEWs). Това са виртуални таблици, които предоставят статистика по конкретна подсистема или по набор от събития в базата данни.
Тези вградени представления (VIEWs) са основният интерфейс за работа с статистиката. Те са достъпни по подразбиране без каквато и да е допълнителна настройка, можете веднага да ги използвате, да гледате, да вземете информация от тях. А също така има и contrib пакети. Contrib пакетите са официални. Можете да инсталирате пакета postgresql-contrib (например, postgresql94-contrib), да заредите необходимия модул в конфигурацията, да зададете параметри за него, да рестартирате PostgreSQL и да можете да ползвате. (Забележка. В зависимост от дистрибутива, в последните версии, contrib пакетът е част от основния пакет.).
Има и неофициални contrib. Те не се доставят в стандартната доставка на PostgreSQL. Трябва или да бъдат компилирани, или да бъдат инсталирани като библиотека. Вариантите могат да бъдат много различни, в зависимост от това, което е измислил разработчикът на този неофициален contrib.

На този слайд са представени всички VIEWs и част от функциите, които са налични в PostgreSQL 9.4. Както виждаме, те са много. И е доста лесно да се объркате, ако се сблъскате с това за първи път.

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

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

Какво полезно можем да вземем оттам? Нека започнем с най-простите неща.

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;

Какво още можем да вземем от това VIEW? Можем да разгледаме аномалиите, които се случват в базата. Какво е показано тук? Има commits, rollbacks, създаване на временни файлове, техния обем, deadlocks и конфликти.

Можем да използваме тази заявка. Този SQL е доста прост. И можем да видим тези данни при себе си.

И тук веднага са праговите стойности. Ние наблюдаваме съотношението между commits и rollbacks. Commits – това е успешно потвърждение на транзакция. Rollbacks – това е откат, т.е. транзакцията е извършила някаква работа, е напрегнала базата, нещо е изчислило, а след това е настъпила грешка и резултатите от транзакцията се отхвърлят. Т.е. броят на постоянно увеличаващите се rollbacks е лошо. И трябва да се избягват и да се поправя кодът, за да не се случва така.

Конфликтите (conflicts) са свързани с репликацията. И те също трябва да се избягват. Ако имате някакви заявки, които се изпълняват на реплика и възникват конфликти, трябва да разбирате тези конфликти, да проверите какво се случва. Подробности можете да намерите в логовете. И да отстранявате конфликтни ситуации, за да работят заявките на приложението без грешки.

Deadlocks – това е също лоша ситуация. Когато заявки се състезават за ресурси, едната заявка се обърнала към един ресурс и е взела блокировка, а втората заявка се е обърнала към втория ресурс и също е взела блокировка, а след това и двете заявки се обадили един на друг за ресурси и са се блокирали в очакване, когато съседят ще освободи блокировката. Това също е проблемна ситуация. Трябва да се решава на ниво преписване на приложения и сериализация на достъпа до ресурсите. И ако виждате, че вашите deadlocks постоянно нарастват, трябва да проверите подробностите в логовете, да разгледате възникналите ситуации и да видите каква е проблемът.

Временните файлове (temp_files) – това също е лошо. Когато на потребителската заявка не ѝ достига памет за разполагане на оперативни, времени данни, тя създава файл на диска. И всички операции, които би могла да извърши в временния буфер в паметта, започва да извършва вече на диска. Това е бавно. Това увеличава времето за изпълнение на заявката. И клиентът, изпратил заявка към PostgreSQL, ще получи отговор малко по-късно. Ако всички тези операции се извършват в паметта, Postgres ще отговори много по-бързо и клиентът ще чака по-малко.

Pg_stat_bgwriter – това представление описва работата на двете фонови подсистеми на PostgreSQL: това е checkpointer и background writer.

Първо, да разгледаме контрольните точки, т.н. checkpoints. Какво представляват контролните точки? Контролна точка е позиция в дневника на транзакциите, която информира, че всички изменения на данните, записани в дневника, успешно са синхронизирани с данните на диска. Процесът, в зависимост от натоварването и настройките, може да отнеме време и основно се състои в синхронизация на

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

Съществуват два типа контролни точки. Едната контролна точка се извършва по времеви интервал. Това е полезна и добра контролна точка – checkpoint_timed. И има контролни точки по искане – checkpoint required. Тази контролна точка се извършва, когато имаме много голямо записване на данни. Записваме много транзакционни дневници. И PostgreSQL смята, че трябва да синхронизира всичко това възможно най-бързо, да извърши контролна точка и да продължи напред.

Ако сте разгледали статистиката pg_stat_bgwriter и сте видели, че вашето checkpoint_req е много повече от checkpoint_timed, това е лошо. Защо е лошо? Това означава, че PostgreSQL е в постоянно стресово положение, когато трябва да записва данните на диска. Контролната точка по времеви интервал е по-малко стресова и се изпълнява според вътрешния график и е разстегната във времето. PostgreSQL има възможност да направи паузи в работата си и да не натоварва дисковата система. Това е полезно за PostgreSQL. Запитванията, които се изпълняват по време на контролна точка, няма да изпитват стрес от факта, че дисковата система е натоварена.

И за настройка на checkpoint има три параметъра:

checkpoint_segments.
checkpoint_timeout.
checkpoint_completion_target.

Те позволяват да регулират работата на контрольните точки. Но няма да се спирам на тях. Влиянието им – това е отделна тема.

Внимание: Версията, обсъждана в доклада – 9.4, вече не е актуална. В съвременните версии на PostgreSQL параметърът checkpoint_segments е заменен с параметрите min_wal_size и max_wal_size.

Следващата подсистема е фонът писател — background writer. Какво прави той? Работи постоянно в безкраен цикъл. Сканирa страниците в shared buffers и замърсените страницки, които е намерил, ги записва на диск. По този начин помага на checkpointer’а да свърши по-малко работа по време на изпълнението на контрольните точки.

За какво още е нужен? Осигурява нуждата от чисти страници в shared buffers, ако те изведнъж са необходими (в голямо количество и веднага) за разположението на данни. Да предположим, че се е появила ситуация, при която за изпълнението на запитване са необходими чисти страници и те вече са налични в shared buffers. PostgreSQL просто ги взима и използва, не му се налага сам да чисти нищо. Но ако изведнъж такива страници няма, бекендът спира работата и започва да търси страниците, за да ги сброси на диск и да ги вземе за своите нужди — което негативно се отразява на времето на изпълнение на текущото запитване. backend Ако виждате, че параметърът maxwritten_clean е голям, това означава, че фонът писател не се справя със задълженията си и е необходимо да се увеличат параметрите bgwriter_lru_maxpages , за да може той да извършва повече работа за един цикъл, да чисти повече страници.И друг много полезен показател – това е

buffers_backend_fsync Бекендите не правят fsync, защото е бавно. Те предават fsync по-високо в IO стека на checkpointer’а. Checkpointer-ът има собствена опашка, той периодично обработва fsync и синхронизира страниците в паметта с файловете на диска.. Ако опашката на checkpointer е голяма и запълнена, бекендът е принуден сам да прави fsync и това забавя работата на бекенда, т.е. клиентът ще получи отговор по-късно, отколкото би могъл. Ако виждате, че стойността ви е по-голяма от нула, то това вече е проблем и трябва да се обърне внимание на настройките на фон писателя и също да се оцени производителността на дисковата подсистема., т.е. клиентът ще получи отговор по-късно, отколкото би могъл. Ако видите, че стойността е по-голяма от нула, вече имате проблем и трябва да обърнете внимание на настройките на background writer-а и също да оцените производителността на дисковата подсистема.

Внимание: _Следващият текст описва статистическите представяния, свързани с репликацията. По-голямата част от имената на представяния и функции бяха преименувани в Postgres 10. Същността на преименуванията е да се замени xlog на wal и location на в имената на функции/представяния и т.н. Частен пример, функцията pg_xlog_location_diff() беше преименувана на pg_wal_lsn_diff() Тук също имаме много неща. Но ще ни трябват само точките, свързани с местоположението.._

Ако видим, че всички стойности са равни, то това е идеалният вариант и репликата не изостава от мастера.

Тази шестнадесетична позиция – това е позиция в журнала на транзакциите. Тя постоянно се увеличава, ако в базата има някаква активност: вставки, изтривания и т.н.

колко е записано 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) от pg_stat_replication; изоставането на репликацията в секунди $ select extract(epoch от now() - pg_last_xact_replay_timestamp());

Ако тези неща се различават, значи има някакво изоставане. Изоставането – това е забавянето на репликата спрямо мастера, т.е. данните се различават между сървърите.

Има три причини за изоставане:

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

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

pg_xlog_location_diff и можем да оценим изоставането на репликацията в байтове и секунди. Също така, за това използваме стойността от това представяне (VIEWs). _Вместо pg_xlog_location

Бележка: diff() функцията може да се използва оператор за изваждане и да се извади едно местоположение от друго. Удобно е.С изоставането, което в секунди, има един момент. Ако на мастера не се случва никаква активност, транзакцията там е била преди около 15 минути и няма активност, и ако на репликата погледнем това изоставане, ще видим изоставане от 15 минути. За това трябва да се помни. И това може да обърка, когато разгледате това изоставане.

С лаг, който е в секунди, има един момент. Ако на мастера няма активност, транзакцията е била преди около 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, който не предизвиква пре costruction на индексите. Т.е. актуализираме записа, но в същото време се актуализира само записа в страницата (която принадлежи на таблицата), а индексите все още сочат към същия запис в страницата. Там работи малко интересна логика, когато дойде вакуумът, той тези вериги hot преструктурира и всичко продължава да работи без обновление на индексите, и всичко става с по-малко разходи на ресурси.

И когато имате n_tup_hot_upd голямо, то това е много добре. Това означава, че леките updates преобладават и ресурсите излизат по-евтино и всичко е прекрасно.

ALTER TABLE table_name SET (fillfactor = 70);

Как да увеличим обема на hot updateите? Можем да използваме fillfactor. Той определя размера на резервираното свободно място при попълване на страницата в таблицата с INSERT'и. Когато в таблицата влизат inserts, те напълно запълват страницата, не оставят празно място в нея. След това се заделя нова страница. Отново данните се запълват. И това е поведение по подразбиране, fillfactor = 100%.

Можем да настроим fillfactor на 70%. Тоест, при вставки се пределява нова страница, но запълва само 70% от нея. А 30% остават за резерв. Когато трябва да правим актуализация, тя с висока вероятност ще се случи на същата страница, и новата версия на реда ще се постави на същата страница. Ще бъде направено 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, което значително улеснява въпроса за мониторинга на автовакуума.

Можем да използваме един опростен заявка. И можем да видим, кога трябва да бъде направен вакуум. Но, кога и как трябва да стартира вакуума? Ето тези остарели версии на редовете, за които говорех преди. Актуализация настъпи, новата версия на реда бе вставена. Появила се остаряла версия на реда. В таблицата 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 (линкът на слайда е невалиден и е обновен в текста).

Те написаха за munin plugin, който взема предвид тези неща. Има дълъг списък на два листа. Но той изчислява правилно и доста ефективно позволява да оценим къде имаме много вакуум за таблиците, а къде – малко.

Какво можем да направим с това? Ако имаме голяма опашка и автоматичният вакуум не справя, можем да увеличим броя на работниците за вакуума или просто да направим вакуума по-агресивен, за да се тригерира по-рано и да обработва таблицата на малки парченца. И така опашката ще се намали. — Основното тук е да се следи натоварването на дисковете, тъй като вакуумът не е безплатен, въпреки че с появата на SSD/NVMe устройства проблемът стана по-малко забележим.

Pg_stat_all_indexes – това е статистика за индексите. Тя е малка. И можем да получим информация за използването на индексите. Например можем да определим кои индекси ни излишни.

Както вече споменах, update – това не е само обновление на таблиците, а също и обновление на индексите. Съответно, ако имаме много индекси на таблицата, при обновление на редовете, индексите на индексираните полета също трябва да бъдат обновени, и ако имаме неизползвани индекси, по които няма индексни сканирания, те ни тежат баласт. И трябва да се отървем от тях. За това ни трябва поле idx_scan. Просто гледаме броя на индексните сканирания. Ако индексите имат нула сканирания за относително дълъг период на съхранение на статистиката (не по-малко от 2-3 седмици), най-вероятно тези индекси са лоши, трябва да се отървем от тях.

Бележка: При търсене на неизползвани индекси в случай на клъстери на потокова репликация е нужно да проверим всички възли на клъстера, тъй като статистиката не е глобална, и ако индексът не се използва на майстора, той може да се използва на репликите (ако там има натоварване).

Два линка:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Това са по-напреднали примери за запитвания как да търсим неизползвани индекси.

Втората врска е доста интересен запрос. Тука е вградена доста нетривијална логика. Ја препорачувам за запознавање.

Што уште да се резимира за индексите?

Неискористените индекси – тоа е лошо.
Земаат простор.
Задоцнуваат операциите за ажурирање.
Вишок работа за вакуумот.

Ако ги избришиме неискористените индекси, само ќе ја подобриме базата.

Следното претставување е 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;

Можеме да извршиме оваков запрос и да ја погледнеме целокупната процентуална врска во однос на максималниот лимит на поврзувања и да видиме кој најмногу користи поврзувања. И во дадениот случај, гледаме дека корисникот 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 на други таблици, заради архитектурата на транзакционния механизъм на Postgres. И такива транзакции също трябва да бъдат затворени, защото са вредни изобщо, при всякакви обстоятелства.

Ако видите, че имате повече от 5-10-20 от тях в базата, трябва вече да се притеснявате и да започнете нещо да правите.

Тук също използваме clock_timestamp()за време на изчисление. Затваряме транзакциите, оптимизираме приложението.

Както вече споменах, блокирането – това е, когато две или повече транзакции се борят за един или група ресурси. За целта имаме поле waiting с булево значение истинно или неверно.

True – това означава, че процесът е в режим на изчакване, нуждаете се да направите нещо. Когато процесът е в режим на изчакване, значи клиентът, който е инициирал този процес, също чака. Клиентът в браузъра сидят и също чака.

Внимание: _Започвайки от версия Postgres 9.6, полето waiting е премахнато и вместо него са добавени две по-информативни полета wait_event_type и wait_event._

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

И последният, но потенциално не фатален случай – това е възникването на deadlocks. Две транзакции обновили два ресурса, след това отново се обръщат към тях, вече към противоположни ресурси. PostgreSQL в този случай взема и сам затваря транзакцията, за да може другата да продължи работа. Това е безизходна ситуация и тя сама не се разрешава. Затова PostgreSQL е принуден да взема крайни мерки.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

И ето два запитвания, които позволяват проследяване на блокировките. Използваме представление pg_locks, която позволява следене на тежки блокировки.

И първата връзка - това е самият текст на заявката. Той е доста дълъг.

И втората връзка - това е статия за locks. Полезно е да се прочете, тя е много интересна.

И така, какво виждаме? Виждаме две заявки. Транзакция с ALTER TABLE – това е блокираща транзакция. Тя е стартирала, но не е завършила и приложението, стартирало тази транзакция, се занимава с нещо друго. А втората заявка - update. Тя чака, когато завърши alter table, за да продължи работата си.

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

Следващият модул - това е pg_stat_statements. Както вече казах, това е модул. За да го използвате, трябва да заредите неговата библиотека в конфигурацията, да рестартирате PostgreSQL, да инсталирате модула (с една команда) и след това ще получим ново представление.

Средно време за заявка в милисекунди
$ 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. Да видим кой обновява или изтрива данни.

И можем просто да погледнем различна статистика по тези заявки.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Ние 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. Събрахте заявка, съставихте, изпратихте, видяхте.
Статистиката помага да отговорите на въпросите. Ако имате въпроси, обръщате се към статистиката – гледате, правите изводи, анализирате резултатите.
И експериментирайте. Заявките са много, данните са много. Винаги можете да оптимизирате някоя вече съществуваща заявка. Можете да направите своя версия на заявката, която ви подхожда повече от оригинала и да я използвате.