Задълбочено потапяне във вътрешната статистика на PostgreSQL. Алексей Лесовски

Транскрипция на доклада от 2015 г. на Алексей Лесовски „Задълбочено потапяне във вътрешната статистика на PostgreSQL“

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Този слайд показва всички тези изгледи (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;

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

Ясно е, че колкото повече кеш хитове имаме, толкова по-добре. Ние оценяваме този показател като процент. И например, ако имаме процент от тези посещения на кеша над 90%, тогава това е добре. Ако падне под 90%, тогава нямаме достатъчно памет, за да запазим горещата глава от данни в паметта. И за да използва тези данни, PostgreSQL е принуден да получи достъп до диска и това е по-бавно, отколкото ако данните се четат от паметта. И трябва да помислите за увеличаване на паметта: или увеличете споделените буфери, или увеличете желязната памет (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;

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

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

А ето и праговите стойности. Разглеждаме съотношението на ангажименти и връщания назад. Комити е успешно потвърждение на транзакцията. Rollbacks е връщане назад, т.е. транзакцията е свършила някаква работа, напрегнала е базата данни, обмислила е нещо и след това е възникнала грешка и резултатите от транзакцията са отхвърлени. т.е. постоянно нарастващият брой връщания назад е лош. И трябва по някакъв начин да ги избегнете и да редактирате кода, така че това да не се случи.

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

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

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

pg_stat_bgwriter - Този изглед описва работата на две фонови подсистеми на PostgreSQL: checkpointer и background writer.

Като за начало нека анализираме контролните точки, т.нар. checkpoints. Какво представляват контролните точки? Контролна точка е позиция в регистрационния файл на транзакциите, показваща, че всички промени в данните, извършени в регистрационния файл, са успешно синхронизирани с данните на диска. Процесът, в зависимост от натоварването и настройките, може да бъде дълъг и най-вече се състои от синхронизиране на мръсни страници в споделени буфери с файлове с данни на диска. За какво е? Ако PostgreSQL осъществяваше достъп до диска през цялото време и вземаше данни от там и записваше данни при всеки достъп, щеше да е бавно. Следователно PostgreSQL има сегмент от паметта, чийто размер зависи от параметрите в конфигурацията. Postgres разпределя оперативни данни в тази памет за по-нататъшна обработка или заявки. В случай на заявки за промяна на данни, те се променят. И получаваме две версии на данните. Единият е в паметта, другият е на диска. И периодично трябва да синхронизирате тези данни. Нуждаем се от това, което се променя в паметта, да бъде синхронизирано с диска. Това изисква контролно-пропускателен пункт.

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

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

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

И има три параметъра за регулиране на контролната точка:

• сheckpoint_segments.

• сheckpoint_timeout.

• сheckpoint_competion_target.

Те ви позволяват да контролирате работата на контролните точки. Но няма да се спирам на тях. Тяхното влияние е отделен въпрос.

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

Следващата подсистема е фоновият запис − background writer. Какво прави той? Той работи постоянно в безкраен цикъл. Той сканира страници в споделени буфери и изтрива мръсните страници, които открива, на диска. По този начин помага на чекпойнтера да върши по-малко работа по време на чекпойнтъра.

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

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

Предупреждение: _Следващият текст описва статистическите изгледи, свързани с репликацията. Повечето от имената на изгледите и функциите са преименувани в Postgres 10. Същността на преименуванията беше да заменят xlog на wal и location на lsn в имена на функции/изгледи и т.н. Конкретен пример, функция 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)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());

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

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

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

И ето три заявки, които ни позволяват да използваме статистика. Можем да преценим колко е записано в нашия регистър на транзакциите. Има такава функция pg_xlog_location_diff и можем да оценим забавянето на репликацията в байтове и секунди. Ние също използваме стойността от този изглед (VIEWS) за това.

Забележка: _Вместо pg_xlog_locationфункция diff(), можете да използвате оператора за изваждане и да извадите едно местоположение от друго. Удобен.

Със закъснение, което е в секунди, има един момент. Ако няма активност на главния, транзакцията е била там преди около 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, това вече е индикатор, че може да се наложи да изградите индекс някъде, така че достъпите да са по индекс, или е възможно да оптимизирате заявки, които използват такива последователни сканирания, така че това не се случва.беше.

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

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

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

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

ALTER TABLE table_name SET (fillfactor = 70);

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

Можем да настроим фактора на запълване на 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));

Автовакуумна опашка. Autovacuum е такава подсистема, за която има много малко статистики в PostgreSQL. Можем да видим само в таблиците в pg_stat_activity колко вакуума имаме в момента. Въпреки това е много трудно да се разбере колко маси в опашката има в движение.

Забележка: _От Postgres 10 ситуацията с проследяването на вакуумния вакуум се подобри много - появи се изгледът pg_stat_progressвакуум, което значително опростява проблема с автовакуумния мониторинг.

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

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

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)';

