PostgreSQL užklausų našumo stebėjimas. 1 dalis – ataskaitų teikimas

Inžinierius – išvertus iš lotynų kalbos – įkvėptas.
Inžinierius gali bet ką. c) R. Dyzelis.
Epigrafai.

Arba istorija apie tai, kodėl duomenų bazės administratorius turi prisiminti savo programavimo praeitį.

pratarmė

Visi vardai buvo pakeisti. Rungtynės atsitiktinės. Medžiaga yra tik asmeninė autoriaus nuomonė.

Garantijų atsisakymas: planuojamoje straipsnių serijoje detalaus ir tikslaus naudojamų lentelių ir raštų aprašymo nebus. Medžiagos negali būti naudojamos iš karto „TOKIOS, KOKIA YRA“.
Pirma, dėl didelio medžiagų kiekio,
antra, dėl aštrumo su tikro kliento gamybos baze.
Todėl straipsniuose bus pateikiamos tik idėjos ir aprašymai pačia bendriausia forma.
Galbūt ateityje sistema išaugs iki paskelbimo GitHub, o gal ir ne. Laikas parodys.

Istorijos pradžia -Ar prisimeni, kaip viskas prasidėjo".
Kas nutiko dėl to, bendrais bruožais - "Sintezė kaip vienas iš būdų pagerinti PostgreSQL našumą»

Kam man viso to reikia?

Na, pirmiausia, kad nepamirštumėte savęs, prisimindami šlovingas dienas išėjus į pensiją.
Antra, susisteminti tai, kas parašyta. Jau pats kartais pradedu pasimesti ir pamiršti atskiras dalis.

Na, o svarbiausia – staiga tai gali kam nors praversti ir padėti neišradinėti dviračio iš naujo ir nesurinkti grėblio. Kitaip tariant, gerinkite savo karmą (ne Chabrovskio). Nes pats vertingiausias dalykas šiame pasaulyje yra idėjos. Svarbiausia rasti idėją. O idėją paversti realybe – jau grynai techninis klausimas.

Taigi pradėkime lėtai...

Problemos formulavimas.

Galima:

PostgreSQL (10.5), mišri apkrova (OLTP+DSS), vidutinė ir lengva apkrova, talpinama AWS debesyje.
Nėra duomenų bazės stebėjimo, infrastruktūros stebėjimas pateikiamas kaip standartiniai AWS įrankiai minimalia konfigūracija.

Reikia:

Stebėkite duomenų bazės našumą ir būseną, raskite ir turėkite pradinę informaciją, kad optimizuotumėte sunkias duomenų bazės užklausas.

Trumpas įvadas arba sprendimų analizė

Pirmiausia pabandykime išanalizuoti problemos sprendimo galimybes lyginamosios inžinieriaus naudos ir bėdų analizės požiūriu ir leiskite tiems, kurie turėtų būti įdarbinti, susidoroti su nauda ir nuostoliais. valdymas.

1 variantas – „Darbas pagal poreikį“

Paliekame viską kaip yra. Jei kliento kažkas netenkina dėl duomenų bazės ar programos sveikatos, veikimo, jis apie tai praneš DBA inžinieriams el. paštu arba sukurdamas incidentą bilietų dėžutėje.
Inžinierius, gavęs pranešimą, supras problemą, pasiūlys sprendimą arba problemą nustums į lentyną, tikėdamasis, kad viskas išsispręs savaime, ir šiaip viskas greitai pasimirš.
Meduoliai ir spurgos, mėlynės ir nelygumaiMeduoliai ir spurgos:
1. Nieko papildomai nereikia daryti
2. Visada yra galimybė išlipti ir išsipurvinti.
3. Daug laiko, kurį galite skirti sau.
Sumušimai ir nelygumai:
1. Anksčiau ar vėliau klientas susimąstys apie būties esmę ir visuotinį teisingumą šiame pasaulyje ir dar kartą užduos sau klausimą – kodėl aš jiems moku savo pinigus? Pasekmė visada ta pati – tik klausimas, kada klientui nusibosta ir pamojavo atsisveikinti. Ir šėrykla tuščia. Liudna.
2. Inžinieriaus išsivystymas lygus nuliui.
3. Sunkumai planuojant darbus ir pakrovimą

2 variantas – „Šok su tamburinais, apsiauk ir apsiauk batus“

