Monitorizarea performanței interogărilor PostgreSQL. Partea 1 - raportare

Inginer - tradus din latină - inspirat.
Un inginer poate face orice. (c) R. Diesel.
Epigrafe.

Sau o poveste despre motivul pentru care un administrator de baze de date trebuie să-și amintească trecutul de programare.

Prefață

Toate numele au fost schimbate. Coincidențele sunt întâmplătoare. Materialul reprezintă numai opinia personală a autorului.

Exonerare de garantie: Seria planificată de articole nu va conține o descriere detaliată și precisă a tabelelor și scripturilor utilizate. Materialele nu pot fi folosite imediat „CA STARE”.
În primul rând, datorită volumului mare de material,
în al doilea rând, datorită relației strânse cu baza de producție a unui client real.
Prin urmare, articolele vor conține doar idei și descrieri în cea mai generală formă.
Poate că în viitor sistemul va crește până la nivelul de a fi postat pe GitHub, sau poate nu. Timpul se va arăta.

Începutul poveștii - "Îți amintești cum a început totul".
Ce s-a întâmplat ca urmare, în termenii cei mai generali - „Sinteza ca una dintre metodele de îmbunătățire a performanței PostgreSQL»

De ce am nevoie de toate astea?

Ei bine, în primul rând, pentru a nu uita, amintirea zilelor glorioase de la pensie.
În al doilea rând, să sistematizăm ceea ce a fost scris. Pentru că uneori încep să mă confund și să uit anumite părți.

Ei bine, și cel mai important lucru este că ar putea fi de folos cuiva și să-l ajute să evite reinventarea roții și să nu colecteze o greblă. Cu alte cuvinte, îmbunătățiți-vă karma (nu a lui Khabrov). Pentru că cel mai valoros lucru din lumea asta sunt ideile. Principalul lucru este să găsești o idee. Dar transformarea unei idei în realitate este o întrebare pur tehnică.

Deci, să începem, încetul cu încetul...

Formularea problemei.

Disponibil:

Baza de date PostgreSQL(10.5), tip de încărcare mixtă (OLTP+DSS), încărcare medie-ușoară, situată în cloud AWS.
Nu există monitorizare a bazei de date; monitorizarea infrastructurii este furnizată sub formă de instrumente AWS standard într-o configurație minimă.

Este necesar:

Monitorizați performanța și starea bazei de date, găsiți și aveți informații inițiale pentru optimizarea interogărilor grele ale bazei de date.

Scurtă introducere sau analiză a opțiunilor de soluție

Pentru început, să încercăm să analizăm opțiunile de rezolvare a problemei din punctul de vedere al unei analize comparative a beneficiilor și dezavantajelor pentru inginer și lăsăm pe cei care au dreptul la aceasta conform programului de personal să se ocupe de beneficii și pierderi de management.

Opțiunea 1 - „Lucrul la cerere”

Lăsăm totul așa cum este. Dacă clientul nu este mulțumit de ceva în funcționalitatea, performanța bazei de date sau a aplicației, va anunța inginerii DBA prin e-mail sau prin crearea unui incident în tava de bilete.
Inginerul, după ce a primit notificarea, va înțelege problema, va oferi o soluție sau va pune problema pe dos, sperând că totul se va rezolva de la sine și, oricum, totul va fi uitat în curând.
Turtă dulce și gogoși, vânătăi și umflăturiTurtă dulce și gogoși:
1. Nu este nevoie să faceți nimic în plus.
2. Există întotdeauna posibilitatea de a vă scuza și de a da peste cap.
3. Mult timp pe care îl poți petrece la discreția ta.
Vânătăi și umflături:
1. Mai devreme sau mai târziu, clientul se va gândi la esența existenței și a dreptății universale în această lume și își va pune din nou întrebarea - de ce le plătesc banii mei? Consecința este întotdeauna aceeași - singura întrebare este când clientul se va plictisi și se va lua rămas bun. Și alimentatorul va fi gol. Este trist.
2. Dezvoltare inginer - zero.
3. Dificultăți în planificarea lucrărilor și a încărcării

Opțiunea 2- „Dansez cu tamburine, aburind și încălțăm pantofi”

