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

Разшифровка на доклад от 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 минути, няма да можете да се възползвате от статистиката и да видите какво е станало преди 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 и го съпоставим с този списък, ще получим такава картинка. Всяко представление (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;

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

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

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

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

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

Временните файлове (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. И запитванията, които се изпълняват по време на контрольната точка, няма да изпитват стрес от това, че дисковата подсистема е заета.

И за регулиране на checkpoint има три параметъра:

• checkpoint_segments.

• checkpoint_timeout.

• checkpoint_competion_target.

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

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

Следващата подсистема е фонова писачка — background writer. Какво прави тя? Работи постоянно в безкраен цикъл. Скантира страниците в 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 и това забавя работата на бекенда,, т.е. клиентът получава отговор по-късно, отколкото би могъл. Ако виждате, че това значение е по-голямо от нула, то това вече е проблем и трябва да обърнете внимание на настройките на фоновата писачка и също така да оцените производителността на дисковата подсистема.

Внимание: _Следващият текст описва статистическите представяния, свързани с репликацията. По-голямата част от имената на представянията и функциите бяха променени в 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 от now() - pg_last_xact_replay_timestamp());

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

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

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

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

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

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

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

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

ALTER TABLE table_name SET (fillfactor = 70);

Как да увеличим обема на горещите обновленияможем да използваме fillfactor. Той определя размера на резервирания свободен пространство при запълване на страницата в таблицата с помощта на INSERT-ове. Когато в таблицата се правят вмъквания, те напълно запълват страницата, не оставят в нея празно място. След това се выделя нова страница. Отново данните се запълват. И това поведение по подразбиране е, 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 – това е статистика за индексите. Тя е малка. И можем да получим информация относно употребата на индексите. И например можем да определим кои индекси са излишни.

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

Ако имаме 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 или false.

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(), т.е. той позволява да се зареди таблица в shared buffers или да се източи. И позволява освен това да извършвате инспекция на страничния кеш на операционната система, т.е. в какъв обем се намира таблицата в page cache, в shared buffers и просто позволява да се оцени натоварването на таблицата.

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

• Умението да се използва статистиката – полезно е. Не ви трябват външни програми. Можете сами да надникнете, да видите, да направите нещо, да изпълнявате.

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

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

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

Връзки

Страхотни линкове, които са били споменати в статията, по материалите на която беше докладът.

Авторът пише още
https://dataegret.com/news-blog (eng)

Събирач на статистика
https://www.postgresql.org/docs/current/monitoring-stats.html

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

Модули contrib
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

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

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