1 dalis-Kam reikalinga stebėjimo sistema, visus prašymus gausime. Paleidžiame aibę įvairiausių užklausų į duomenų žodyną ir dinaminius rodinius, įjungiame visokius skaitiklius, sudedame viską į lenteles, periodiškai tarsi analizuojame sąrašus ir lenteles. Dėl to turime gražius arba nelabai grafikus, lenteles, ataskaitas. Svarbiausia, kad būtų daugiau, daugiau.
2 dalis- Sukurkite veiklą - atlikite viso to analizę.
3 dalis-Ruošiame tam tikrą dokumentą, šį dokumentą vadiname paprastai – „kaip įrengiame duomenų bazę“.
4 dalis– Klientas, matydamas visą šį grafikų ir figūrų didingumą, yra vaikiškai naivus pasitikėjimas – dabar viskas mums pasiseks, greitai. Ir lengvai bei neskausmingai dalinkitės savo finansiniais ištekliais. Vadovybė taip pat įsitikinusi, kad mūsų inžinieriai sunkiai dirba. Maksimalus pakrovimas.
5 dalis- Reguliariai kartokite 1 veiksmą.
Meduoliai ir spurgos, mėlynės ir nelygumaiMeduoliai ir spurgos:
1. Vadovų ir inžinierių gyvenimas paprastas, nuspėjamas ir kupinas veiklos. Viskas zuja, visi užsiėmę.
2. Kliento gyvenimas taip pat neblogas – jis visada įsitikinęs, kad reikia šiek tiek apsišarvuoti kantrybe ir viskas susitvarkys. Negerėja, na, gerai – šis pasaulis nesąžiningas, kitame gyvenime – tau pasiseks.
Sumušimai ir nelygumai:
1. Anksčiau ar vėliau atsiras gudresnis panašios paslaugos teikėjas, kuris atliks tą patį, tik šiek tiek pigiau. O jei rezultatas toks pat, kam mokėti daugiau. Dėl to vėl dings tiektuvas.
2. Tai nuobodu. Kaip nuobodi bet kokia maža prasminga veikla.
3. Kaip ir ankstesnėje versijoje – jokios plėtros. Tačiau inžinieriui minusas yra tas, kad, skirtingai nei pirmasis variantas, čia reikia nuolat generuoti IDB. Ir tam reikia laiko. Kuris gali būti išleistas savo mylimo žmogaus labui. Nes tu negali savimi pasirūpinti, visi tavimi rūpinasi.

3 variantas – nereikia išradinėti dviračio, reikia jį nusipirkti ir juo važiuoti.

Kitų įmonių inžinieriai sąmoningai valgo picas su alumi (o, geri Sankt Peterburgo laikai 90-aisiais). Naudokime stebėjimo sistemas, kurios yra pagamintos, derinamos ir veikiančios, o apskritai naudingos (na, bent jau jų kūrėjams).
Meduoliai ir spurgos, mėlynės ir nelygumaiMeduoliai ir spurgos:
1. Nereikia gaišti laiko išradinėti tai, kas jau išrasta. Imk ir naudok.
2. Stebėjimo sistemas rašo ne kvailiai ir, žinoma, jos naudingos.
3. Veikiančios stebėjimo sistemos dažniausiai suteikia naudingos filtruotos informacijos.
Sumušimai ir nelygumai:
1. Inžinierius šiuo atveju nėra inžinierius, o tik kažkieno kito produkto vartotojas arba vartotojas.
2. Klientas turi būti įsitikinęs, kad reikia pirkti tai, ko jis apskritai nenori suprasti, o ir neturėtų, ir apskritai metų biudžetas yra patvirtintas ir nesikeis. Tada reikia skirti atskirą išteklių, sukonfigūruoti jį konkrečiai sistemai. Tie. Pirmiausia reikia mokėti, mokėti ir dar kartą mokėti. O klientas šykštus. Tai yra šio gyvenimo norma.

Ką daryti, Černyševski? Jūsų klausimas labai aktualus. (Su)

Šiuo konkrečiu atveju ir esama situacija galite pasielgti šiek tiek kitaip – sukurkime savo stebėjimo sistemą.

Na, aišku, ne sistema, visa to žodžio prasme, tai per garsu ir įžūlu, bet bent kažkaip palengvinkite sau ir surinkite daugiau informacijos, kad išspręstumėte veiklos incidentus. Kad neatsidurtumėte situacijoje - „eik ten, aš nežinau kur, rask tai, aš nežinau ką“.