Paragraful 1-De ce avem nevoie de un sistem de monitorizare, vom primi totul cu cereri. Efectuăm o grămadă de tot felul de interogări către dicționarul de date și vizualizările dinamice, activăm tot felul de contoare, punem totul în tabele și analizăm periodic liste și tabele. Drept urmare, avem grafice, tabele, rapoarte frumoase sau nu atât de frumoase. Principalul lucru este să ai mai mult, mai mult.
Paragraful 2-Generăm activitate și lansăm analiza tuturor acestor lucruri.
Paragraful 3-Pregătim un anumit document, numim acest document simplu - „cum ar trebui să creăm o bază de date”.
Paragraful 4-Clientul, văzând toată această splendoare de grafice și numere, este într-o încredere copilărească, naivă - acum totul va funcționa pentru noi, în curând. Și, se desparte ușor și fără durere de resursele sale financiare. De asemenea, conducerea este încrezătoare că inginerii noștri lucrează excelent. Încărcare la maxim.
Paragraful 5-Repetați pasul 1 în mod regulat.
Turtă dulce și gogoși, vânătăi și umflăturiTurtă dulce și gogoși:
1. Viața managerilor și a inginerilor este simplă, previzibilă și plină de activitate. Totul bâzâie, toată lumea este ocupată.
2. Nici viața clientului nu este rea - el este întotdeauna sigur că trebuie doar să aibă puțină răbdare și totul se va rezolva. Nu este din ce în ce mai bine, ei bine, lumea este nedreaptă, în viața următoare vei avea noroc.
Vânătăi și umflături:
1. Mai devreme sau mai târziu, va exista un furnizor mai rapid al unui serviciu similar care va face același lucru, dar puțin mai ieftin. Și dacă rezultatul este același, de ce să plătești mai mult. Ceea ce va duce din nou la dispariția alimentatorului.
2. Este plictisitor. Cât de plictisitoare este orice activitate fără sens.
3. Ca și în versiunea anterioară, nu există nicio dezvoltare. Dar pentru un inginer, dezavantajul este că, spre deosebire de prima opțiune, trebuie să generați constant un IBD. Și asta necesită timp. Pe care o poți cheltui în beneficiul persoanei dragi. Pentru că nu poți avea grijă de tine, nimănui nu-i pasă de tine.

Opțiunea 3 - Nu trebuie să inventați o bicicletă, trebuie doar să o cumpărați și să o conduceți.

Nu degeaba inginerii de la alte companii mănâncă pizza cu bere (oh, zilele de glorie din Sankt Petersburg în anii 90). Să folosim sisteme de monitorizare care sunt realizate, depanate și funcționează și, în general, beneficiază (bine, cel puțin pentru creatorii lor).
Turtă dulce și gogoși, vânătăi și umflăturiTurtă dulce și gogoși:
1. Nu este nevoie să pierzi timp venind cu ceva care a fost deja inventat. Ia-l și folosește-l.
2. Sistemele de monitorizare nu sunt scrise de proști și sunt, desigur, utile.
3. Sistemele de monitorizare funcționale oferă de obicei informații utile filtrate.
Vânătăi și umflături:
1. În acest caz, inginerul nu este un inginer, ci doar un utilizator al produsului altcuiva sau un utilizator.
2. Clientul trebuie să fie convins de necesitatea de a cumpăra ceva ce, în general, nu vrea să înțeleagă, și nu ar trebui, iar în general bugetul pe an a fost aprobat și nu se va modifica. Apoi, trebuie să alocați o resursă separată și să o configurați pentru un anumit sistem. Acestea. mai întâi trebuie să plătești, să plătești și să plătești din nou. Iar clientul este zgârcit. Aceasta este norma acestei vieți.

Ce să faci - Chernyshevsky? Întrebarea ta este foarte relevantă. (Cu)

În acest caz particular și în situația actuală, o puteți face puțin diferit - hai sa ne facem propriul sistem de monitorizare.

Ei bine, nu un sistem, desigur, în sensul deplin al cuvântului, care este prea zgomotos și prezumțios, dar cel puțin vă face sarcina mai ușoară și colectați mai multe informații pentru a rezolva incidentele de performanță. Pentru a nu te găsi într-o situație - „du-te acolo, nu știu unde, găsește ceva, nu știu ce”.

