Der Ingenieur – abgeleitet aus dem Lateinischen – ist inspiriert.
Ein Ingenieur kann alles. (c) R. Diesel.
Episoden.
Oder die Geschichte darüber, warum ein Datenbankadministrator seine Programmiervergangenheit nicht vergessen sollte.
Vorwort
Alle Namen sind geändert. Alle Ähnlichkeiten sind rein zufällig. Der Inhalt stellt ausschließlich die persönliche Meinung des Autors dar.
Haftungsausschluss: In der geplanten Artikelreihe wird es keine detaillierte und genaue Beschreibung der verwendeten Tabellen und Skripte geben. Das Material kann nicht sofort 'AS IS' verwendet werden.
Erstens aufgrund des großen Umfangs des Materials,
zweitens aufgrund der Anpassung an die Produktionsdatenbank des tatsächlichen Kunden.
Daher werden in den Artikeln nur Ideen und Beschreibungen in allgemeinster Form dargestellt.
Möglicherweise wird das System in der Zukunft den Status erreichen, auf GitHub veröffentlicht zu werden, vielleicht auch nicht. Die Zeit wird es zeigen.
Der Beginn der Geschichte – „».
Was daraus geworden ist, sehr allgemein – „»
Warum ist das alles für mich wichtig?
Nun, erstens, um selbst nicht zu vergessen und in der Rente an die glorreichen Tage zurückzudenken.
Und zweitens, um das Geschriebene zu systematisieren. Denn ich selbst beginne manchmal, mich zu verwirren und einzelne Teile zu vergessen.
Und das Wichtigste: Vielleicht kann es jemandem nützlich sein und helfen, das Rad nicht neu zu erfinden und keine Fehler zu sammeln. Mit anderen Worten, verbessern Sie Ihr Karma (nicht das von Habr). Denn das Wertvollste in dieser Welt sind Ideen. Es geht darum, die richtige Idee zu finden. Die Umsetzung dieser Idee in die Realität ist dann schon eine rein technische Angelegenheit.
Lassen Sie uns also langsam beginnen...
Aufgabenstellung.
Folgendes habe ich:
Datenbank PostgreSQL (10.5), gemischte Arbeitslast (OLTP+DSS), mittel bis gering belastet, gehostet in der AWS-Cloud.
Monitoring der Datenbank ist nicht vorhanden, das Monitoring der Infrastruktur erfolgt durch die standardmäßigen AWS-Tools in minimaler Konfiguration.
Anforderungen:
Leistung und Zustand der Datenbank überwachen, Informationen sammeln und zur Optimierung schwerer Datenbankabfragen bereitstellen.
Kurze Einleitung oder Analyse möglicher Lösungsansätze
Zunächst versuchen wir, die Lösungsansätze aus der Sicht eines Vergleichs der Vor- und Nachteile für den Ingenieur zu analysieren, während sich das Management um Nutzen und Verluste kümmert, was deren Aufgabe gemäß der Stellenbeschreibung ist.
Option 1 - „Working on demand“
Wir lassen alles wie es ist. Wenn der Kunde mit der Funktionsweise oder der Leistung der Datenbank oder Anwendung unzufrieden ist, wird er die DBA-Ingenieure per E-Mail benachrichtigen oder ein Ticket im Ticketsystem erstellen.
Der Ingenieur, der die Benachrichtigung erhält, wird das Problem analysieren, eine Lösung vorschlagen oder das Problem auf die lange Bank schieben, in der Hoffnung, dass sich alles von selbst regelt, und ohnehin bald wieder vergessen wird.
Süßgebäck und Leckereien, blaue Flecken und BeulenSüßgebäck und Leckereien:
1. Es ist nichts überflüssiges zu tun.
2. Es gibt immer die Möglichkeit, sich herauszureden und faul zu sein.
3. Eine Menge Zeit, die man nach eigenem Ermessen verbringen kann.
Blaue Flecken und Beulen:
1. Früh oder spät wird der Kunde über den Sinn des Lebens und die universelle Gerechtigkeit in dieser Welt nachdenken und sich erneut die Frage stellen – wofür zahle ich ihnen mein Geld? Die Konsequenz ist immer die gleiche – die Frage ist nur, wann der Kunde sich langweilt und auf Wiedersehen winkt. Dann wird die Futterquelle leer sein. Das ist traurig.
2. Die Entwicklung des Ingenieurs – null.
3. Schwierigkeiten bei der Planung von Arbeit und Belastung.
Option 2 - ‚Wir tanzen mit Trommeln, verkaufen und schuhen‘.
Punkt 1-Warum brauchen wir ein Monitoring-System? Wir erhalten alles über Anfragen. Wir starten eine Vielzahl von Anfragen an das Datenwörterbuch und dynamische Darstellungen, aktivieren verschiedene Zähler und fassen alles in Tabellen zusammen, analysieren in regelmäßigen Abständen diese Listen und Tabellen. Am Ende haben wir hübsche oder weniger hübsche Grafiken, Tabellen, Berichte. Das Wichtigste ist, dass wir von allem mehr haben.
Punkt2-Wir generieren Aktivität – analysieren das Ganze.
Punkt3-Wir erstellen ein Dokument, das wir einfach „Wie wir die Datenbank einrichten“ nennen.
Punkt4-Der Kunde betrachtet all diese großartigen Grafiken und Zahlen und glaubt naiv: Jetzt wird alles funktionieren, bald. Und er gibt bereitwillig seine finanziellen Mittel aus. Das Management ist ebenfalls überzeugt – unsere Ingenieure sind echte Experten. Die Auslastung ist auf dem Maximum.
Punkt5-Punkt 1 regelmäßig wiederholen.
Süßgebäck und Leckereien, blaue Flecken und BeulenSüßgebäck und Leckereien:
1. Das Leben der Manager und Ingenieure ist einfach, vorhersehbar und voller Aktivität. Alles summt, alle sind beschäftigt.
2. Das Leben eines Kunden ist ebenfalls nicht schlecht – er ist sich immer sicher, dass man nur ein wenig Geduld haben muss, und alles wird gut. Wenn es nicht funktioniert, naja, das ist die ungerechte Welt, in der nächsten Leben wird es besser.
Blaue Flecken und Beulen:
1. Früher oder später wird es einen agilen Anbieter für ähnliche Dienstleistungen geben, der das Gleiche etwas günstiger macht. Wenn das Ergebnis dasselbe ist, warum also mehr bezahlen? Das führt wieder zum Verschwinden der Einnahmequelle.
2. Das ist langweilig. So langweilig wie jede bedeutungslose Aktivität.
3. Wie im vorherigen Fall – es gibt keine Entwicklung. Aber für Ingenieure ist es ein Nachteil, dass man im Gegensatz zur ersten Variante hier ständig die Datenbanken aktualisieren muss. Und das kostet Zeit. Zeit, die man sinnvoll für sich selbst nutzen könnte. Denn wenn man sich nicht um sich selbst kümmert, kümmert sich auch niemand um einen.
Option 3 – Man muss das Rad nicht neu erfinden, man sollte es kaufen und fahren.
Ingenieure anderer Firmen essen nicht umsonst Pizza und trinken dazu Bier (ach, die guten alten Zeiten in St. Petersburg der 90er). Lass uns die Überwachungssysteme nutzen, die gemacht, getestet und funktionierend sind, und die grundsätzlich Nutzen bringen (zumindest für ihre Schöpfer).
Süßgebäck und Leckereien, blaue Flecken und BeulenSüßgebäck und Leckereien:
1. Verschwendet keine Zeit mit dem Erfinden von Dingen, die bereits erfunden wurden. Nutzt, was vorhanden ist.
2. Überwachungssysteme werden nicht von Dummköpfen entworfen, und sie sind auf jeden Fall nützlich.
3. Funktionierende Überwachungssysteme liefern in der Regel nützliche gefilterte Informationen.
Blaue Flecken und Beulen:
1. In diesem Fall ist der Ingenieur kein Ingenieur, sondern lediglich ein Benutzer eines fremden Produkts. Oder ein Nutzer.
2. Der Kunde muss davon überzeugt werden, etwas zu kaufen, mit dem er sich grundsätzlich nicht auskennt und auch nicht sollte; außerdem ist das Jahresbudget genehmigt und wird sich nicht ändern. Dann muss ein separates Ressource bereitgestellt und für das spezifische System konfiguriert werden. Zuerst muss man bezahlen, bezahlen und nochmals bezahlen. Und der Kunde ist knauserig. Das ist die Norm in dieser Welt.
Was sollen wir tun, Tschernyschewski? Deine Frage ist sehr passend. (c)
In diesem konkreten Fall und der gegebenen Situation können wir es ein wenig anders machen — lass uns unser eigenes Überwachungssystem erstellen.
Das ist zwar nicht im vollen Sinne des Wortes ein System, das wäre zu hochtrabend und überheblich, aber es kann helfen, die Aufgabe zu erleichtern und mehr Informationen zum Lösen von Leistungsproblemen zu sammeln. Damit man nicht in die Situation kommt: „Geh dorthin, wo ich nicht weiß, wo, finde das, was ich nicht kenne“.
Welche Vor- und Nachteile hat diese Option?
Vorteile:
1. Es ist interessant. Zumindest interessanter als ständiges „Datafile verkleinern, Tablespace ändern usw.“.
2. Es sind neue Fähigkeiten und neue Entwicklungen. Die langfristig irgendwann die verdienten Belohnungen und Erfolge bringen werden.
Nachteile:
1. Man wird arbeiten müssen. Viel arbeiten.
2. Man wird regelmäßig den Sinn und die Perspektiven der gesamten Aktivitäten erklären müssen.
3. Man muss irgendwo Abstriche machen, denn die einzige Ressource, die einem Ingenieur zur Verfügung steht, ist die Zeit, und die ist im Universum begrenzt.
4. Das Schlimmste und Unangenehmste ist, dass am Ende etwas herauskommen kann, das weder Maus noch Frosch, sondern ein unbekanntes Wesen ist.
Wer nicht riskiert, trinkt keinen Champagner.
Und jetzt beginnt das Interessanteste.
Die allgemeine Idee – schematisch
(Die Abbildung stammt aus einem Artikel. «»)
Erklärung:
- In der Ziel-Datenbank wird die Standarderweiterung PostgreSQL – „pg_stat_statements“ – installiert.
- In der Überwachungsdatenbank erstellen wir einen Satz von Servicetabellen zur Speicherung der Historie von pg_stat_statements in der Anfangsphase sowie zur Konfiguration von Metriken und Monitoring für die Zukunft.
- Auf dem Überwachungs-Host erstellen wir eine Reihe von Bash-Skripten, unter anderem zur Generierung von Vorfällen im Ticketsystem.
Servicetabellen
Zunächst eine schematisch vereinfachte ERD, was am Ende herausgekommen ist:
Kurzbeschreibung der Tabellenendpoint — Host, Verbindungsstelle zur Instanz
database — Datenbankparameter
pg_stat_history — Historische Tabelle zur Speicherung von Schnappschüssen der Darstellung von pg_stat_statements der Ziel-Datenbank
metric_glossary — Glossar der Leistungsmetriken
metric_config — Konfiguration einzelner Metriken
metric — Konkrete Metrik für die überwachte Anfrage
metric_alert_history — Historie der Leistungswarnungen
log_query — Servicetabelle zur Speicherung der analysierten Einträge aus der PostgreSQL-Logdatei, die von AWS geladen wird.
baseline — Parameter für den Zeitraum, der als Basis verwendet wird
checkpoint — Konfiguration der Metriken zur Überprüfung des Zustands der Datenbank
checkpoint_alert_history — Historie der Warnungen zu den Metriken der Zustandüberprüfung der Datenbank
pg_stat_db_queries — Servicetabelle aktiver Anfragen
activity_log — Systemtabelle für Aktivitätsprotokolle
trap_oid — Systemtabelle für Trap-Konfiguration
Schritt 1 – Sammeln von Leistungsstatistiken und Erstellen von Berichten
Die Tabelle dient zur Speicherung von Statistiken pg_stat_history
Struktur der Tabelle pg_stat_history
Tabelle "public.pg_stat_history"
Spalte | Typ | Modifikatoren
---------------------+-------------------------------+-------------------------------------------
id | integer | nicht null Standard nextval('pg_stat_history_id_seq'::regclass)
snapshot_timestamp | timestamp ohne Zeitzone |
database_id | integer |
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 |
baseline_id | integer |
Indizes:
"pg_stat_history_pkey" PRIMÄRSCHLÜSSEL, btree (id)
"database_idx" btree (database_id)
"queryid_idx" btree (queryid)
"snapshot_timestamp_idx" btree (snapshot_timestamp)
Fremdschlüsselbeschränkungen:
"database_id_fk" FREMDSCHLÜSSEL (database_id) VERWEIST AUF database(id) BEI LÖSCHEN CASCADEDie Tabelle stellt lediglich kumulierte Daten dar. pg_stat_statements in der Zieldatenbank.
Die Nutzung dieser Tabelle ist sehr einfach.
pg_stat_history Sie zeigt die akkumulierte Statistik der ausgeführten Abfragen pro Stunde an. Zu Beginn jeder Stunde wird, nachdem die Tabelle gefüllt wurde, die Statistik pg_stat_statements mit Hilfe von pg_stat_statements_reset().
Hinweis: gesammelt, wobei die Abfragen mit einer Ausführungsdauer von über 1 Sekunde berücksichtigt werden.
Die Tabelle pg_stat_history wird gefüllt.
--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 'NEUES SNAPSHOT WIRD ERSTELLT';
-- Verbindung zur Ziel-Datenbank herstellen
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'Host % und Datenbankname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Erstelle Snapshot von pg_stat_statements für Datenbank %',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 'Erstelle Snapshot von pg_stat_statements für Abfragen mit min_time größer als 1000ms';
FOR pg_stat_snapshot IN
-- Alle Abfragen mit max_time größer als 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;Nach einer gewissen Zeit in der Tabelle pg_stat_history werden wir eine Sammlung von Snapshot-Inhalten der pg_stat_statements Ziel-Datenbank haben.
Eigentlich das Reporting
Mit einfachen Abfragen lassen sich durchaus nützliche und interessante Berichte erstellen.
Aggregierte Daten für den angegebenen Zeitraum
Abfrage
SELECT
database_id ,
SUM(calls) AS calls ,SUM(total_time) AS total_time ,
SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,
SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit ,
SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied ,
SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read,
SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time ,
SUM(blk_write_time) AS blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;DB-Zeit
to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O Zeit
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 nach total_time
Abfrage
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 NACH GESAMTER AUSFÜHRUNGSDAUER | #| queryid| Aufrufe| Aufrufe %| Gesamtzeit (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 nach gesamter I/O-Zeit
Abfrage
SELECT
queryid ,
SUM(auftritte) AS aufrufe ,
SUM(blk_read_time + blk_write_time) AS io_zeit
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 NACH GESAMTER I/O-ZEIT | #| queryid| Aufrufe| Aufrufe %| I/O-Zeit (ms)|DB I/O-Zeit % +----+-----------+-----------+-----------+--------------------------------+------------- | 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 nach maximaler Ausführungszeit
Abfrage
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 NACH MAX. AUSFÜHRUNGSZEIT | #| 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 nach GETEILTEN Puffer Lese-/Schreibvorgängen
Abfrage
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 DURCH GETEILTEN BUFFER LESEN/SCHREIBEN | #| Snapshot| SnapshotID| AbfrageID| Gelesene gemeinsame Blöcke| Geschriebene gemeinsame Blöcke +----+------------------+-----------+-----------+----------------------------+---------------------------- | 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 --------------------------------------------------------------------------------------------
Histogramm der Anfragenverteilung nach maximaler Ausführungszeit
Abfragen
WÄHLEN
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) als hist_width
VON
pg_stat_history
WO
queryid IST NICHT NULL UND
database_id = DATABASE_ID UND
snapshot_timestamp ZWISCHEN BEGIN_TIMEPOINT UND END_TIMEPOINT ;
WÄHLEN
SUM(calls) AS calls
VON
pg_stat_history
WO
queryid IST NICHT NULL UND
database_id =DATABASE_ID UND
snapshot_timestamp ZWISCHEN BEGIN_TIMEPOINT UND END_TIMEPOINT UND
( max_time >= hist_current_min UND max_time < hist_current_max ) ;
|----------------------------------------------------------------------------------------------- | MAX_TIME HISTOGRAMM | GESAMTE ANRUFE : 33851920 | MINZEIT : 00:00:01.063 | MAXZEIT : 00:02:01.869 --------------------------------------------------------------------------------- | min Dauer| max Dauer| Anrufe +----------------------------------+----------------------------------+---------- | 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 Snapshots nach Abfrage pro Sekunde
Abfragen
--pg_qps.sql
--Berechnung der Abfragen pro Sekunde
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 'FEHLER - pg_stat_history für id = % nicht gefunden',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 Schnappschüsse nach QueryPerSeconds-Zahlen geordnet ----------------------------------------------------------------------------------------------------------------------------------------------- | #| Snapshot| SnapshotID| Abrufe| Gesamt-Datenbankzeit| QPS| I/O-Zeit| I/O-Zeit % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 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
Stündlicher Ausführungsverlauf mit Abfragen pro Sekunde und I/O-Zeit
Abfrage
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
|----------------------------------------------------------------------------------------------- | STUNDENVERLAUF DER AUSFÜHRUNG MIT QueryPerSeconds und I/O-Zeit ----------------------------------------------------------------------------------------------------------------------------------------------- | ABFRAGEPROTOKOLL PRO SEKUNDE | #| Schnappschuss| SchnappschussID| Aufrufe| gesamte dbzeit| QPS| I/O-Zeit| I/O-Zeit % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 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
Text aller SQL-Abfragen
Abfrage
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
Zusammenfassung
Wie man sieht, kann man mit relativ einfachen Mitteln eine Menge nützlicher Informationen über die Auslastung und den Zustand der Datenbank erhalten.
Anmerkung:Wenn man die queryid in den Abfragen protokolliert, erhält man die Historie für eine einzelne Abfrage (um Speicherplatz zu sparen, sind Berichte für einzelne Abfragen weggelassen).
Die statistischen Daten zur Abfrageleistung sind vorhanden und werden gesammelt.
Die erste Phase der "Datensammlung" ist abgeschlossen.
Jetzt können wir zur zweiten Phase – der "Optimierung der Leistungskennzahlen" – übergehen.
Aber das ist schon eine ganz andere Geschichte.
Die Fortsetzung folgt…
Quelle: habr.com