Лошите транзакции са неактивни в транзакция и неактивни в транзакция (прекратени) транзакции.

Какво означава? Транзакциите имат множество състояния. И едно от тези състояния може да отнеме по всяко време. Има поле за дефиниране на състояния state в този изглед. И ние го използваме, за да определим състоянието.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

И, както казах по-горе, тези две състояния неактивен в транзакция и неактивен в транзакция (прекратен) са лоши. Какво е? Това е моментът, в който приложението отвори транзакция, извърши някои действия и се зае със своята работа. Транзакцията остава отворена. Той виси, нищо не се случва в него, отнема връзка, заключва променени редове и потенциално все още увеличава раздуването на други таблици, поради архитектурата на транзакционния двигател на Postrges. И такива сделки също трябва да се разстрелват, защото са вредни като цяло, във всеки случай.

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

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

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

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

Предупреждение: _Започвайки от Postgres 9.6, полето waiting премахнати и заменени с още две информативни полета wait_event_type и wait_event._

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

И екстремният, но в същото време потенциално не фатален случай е възникване на задънени блокировки. Две транзакции са актуализирали два ресурса, след което имат достъп до тях отново, вече към противоположни ресурси. 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, което ви позволява да проследявате тежки ключалки.

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

А втората връзка е статия за брави. Полезно е да се чете, много е интересно.

И така, какво виждаме? Виждаме две искания. Сделка с 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 реагира бавно и трябва да се направи нещо.

Можем да видим най-активните записващи транзакции в базата данни, които променят данни в споделени буфери. Вижте кой актуализира или изтрива данни там.

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

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

Ние pg_stat_statements използвани за създаване на отчети. Нулираме статистиката веднъж на ден. Да го натрупаме. Преди да нулираме статистиката следващия път, изграждаме отчет. Ето линк към доклада. Можете да го гледате.

Какво правим? Ние изчисляваме общата статистика за всички заявки. След това за всяка заявка отчитаме нейния индивидуален принос към тази обща статистика.

И какво можем да видим? Можем да видим общото време за изпълнение на всички заявки от определен тип на фона на всички останали заявки. Можем да разгледаме използването на CPU и I/O във връзка с цялостната картина. И вече за оптимизиране на тези заявки. Създаваме водещи заявки въз основа на този отчет и вече получаваме храна за размисъл какво да оптимизираме.

Какво имаме зад кулисите? Все още има няколко предложения, които не разгледах, защото времето е ограничено.

Има pgstattuple също е допълнителен модул от стандартния пакет за принос. Позволява ви да оцените bloat маси, т.нар. фрагментация на таблицата. И ако фрагментацията е голяма, трябва да я премахнете, използвайте различни инструменти. И функция pgstattuple работи дълго време. И колкото повече маси, толкова по-дълго ще работи.

Следващият принос е pg_buffercache. Позволява ви да проверявате споделените буфери: колко интензивно и за кои таблици се използват буферните страници. И просто ви позволява да погледнете в споделените буфери и да оцените какво се случва там.

Следващият модул е pgfincore. Тя ви позволява да извършвате операции с таблици на ниско ниво чрез системно повикване mincore(), т.е. позволява ви да заредите таблицата в споделени буфери или да я разтоварите. И позволява, наред с други неща, да се инспектира кеша на страниците на операционната система, тоест колко таблицата заема в кеша на страниците, в споделени буфери, и просто ви позволява да оцените натоварването на таблицата.

Следващият модул е pg_stat_kcache. Той също така използва системното повикване getrusage(). И го изпълнява преди и след изпълнение на заявката. И в получената статистика ни позволява да оценим колко нашата заявка е изразходвала за I / O на диска, т.е. операции с файловата система и разглежда използването на процесора. Модулът обаче е млад (кхе-кхе) и за работата си изисква PostgreSQL 9.4 и pg_stat_statements, за които споменах по-рано.

• Възможността за използване на статистика е полезна. Нямате нужда от софтуер на трети страни. Можете да гледате, да виждате, да правите нещо, да изпълнявате.

• Използването на статистика е лесно, това е обикновен SQL. Събрахте заявка, компилирахте я, изпратихте я, разгледахте я.

• Статистиката помага да се отговори на въпроси. Ако имате въпроси, обръщате се към статистиката - гледайте, правете изводи, анализирайте резултатите.

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

Позоваването

Валидни връзки, които бяха намерени в статията, въз основа на които, бяха в доклада.

Автор пиши повече
https://dataegret.com/news-blog (англ.)

Колекторът на статистика
https://www.postgresql.org/docs/current/monitoring-stats.html

Системни административни функции
https://www.postgresql.org/docs/current/functions-admin.html

Модули за принос
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL помощни програми и примери за sql код
https://github.com/dataegret/pg-utils

Благодаря на всички за вниманието!

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