Kokie yra šios parinkties privalumai ir trūkumai:

Argumentai "už":
1. Tai įdomu. Na, bent jau įdomiau nei nuolatinis „sutraukti duomenų failą, pakeisti lentelės erdvę ir pan.“.
2. Tai nauji įgūdžiai ir naujas tobulėjimas. Kuris ateityje, anksčiau ar vėliau, duos pelnytus meduolius ir spurgas.
Trūkumai:
1. Turi dirbti. Dirbk daug.
2. Turėsite reguliariai paaiškinti visos veiklos prasmę ir perspektyvas.
3. Kažką teks paaukoti, nes vienintelį inžinieriaus prieinamą resursą – laiką – riboja Visata.
4. Blogiausia ir nemaloniausia - dėl to gali pasirodyti šiukšlės, tokios kaip „Ne pelė, ne varlė, o nežinomas mažas gyvūnas“.

Kas kažkuo nerizikuoja, tas negeria šampano.
Taigi, linksmybės prasideda.

Bendra idėja – schema

(Iliustracija paimta iš straipsnio «Sintezė kaip vienas iš būdų pagerinti PostgreSQL našumą"),

Paaiškinimas:

• Tikslinė duomenų bazė įdiegta su standartiniu PostgreSQL plėtiniu „pg_stat_statements“.
• Stebėjimo duomenų bazėje sukuriame paslaugų lentelių rinkinį, skirtą saugoti pg_stat_statements istoriją pradiniame etape ir konfigūruoti metriką bei stebėjimą ateityje.
• Stebėjimo pagrindiniame kompiuteryje sukuriame bash scenarijų rinkinį, įskaitant tuos, kurie skirti incidentams bilietų sistemoje generuoti.

Serviso stalai

Pirmiausia schematiškai supaprastintas ERD, kas atsitiko pabaigoje:

Trumpas lentelių aprašymasvertinamoji baigtis - pagrindinis kompiuteris, prisijungimo prie egzemplioriaus taškas
duomenų bazė - duomenų bazės parinktys
pg_stat_history - istorinė lentelė, skirta saugoti laikinus tikslinės duomenų bazės pg_stat_statements rodinio momentinius vaizdus
metrinis_žodynėlis - Veiklos rodiklių žodynas
metrinės_konfigūracijos - atskirų metrikų konfigūravimas
metrinis - konkreti stebimos užklausos metrika
metric_alert_history - įspėjimų apie našumą istorija
log_query - aptarnavimo lentelė, skirta saugoti analizuotus įrašus iš PostgreSQL žurnalo failo, atsisiųsto iš AWS
pradinis - laiko periodo parametrai, naudojami kaip pagrindas
kontrolinis taškas - duomenų bazės būsenos tikrinimo metrikų konfigūravimas
checkpoint_alert_history - duomenų bazės būsenos tikrinimo metrikų įspėjimo istorija
pg_stat_db_queries — aktyvių užklausų aptarnavimo lentelė
veiklos žurnalas — veiklos žurnalo paslaugų lentelė
trap_oid - spąstų konfigūracijos aptarnavimo lentelė

1 etapas – rinkti našumo statistiką ir gauti ataskaitas

Statistinei informacijai saugoti naudojama lentelė. pg_stat_history
pg_stat_history lentelės struktūra

                                          Lentelė "public.pg_stat_history" Stulpelis | tipas | Modifikatoriai--------------------+--------------------- --+----- -------------------------------- id | sveikasis skaičius | not null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | laiko žyma be laiko juostos | duomenų bazės_id | sveikasis skaičius | dbid | oid | vartotojoid | oid | queryid | bigint | užklausa | tekstas | skambučiai | bigint | total_time | dvigubas tikslumas | min_time | dvigubas tikslumas | max_time | dvigubas tikslumas | vidutinis_laikas | dvigubas tikslumas | stddev_time | dvigubas tikslumas | eilutės | bigint | share_blks_hit | bigint | share_blks_read | bigint | shared_blks_dirtied | bigint | share_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 | dvigubas tikslumas | blk_write_time | dvigubas tikslumas | bazinės_id | sveikasis skaičius | Indeksai: "pg_stat_history_pkey" PIRMINIS RAKTAS, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Fight_key KEY_tabestraase id) NUORODOS duomenų bazė (id ) DĖL IŠTRINIMO KASKADOS

