Inženýr – přeloženo z latiny – inspirovaný.
Inženýr může dělat cokoliv. (c) R. Diesel.
Epigrafy.
Nebo příběh o tom, proč si správce databáze musí pamatovat svou programátorskou minulost.
předmluva
Všechna jména byla změněna. Zápasy jsou náhodné. Materiál je pouze osobním názorem autora.
Zřeknutí se záruk: v plánované sérii článků nebude podrobný a přesný popis použitých tabulek a skriptů. Materiály nelze okamžitě použít "TAK JAK JSOU".
Za prvé, kvůli velkému množství materiálu,
za druhé kvůli ostrosti s výrobní základnou skutečného zákazníka.
V článcích proto budou uvedeny pouze myšlenky a popisy v nejobecnější podobě.
Možná v budoucnu systém poroste na úroveň postování na GitHubu, nebo možná ne. Čas ukáže.
Začátek příběhu -
Co se stalo jako výsledek, v nejobecnějších termínech –“
Proč to všechno potřebuji?
No, za prvé, abyste nezapomněli na sebe, vzpomínáte na slavné dny v důchodu.
Za druhé, systematizovat to, co bylo napsáno. Za sebe už někdy začínám být zmatený a zapomínám na jednotlivé části.
No a hlavně – najednou to může někomu přijít vhod a pomoci nevynalézat znovu kolo a nesbírat hrábě. Jinými slovy, vylepšete svou karmu (ne Khabrovského). Neboť to nejcennější na tomto světě jsou nápady. Hlavní je najít nápad. A převést myšlenku do reality je již čistě technická záležitost.
Tak začneme pomalu...
Prohlášení o problému.
Dostupný:
PostgreSQL(10.5), smíšené zatížení (OLTP+DSS), střední až lehké zatížení, hostováno v cloudu AWS.
Nedochází k monitorování databáze, monitorování infrastruktury je prezentováno jako standardní nástroje AWS v minimální konfiguraci.
Požadováno:
Monitorujte výkon a stav databáze, vyhledejte a mějte počáteční informace pro optimalizaci náročných databázových dotazů.
Stručný úvod nebo analýza řešení
Pro začátek zkusme analyzovat možnosti řešení problému z pohledu srovnávací analýzy přínosů a problémů pro inženýra a nechme ty, kteří mají být na seznamu zaměstnanců, aby se s přínosy a ztrátami vypořádali. řízení.
Možnost 1 – „Práce na vyžádání“
Vše necháme tak, jak je. Pokud zákazník není spokojen s něčím v oblasti zdraví, výkonu databáze nebo aplikace, upozorní na to techniky DBA e-mailem nebo vytvořením incidentu v pokladně.
Inženýr, který obdrží upozornění, problém pochopí, nabídne řešení nebo problém odloží v naději, že se vše vyřeší samo, a stejně bude brzy zapomenuto.
Perník a koblihy, modřiny a boulePerník a koblihy:
1. Nic extra
2. Vždy je možnost vystoupit a ušpinit se.
3. Spoustu času, který můžete trávit sami.
Modřiny a boule:
1. Zákazník se dříve nebo později zamyslí nad podstatou bytí a univerzální spravedlnosti v tomto světě a znovu si položí otázku – proč jim platím své peníze? Důsledek je vždy stejný – otázkou je pouze to, kdy se zákazník nudí a mávne rukou na rozloučenou. A podavač je prázdný. Je to smutné.
2. Vývoj inženýra je nulový.
3. Potíže s plánováním práce a nakládáním
Možnost 2 - „Tanec s tamburínami, nazout a obout boty“
odstavec 1-Proč potřebujeme monitorovací systém, budeme dostávat všechny požadavky. Spouštíme hromadu nejrůznějších dotazů do datového slovníku a dynamických pohledů, zapínáme nejrůznější počítadla, převádíme vše do tabulek, periodicky analyzujeme seznamy a tabulky, jak to bylo. Ve výsledku máme krásné nebo nepříliš mocné grafy, tabulky, reporty. Hlavní věc - to by bylo víc, víc.
odstavec 2-Generovat aktivitu -provádět analýzu toho všeho.
odstavec 3-Připravujeme určitý dokument, tomuto dokumentu říkáme jednoduše - "jak vybavíme databázi."
odstavec 4- Zákazník, který vidí všechnu tu velkolepost grafů a obrázků, je v dětské naivní důvěře - nyní nám bude vše brzy fungovat. A snadno a bezbolestně se rozloučit se svými finančními prostředky. Vedení je také jisté, že naši inženýři tvrdě pracují. Maximální zatížení.
odstavec 5- Pravidelně opakujte krok 1.
Perník a koblihy, modřiny a boulePerník a koblihy:
1. Život manažerů a inženýrů je jednoduchý, předvídatelný a plný aktivit. Všechno bzučí, všichni jsou zaneprázdněni.
2. Život zákazníka také není špatný – vždy si je jistý, že je potřeba být trochu trpělivý a vše klapne. Nelepší se, no, no - tento svět je nespravedlivý, v příštím životě - štěstí.
Modřiny a boule:
1. Dříve nebo později se najde chytřejší poskytovatel podobné služby, který bude dělat to samé, ale o něco levněji. A když je výsledek stejný, proč platit víc. Což opět povede ke zmizení krmítka.
2. Je to nuda. Jak nudná každá trochu smysluplná činnost.
3. Stejně jako v předchozí verzi - žádný vývoj. Ale pro inženýra je mínus, že na rozdíl od první možnosti zde musíte neustále generovat IDB. A to chce čas. Které lze utratit ve prospěch vašeho blízkého. Protože se o sebe nedokážeš postarat, všem na tobě záleží.
Možnost 3-Netřeba vymýšlet kolo, je potřeba si ho koupit a jezdit na něm.
Inženýři z jiných firem vědomě jedí pizzu s pivem (ach, ty slavné časy Petrohradu v 90. letech). Používejte monitorovací systémy, které jsou vyrobené, odladěné a fungující a obecně řečeno přinášejí výhody (teda alespoň jejich tvůrcům).
Perník a koblihy, modřiny a boulePerník a koblihy:
1. Není třeba ztrácet čas vymýšlením toho, co je již vymyšleno. Vezměte a použijte.
2. Monitorovací systémy nepíšou hlupáci a samozřejmě jsou užitečné.
3. Funkční monitorovací systémy obvykle poskytují užitečné filtrované informace.
Modřiny a boule:
1. Inženýr v tomto případě není inženýr, ale pouze uživatel produktu někoho jiného nebo uživatel.
2. Zákazník musí být přesvědčen o nutnosti koupit něco, čemu obecně nechce rozumět a neměl by a celkově rozpočet na rok byl schválen a nebude se měnit. Poté musíte přidělit samostatný zdroj a nakonfigurovat jej pro konkrétní systém. Tito. Nejprve musíte zaplatit, zaplatit a znovu zaplatit. A zákazník je lakomý. To je norma tohoto života.
Co dělat, Chernyshevsky? Vaše otázka je velmi aktuální. (S)
V tomto konkrétním případě a současné situaci můžete udělat trochu jinak - pojďme vytvořit vlastní monitorovací systém.
No, ne systém, samozřejmě v plném slova smyslu, tohle je příliš hlasité a troufalé, ale alespoň si to nějak usnadněte a shromážděte více informací pro řešení incidentů s výkonem. Abyste se neocitli v situaci - "jdi tam, nevím kam, najdi to, nevím co."
Jaké jsou výhody a nevýhody této možnosti:
výhody:
1. Je to zajímavé. No, přinejmenším zajímavější než neustálé "zmenšit datový soubor, změnit tabulkový prostor atd."
2. Jedná se o nové dovednosti a nový vývoj. Které v budoucnu dříve nebo později dají zasloužené perníčky a koblihy.
nevýhody:
1. Musí pracovat. Hodně pracovat.
2. Budete muset pravidelně vysvětlovat význam a perspektivy veškeré činnosti.
3. Něco bude muset být obětováno, protože jediný zdroj, který má inženýr k dispozici – čas – je omezen Vesmírem.
4. Nejhorší a nejnepříjemnější - v důsledku toho se mohou objevit odpadky typu "Ne myš, ne žába, ale neznámé malé zvířátko".
Kdo něco neriskuje, nepije šampaňské.
Takže zábava začíná.
Obecná představa - schéma
(Ilustrace převzata z článku «
Vysvětlení:
- Cílová databáze je nainstalována se standardním rozšířením PostgreSQL „pg_stat_statements“.
- V databázi monitorování vytváříme sadu tabulek služeb pro ukládání historie pg_stat_statements v počáteční fázi a pro konfiguraci metrik a monitorování v budoucnu.
- Na monitorovacím hostiteli vytváříme sadu bash skriptů, včetně těch pro generování incidentů v tiketovém systému.
Servisní stoly
Pro začátek schematicky zjednodušený ERD, co se stalo nakonec:
Stručný popis tabulekKoncový bod - hostitel, bod připojení k instanci
databáze - možnosti databáze
pg_stat_history - historická tabulka pro ukládání dočasných snímků pohledu pg_stat_statements cílové databáze
metrický_slovník - Slovník metrik výkonu
metric_config - konfigurace jednotlivých metrik
metrický - specifická metrika pro požadavek, který je monitorován
metric_alert_history - historie varování o výkonu
log_query - servisní tabulka pro ukládání analyzovaných záznamů ze souboru protokolu PostgreSQL staženého z AWS
základní - parametry časového období použitého jako základ
kontrolní bod - konfigurace metrik pro kontrolu stavu databáze
checkpoint_alert_history - historie varování u metrik kontroly stavu databáze
pg_stat_db_queries — servisní tabulka aktivních požadavků
protokol aktivit — tabulka služeb protokolu činností
trap_oid - tabulka služeb konfigurace trapu
Fáze 1 – shromažďování statistik výkonu a získávání zpráv
Pro ukládání statistických informací se používá tabulka. pg_stat_history
Struktura tabulky pg_stat_history
Tabulka "public.pg_stat_history" Sloupec | typ | Modifikátory--------------------+-------------------- --+---- -------------------------------- id | celé číslo | není null výchozí nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | časové razítko bez časového pásma | Database_id | celé číslo | dbid | oid | uživatelské id | oid | id dotazu | bigint | dotaz | text | volá | bigint | celkový_čas | dvojitá přesnost | min_time | dvojitá přesnost | max_time | dvojitá přesnost | střední_čas | dvojitá přesnost | stddev_time | dvojitá přesnost | řádky | 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 | dvojitá přesnost | blk_write_time | dvojitá přesnost | základní_id | celé číslo | Indexy: "pg_stat_history_pkey" PRIMÁRNÍ KLÍČ, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Omezení cizích klíčů (databáze_databáze: FEIk GNORENCEDATAbase_KEY) FOREIFERENCE ) NA VYMAZÁNÍ KASKÁDY
Jak vidíte, tabulka je pouze kumulativní zobrazení dat pg_stat_statements v cílové databázi.
Použití tohoto stolu je velmi jednoduché.
pg_stat_history bude představovat nashromážděné statistiky provádění dotazu za každou hodinu. Na začátku každé hodiny po vyplnění tabulky statistika pg_stat_statements resetovat pomocí pg_stat_statements_reset().
Poznámka: Statistiky se shromažďují pro požadavky s trváním delším než 1 sekunda.
Naplnění tabulky 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 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',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 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 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;
V důsledku toho po určité době v tabulce pg_stat_history budeme mít sadu snímků obsahu tabulky pg_stat_statements cílové databáze.
Vlastně hlášení
Pomocí jednoduchých dotazů můžete získat docela užitečné a zajímavé zprávy.
Agregovaná data za dané časové období
Dotaz
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 ;
D.B. Čas
to_char(interval '1 milisekunda' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Čas I/O
to_char(interval '1 milisekunda' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP 10 SQL podle total_time
Dotaz
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 PODLE CELKOVÉHO ČASU PROVEDENÍ | #| id dotazu| hovory| volá %| celkový_čas (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 podle celkového času I/O
Dotaz
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
-------------------------------------------------- --------------------------------------- | TOP 10 SQL PODLE CELKOVÉHO ČASU I/O | #| id dotazu| hovory| volá %| I/O čas (ms)|db I/O čas % +----+-----------+-----------+------- -----+--------------------------------+------------ -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06. června | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35:4 | 2460318461| 1| 00000| .00|04:05.981:245981.117( 14.93 ms.)| 5 | 1484454471| 4| 00001| .00|00:39.144:39144.221( 2.38 ms.)| 6 | 2194493487| 4| 00001| .00|00:18.182:18182.816( 1.10 ms.)| 7 | 1053044345| 1| 00000| .00|00:16.611:16611.722( 1.01 ms.)| 8 | 3644780286| 1| 00000| .00|00:00.436:436.205( 03 ms.)| .XNUMX
TOP10 SQL podle maximální doby provedení
Dotaz
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
-------------------------------------------------- ------------------------------------- | TOP10 SQL PODLE MAXIMÁLNÍ DOBY PROVEDENÍ | #| snímek| ID snímku| id dotazu| max_time (ms) +----+------------------+-----------+--------- --+----------------------------------------- | 1| 05.04.2019 01:03| 4169| 655729273| 00:02:01.869( 121869.981 ms.) | 2| 04.04.2019. 17. 00 4153:821760255| 00| 01| 41.570:101570.841:3( 04.04.2019 ms.) | 16| 00. 4146. 821760255 00:01| 41.570| 101570.841| 4:04.04.2019:16( 00 ms.) | 4144| 4152624390. 00. 01 36.964:96964.607| 5| 04.04.2019| 17:00:4151( 4152624390 ms.) | 00| 01. 36.964. 96964.607 6:05.04.2019| 10| 00| 4188:1484454471:00( 01 ms.) | 33.452| 93452.150. 7. 04.04.2019 17:00 | 4150| 2460318461| 00:01:33.113( 93113.835 ms.) | 8| 04.04.2019. 15. 00 4140:1484454471| 00| 00| 11.892:11892.302:9( 04.04.2019 ms.) | 16| 00. 4145. 1484454471 00:00| 11.892| 11892.302| 10:04.04.2019:17( 00 ms.) | 4152| 1484454471. 00. 00 11.892:11892.302| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX. XNUMX. XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)
TOP10 SQL podle SDÍLENÉ vyrovnávací paměti pro čtení/zápis
Dotaz
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
-------------------------------------------------- ------------------------------------- | TOP10 SQL PODLE SDÍLENÉ VYROVNÁVACÍ PAMĚTI ČTENÍ/ZÁPIS | #| snímek| ID snímku| id dotazu| sdílené bloky čtení| sdílené bloky zápis +----+------------------+-----------+----------- -+---------------------+---------------------- | 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 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX. XNUMX. XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX ------------------------------------------------- -------------------------------------------------
Histogram rozložení dotazu podle maximální doby provedení
žádosti
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 HISTOGRAM | CELKEM VOLÁNÍ : 33851920 | MINIMÁLNÍ ČAS : 00:00:01.063 | MAX. ČAS: 00:02:01.869 ---------------------------------- -------- ----------------------------- | min trvání| maximální trvání| hovory +----------------------------------+-------------- ---------------------+----------- | 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
TOP 10 snímků podle dotazu za sekundu
žádosti
--pg_qps.sql
--Calculate Query Per Second
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 'ERROR - Not found pg_stat_history for 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 < pg_stat_history_rec.id ;
IF prev_pg_stat_history_id IS NULL
THEN
RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
RETURN NULL ;
END IF;
SELECT *
INTO prev_pg_stat_history_rec
FROM
pg_stat_history
WHERE id = prev_pg_stat_history_id ;
--RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;
total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
--RAISE NOTICE 'total_seconds = % ', total_seconds ;
--RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;
IF total_seconds > 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
|------------------------------------------------- ----------------------------------------- | TOP 10 snímků seřazených podle čísel QueryPerSeconds -------------------------------------- ------ -------------------------------------------- ------ -------------------------------------------- | #| snímek| ID snímku| hovory| celkový dbtime| QPS | I/O čas | % času I/O +-----+------------------+-----------+-------- ----+----------------------------------+----------- -+----------------------------------+------------ | 1| 04.04.2019. 20. 04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00. 4149. 3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360. 00. 10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03( 4163 ms.)| 2781536 | 00| 03. 06.470. 186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755. 776.979. 00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00 ms.)| 4137| 2397326:00:04( 43.033 ms.)| .283033.854 | 665.924| 00. 00. 00.024 24.505:009 | 7| 04.04.2019| 15:00:4139( 2394416 ms.)| 00| 04:51.435:291435.010( 665.116 ms.)| .00 | 00| 12.025. 12025.895. 4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04 ms.)| 26.791| 266791.988:659.179:00( 00 ms.)| 00.064 | 64.261| 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 | XNUMX| XNUMX. XNUMX. XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX
Hodinová historie spouštění s QueryPerSeconds a I/O Time
Dotaz
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
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HOURLY EXECUTION HISTORY WITH QueryPerSeconds and I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | QUERY PER SECOND HISTORY | #| snapshot| snapshotID| calls| total dbtime| QPS| I/O time| I/O time % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 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
Text všech SQL výběrů
Dotaz
SELECT
queryid ,
query
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 , query
Celkový
Jak vidíte, poměrně jednoduchými prostředky můžete získat mnoho užitečných informací o vytížení a stavu databáze.
Poznámka:Pokud v dotazech opravíte ID dotazu, získáme historii pro samostatný požadavek (z důvodu úspory místa jsou vynechány sestavy pro samostatný požadavek).
Jsou tedy k dispozici a shromažďována statistická data o výkonu dotazů.
První etapa „sběr statistických údajů“ je dokončena.
Můžete přejít do druhé fáze – „konfigurace metrik výkonu“.
Ale je to úplně jiný příběh.
Chcete-li se pokračovat ...
Zdroj: www.habr.com