Inženjer - u prevodu sa latinskog - inspirisan.
Inženjer može sve. (c) R. Diesel.
Epigrafi.
Ili priča o tome zašto administrator baze podataka treba da se sjeća svoje programske prošlosti.
Predgovor
Sva imena su promijenjena. Utakmice su nasumične. Materijal je isključivo lično mišljenje autora.
Odricanje od garancije: u planiranoj seriji članaka neće biti detaljnog i tačnog opisa korištenih tabela i skripti. Materijali se ne mogu odmah koristiti "KAKO JESU".
Prvo, zbog velike količine materijala,
drugo, zbog oštrine sa proizvodnom bazom pravog kupca.
Stoga će se u člancima dati samo ideje i opisi u najopštijem obliku.
Možda će u budućnosti sistem porasti do nivoa objavljivanja na GitHub-u, a možda i ne. Vrijeme će pokazati.
Početak priče-
Ono što se desilo kao rezultat, najopštije rečeno - "
Zašto mi sve ovo treba?
Pa, prvo, da ne zaboraviš sebe, prisjećajući se slavnih dana u penziji.
Drugo, sistematizovati napisano. Za sebe, ponekad se zbunim i zaboravljam pojedine dijelove.
Pa, i što je najvažnije - odjednom može nekome dobro doći i pomoći da ne izmisli točak i da ne skuplja grabulje. Drugim riječima, poboljšajte svoju karmu (ne Khabrovsky). Jer najvrednije na ovom svijetu su ideje. Glavna stvar je pronaći ideju. A prevesti ideju u stvarnost već je čisto tehničko pitanje.
Pa počnimo polako...
Formulacija problema.
Dostupan:
PostgreSQL(10.5), mješovito opterećenje (OLTP+DSS), srednje do lagano opterećenje, smješteno u AWS oblaku.
Ne postoji nadzor baze podataka, nadzor infrastrukture je predstavljen kao standardni AWS alati u minimalnoj konfiguraciji.
Potrebno je:
Pratite performanse i status baze podataka, pronađite i imate početne informacije za optimizaciju teških upita baze podataka.
Kratak uvod ili analiza rješenja
Za početak, pokušajmo analizirati mogućnosti rješavanja problema sa stanovišta komparativne analize koristi i problema za inženjera, a koristima i gubicima neka se bave oni koji bi trebali biti na listi osoblja. menadžmenta.
Opcija 1 - "Rad na zahtjev"
Ostavljamo sve kako jeste. Ako korisnik nije zadovoljan nečim u zdravlju, performansama baze podataka ili aplikacije, obavijestit će DBA inženjere e-poštom ili kreiranjem incidenta u kutiji za ulaznice.
Inženjer će, nakon što dobije obavijest, shvatiti problem, ponuditi rješenje ili problem odložiti, nadajući se da će se sve riješiti samo od sebe, a ionako će sve uskoro biti zaboravljeno.
Medenjaci i krofne, modrice i kvrgeMedenjaci i krofne:
1. Ništa dodatno za raditi
2. Uvijek postoji prilika da izađete i uprljate se.
3. Puno vremena koje možete provesti sami.
Modrice i kvrge:
1. Prije ili kasnije, kupac će razmisliti o suštini bića i univerzalne pravde na ovom svijetu i još jednom sebi postaviti pitanje - zašto im ja plaćam svoj novac? Posljedica je uvijek ista - pitanje je samo kada se kupcu dosadi i mahne mu na pozdrav. A hranilica je prazna. To je tužno.
2. Razvoj inženjera je nula.
3. Poteškoće u planiranju rada i utovara
Opcija 2 - "Pleši s tamburama, obuj se i obuj"
Stav 1-Zašto nam je potreban sistem monitoringa, mi ćemo dobiti sve zahtjeve. Pokrećemo gomilu svih vrsta upita prema rječniku podataka i dinamičkim prikazima, uključujemo sve vrste brojača, sve unosimo u tabele, periodično analiziramo liste i tabele, takoreći. Kao rezultat, imamo lijepe ili ne baš grafike, tabele, izvještaje. Glavna stvar - to bi bilo više, više.
Stav 2-Generirajte aktivnost - pokrenite analizu svega ovoga.
Stav 3-Pripremamo određeni dokument, zovemo ovaj dokument, jednostavno - "kako da opremimo bazu podataka".
Stav 4- Kupac je, videći svu tu veličanstvenost grafikona i figura, u detinjastom naivnom poverenju - sada će nam sve proraditi, uskoro. I, lako i bezbolno se rastaju od svojih finansijskih sredstava. Uprava je također sigurna da naši inženjeri vrijedno rade. Max loading.
Stav 5- Redovno ponavljajte korak 1.
Medenjaci i krofne, modrice i kvrgeMedenjaci i krofne:
1. Život menadžera i inženjera je jednostavan, predvidljiv i ispunjen aktivnostima. Sve zuji, svi su zauzeti.
2. Život kupca takođe nije loš - on je uvek siguran da treba malo da se strpite i sve će uspeti. Ne postaje bolje, pa, dobro - ovaj svijet je nepravedan, u sljedećem životu - srećan.
Modrice i kvrge:
1. Prije ili kasnije, pojavit će se pametniji provajder slične usluge koji će učiniti istu stvar, ali malo jeftinije. A ako je rezultat isti, zašto plaćati više. Što će opet dovesti do nestanka hranilice.
2. Dosadno je. Kako je dosadna svaka mala značajna aktivnost.
3. Kao iu prethodnoj verziji - nema razvoja. Ali za inženjera, minus je što, za razliku od prve opcije, ovdje morate stalno generirati IDB. A za to treba vremena. Koje možete potrošiti za dobrobit voljene osobe. Jer ne možete da brinete o sebi, svi brinu o vama.
Opcija 3-Nema potrebe da izmišljate bicikl, morate ga kupiti i voziti.
Inženjeri iz drugih kompanija svjesno jedu pizzu s pivom (oh, slavna vremena Sankt Peterburga 90-ih). Hajde da koristimo sisteme za praćenje koji su napravljeni, otklonjeni i rade, a generalno govoreći, donose koristi (pa, barem svojim kreatorima).
Medenjaci i krofne, modrice i kvrgeMedenjaci i krofne:
1. Nema potrebe da gubite vreme na izmišljanje onoga što je već izmišljeno. Uzmi i koristi.
2. Sisteme za nadzor ne pišu budale, a naravno da su korisni.
3. Radni sistemi za praćenje obično pružaju korisne filtrirane informacije.
Modrice i kvrge:
1. Inženjer u ovom slučaju nije inženjer, već samo korisnik tuđeg proizvoda ili korisnik.
2. Kupac mora biti ubijeđen u potrebu kupovine nečega što generalno ne želi da razumije, a i ne bi trebao, a generalno je budžet za godinu odobren i neće se mijenjati. Zatim morate dodijeliti poseban resurs, konfigurirati ga za određeni sistem. One. Prvo morate platiti, platiti i ponovo platiti. I mušterija je škrta. Ovo je norma ovog života.
Šta da se radi, Černiševski? Vaše pitanje je veoma relevantno. (sa)
U ovom konkretnom slučaju i trenutnoj situaciji, možete učiniti malo drugačije - hajde da napravimo sopstveni sistem za praćenje.
Pa, nije sistem, naravno, u punom smislu te riječi, ovo je preglasno i drsko, ali barem nekako olakšajte sebi i prikupite više informacija za rješavanje incidenata performansi. Da se ne biste našli u situaciji - "idi tamo, ne znam gde, nađi to, ne znam šta."
Koje su prednosti i mane ove opcije:
Pros:
1. Zanimljivo je. Pa, barem zanimljivije od konstante "smanjite datoteku podataka, promijenite prostor tablice, itd."
2. To su nove vještine i novi razvoj. Koji će u budućnosti prije ili kasnije dati zaslužene medenjake i krofne.
Cons:
1. Moram raditi. Radite puno.
2. Morat ćete redovno objašnjavati značenje i perspektive svih aktivnosti.
3. Nešto će se morati žrtvovati, jer jedini resurs koji inženjer ima - vrijeme - ograničen je Univerzumom.
4. Najgore i najneugodnije - kao rezultat, može ispasti smeće poput "Ne miš, ne žaba, već nepoznata mala životinja".
Ko nesto ne rizikuje ne pije šampanjac.
Dakle, zabava počinje.
Opća ideja - shematski
(Ilustracija preuzeta iz članka «
Objašnjenje:
- Ciljna baza podataka je instalirana sa standardnom PostgreSQL ekstenzijom “pg_stat_statements”.
- U bazi podataka za praćenje kreiramo skup tabela usluga za pohranjivanje historije pg_stat_statements u početnoj fazi i za konfiguriranje metrike i nadzora u budućnosti
- Na hostu za praćenje kreiramo skup bash skripti, uključujući one za generisanje incidenata u sistemu tiketa.
Servisni stolovi
Za početak, shematski pojednostavljeni ERD, šta se na kraju dogodilo:
Kratak opis tabelakrajnja tačka - host, tačka veze sa instancom
baza podataka - opcije baze podataka
pg_stat_history - historijska tabela za pohranjivanje privremenih snimaka pg_stat_statements pogleda ciljne baze podataka
metric_glossary - Rečnik metrike performansi
metric_config - konfiguracija pojedinačnih metrika
metrički - specifična metrika za zahtjev koji se prati
metric_alert_history - istorijat upozorenja o performansama
log_query - servisna tabela za skladištenje raščlanjenih zapisa iz PostgreSQL log fajla preuzete sa AWS-a
baseline - parametri vremenskog perioda koji se koriste kao osnova
kontrolna točka - konfiguracija metrike za provjeru statusa baze podataka
checkpoint_alert_history - historija upozorenja metrike provjere statusa baze podataka
pg_stat_db_queries — servisna tabela aktivnih zahteva
dnevnik_aktivnosti — servisna tabela dnevnika aktivnosti
trap_oid - tabela usluge konfiguracije zamki
Faza 1 - prikupiti statistiku učinka i dobiti izvještaje
Tabela se koristi za pohranjivanje statističkih informacija. pg_stat_history
pg_stat_history struktura tabele
Tabela "public.pg_stat_history" Kolona | tip | Modifikatori-----------+--------------------- --+---- -------------------------------- id | cijeli broj | not null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | vremenska oznaka bez vremenske zone | database_id | cijeli broj | dbid | oid | userid | oid | queryid | bigint | upit | tekst | pozivi | bigint | ukupno_vrijeme | dvostruka preciznost | min_time | dvostruka preciznost | max_time | dvostruka preciznost | srednje_vrijeme | dvostruka preciznost | stddev_time | dvostruka preciznost | redovi | 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 | dvostruka preciznost | blk_write_time | dvostruka preciznost | baseline_id | cijeli broj | Indeksi: "pg_stat_history_pkey" PRIMARNI KLJUČ, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) btree (snapshot_timestamp) Foreign_key (snimka_timestamp) baza_id) REFERENCE baza podataka(id ) NA DELETE CASCADE
Kao što vidite, tabela je samo kumulativni prikaz podataka pg_stat_statements u ciljnoj bazi podataka.
Upotreba ove tablice je vrlo jednostavna.
pg_stat_history će predstavljati akumuliranu statistiku izvršenja upita za svaki sat. Na početku svakog sata, nakon popunjavanja tabele, statistika pg_stat_statements resetujte sa pg_stat_statements_reset().
Napomena: statistika se prikuplja za zahtjeve koji traju duže od 1 sekunde.
Popunjavanje tabele 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;
Kao rezultat, nakon određenog vremenskog perioda u tabeli pg_stat_history imaćemo skup snimaka sadržaja tabele pg_stat_statements ciljna baza podataka.
Zapravo izvještavam
Koristeći jednostavne upite, možete dobiti prilično korisne i zanimljive izvještaje.
Agregirani podaci za određeni vremenski period
Zahtjev
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. Vrijeme
to_char(interval '1 milisekunda' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O Time
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')
TOP10 SQL prema ukupno_vrijeme
Zahtjev
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 PO UKUPNOM VREMENU IZVRŠENJA | #| queryid| pozivi| poziva %| ukupno_vrijeme (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 po ukupnom I/O vremenu
Zahtjev
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
-------------------------------------------------- -------------------------------------- | TOP10 SQL PO UKUPNOM I/O VREMENU | #| queryid| pozivi| poziva %| I/O vrijeme (ms)|db I/O vrijeme % +----+-----------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06. juna | 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 ms.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 ms.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 ms.)| .03
TOP10 SQL po maksimalnom vremenu izvršenja
Zahtjev
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 PO MAKSIMALNOM VREMENU IZVRŠENJA | #| snimak| snapshotID| queryid| max_vrijeme (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 ms.) | 3| 04.04.2019 16:00| 4146| 821760255| 00:01:41.570( 101570.841 ms.) | 4| 04.04.2019 16:00| 4144| 4152624390| 00:01:36.964( 96964.607 ms.) | 5| 04.04.2019 17:00| 4151| 4152624390| 00:01:36.964( 96964.607 ms.) | 6| 05.04.2019 10:00 | 4188| 1484454471| 00:01:33.452( 93452.150 ms.) | 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 ms.) | 9| 04.04.2019 16:00| 4145| 1484454471| 00:00:11.892( 11892.302 ms.) | 10| 04.04.2019 17:00| 4152| 1484454471| 00:00:11.892( 11892.302 ms.)
TOP10 SQL po SHARED baferu čitanje/pisanje
Zahtjev
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 PO DIJELJENOM BUFERU ČITANJE/PISANJE | #| snimak| snapshotID| queryid| dijeljeni blokovi read| dijeljeni blokovi upisuju +----+------------------+-----------+---------- -+---------------------+--------------------- | 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 -------------------------------------------------- --------------------------------------------------
Histogram distribucije upita prema maksimalnom vremenu izvršenja
Zahtjevi
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 | UKUPNO POZIVA : 33851920 | MIN VRIJEME : 00:00:01.063 | MAX TIME : 00:02:01.869 ---------------------------------- -------- ---------------------------- | min trajanje| maksimalno trajanje| pozivi +----------------------------------+------------ ---------------------+---------- | 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
TOP10 snimaka prema upitu u sekundi
Zahtjevi
--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
|------------------------------------------------ ---------------------------------------- | TOP10 snimaka poredanih po QueryPerSeconds brojevima ------------------------------------- ------ -------------------------------------------- ------ ------------------------------------------- | #| snimak| snapshotID| pozivi| ukupno dbtime| QPS | I/O vrijeme | I/O vrijeme % +-----+------------------+-----------+------- ----+---------------------------------+--------- -+---------------------------------+----------- | 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
Istorija izvršavanja po satu sa QueryPerSeconds i I/O vremenom
Zahtjev
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
Tekst svih SQL odabira
Zahtjev
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
Rezultat
Kao što vidite, na prilično jednostavan način možete dobiti mnogo korisnih informacija o radnom opterećenju i stanju baze podataka.
Bilješka:Ako popravite queryid u upitima, tada ćemo dobiti historiju za poseban zahtjev (da bismo uštedjeli prostor, izvještaji za poseban zahtjev su izostavljeni).
Dakle, statistički podaci o performansama upita su dostupni i prikupljeni.
Prva faza "prikupljanje statističkih podataka" je završena.
Možete prijeći na drugu fazu - "konfiguriranje metrike performansi".
Ali to je druga priča.
Da se nastavi ...
izvor: www.habr.com