Kaip matote, lentelė yra tik kaupiamieji peržiūros duomenys pg_stat_statymai tikslinėje duomenų bazėje.

Šios lentelės naudojimas yra labai paprastas.

pg_stat_history parodys sukauptą kiekvienos valandos užklausos vykdymo statistiką. Kiekvienos valandos pradžioje, užpildžius lentelę, statistika pg_stat_statymai iš naujo nustatyti su pg_stat_statements_reset().
Pastaba: statistika renkama už užklausas, kurių trukmė ilgesnė nei 1 sekundė.
Lentelės pg_stat_history užpildymas

--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;

Dėl to po tam tikro laiko tarpo lentelėje pg_stat_history turėsime lentelės turinio momentinių vaizdų rinkinį pg_stat_statymai tikslinė duomenų bazė.

Tiesą sakant, praneša

Naudodami paprastas užklausas galite gauti gana naudingų ir įdomių ataskaitų.

Apibendrinti tam tikro laikotarpio duomenys

Prašymas

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. laikas

to_char(intervalas '1 milisekundė' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

I/O laikas

to_char(interval '1 milisekundė' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

TOP10 SQL pagal total_time

Prašymas

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

-------------------------------------------------- ------------------------------------- | 10 geriausių SQL PAGAL VISĄ VYKDYMO LAIKĄ | #| queryid| skambučiai| skambučiai %| total_time (ms) | dbtime % +----+-----------+-----------+-------------------- --------------------+----------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 ms.)| .03

TOP10 SQL pagal bendrą I/O laiką

Prašymas

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------- --------------------------------------- | 10 geriausių SQL PAGAL VISĄ I/O LAIKĄ | #| queryid| skambučiai| skambučiai %| I/O laikas (ms)|db I/O laikas % +----+-----------+------------+------- -----+---------------------------------+------------ -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| birželio 31.06 d. | 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 pagal maksimalų vykdymo laiką

Prašymas

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 MAKSIAUSIAUS VYKDYMO LAIKAS | #| momentinė nuotrauka| momentinis ID| queryid| maksimalus_laikas (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 pagal SHARED buferį skaitymo / rašymo

Prašymas

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10

-------------------------------------------------- ------------------------------------- | 10 TOP 1 SQL BENDRAMOJO BUFERIU SKAITYTI/RAŠYTI | #| momentinė nuotrauka| momentinis ID| queryid| bendrinti blokai skaityti| bendrinami blokai rašyti +----+------------------+------------+----------- -+---------------------+----------------------- | 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| XNUMX -------------------------------------------------- -------------------------------------------------

Užklausos pasiskirstymo pagal maksimalų vykdymo laiką histograma

Paklausimai

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 | IŠ VISO SKAMBUČIŲ : 33851920 | MIN. LAIKAS: 00:00:01.063 | MAKSIAUSIS LAIKAS: 00:02:01.869 ---------------------------------- --------- ----------------------------- | min trukmė| maksimali trukmė| skambučiai +-----------------------------------+-------------- ---------------------+----------- | 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 momentinių vaizdų pagal užklausą per sekundę

Paklausimai

--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 momentinių vaizdų, išdėstytų pagal QueryPerSeconds skaičius -------------------------------------- ------ ------------------------------------------- ------ -------------------------------------------- | #| momentinė nuotrauka| momentinis ID| skambučiai| bendras dbtime| QPS | I/O laikas | Įvesties/išvesties laikas % +-----+-------------------+------------+-------- ----+-----------------------------------+----------- -+-----------------------------------+------------ | 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:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX | XNUMX| XNUMX-XNUMX-XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX

Valandinė vykdymo istorija su QueryPerSeconds ir įvesties / išvesties laiku

Prašymas

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

Visų pasirinktų SQL tekstai

Prašymas

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

Visas

Kaip matote, gana paprastomis priemonėmis galite gauti daug naudingos informacijos apie darbo krūvį ir duomenų bazės būseną.

Pastaba:Jei užklausose pataisysite užklausos ID, istoriją gausime atskirai užklausai (taupydami vietos, atskiros užklausos ataskaitos praleidžiamos).

Taigi, statistiniai duomenys apie užklausos našumą yra prieinami ir renkami.
Baigiamas pirmasis „statistinių duomenų rinkimo“ etapas.

Galite pereiti prie antrojo etapo - „našumo metrikos konfigūravimas“.


Bet tai jau visai kita istorija.

Turi būti tęsiama ...

Šaltinis: www.habr.com