Care sunt avantajele și dezavantajele acestei opțiuni:

Pro-uri:
1. Acest lucru este interesant. Ei bine, cel puțin este mai interesant decât constanta „reduceți fișierul de date, modificați spațiul tabelului etc.”
2. Acestea sunt noi abilități și noi dezvoltări. Care, mai devreme sau mai târziu, vă va oferi turtă dulce și gogoși bine meritate.
Contra:
1. Va trebui să lucrezi. Muncește din greu.
2. Va trebui să explicați în mod regulat sensul și perspectivele tuturor activităților.
3. Va trebui sacrificat ceva, pentru că singura resursă disponibilă unui inginer – timpul – este limitată de Univers.
4. Cel mai rău și mai neplăcut lucru - rezultatul poate fi o prostie de genul „Nu un șoarece, nu o broască, ci un animal necunoscut”.

Cei care nu riscă nu beau șampanie.
Deci - începe distracția.

Idee generală – schematic

(Ilustrație preluată din articol «Sinteza ca una dintre metodele de îmbunătățire a performanței PostgreSQL»)

explicaţie:

• Extensia standard PostgreSQL „pg_stat_statements” este instalată în baza de date țintă.
• În baza de date de monitorizare, creăm un set de tabele de servicii pentru stocarea istoricului pg_stat_statements în etapa inițială și pentru configurarea valorilor și monitorizării în viitor
• Pe gazda de monitorizare, creăm un set de scripturi bash, inclusiv cele pentru generarea de incidente în sistemul de bilete.

Mesele de serviciu

În primul rând, un ERD schematic simplificat, ce sa întâmplat în cele din urmă:

Scurtă descriere a tabelelorpunct final — gazdă, punct de conectare la instanță
Baza de date - parametrii bazei de date
pg_stat_history - un tabel istoric pentru stocarea instantaneelor ​​temporare ale vizualizării pg_stat_statements a bazei de date țintă
glosar_metric - dicționar de metrici de performanță
metric_config — configurarea valorilor individuale
metric — o măsură specifică pentru cererea care este monitorizată
metric_alert_history - istoricul avertismentelor de performanță
log_query — un tabel de servicii pentru stocarea înregistrărilor analizate dintr-un fișier jurnal PostgreSQL descărcat de pe AWS
de bază — parametrii perioadelor de timp utilizate ca bază
punctul de control — configurarea metricilor pentru verificarea stării bazei de date
checkpoint_alert_history — istoricul avertismentelor privind valorile de verificare a stării bazei de date
pg_stat_db_queries — tabelul de servicii al cererilor active
Jurnalul de activitate — tabelul serviciului jurnal de activitate
trap_oid — tabelul de servicii de configurare a capcanelor

Etapa 1 - colectați informații statistice despre performanță și primiți rapoarte

Un tabel este folosit pentru a stoca informații statistice pg_stat_history
Structura tabelului pg_stat_history

                                          Tabelul „public.pg_stat_history” Coloana | Tip | Modificatori---------------------+--------------------------- -+----------------------------------------- id | întreg | nu este nul implicit nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | marca temporală fără fus orar | ID_bază de date | întreg | dbid | oid | userid | oid | queryid | bigint | interogare | text | apeluri | bigint | timpul_total | dubla precizie | timp_min | dubla precizie | timp_max | dubla precizie | timp_mediu | dubla precizie | stddev_time | dubla precizie | rânduri | 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_scris | bigint | temp_blks_read | bigint | temp_blks_scris | bigint | blk_read_time | dubla precizie | blk_write_time | dubla precizie | id-ul de bază | întreg | Indecși: „pg_stat_history_pkey” CHEIE PRIMĂRĂ, btree (id) „database_idx” btree (database_id) „queryid_idx” btree (queryid) „snapshot_timestamp_idx” btree (snapshot_timestamp) Foreign-key "database constraints" FOREIGN_NCESEIGN_DE_BAZA DE date baza de date (id ) ON DELETE CASCADE

După cum puteți vedea, tabelul este doar datele de vizualizare cumulate pg_stat_statements în baza de date țintă.

Utilizarea acestui tabel este foarte simplă

