Инженер — в превода от латински — вдъхновен.
Инженерът може всичко. (с) Р.Дизел.
Епиграфи.
Или история за това, защо администраторът на бази данни трябва да помни програматорското си минало.
Предговор
Всички имена са променени. Съвпаденията са случайни. Материалът представлява изцяло лично мнение на автора.
Отказ от гаранции: в планирания цикъл статии няма да има подробно и точно описание на използваните таблици и скриптове. Материалите не могат да се използват веднага "AS IS".
Първо, заради голямото количество материала,
второ, поради приспособяването с производствената база на реалния клиент.
Затова в статията ще бъдат представени само идеи и описания в най-общ вид.
Може би в бъдеще системата ще достигне нивото на публикуване в GitHub, а може би и не. Времето ще покаже.
Началото на историята — "».
Какво се получи като резултат, в най-общи линии — "»
Защо всичко това ми е?
Ну, на първо място, за да не забравя сам, спомняйки си славните дни на пенсия.
На второ място, за да систематизирам написаното. Защото вече сам, понякога започвам да се обърквам и да забравям отделни части.
И най-важното — изведнъж може да е полезно за някого и да помогне да не се изобретява велосипеда и да не се събират грабли. С други думи, да се подобри кармата (не хабровската). Защото най-ценните неща в този свят са идеите. Най-важното е да намериш идеята. А да реализираш идеята в реалност вече е чисто технически въпрос.
Така че, да започнем, малко по малко...
Постановка на задачата.
Има:
База данни PostgreSQL (10.5), смесен тип натоварване (OLTP+DSS), със средна-ниска натовареност, разположена в облака AWS.
Мониторинг на базата данни липсва, мониторингът на инфраструктурата е представен под формата на стандартни средства на AWS в минимална конфигурация.
Изисква се:
Да следим производителността и състоянието на базата данни, да намираме и имаме начална информация за оптимизация на тежки заявки към БД.
Кратко предисловие или анализ на варианти за решение
За начало ще се опитаме да разгледаме вариантите за решение на задачата от гледна точка на сравнителен анализ на ползи и неприятности за инженера, а ползите и загубите на управлението да се занимават с тях, на когото е отредено по длъжност.
Вариант 1 - „Работа по запитване“
Оставяме всичко както е. Ако поръчителят не е доволен от работоспособността, производителността на базата данни или приложението, той ще уведоми DBA инженери по e-mail или като създаде инцидент в тикет системата.
Инженерът, получил известие, ще се запознае с проблема, ще предложи решение или ще отложи проблема, надявайки се, че всичко ще се разреши само, а в крайна сметка скоро всичко ще бъде забравено.
Сладкиши и тестени изделия, синини и ударни точкиСладкиши и тестени изделия:
1. Няма нужда да правим нищо излишно
2. Винаги има възможност да се откажем и да избегнем работа.
3. Много време, което можем да използваме по собствено усмотрение.
Синини и ударни точки:
1. Рано или късно поръчителят ще се замисли за същността на живота и универсалната справедливост в този свят и отново ще си зададе въпроса - за какво плащам с парите си? Последствието винаги е едно - въпросът е само кога поръчителят ще се отегчи и ще махне с ръка за сбогом. И храната ще изчезне. Това е тъжно.
2. Развитието на инженера - нула.
3. Трудности при планирането на работата и натоварването
Вариант 2 - „Танцуваме с бубни, продаваме и обуваме“
Пункт 1-Защо ни е система за мониторинг, ще получаваме всичко чрез заявки. Пускаме куп всякакви заявки към данни и динамични представления, включваме всякакви броячи, обобщаваме всичко в таблици, периодично сякаш анализираме списъците и таблиците. В резултат имаме красиви или не особено графики, таблици, отчети. Важно е да е колкото се може повече.
Пункт 2-Генерираме активност - стартираме анализ на всичко това.
Пункт 3-Подготвяме някакъв документ, наричаме този документ просто - "как да започнем с базата данни".
Пункт 4-Поръчителят, виждайки всичкото това великолепие от графики и цифри, остава в детската наивна увереност - ето, сега всичко ще работи, скоро. И лесно и безболезнено се разделя със своите финансови ресурси. Мениджмънтът също е убеден - инженерите ни работят на максимум. Натоварването е на макс.
Пункт 5-Регулярно повторете Пункт 1.
Сладкиши и тестени изделия, синини и ударни точкиСладкиши и тестени изделия:
1. Животът на мениджърите и инженерите е прост, предвидим и изпълнен с активност. Всичко жужи, всички са заети.
2. Животът на поръчителя също не е лош - той винаги е уверен, че трябва само да изчака малко и всичко ще се нареди. Не се нарежда, какво да се прави - светът е несправедлив, в следващия живот - ще му повърви.
Синини и ударни точки:
1. Рано или късно, по-добър доставчик на аналогична услуга ще направи същото, но малко по-евтино. И ако резултатът е същият, защо да плащате повече? Това отново ще доведе до изчезването на доходите.
2. Това е скучно. Както е скучна всяка неуместна дейност.
3. Както в предишния вариант — няма развитие. Но за инженера е минус, тъй като тук трябва постоянно да генерира база данни. А това отнема време. Който може да бъде изразходван с полза за себе си. Защото ако не се грижиш за себе си, никой друг не се интересува.
Вариант 3 - Не е нужно да измисляте колелото, просто купете го и карайте.
Инженерите от другите компании не ядат пица и не пият бира без причина (ах, славните времена в Петербург през 90-те). Нека да използваме мониторингови системи, които са създадени, отлагани и работят, и които всъщност носят полза (поне на техните създатели).
Сладкиши и тестени изделия, синини и ударни точкиСладкиши и тестени изделия:
1. Не е нужно да губите време, за да измисляте неща, които вече са измислени. Просто вземете и ползвайте.
2. Мониторинговите системи не са писани от глупаци, те определено са полезни.
3. Работещите мониторингови системи обикновено предоставят полезна филтрирана информация.
Синини и ударни точки:
1. Инженерът тук не е инженер, а просто потребител на чужд продукт. Или потребител.
2. Трябва да убедите клиента в необходимостта да купи нещо, в което той наистина не иска да се разбира, и не трябва, и изобщо бюджетът за годината е потвърден и няма да се променя. След това трябва да се отделят ресурси и да се настрои конкретната система. Тоест, първо трябва да платите, да платите и отново да платите. А клиентът е стиснат. Това е нормално за този живот.
Какво да правим, Чернышевски? Въпросът ти е напълно уместен. (с)
В конкретния случай и при сложилата се ситуация можем да подходим малко по-различно — а да създадем наша собствена мониторингова система.
Не точно система в пълния смисъл на думата, това звучи твърде помпозно и самонадеяно, но поне малко да улесним задачата за събиране на повече информация за решаване на проблеми с производителността. За да не се окажем в ситуацията — „иди там, не знам къде, намери онова, не знам какво“.
Какви са плюсовете и минусите на този вариант:
Плюсове:
1. Това е интересно. Най-малкото е по-интересно, отколкото постоянното „shrink datafile, alter tablespace и т.н.“
2. Това са нови умения и ново развитие. В перспектива, рано или късно, те ще донесат заслужените награди и изненади.
Недостатъци:
1. Ще се наложи да работите. Да работите много.
2. Ще се наложи редовно да обяснявате смисъла и перспективите на цялата активност.
3. Нещо ще се наложи да пожертвате, защото единственият ресурс, достъпен за инженера — времето — е ограничен от Вселената.
4. Най-страшното и неприятното — в резултат може да се получи нещо като „Не мишле, не жаба, а неизвестно създание.“
Който не рискува, не пие шампанско.
И така — започва най-интересното.
Общата идея — схемно
(Илюстрацията е взета от статия «»)
Обяснение:
- В целевата база се инсталира стандартното разширение PostgreSQL — “pg_stat_statements”.
- В базата данни за мониторинг създаваме набор от служебни таблици за съхранение на историята на pg_stat_statements в началния етап и за настройка на метрики и мониторинг в бъдеще.
- На хостинга за мониторинг създаваме набор от bash-скриптове, включително за генериране на инциденти в системата за тикети.
Служебни таблици
За започване, схемно опростена ERD, какво всъщност се получи:
Кратко описание на таблицитеendpoint — хост, точка на свързване с инстанцията
database — параметри на базата данни
pg_stat_history — историческа таблица за съхранение на времеви моменти на представянето на pg_stat_statements на целевата база данни
metric_glossary — речник на метриките за производителност
metric_config — конфигурация на отделни метрики
metric — конкретна метрика за запитването, което се наблюдава
metric_alert_history — история на предупрежденията за производителността
log_query — служебна таблица за съхранение на анализирани записи от лог файла на PostgreSQL, зареждан с AWS
baseline — параметри на времевия период, използван като базов
checkpoint — конфигурация на метриките за проверка на състоянието на базата данни
checkpoint_alert_history — история на предупрежденията на метриките за проверка на състоянието на базата данни
pg_stat_db_queries — служебна таблица с активни запитвания
activity_log — служебна таблица на журнал на активността
trap_oid — служебна таблица за конфигурация на trap
Етап 1 — събираме статистическа информация за производителността и получаваме отчети
За съхранение на статистическата информация служи таблицата pg_stat_history
Структура на таблицата pg_stat_history
Таблица "public.pg_stat_history"
Колонка | Тип | Модификатори
---------------------+-----------------------------+-------------------------------------------
id | цялостно | не е null по подразбиране nextval('pg_stat_history_id_seq'::regclass)
snapshot_timestamp | времева печат без времева зона |
database_id | цялостно |
dbid | oid |
userid | oid |
queryid | голямо цяло |
query | текст |
calls | голямо цяло |
total_time | двойна прецизност |
min_time | двойна прецизност |
max_time | двойна прецизност |
mean_time | двойна прецизност |
stddev_time | двойна прецизност |
rows | голямо цяло |
shared_blks_hit | голямо цяло |
shared_blks_read | голямо цяло |
shared_blks_dirtied | голямо цяло |
shared_blks_written | голямо цяло |
local_blks_hit | голямо цяло |
local_blks_read | голямо цяло |
local_blks_dirtied | голямо цяло |
local_blks_written | голямо цяло |
temp_blks_read | голямо цяло |
temp_blks_written | голямо цяло |
blk_read_time | двойна прецизност |
blk_write_time | двойна прецизност |
baseline_id | цялостно |
Индекси:
"pg_stat_history_pkey" ПЪРВИЧЕН КЛЮЧ, btree (id)
"database_idx" btree (database_id)
"queryid_idx" btree (queryid)
"snapshot_timestamp_idx" btree (snapshot_timestamp)
Чуждестранни ключови ограничения:
"database_id_fk" ЧУЖДЕСТРАНЕН КЛЮЧ (database_id) УКАЗВА на database(id) ПРИ ИЗТРИВАНЕ CASCADEКакто се вижда, таблицата представлява само кумулативни данни от представяне pg_stat_statements в целевата база данни.
Използването на тази таблица е много просто
pg_stat_history ще представлява натрупана статистика за изпълнението на заявките за всеки час. В началото на всеки час, след запълването на таблицата, статистиката pg_stat_statements се нулира с помощта на pg_stat_statements_reset().
Бележка: статистика се събира за заявки с времетраене на изпълнението над 1 секунда.
Запълване на таблицата pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'СЪЗДАВА СЕ НОВ СНИМКА';
--Свързване с целевата база данни
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % и dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Създаване на снимка на pg_stat_statements за базата данни %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Създаване на снимка на pg_stat_statements за заявки с min_time по-голям от 1000ms';
FOR pg_stat_snapshot IN
--Всички заявки с max_time по-голям от 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( dbid oid , userid oid , queryid bigint ,query text , calls bigint ,
total_time double precision ,min_time double precision ,max_time double precision , mean_time double precision , stddev_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;В резултат на това, след известно време в таблицата pg_stat_history ще имаме набор от снимки на съдържанието на таблицата pg_stat_statements на целевата база данни.
Същинско отчитане
Използвайки прости заявки, можем да получим доста полезни и интересни отчети.
Агрегирани данни за зададен период от време
Запитване
SELECT
database_id ,
SUM(calls) AS calls ,SUM(total_time) AS total_time ,
SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,
SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit ,
SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied ,
SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read,
SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time ,
SUM(blk_write_time) AS blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;Време на базата данни
to_char(interval ‘1 millisecond’ * pg_total_stat_history_rec.total_time, ‘HH24:MI:SS.MS’)
I/O Време
to_char(interval ‘1 millisecond’ * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), ‘HH24:MI:SS.MS’)
TOP10 SQL по общо време
Запитване
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(total_time) AS total_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10------------------------------------------------------------------------------------- | TOP10 SQL ПО ОБЩО ВРЕМЕ ЗА ИЗПЪЛНЕНИЕ | #| queryid| calls| calls %| total_time (ms) | dbtime % +----+-----------+-----------+-----------+--------------------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 ms.)| .03
TOP10 SQL по общо I/O време
Запитване
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(blk_read_time + blk_write_time) AS io_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10---------------------------------------------------------------------------------------- | ТОП10 SQL ПО ОБЩО ВРЕМЕ ЗА ВВОД/ИЗВОД | #| queryid| извиквания| % извиквания| Време за I/O (мс)|% време за I/O на база данни +----+-----------+-----------+-----------+--------------------------------+------------- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 мс.)| 31.06 | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 мс.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 мс.)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 мс.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 мс.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 мс.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 мс.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 мс.)| .03
ТОП10 SQL по максимално време на изпълнение
Запитване
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
max_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC
LIMIT 10----------------------------------------------------------------------------------------- | ТОП10 SQL ПО МАКСИМАЛНО ВРЕМЕ НА ИЗПЪЛНЕНИЕ | #| snapshot| snapshotID| queryid| max_time (мс) +----+------------------+-----------+-----------+---------------------------------------- | 1| 05.04.2019 01:03| 4169| 655729273| 00:02:01.869( 121869.981 мс.) | 2| 04.04.2019 17:00| 4153| 821760255| 00:01:41.570( 101570.841 мс.) | 3| 04.04.2019 16:00| 4146| 821760255| 00:01:41.570( 101570.841 мс.) | 4| 04.04.2019 16:00| 4144| 4152624390| 00:01:36.964( 96964.607 мс.) | 5| 04.04.2019 17:00| 4151| 4152624390| 00:01:36.964( 96964.607 мс.) | 6| 05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150 мс.) | 7| 04.04.2019 17:00| 4150| 2460318461| 00:01:33.113( 93113.835 мс.) | 8| 04.04.2019 15:00| 4140| 1484454471| 00:00:11.892( 11892.302 мс.) | 9| 04.04.2019 16:00| 4145| 1484454471| 00:00:11.892( 11892.302 мс.) | 10| 04.04.2019 17:00| 4152| 1484454471| 00:00:11.892( 11892.302 мс.)
ТОП10 SQL по четене/писане на SHARED buffer
Запитване
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC , 5 DESC
LIMIT 10-------------------------------------------------------------------------------------------- | ТОП10 SQL ПО ЧИТАНЕ/ЗАПИС В СПОДЕЛЕН БУФЕР | #| момент| snapshotID| queryid| прочетени споделени блокове| записани споделени блокове +----+------------------+-----------+-----------+---------------------+--------------------- | 1| 04.04.2019 17:00| 4153| 821760255| 797308| 0 | 2| 04.04.2019 16:00| 4146| 821760255| 797308| 0 | 3| 05.04.2019 01:03| 4169| 655729273| 797158| 0 | 4| 04.04.2019 16:00| 4144| 4152624390| 756514| 0 | 5| 04.04.2019 17:00| 4151| 4152624390| 756514| 0 | 6| 04.04.2019 17:00| 4150| 2460318461| 734117| 0 | 7| 04.04.2019 17:00| 4155| 3644780286| 52973| 0 | 8| 05.04.2019 01:03| 4168| 1053044345| 52818| 0 | 9| 04.04.2019 15:00| 4141| 2194493487| 52813| 0 | 10| 04.04.2019 16:00| 4147| 2194493487| 52813| 0 --------------------------------------------------------------------------------------------
Хистограма на разпределението на заявките по максимално време за изпълнение
Запитвания
SELECT
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;
SELECT
SUM(calls) AS calls
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( max_time >= hist_current_min AND max_time < hist_current_max ) ;
|----------------------------------------------------------------------------------------------- | ХИСТОГРАМА НА MAX_TIME | ОБЩО ПОЗВОНЯВАНИЯ : 33851920 | МИНИМАЛНО ВРЕМЕ : 00:00:01.063 | МАКСИМАЛНО ВРЕМЕ : 00:02:01.869 --------------------------------------------------------------------------------- | мин продължителност| макс продължителност| повиквания +----------------------------------+----------------------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0
ТОП10 Снимки по Заявки на Секунда
Запитвания
--pg_qps.sql
--Изчисляване на Запитвания на Втора
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
pg_stat_history_rec record ;
prev_pg_stat_history_id integer ;
prev_pg_stat_history_rec record;
total_seconds double precision ;
result double precision;
BEGIN
result = 0 ;
SELECT *
INTO pg_stat_history_rec
FROM
pg_stat_history
WHERE id = pg_stat_history_id ;
IF pg_stat_history_rec.snapshot_timestamp IS NULL
THEN
RAISE EXCEPTION 'ГРЕШКА - Не е намерена pg_stat_history за id = %',pg_stat_history_id;
END IF ;
--RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id ,
pg_stat_history_rec.snapshot_timestamp ;
SELECT
MAX(id)
INTO
prev_pg_stat_history_id
FROM
pg_stat_history
WHERE
database_id = pg_stat_history_rec.database_id AND
queryid IS NULL AND
id 0
THEN
result = pg_stat_history_rec.calls / total_seconds ;
ELSE
result = 0 ;
END IF;
RETURN result ;
END
$$ LANGUAGE plpgsql;
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( select pg_qps( id )) IS NOT NULL
ORDER BY 5 DESC
LIMIT 10
|----------------------------------------------------------------------------------------------- | TOP10 Снимки по брой Запитвания в секунди ----------------------------------------------------------------------------------------------------------------------------------------------- | #| снимка| ID на снимка| повиквания| общо време в БД| QPS| I/O време| % I/O време +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 2| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 3| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 4| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 5| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 6| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 7| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 8| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 9| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 10| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666
Часова история на изпълнението с QPS и I/O време
Запитване
SELECT
id ,
време_на_снимката ,
повиквания ,
общо_време ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
време_на_снимката BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | ИСТОРИЯ НА ИЗПЪЛНЕНИЕ ПО ЧАСОВЕ С QueryPerSeconds и I/O време ----------------------------------------------------------------------------------------------------------------------------------------------- | ИСТОРИЯ НА КВОТИТЕ ЗА СЕКУНДА | #| момент| snapshotID| повиквания| общо време в БД| QPS| I/O време| % I/O време +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 11:00| 4131| 3747| 00:00:00.835( 835.374 ms.)| 1.041| 00:00:00.000( .000 ms.)| .000 | 2| 04.04.2019 12:00| 4133| 1002722| 00:01:52.419( 112419.376 ms.)| 278.534| 00:00:00.149( 149.105 ms.)| .133 | 3| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 4| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 5| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 6| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 7| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 8| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666 | 9| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 10| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 11| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 12| 04.04.2019 23:03| 4165| 1443155| 00:01:34.467( 94467.539 ms.)| 200.438| 00:00:00.015( 15.287 ms.)| .016 | 13| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 14| 05.04.2019 02:03| 4171| 189852| 00:00:10.989( 10989.899 ms.)| 52.737| 00:00:00.539( 539.110 ms.)| 4.906 | 15| 05.04.2019 03:01| 4173| 3627| 00:00:00.103( 103.000 ms.)| 1.042| 00:00:00.004( 4.131 ms.)| 4.010 | 16| 05.04.2019 04:00| 4175| 3627| 00:00:00.085( 85.235 ms.)| 1.025| 00:00:00.003( 3.811 ms.)| 4.471 | 17| 05.04.2019 05:00| 4177| 3747| 00:00:00.849( 849.454 ms.)| 1.041| 00:00:00.006( 6.124 ms.)| .721 | 18| 05.04.2019 06:00| 4179| 3747| 00:00:00.849( 849.561 ms.)| 1.041| 00:00:00.000( .051 ms.)| .006 | 19| 05.04.2019 07:00| 4181| 3747| 00:00:00.839( 839.416 ms.)| 1.041| 00:00:00.000( .062 ms.)| .007 | 20| 05.04.2019 08:00| 4183| 3747| 00:00:00.846( 846.382 ms.)| 1.041| 00:00:00.000( .007 ms.)| .001 | 21| 05.04.2019 09:00| 4185| 3747| 00:00:00.855( 855.426 ms.)| 1.041| 00:00:00.000( .065 ms.)| .008 | 22| 05.04.2019 10:00| 4187| 3797| 00:01:40.150( 100150.165 ms.)| 1.055| 00:00:21.845( 21845.217 ms.)| 21.812
Текст на всички SQL-заявки
Запитване
ИЗБЕРИ
queryid ,
query
ОТ
pg_stat_history
КЪДЕ
queryid Е НЕ NULL И
database_id = DATABASE_ID И
snapshot_timestamp НАМЕСТО BEGIN_TIMEPOINT И END_TIMEPOINT
ГРУПИРАЙ ПО queryid , query
Резюме
Както може да се види, с доста прости средства можем да получим доста полезна информация за натовареността и състоянието на базата.
Забележка:Ако в запитванията фокусираме queryid, ще получим история за отделно запитване (с цел спестяване на място, отчетите за отделно запитване са пропуснати).
И така, статистическите данни за производителността на запитванията — налични и събирани.
Първият етап „събиране на статистически данни“ — завършен.
Можем да преминем към втория етап – „настройка на метриките за производителност“.
Но това вече е съвсем друга история.
Продължение следва…
Източник: habr.com
