Inženieris - tulkojumÄ no latÄ«Åu valodas - iedvesmots.
Inženieris var darīt jebko. c) R. Dīzelis.
EpigrÄfi.
Vai stÄsts par to, kÄpÄc datu bÄzes administratoram ir jÄatceras sava programmÄÅ”anas pagÄtne.
priekÅ”vÄrds
Visi nosaukumi ir mainÄ«ti. SakritÄ«bas ir nejauÅ”as. MateriÄls atspoguļo tikai autora personÄ«go viedokli.
Garantiju atruna: PlÄnotÄ rakstu sÄrija nesaturÄs detalizÄtu un precÄ«zu izmantoto tabulu un skriptu aprakstu. MateriÄlus nevar izmantot uzreiz āKÄDI TÄ IRā.
PirmkÄrt, lielÄ materiÄla apjoma dÄļ
otrkÄrt, pateicoties cieÅ”ajÄm attiecÄ«bÄm ar reÄla klienta ražoÅ”anas bÄzi.
TÄpÄc rakstos bÅ«s tikai idejas un apraksti visvispÄrÄ«gÄkajÄ formÄ.
VarbÅ«t nÄkotnÄ sistÄma pieaugs lÄ«dz lÄ«menim, kas tiks publicÄta GitHub, vai varbÅ«t nÄ. Laiks rÄdÄ«s.
StÄsta sÄkums - "
RezultÄtÄ notikuÅ”ais vispÄrÄ«gÄkajÄ izteiksmÄ - "
KÄpÄc man tas viss ir vajadzÄ«gs?
Nu, pirmkÄrt, lai neaizmirstu, atceroties krÄÅ”ÅÄs dienas pensijÄ.
OtrkÄrt, sistematizÄt rakstÄ«to. Jo dažreiz es sÄku apjukt un aizmirst dažas daļas.
Nu, un vissvarÄ«gÄkais ir tas, ka kÄdam tas var noderÄt un palÄ«dzÄt izvairÄ«ties no riteÅa no jauna izgudroÅ”anas un grÄbekļa nesavÄkÅ”anas. Citiem vÄrdiem sakot, uzlabojiet savu karmu (nevis Habrova). Jo visvÄrtÄ«gÄkÄ lieta Å”ajÄ pasaulÄ ir idejas. Galvenais ir atrast ideju. TaÄu idejas pÄrvÄrÅ”ana realitÄtÄ ir tÄ«ri tehnisks jautÄjums.
TÄtad, sÄksim, pamazÄm...
ProblÄmas formulÄÅ”ana.
Pieejams:
PostgreSQL(10.5) datubÄze, jauktas slodzes veids (OLTP+DSS), vidÄji viegla slodze, atrodas AWS mÄkonÄ«.
Nav datu bÄzes uzraudzÄ«bas, infrastruktÅ«ras uzraudzÄ«ba tiek nodroÅ”inÄta standarta AWS rÄ«ku veidÄ minimÄlÄ konfigurÄcijÄ.
NepiecieŔams:
PÄrraugiet datu bÄzes veiktspÄju un statusu, atrodiet un iegÅ«stiet sÄkotnÄjo informÄciju smago datu bÄzes vaicÄjumu optimizÄÅ”anai.
ÄŖss ievads vai risinÄjuma iespÄju analÄ«ze
SÄkumÄ mÄÄ£inÄsim analizÄt problÄmas risinÄÅ”anas iespÄjas no inženiera ieguvumu un trÅ«kumu salÄ«dzinoÅ”Äs analÄ«zes viedokļa un ļaujiet tiem, kam tas ir tiesÄ«bas saskaÅÄ ar personÄla grafiku, nodarboties ar ieguvumiem un vadÄ«bas zaudÄjumi.
1. iespÄja ā āDarbs pÄc pieprasÄ«jumaā
AtstÄjam visu kÄ ir. Ja klientu kaut kas neapmierina datu bÄzes vai aplikÄcijas funkcionalitÄte, veiktspÄja, viÅÅ” par to paziÅos DBA inženieriem pa e-pastu vai izveidojot incidentu biļeÅ”u kastÄ.
Inženieris, saÅÄmis paziÅojumu, sapratÄ«s problÄmu, piedÄvÄs risinÄjumu vai noliks problÄmu otrÄ pusÄ, cerot, ka viss atrisinÄsies pats no sevis, un vienalga, viss drÄ«z aizmirsies.
Piparkūkas un virtuļi, zilumi un pumpasPiparkūkas un virtuļi:
1. Neko papildus nevajag darīt.
2. VienmÄr ir iespÄja aizbildinÄties un ieskrÅ«vÄt.
3. Daudz laika, ko varat pavadÄ«t pÄc saviem ieskatiem.
Zilumi un izciļÅi:
1. Agri vai vÄlu klients padomÄs par eksistences bÅ«tÄ«bu un universÄlo taisnÄ«gumu Å”ajÄ pasaulÄ un vÄlreiz uzdos sev jautÄjumu - kÄpÄc es maksÄju viÅiem savu naudu? Sekas vienmÄr ir vienas un tÄs paÅ”as ā jautÄjums tikai, kad klientam paliks garlaicÄ«gi un pamÄja ardievas. Un barotava bÅ«s tukÅ”a. Tas ir skumji.
2. Inženieru attīstība - nulle.
3. GrÅ«tÄ«bas darba plÄnoÅ”anÄ un iekrauÅ”anÄ
2. variants ā āDejoÅ”ana ar tamburÄ«niem, tvaicÄÅ”ana un apavu uzvilkÅ”anaā
1. punkts-KÄpÄc mums vajadzÄ«ga monitoringa sistÄma, visu saÅemsim ar pieprasÄ«jumiem. MÄs izpildÄm daudz dažÄdu vaicÄjumu datu vÄrdnÄ«cai un dinamiskajiem skatiem, ieslÄdzam visu veidu skaitÄ«tÄjus, ievietojam visu tabulÄs un periodiski analizÄjam sarakstus un tabulas. RezultÄtÄ mums ir skaisti vai ne tik skaisti grafiki, tabulas, atskaites. Galvenais, lai bÅ«tu vairÄk, vairÄk.
2. punkts-MÄs Ä£enerÄjam aktivitÄti un sÄkam visa tÄ analÄ«zi.
3. punkts- MÄs gatavojam noteiktu dokumentu, mÄs to saucam vienkÄrÅ”i - "kÄ mums vajadzÄtu izveidot datu bÄzi."
4. punkts-PasÅ«tÄ«tÄjs, redzot visu Å”o grafiku un skaitļu krÄÅ”Åumu, ir bÄrniŔķīgÄ, naivÄ pÄrliecÄ«bÄ - tagad mums viss nostrÄdÄs, drÄ«z. Un viÅÅ” viegli un nesÄpÄ«gi Ŕķiras no saviem finanÅ”u resursiem. ArÄ« vadÄ«ba ir pÄrliecinÄta, ka mÅ«su inženieri strÄdÄ lieliski. MaksimÄlÄ ielÄde.
5. punkts- RegulÄri atkÄrtojiet 1. darbÄ«bu.
Piparkūkas un virtuļi, zilumi un pumpasPiparkūkas un virtuļi:
1. VadÄ«tÄju un inženieru dzÄ«ve ir vienkÄrÅ”a, paredzama un aktivitÄtes pilna. Viss kÅ«sÄ, visi ir aizÅemti.
2. ArÄ« klienta dzÄ«ve nav slikta - viÅÅ” vienmÄr ir pÄrliecinÄts, ka tikai nedaudz jÄpacieÅ”, un viss izdosies. Tas nepaliek labÄk, pasaule ir negodÄ«ga, nÄkamajÄ dzÄ«vÄ jums veiksies.
Zilumi un izciļÅi:
1. Agri vai vÄlu atradÄ«sies kÄds ÄtrÄks lÄ«dzÄ«ga pakalpojuma sniedzÄjs, kurÅ” darÄ«s to paÅ”u, bet nedaudz lÄtÄk. Un, ja rezultÄts ir vienÄds, kÄpÄc maksÄt vairÄk. Kas atkal novedÄ«s pie barotavas pazuÅ”anas.
2. Tas ir garlaicÄ«gi. Cik garlaicÄ«ga ir jebkura bezjÄdzÄ«ga darbÄ«ba.
3. TÄpat kÄ iepriekÅ”ÄjÄ versijÄ, nav izstrÄdes. Bet inženierim mÄ«nuss ir tÄds, ka atŔķirÄ«bÄ no pirmÄs iespÄjas jums pastÄvÄ«gi jÄÄ£enerÄ IBD. Un tas prasa laiku. Kuru varat iztÄrÄt sava mīļotÄ labÄ. TÄ kÄ jÅ«s nevarat parÅ«pÄties par sevi, neviens par jums nerÅ«pÄjas.
3. iespÄja ā jums nav jÄizgudro velosipÄds, jums tas vienkÄrÅ”i jÄiegÄdÄjas un jÄbrauc.
Ne velti citu kompÄniju inženieri Äd picu ar alu (ak, SanktpÄterburgas slavas laiki 90. gados). Izmantosim uzraudzÄ«bas sistÄmas, kas ir izgatavotas, atkļūdotas un darbojas, un vispÄrÄ«gi runÄjot par labu (labi, vismaz to radÄ«tÄjiem).
Piparkūkas un virtuļi, zilumi un pumpasPiparkūkas un virtuļi:
1. Nav jÄtÄrÄ laiks, izdomÄjot kaut ko, kas jau ir izgudrots. PaÅemiet to un izmantojiet to.
2. Monitoringa sistÄmas nav muļķu rakstÄ«tas un tÄs, protams, noder.
3. Darba uzraudzÄ«bas sistÄmas parasti nodroÅ”ina noderÄ«gu filtrÄtu informÄciju.
Zilumi un izciļÅi:
1. Inženieris Å”ajÄ gadÄ«jumÄ nav inženieris, bet tikai kÄda cita produkta lietotÄjs vai lietotÄjs.
2. Klientam ir jÄbÅ«t pÄrliecinÄtam par nepiecieÅ”amÄ«bu iegÄdÄties kaut ko tÄdu, ko viÅÅ”, vispÄrÄ«gi runÄjot, nevÄlas saprast un nedrÄ«kst, un kopumÄ gada budžets ir apstiprinÄts un nemainÄ«sies. PÄc tam jums ir jÄpieŔķir atseviŔķs resurss un jÄkonfigurÄ tas noteiktai sistÄmai. Tie. vispirms ir jÄmaksÄ, jÄmaksÄ un vÄlreiz jÄmaksÄ. Un klients ir skops. TÄ ir Ŕīs dzÄ«ves norma.
Ko darÄ«t - ÄerniÅ”evskis? JÅ«su jautÄjums ir ļoti aktuÄls. (ar)
Å ajÄ konkrÄtajÄ gadÄ«jumÄ un paÅ”reizÄjÄ situÄcijÄ varat to darÄ«t nedaudz savÄdÄk - izveidosim savu uzraudzÄ«bas sistÄmu.
Protams, ne sistÄma, Ŕī vÄrda pilnÄ nozÄ«mÄ, tÄ ir pÄrÄk skaļa un pÄrgalvÄ«ga, taÄu vismaz kaut kÄ atvieglo jÅ«su uzdevumu un apkopo vairÄk informÄcijas, lai atrisinÄtu darbÄ«bas traucÄjumus. Lai nenonÄktu situÄcijÄ - āej tur, es nezinu kur, atrodi kaut ko, es nezinu koā.
KÄdi ir Ŕīs opcijas plusi un mÄ«nusi:
Plusi:
1. Tas ir interesanti. Nu, vismaz tas ir interesantÄks par pastÄvÄ«go "samazinÄt datu failu, mainÄ«t tabulas vietu utt."
2. TÄs ir jaunas prasmes un jauna attÄ«stÄ«ba. Kas agri vai vÄlu dos tev pelnÄ«tas piparkÅ«kas un virtuļus.
MÄ«nusi:
1. BÅ«s jÄstrÄdÄ. Smagi strÄdÄt.
2. Jums bÅ«s regulÄri jÄskaidro visu darbÄ«bu jÄga un perspektÄ«vas.
3. Kaut kas bÅ«s jÄupurÄ, jo vienÄ«gais inženierim pieejamais resurss - laiks - ir ierobežots ar Visumu.
4. SliktÄkÄ un nepatÄ«kamÄkÄ lieta - rezultÄts var bÅ«t muļķības, piemÄram, "Ne pele, nevis varde, bet nezinÄms dzÄ«vnieks."
Kas neriskÄ, tas nedzer Å”ampanieti.
TÄtad - jautrÄ«ba sÄkas.
VispÄrÄjÄ ideja - shematiski
(IlustrÄcija Åemta no raksta Ā«
Paskaidrojums:
- Standarta PostgreSQL paplaÅ”inÄjums āpg_stat_statementsā ir instalÄts mÄrÄ·a datu bÄzÄ.
- UzraudzÄ«bas datu bÄzÄ mÄs izveidojam pakalpojumu tabulu kopu pg_stat_statements vÄstures glabÄÅ”anai sÄkotnÄjÄ posmÄ un metrikas iestatÄ«Å”anai un uzraudzÄ«bai nÄkotnÄ.
- UzraudzÄ«bas resursdatorÄ mÄs izveidojam bash skriptu kopu, tostarp tos, kas paredzÄti incidentu Ä£enerÄÅ”anai biļeÅ”u sistÄmÄ.
Servisa galdi
PirmkÄrt, shematisks vienkÄrÅ”ots ERD, kas notika beigÄs:
ÄŖss tabulu aprakstsgalapunkts ā resursdators, savienojuma punkts ar gadÄ«jumu
datubÄze - datu bÄzes parametri
pg_stat_history - vÄsturiska tabula mÄrÄ·a datu bÄzes skata pg_stat_statements pagaidu momentuzÅÄmumu glabÄÅ”anai
metriskÄ_vÄrdnÄ«ca - veiktspÄjas rÄdÄ«tÄju vÄrdnÄ«ca
metric_config ā atseviŔķu metriku konfigurÄÅ”ana
metriskÄ ā konkrÄts rÄdÄ«tÄjs pieprasÄ«jumam, kas tiek uzraudzÄ«ts
metric_alert_history - veiktspÄjas brÄ«dinÄjumu vÄsture
log_query ā pakalpojumu tabula parsÄtu ierakstu glabÄÅ”anai no PostgreSQL žurnÄlfaila, kas lejupielÄdÄts no AWS
bÄzes ā par bÄzi izmantoto laika periodu parametri
kontrolpunkts ā metrikas konfigurÄcija datu bÄzes statusa pÄrbaudei
checkpoint_alert_history ā datu bÄzes veselÄ«bas pÄrbaudes metrikas brÄ«dinÄjumu vÄsture
pg_stat_db_queries ā aktÄ«vo pieprasÄ«jumu apkalpoÅ”anas tabula
AktivitÄÅ”u vÄsture ā darbÄ«bu žurnÄla pakalpojumu tabula
trap_oid ā lamatas konfigurÄcijas pakalpojumu tabula
1. posms ā apkopojiet statistisko informÄciju par veiktspÄju un saÅemiet atskaites
Statistikas informÄcijas glabÄÅ”anai tiek izmantota tabula pg_stat_history
pg_stat_history tabulas struktūra
Tabula "public.pg_stat_history" Kolonna | Tips | Modifikatori---------------------+-------------------------- -+------------------------------------------ id | vesels skaitlis | nav nulles noklusÄjuma nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | laikspiedols bez laika joslas | datu bÄzes_id | vesels skaitlis | dbid | oid | lietotÄja ID | oid | queryid | bigint | vaicÄjums | teksts | zvani | bigint | kopÄjais_laiks | dubultÄ precizitÄte | min_laiks | dubultÄ precizitÄte | max_time | dubultÄ precizitÄte | vidÄjais_laiks | dubultÄ precizitÄte | stddev_time | dubultÄ precizitÄte | rindas | 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 | dubultÄ precizitÄte | blk_write_time | dubultÄ precizitÄte | bÄzes lÄ«nijas_id | vesels skaitlis | Indeksi: "pg_stat_history_pkey" PRIMÄRÄ ATSLÄGA, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) FIRMA_Key_key id) ATSAUCES datu bÄze(id ) PAR KASKÄDES DZÄÅ ANU
KÄ redzat, tabula ir tikai kumulatÄ«vi skata dati pg_stat_statements mÄrÄ·a datu bÄzÄ.
Å Ä«s tabulas izmantoÅ”ana ir ļoti vienkÄrÅ”a
pg_stat_history attÄlos uzkrÄto statistiku par vaicÄjumu izpildi par katru stundu. Katras stundas sÄkumÄ pÄc tabulas aizpildÄ«Å”anas statistika pg_stat_statements atiestatÄ«t ar pg_stat_statements_reset().
PiezÄ«me: Statistika tiek apkopota par vaicÄjumiem, kuru izpildes ilgums pÄrsniedz 1 sekundi.
Tabulas pg_stat_history aizpildīŔana
--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;
RezultÄtÄ pÄc kÄda laika tabulÄ pg_stat_history mums bÅ«s tabulas satura momentuzÅÄmumu komplekts pg_stat_statements mÄrÄ·a datu bÄze.
PatiesÄ«bÄ ziÅo
Izmantojot vienkÄrÅ”us vaicÄjumus, varat iegÅ«t diezgan noderÄ«gus un interesantus pÄrskatus.
Apkopoti dati par noteiktu laika periodu
Pretenzija
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 ;
DB laiks
to_char(intervalls '1 milisekunde' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O laiks
to_char(interval '1 milisekunde' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL pÄc total_time
Pretenzija
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 PÄC KOPÄJÄ IZPILDES LAIKA | #| queryid| zvani| zvani %| kopÄjais_laiks (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 pÄc kopÄjÄ I/O laika
Pretenzija
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 PÄC KOPÄJÄ I/O LAIKA | #| queryid| zvani| zvani %| I/O laiks (ms)|db I/O laiks % +----+-----------+-----------+------- -----------+---------------------------------+------ ------ -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 | 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 pÄc maksimÄlÄ izpildes laika
Pretenzija
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 BY MAX IZPILDES LAIKA | #| momentuzÅÄmums| momentuzÅÄmuma ID| queryid| max_time (ms) +----+-------------------+------------+---------- --+---------------------------------------- | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00(01 ms.) | 36.964| 96964.607/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:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16(00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)
TOP10 SQL, izmantojot SHARED bufera lasīŔanu/rakstīŔanu
Pretenzija
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 PÄC KOPÄŖGOTÄ BUFERA LASÄŖÅ ANA/RAKSTÄŖÅ ANA | #| momentuzÅÄmums| momentuzÅÄmuma ID| queryid| koplietotie bloki lasÄ«t| koplietotie bloki rakstiet +----+------------------+------------+---------- -+---------------------+---------------------- | 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| XNUMX| XNUMX| XNUMX| XNUMX -------------------------------------------------- -------------------------------------------
PieprasÄ«jumu sadalÄ«juma histogramma pÄc maksimÄlÄ izpildes laika
pieprasījumi
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 HISTOGRAMMA | KOPÄ ZVANU: 33851920 | MIN. LAIKS: 00:00:01.063 | MAKSIMÄLAIS LAIKS: 00:02:01.869 ------------------------------------------- ---------------------------------------- | min ilgums| max ilgums| zvani +-----------------------------------+-------------- ---------------------+----------- | 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 momentuzÅÄmumi pÄc vaicÄjuma sekundÄ
pieprasījumi
--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 momentuzÅÄmumi, kas sakÄrtoti pÄc QueryPerSeconds skaitļiem --------------------------------------------- -------------------------------------------------- -------------------------------------------------- | #| momentuzÅÄmums| momentuzÅÄmuma ID| zvani| kopÄjais dbtime| QPS| I/O laiks| I/O laiks % +-----+-------------------+------------+-------- ----+-----------------------------------+----------- -+------------------------+------------ | 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 ms.)| 14| 00:4137:2397326( 00 ms.)| .04 | 43.033| 283033.854/665.924/00 00:00.024| 24.505| 009| 7:04.04.2019:15( 00 ms.)| 4139| 2394416:00:04(51.435 ms.)| .291435.010 | 665.116| 00/00/12.025 12025.895:4.126| 8| 04.04.2019| 13:00:4135( 2373043 ms.)| 00| 04:26.791:266791.988( 659.179 ms.)| 00 | 00| 00.064 64.261:024| 9| 05.04.2019| 01:03:4167( 4387191 ms.)| 00| 06:51.380:411380.293( 609.332 ms.)| .00 | 05| 18.847/318847.407/77.507 10:04.04.2019| 18| 01| 4157:1145596:00( 01 ms.)| 19.217| 79217.372:313.004:00( 00 ms.)| 01.319 | 1319.676| 1.666/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX
Stundu izpildes vÄsture ar QueryPerSeconds un I/O laiku
Pretenzija
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
Visu SQL atlases teksts
Pretenzija
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
Kopsavilkums
KÄ redzat, izmantojot diezgan vienkÄrÅ”us lÄ«dzekļus, jÅ«s varat iegÅ«t daudz noderÄ«gas informÄcijas par datu bÄzes darba slodzi un stÄvokli.
PiezÄ«me:Ja vaicÄjumos ierakstÄ«sim vaicÄjumu ID, mÄs iegÅ«sim atseviŔķa vaicÄjuma vÄsturi (lai ietaupÄ«tu vietu, atseviŔķa vaicÄjuma atskaites tiek izlaistas).
TÄtad statistikas dati par vaicÄjumu veiktspÄju ir pieejami un apkopoti.
Ir pabeigts pirmais posms āstatistikas datu vÄkÅ”anaā.
Varat pÄriet uz otro posmu - āveiktspÄjas metrikas iestatÄ«Å”anaā.
Bet tas ir pavisam cits stÄsts.
Lai varÄtu turpinÄt ...
Avots: www.habr.com