Ingegnere — in traduzione dal latino — ispirato.
L'ingegnere può fare tutto. (c) R.Diesel.
Epigrafi.
Oppure è una storia su perché un amministratore di database dovrebbe ricordare il suo passato da programmatore.
Introduzione
Tutti i nomi sono stati cambiati. Le somiglianze sono casuali. Il materiale rappresenta esclusivamente l'opinione personale dell'autore.
Dichiarazione di non garanzia: nel ciclo di articoli pianificato non ci sarà una descrizione dettagliata e precisa delle tabelle e degli script utilizzati. I materiali non possono essere utilizzati immediatamente "COSÌ COME SONO".
In primo luogo, a causa dell'ampio volume di materiale,
in secondo luogo a causa della specificità rispetto alla base di produzione del cliente reale.
Pertanto, negli articoli verranno presentate solo idee e descrizioni in termini molto generali.
Forse in futuro il sistema crescerà al punto da essere pubblicato su GitHub, o forse no. Solo il tempo dirà.
Inizio della storia - "».
Cosa è risultato alla fine, in termini molto generali - "»
Perché tutto questo per me?
Beh, prima di tutto, per non dimenticare, ricordando in pensione i bei tempi.
In secondo luogo, per sistematizzare ciò che ho scritto. Perché già da solo, a volte, inizio a confondermi e dimenticare delle parti.
E, cosa più importante, magari potrebbe essere utile a qualcuno e aiutare a non reinventare la ruota e non raccogliere attrezzi. In altre parole, migliorare il proprio karma (non quello di Habr). Perché, ciò che è più prezioso in questo mondo sono le idee. L'importante è trovare un'idea. Realizzare un'idea nella realtà è già una questione puramente tecnica.
Dunque, iniziamo, lentamente...
Definizione del problema.
Disponiamo di:
Database PostgreSQL (10.5), tipo di carico misto (OLTP+DSS), con carico medio-basso, situato nel cloud AWS.
La monitoraggio del database è assente, il monitoraggio dell'infrastruttura è fornito dai normali strumenti AWS in configurazione minima.
Richiesto:
Monitorare le performance e lo stato del database, trovare e avere informazioni iniziali per ottimizzare le query pesanti al DB.
Breve introduzione o analisi delle opzioni di soluzione
Iniziamo a esaminare le opzioni di soluzione del problema dal punto di vista di un'analisi comparativa dei vantaggi e dei problemi per l'ingegnere, mentre la gestione si occupa dei benefici e delle perdite come dovrebbe essere secondo l'organizzazione.
Opzione 1 - "Lavorare su richiesta"
Lasciamo tutto così com'è. Se il cliente non è soddisfatto di qualcosa nella funzionalità, nelle prestazioni del database o dell'applicazione, informerà gli ingegneri DBA via e-mail o creando un incidente nel sistema di ticketing.
L'ingegnere, ricevuta la notifica, si occuperà del problema, proporrà una soluzione o rimanderà il problema in un secondo momento, sperando che si risolva da solo e in ogni caso, presto verrà dimenticato.
Pasticcini e dolci, lividi e bozziPasticcini e dolci:
1. Non è necessario fare nulla di superfluo
2. C'è sempre l'opportunità di tirarsi indietro e fare finta di niente.
3. Una quantità di tempo che può essere spesa a discrezione.
Lividi e bozzi:
1. Prima o poi il cliente riflette sull'essenza dell'esistenza e sulla giustizia universale in questo mondo e si pone di nuovo la domanda: per cosa sto pagando? La conseguenza è sempre la stessa: la questione è solo quanto il cliente si annoierà e abbandonerà. E il sostentamento si esaurirà. È triste.
2. Lo sviluppo dell'ingegnere è zero.
3. Difficoltà nella pianificazione del lavoro e del carico
Opzione 2 - "Balliamo con i tamburi, facciamo pressioni e vestiamo"
Punto 1- Perché abbiamo bisogno di un sistema di monitoraggio, riceveremo tutto tramite richieste. Eseguiamo un sacco di richieste verso il dizionario dei dati e le viste dinamiche, attiviamo vari contatori, raccogliamo tutto in tabelle, e periodicamente analizziamo le liste e le tabelle. In risultato abbiamo grafici belli o meno, tabelle, report. L'importante è avere di più, di più.
Punto 2- Generiamo attività: avviamo l'analisi di tutto questo.
Punto 3- Prepariamo un documento, semplicemente lo chiamiamo "come organizzare il database".
Punto 4- Il cliente, vedendo questa meraviglia di grafici e numeri, è in un'innocente fiducia infantile - ora tutto funzionerà, presto. E, facilmente e senza dolore, si separa dalle sue risorse finanziarie. Anche la gestione è sicura - i nostri ingegneri stanno lavorando sodo. Carico al massimo.
Punto 5- Ripetere regolarmente il Punto 1.
Pasticcini e dolci, lividi e bozziPasticcini e dolci:
1. La vita dei manager e degli ingegneri è semplice, prevedibile e piena di attività. Tutto ronzante, tutti impegnati.
2. Anche la vita del cliente non è male - è sempre sicuro che basta aspettare un po' e tutto si sistemerà. Non si sistema, beh, pazienza - questo è un mondo ingiusto, nella prossima vita andrà meglio.
Lividi e bozzi:
1. Prima o poi, si troverà un fornitore più veloce dello stesso servizio, che farà la stessa cosa, ma un po' più economico. E se il risultato è lo stesso, perché pagare di più? Questo porterà ancora una volta all'estinzione della fonte di guadagno.
2. È noioso. Come è noiosa qualsiasi attività poco significativa.
3. Come nella variante precedente, non c'è sviluppo. Ma per l'ingegnere, il problema è che, a differenza della prima variante, qui è necessario generare costantemente DBI. E questo richiede tempo. Tempo che potrebbe essere speso per migliorare se stessi. Perché se non ti prendi cura di te stesso, agli altri non frega niente di te.
Opzione 3 - Non è necessario inventare la ruota, è meglio comprarla e usarla.
Gli ingegneri di altre aziende non mangiano pizza e bevono birra per niente (ah, i bei tempi di San Pietroburgo negli anni '90). Dobbiamo utilizzare sistemi di monitoraggio già creati, messi a punto e funzionanti, e che in genere portano un beneficio (almeno a chi li ha creati).
Pasticcini e dolci, lividi e bozziPasticcini e dolci:
1. Non è necessario perdere tempo a inventare ciò che è già stato inventato. Prendi e usa.
2. I sistemi di monitoraggio non sono scritti da stupidi e sono ovviamente utili.
3. I sistemi di monitoraggio funzionanti di solito forniscono informazioni utili e filtrate.
Lividi e bozzi:
1. In questo caso, l'ingegnere non è un ingegnere, ma solo un utente di un prodotto altrui. O un user.
2. È necessario convincere il cliente della necessità di acquistare qualcosa di cui in realtà non vuole, né deve, occuparsi e il budget per l'anno è stato approvato e non cambierà. Poi è necessario dedicare una risorsa separata, configurare per un sistema specifico. Cioè, prima si deve pagare, pagare e pagare di nuovo. E il cliente è avaro. Questa è la norma della vita.
Cosa fare, Černyševskij? La tua domanda è molto pertinente. (c)
In questo caso specifico e nella situazione attuale, è possibile comportarsi in modo leggermente diverso — facciamo il nostro sistema di monitoraggio.
Non un sistema in senso stretto, è troppo grossa e presuntuosa come affermazione, ma almeno alleggerire il nostro compito e raccogliere più informazioni per risolvere incidenti di prestazioni. Per non trovarsi nella situazione — ‘vai là, non so dove, trova ciò, non so cosa’.
Quali sono i vantaggi e gli svantaggi di questa opzione:
Pro:
1. È interessante. Almeno è più interessante rispetto ai continui «shrink datafile, alter tablespace, ecc.»
2. Queste sono nuove abilità e un nuovo sviluppo. Ciò che, nel lungo termine, darà dolcetti e prelibatezze meritate.
Contro:
1. Dovremo lavorare. Lavorare tanto.
2. Sarà necessario spiegare regolarmente il significato e le prospettive di tutta l'attività.
3. Dovremo sacrificare qualcosa, dato che l'unica risorsa disponibile per l'ingegnere, il tempo, è limitata dall'Universo.
4. La cosa più spaventosa e più sgradevole è che potrebbe risultare una schifezza del tipo "Non un topino, non una rana, ma una bestiolina sconosciuta".
Chi non rischia, non beve champagne.
Quindi, inizia la parte più interessante.
L'idea generale è schematica
(L'illustrazione è tratta da un articolo «»)
Spiegazione:
- Nella base dati di destinazione viene installata l'estensione standard di PostgreSQL — “pg_stat_statements”.
- Nella base dati di monitoraggio creiamo un insieme di tabelle di servizio per memorizzare la storia di pg_stat_statements nelle fasi iniziali e per configurare metriche e monitoraggio successivamente.
- Sull'host di monitoraggio creiamo un insieme di script bash, inclusi quelli per la generazione di incidenti nel sistema di ticket.
Tabelle di servizio
Per iniziare, un ERD semplificata che mostra cosa è stato realizzato:
Descrizione sintetica delle tabelleendpoint — host, punto di connessione all'istanza
database — parametri del database
pg_stat_history — tabella storica per la memorizzazione degli snapshot temporali della vista pg_stat_statements del database di destinazione
metric_glossary — glossario delle metriche di prestazione
metric_config — configurazione di singole metriche
metric — metrica specifica per la query monitorata
metric_alert_history — storia degli avvisi sulle prestazioni
log_query — tabella di servizio per memorizzare le registrazioni analizzate dal file di log di PostgreSQL caricato da AWS
baseline — parametri del periodo di tempo utilizzato come base
checkpoint — configurazione delle metriche di controllo dello stato del database
checkpoint_alert_history — storia degli avvisi delle metriche di controllo dello stato del database
pg_stat_db_queries — tabella di servizio delle query attive
activity_log — tabella di servizio del registro delle attività
trap_oid — tabella di servizio della configurazione del trap
Fase 1 — raccogliamo informazioni statistiche sulle prestazioni e otteniamo report
Per la memorizzazione delle informazioni statistiche viene utilizzata la tabella pg_stat_history
Struttura della tabella pg_stat_history
Tabella "public.pg_stat_history"
Colonna | Tipo | Modificatori
---------------------+-----------------------------+-------------------------------------------
id | intero | non nullo default nextval('pg_stat_history_id_seq'::regclass)
timestamp_snapshot | timestamp senza fuso orario |
database_id | intero |
dbid | oid |
userid | oid |
queryid | bigint |
query | testo |
calls | bigint |
total_time | precisione doppia |
min_time | precisione doppia |
max_time | precisione doppia |
mean_time | precisione doppia |
stddev_time | precisione doppia |
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 | precisione doppia |
blk_write_time | precisione doppia |
baseline_id | intero |
Indici:
"pg_stat_history_pkey" CHIAVE PRIMARIA, btree (id)
"database_idx" btree (database_id)
"queryid_idx" btree (queryid)
"snapshot_timestamp_idx" btree (timestamp_snapshot)
Vincoli di chiave esterna:
"database_id_fk" CHIAVE ESTERNA (database_id) RIFERIMENTI database(id) ON DELETE CASCADECome si può vedere, la tabella rappresenta solo dati cumulativi della vista pg_stat_statements nella base di dati di destinazione.
L'utilizzo di questa tabella è molto semplice
pg_stat_history rappresenterà la statistica accumulata sull'esecuzione delle query ogni ora. All'inizio di ogni ora, dopo la compilazione della tabella, la statistica pg_stat_statements viene azzerata tramite pg_stat_statements_reset().
Nota: la statistica viene raccolta per le query con un tempo di esecuzione superiore a 1 secondo.
Compilazione della tabella 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 'CREAZIONE NUOVO SHAPSHOT';
--Connettersi al DB di destinazione
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % e dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creazione dello snapshot di pg_stat_statements per il 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 'Creazione dello snapshot di pg_stat_statements per le query con min_time superiore a 1000ms';
FOR pg_stat_snapshot IN
--Tutte le query con max_time superiore a 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;Di conseguenza, dopo un certo periodo di tempo nella tabella pg_stat_history avremo un insieme di istantanee del contenuto della tabella pg_stat_statements del database di destinazione.
In effetti, il reporting
Utilizzando query semplici, è possibile ottenere report piuttosto utili e interessanti.
Dati aggregati per un intervallo di tempo specificato
Query
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 ;Tempo DB
to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Tempo I/O
to_char(interval '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL per total_time
Query
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 PER TEMPO TOTALE DI ESECUZIONE | #| queryid| calls| percentuale calls| 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 per tempo totale I/O
Query
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 PER TEMPO TOTALE I/O | #| queryid| chiamate| % chiamate| Tempo I/O (ms)|% Tempo I/O db +----+-----------+-----------+-----------+--------------------------------+------------- | 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 per tempo massimo di esecuzione
Query
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 PER TEMPO MASSIMO DI ESECUZIONE | #| snapshot| snapshotID| queryid| max_time (ms) +----+------------------+-----------+-----------+---------------------------------------- | 1| 05.04.2019 01:03| 4169| 655729273| 00:02:01.869( 121869.981 ms.) | 2| 04.04.2019 17:00| 4153| 821760255| 00:01:41.570( 101570.841 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 per letture/scritture buffer CONDIVISI
Query
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 PER LETTURE/SCRITTURE DI BUFFER CONDIVISO | #| istantanea| snapshotID| queryid| blocchi condivisi letti| blocchi condivisi scritti +----+------------------+-----------+-----------+---------------------+--------------------- | 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 --------------------------------------------------------------------------------------------
Istogramma della distribuzione delle query in base al tempo massimo di esecuzione
Richieste
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 ) ;
|----------------------------------------------------------------------------------------------- | ISTOGRAMMA DEL TEMPO MASSIMO | CHIAMATE TOTALI : 33851920 | TEMPO MINIMO : 00:00:01.063 | TEMPO MASSIMO : 00:02:01.869 --------------------------------------------------------------------------------- | min durata| max durata| chiamate +----------------------------------+----------------------------------+---------- | 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 Istantanee per Query al Secondo
Richieste
--pg_qps.sql
--Calcola 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 'ERRORE - Non trovato pg_stat_history per 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 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 Snapshot ordinati per QueryPerSecond ----------------------------------------------------------------------------------------------------------------------------------------------- | #| snapshot| snapshotID| chiamate| tempo totale db| QPS| I/O tempo| I/O tempo % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 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
Cronologia Esecuzione Oraria con QueryPerSecond e Tempo I/O
Query
SELECT
id ,
snapshot_timestamp ,
chiamate ,
tempo_totale ,
( 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
|----------------------------------------------------------------------------------------------- | STORIA DELL'ESECUZIONE ORARIA CON QueryPerSeconds e I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | STORIA DELLE QUERY PER SECONDO | #| snapshot| snapshotID| chiamate| tempo totale db| QPS| tempo I/O| percentuale tempo I/O% +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 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
Testo di tutti gli SQL-select
Query
SELEZIONA
queryid ,
query
DA
pg_stat_history
DOVE
queryid NON È NULL E
database_id = DATABASE_ID E
snapshot_timestamp TRA BEGIN_TIMEPOINT E END_TIMEPOINT
RAGGRUPPA PER queryid , query
Risultato
Come si può vedere, con strumenti piuttosto semplici, è possibile ottenere molte informazioni utili sul carico e sullo stato del database.
Nota:Se registriamo queryid nelle query, otterremo la storia per una singola query (con l'obiettivo di risparmiare spazio, i report su query separate sono stati omessi).
Quindi, i dati statistici sulle prestazioni delle query sono disponibili e vengono raccolti.
La prima fase di "raccolta dati statistici" è completata.
Possiamo passare alla seconda fase: "impostazione delle metriche sulle prestazioni".
Ma questa è un'altra storia.
Continua…
Fonte: habr.com