pg_stat_history va reprezenta statisticile acumulate ale executării interogării pentru fiecare oră. La începutul fiecărei ore, după completarea tabelului, statistici pg_stat_statements reseta cu pg_stat_statements_reset().
Nota: Statisticile sunt colectate pentru interogările cu o durată de execuție mai mare de 1 secundă.
Se completează tabelul 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;

Ca urmare, după o anumită perioadă de timp în tabel pg_stat_history vom avea un set de instantanee ale conținutului tabelului pg_stat_statements baza de date tinta.

De fapt, raportând

Folosind interogări simple, puteți obține rapoarte destul de utile și interesante.

Date agregate pentru o anumită perioadă de timp

Anchetă

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 ;

Ora DB

to_char(interval „1 milisecundă” * pg_total_stat_history_rec.total_time, „HH24:MI:SS.MS”)

Ora I/O

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

TOP10 SQL după total_time

Anchetă

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 DUPĂ TIMPUL TOTAL DE EXECUTARE | #| queryid| apeluri| apeluri %| timpul_total (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 după timpul total de I/O

Anchetă

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 DUPĂ TIMP TOTAL I/O | #| queryid| apeluri| apeluri %| Timp I/O (ms)|db Timp I/O % +----+-----------+-----------+------ -----------+-------------------------------+----- ------ -- | 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 după timpul maxim de execuție

Anchetă

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 DUPĂ TIMPUL MAX DE EXECUTARE | #| instantaneu| snapshotID| queryid| timp_max (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 prin citire/scriere buffer SHARED

Anchetă

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

-------------------------------------------------- ------------------------------------------ | TOP 10 SQL PRIN CITIRE/SCRIERE PENTRU TAMPON PARTASAT | #| instantaneu| snapshotID| queryid| blocuri partajate citite| blocuri partajate scrie +----+------------------+-----------+---------- -+---------------------+--------------------- | 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 ------------------------------------------------- -------------------------------------------

Histograma distribuției cererilor în funcție de timpul maxim de execuție

cereri

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 | TOTAL Apeluri: 33851920 | MIN TIME: 00:00:01.063 | TIMP MAX: 00:02:01.869 ------------------------------------------ --------------------------------------- | durata min| durata maxima| apeluri +---------------------------------+-------------- ---------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0

TOP 10 instantanee după interogare pe secundă

cereri

--pg_qps.sql
--Calculate Query Per Second 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id < pg_stat_history_rec.id ;

  IF prev_pg_stat_history_id IS NULL 
  THEN
    RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
	RETURN NULL ;
  END IF;
  
  SELECT *
  INTO prev_pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = prev_pg_stat_history_id ;
  
  --RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;    

  total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
  
  --RAISE NOTICE 'total_seconds = % ', total_seconds ;    
  
  --RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;      
  
  IF total_seconds > 0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|------------------------------------------------ --------------------------------------------- | TOP 10 Instantanee ordonate după numere QueryPerSeconds --------------------------------------------- -------------------------------------------------- ------------------------------------------------- | #| instantaneu| snapshotID| apeluri| total dbtime| QPS| Timp I/O| Timp I/O % +-----+------------------+-----------+------- ----+---------------------------------+----------- -+------------------------+----------- | 1| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 2| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 3| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396(521396.555 ms.)| 84.988 | 4| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 5| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 6| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024(24.505 ms.)| .009 | 7| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 8| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 9| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 10| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666

Istoricul orar al execuției cu QueryPerSeconds și I/O Time

Anchetă

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

Textul tuturor selectărilor SQL

Anchetă

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

Total

După cum puteți vedea, folosind mijloace destul de simple, puteți obține o mulțime de informații utile despre volumul de lucru și starea bazei de date.

Notă:Dacă înregistrăm ID-ul interogării în interogări, vom obține istoricul pentru o interogare separată (pentru a economisi spațiu, rapoartele pentru o interogare separată sunt omise).

Deci, datele statistice privind performanța interogărilor sunt disponibile și colectate.
Prima etapă „colectarea datelor statistice” este finalizată.

Puteți trece la a doua etapă - „Configurarea valorilor de performanță”.


Dar asta este cu totul altă poveste.

Pentru a fi continuat ...

Sursa: www.habr.com