Geekly articles weekly

Versuch, ein ASH-Analogon für PostgreSQL zu erstellen

Erklärung des Problems


Um PostgreSQL-Abfragen zu optimieren, ist es wirklich erforderlich, den Aktivitätsverlauf zu analysieren, insbesondere Erwartungen, Sperren, Tabellenstatistiken.

Verfügbare Optionen


Historisches Lastanalysetool oder „AWR für Postgres“ : eine sehr interessante Lösung, aber es gibt keine Historie von pg_stat_activity und pg_locks.

Pgsentinel-Erweiterung :
" Alle gesammelten Informationen werden nur im RAM gespeichert, und die verbrauchte Speichermenge wird durch die Anzahl der zuletzt gespeicherten Datensätze reguliert.

Das Feld queryid wird hinzugefügt - dieselbe queryid aus der Erweiterung pg_stat_statements (eine vorläufige Installation ist erforderlich). ""

Dies würde sicherlich viel helfen, aber das Ärgernis ist der erste Absatz: " Alle gesammelten Informationen werden nur im RAM gespeichert ", d. H. Wir haben einen Einfluss auf die Zielbasis. Darüber hinaus gibt es keine Sperrverlaufs- und Tabellenstatistik. Das heißt, Im Allgemeinen ist die Lösung unvollständig: „ Es gibt noch kein fertiges Paket für die Installation. Es wird vorgeschlagen, die Quellen herunterzuladen und die Bibliothek selbst zu erstellen. Zuerst müssen Sie das Paket "devel" für Ihren Server installieren und den Pfad zu pg_config in die Variable PATH schreiben. ""

Im Allgemeinen - viel Aufhebens und bei seriösen Produktionsdatenbanken gibt es möglicherweise keine Möglichkeit, etwas mit dem Server zu tun. Auch hier müssen Sie sich etwas Eigenes einfallen lassen.

Warnung
Aufgrund der Neuheit des Themas und der Unvollständigkeit des Testzeitraums dient der Artikel hauptsächlich als Orientierungshilfe und nicht als Satz von Abstracts und Zwischenergebnissen.
Detaillierteres Material wird später in Teilen vorbereitet.


Umreißen Sie die Lösungsanforderungen


Es ist notwendig, ein Werkzeug zum Speichern zu entwickeln:

Verlauf der Pg_stat_activity-Ansicht
Sitzungssperrverlauf mithilfe der Ansicht pg_locks

Die Entscheidungsanforderung besteht darin , die Auswirkungen auf die Zieldatenbank zu minimieren.

Die allgemeine Idee ist, dass der Datenerfassungsagent nicht in der Zieldatenbank, sondern in der Überwachungsdatenbank als systemd-Dienst gestartet wird. Ja, ein gewisser Datenverlust ist möglich, dies ist jedoch für die Berichterstellung nicht kritisch, es gibt jedoch keine Auswirkungen von Speicher und Speicherplatz auf die Zieldatenbank. Bei Verwendung eines Verbindungspools sind die Auswirkungen auf Benutzerprozesse minimal.

Implementierungsphasen


1. Servicetabellen


Zum Speichern von Tabellen wird ein separates Schema verwendet, um die Analyse der verwendeten Haupttabellen nicht zu erschweren.

DROP SCHEMA IF EXISTS activity_hist ; CREATE SCHEMA activity_hist AUTHORIZATION monitor ;

Wichtig: Das Schema wird nicht in der Zieldatenbank erstellt, sondern in der Überwachungsdatenbank.

Verlauf der Pg_stat_activity-Ansicht


Verwenden Sie die Tabelle, um die aktuellen Snapshots der Ansicht pg_stat_activity zu speichern

activity_hist.history_pg_stat_activity:
 --ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity; CREATE TABLE activity_hist.history_pg_stat_activity ( timepoint timestamp without time zone , datid oid , datname name , pid integer, usesysid oid , usename name , application_name text , client_addr inet , client_hostname text , client_port integer, backend_start timestamp with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text , queryid bigint );

Um das Einfügen zu beschleunigen - keine Indizes oder Einschränkungen.

Um den Verlauf direkt zu speichern, wird eine partitionierte Tabelle verwendet:

activity_hist.archive_pg_stat_activity:
 DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity; CREATE TABLE activity_hist.archive_pg_stat_activity ( timepoint timestamp without time zone , datid oid , datname name , pid integer, usesysid oid , usename name , application_name text , client_addr inet , client_hostname text , client_port integer, backend_start timestamp with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text , queryid bigint ) PARTITION BY RANGE (timepoint);

Da in diesem Fall keine Anforderungen an die Einfügegeschwindigkeit bestehen, wurden einige Indizes erstellt, um die Berichterstellung zu beschleunigen.

Sitzungssperrverlauf


Verwenden Sie die Tabelle, um die aktuellen Snapshots der Sitzungssperre zu speichern:

activity_hist.history_locking:
 --ACTIVITY_HIST.HISTORY_LOCKING DROP TABLE IF EXISTS activity_hist.history_locking; CREATE TABLE activity_hist.history_locking ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean );

Auch um das Einfügen zu beschleunigen - keine Indizes oder Einschränkungen.

Um den Verlauf direkt zu speichern, wird eine partitionierte Tabelle verwendet:

activity_hist.archive_locking:
 DROP TABLE IF EXISTS activity_hist.archive_locking; CREATE TABLE activity_hist.archive_locking ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) PARTITION BY RANGE (timepoint);

Da in diesem Fall keine Anforderungen an die Einfügegeschwindigkeit bestehen, wurden einige Indizes erstellt, um die Berichterstellung zu beschleunigen.

2. Füllen Sie den aktuellen Verlauf


Um Snapshots einer Ansicht direkt zu erfassen, wird ein Bash-Skript verwendet, das die Funktion plpgsql ausführt.

get_current_activity.sh
 #!/bin/bash ######################################################### #get_current_activity.sh ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S) host=$1 s_name=$2 s_pass=$3 psql -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE line_count=`cat $ERROR_FILE | wc -l` if [[ $line_count != '0' ]]; then rm -f /home/demon/*.err >/dev/null 2>/dev/null cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null fi rm $ERROR_FILE >/dev/null 2>/dev/null exit 0

Die Funktion plpgsql dblink greift auf Ansichten in der Zieldatenbank zu und fügt Zeilen in Servicetabellen in der Überwachungsdatenbank ein.

get_current_activity.sql
 CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$ DECLARE database_rec record; dblink_str text ; BEGIN EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'|| ' user='||current_s_name||' password='||current_s_pass|| ' '')'; -------------------------------------------------------------------- --GET pg_stat_activity stats INSERT INTO activity_hist.history_pg_stat_activity ( SELECT * FROM dblink('LINK1', 'SELECT now() , datid , datname , pid , usesysid , usename , application_name , client_addr , client_hostname , client_port , backend_start , xact_start , query_start , state_change , wait_event_type , wait_event , state , backend_xid , backend_xmin , query , backend_type FROM pg_stat_activity ') AS t ( timepoint timestamp without time zone , datid oid , datname name , pid integer, usesysid oid , usename name , application_name text , client_addr inet , client_hostname text , client_port integer, backend_start timestamp with time zone , xact_start timestamp with time zone , query_start timestamp with time zone , state_change timestamp with time zone , wait_event_type text , wait_event text , state text , backend_xid xid , backend_xmin xid , query text , backend_type text ) ); --------------------------------------- --ACTIVITY_HIST.HISTORY_LOCKING INSERT INTO activity_hist.history_locking ( SELECT * FROM dblink('LINK1', 'SELECT now() , lock.locktype, lock.relation, lock.mode, lock.transactionid as tid, lock.virtualtransaction as vtid, lock.pid, pg_blocking_pids(lock.pid), lock.granted FROM pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database WHERE NOT lock.pid = pg_backend_pid() ') AS t ( timepoint timestamp without time zone , locktype text , relation oid , mode text , tid xid , vtid text , pid integer , blocking_pids integer[] , granted boolean ) ); PERFORM dblink_disconnect('LINK1'); RETURN TRUE ; END $$ LANGUAGE plpgsql;

Zum Sammeln von Snapshots der Ansicht werden der systemd-Dienst und zwei Skripts verwendet:

pg_current_activity.service
 # /etc/systemd/system/pg_current_activity.service [Unit] Description=Collect history of pg_stat_activity , pg_locks Wants=pg_current_activity.timer [Service] Type=forking StartLimitIntervalSec=0 ExecStart=/home/postgres/pgutils/demon/get_current_activity.sh XXXX postgres postgres [Install] WantedBy=multi-user.target

pg_current_activity.timer
 # /etc/systemd/system/pg_current_activity.timer [Unit] Description=Run pg_current_activity.sh every 1 second Requires=pg_current_activity.service [Timer] Unit=pg_current_activity.service OnCalendar=*:*:0/1 AccuracySec=1 [Install] WantedBy=timers.target


Skripten Rechte zuweisen:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Wir starten den Service:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

So wird die Geschichte der Aufführungen in Form jeder zweiten Aufnahme gesammelt. Wenn alles so bleibt, wie es ist, werden die Tische natürlich sehr schnell größer und mehr oder weniger produktive Arbeit wird unmöglich.

Die Datenarchivierung muss organisiert werden.

3. Archivgeschichte


Für die Archivierung werden partitionierte Tabellen * verwendet.

Jede Stunde werden neue Abschnitte erstellt, während alte Daten aus den Verlaufstabellen * gelöscht werden, sodass sich die Größe der Verlaufstabellen * nicht wesentlich ändert und sich die Einfügegeschwindigkeit im Laufe der Zeit nicht verschlechtert.

Die Erstellung neuer Abschnitte erfolgt über die plpgsql-Funktion activity_hist.archive_current_activity. Der Operationsalgorithmus ist sehr einfach (am Beispiel des Abschnitts für die Tabelle archive_pg_stat_activity).

Erstellen Sie einen neuen Abschnitt und füllen Sie ihn aus
 EXECUTE format( 'CREATE TABLE ' || partition_name || ' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_min_range ),'MM')||'-'|| to_char(date_trunc('day', partition_min_range ),'DD')||' '|| to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_max_range ),'MM')||'-'|| to_char(date_trunc('day', partition_max_range ),'DD')||' '|| to_char(date_trunc('hour', partition_max_range ),'HH24')||':00' ); INSERT INTO activity_hist.archive_pg_stat_activity ( SELECT * FROM activity_hist.history_pg_stat_activity WHERE timepoint BETWEEN partition_min_range AND partition_max_range );

Indizes erstellen
 EXECUTE format ( 'CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' ); EXECUTE format ('CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' );

Löschen Sie alte Daten aus der Tabelle history_pg_stat_activity
 DELETE FROM activity_hist.history_pg_stat_activity WHERE timepoint < partition_max_range;

Natürlich werden alte Abschnitte regelmäßig als unnötig gelöscht.

Grundlegende Berichte


Warum wird das alles getan? Berichte aus der Ferne empfangen, die in etwa an Oracle AWR erinnern.
Es ist wichtig hinzuzufügen, dass zum Empfangen von Berichten eine Beziehung zwischen den Ansichten pg_stat_activity und pg_stat_statements erstellt werden muss. Tabellen werden durch Hinzufügen der Spalte 'queryid' zu den Tabellen 'history_pg_stat_activity', 'archive_pg_stat_activity' verknüpft. Die Möglichkeit, einen Spaltenwert hinzuzufügen, geht über den Rahmen dieses Artikels hinaus und wird hier beschrieben - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

GESAMT-CPU-ZEIT FÜR ABFRAGEN


Anfrage:
 WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC Intervall " WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC * Intervall" WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC Backend' AND datname! = 'Postgres' AND (aa.wait_event_type IS NULL) ANDaa.state = WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type IS NULL ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Ein Beispiel:
 ------------------------------------------------------------------- | TOTAL CPU TIME FOR QUERIES : 07:47:36 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 04:28:58 | 2| | 01:07:29 | 3| 1237430309438971376| 00:59:38 | 4| 4710212362688288619| 00:50:48 | 5| 28942442626229688| 00:15:50 | 6| 9150846928388977274| 00:04:46 | 7| -6572922443698419129| 00:00:06 | 8| | 00:00:01 +----+----------------------------------------+--------------------


TOTAL WAITINGS TIME FOR QUERIES


Anfrage:
 WITH hist AS ( SELECT aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type IS NOT NULL ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NOT NULL ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Ein Beispiel:
 ------------------------------------------------------------------- | TOTAL WAITINGS TIME FOR QUERIES : 21:55:04 +----+----------------------------------------+-------------------- | #| queryid| duration +----+----------------------------------------+-------------------- | 1| 389015618226997618| 16:19:05 | 2| | 03:47:04 | 3| 8085340880788646241| 00:40:20 | 4| 4710212362688288619| 00:13:35 | 5| 9150846928388977274| 00:12:25 | 6| 28942442626229688| 00:11:32 | 7| 1237430309438971376| 00:09:45 | 8| 2649515222348904837| 00:09:37 | 9| | 00:03:45 | 10| 3167065002719415275| 00:02:20 | 11| 5731212217001535134| 00:02:13 | 12| 8304755792398128062| 00:01:31 | 13| 2649515222348904837| 00:00:59 | 14| 2649515222348904837| 00:00:22 | 15| | 00:00:12 | 16| 3422818749220588372| 00:00:08 | 17| -5730801771815999400| 00:00:03 | 18| -1473395109729441239| 00:00:02 | 19| 2404820632950544954| 00:00:02 | 20| -6572922443698419129| 00:00:02 | 21| 2369289265278398647| 00:00:01 | 22| 180077086776069052| 00:00:01 +----+----------------------------------------+--------------------

WARTEN AUF FRAGEN


Anfragen:
 WITH hist AS ( SELECT aa.wait_event_type , aa.wait_event FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND aa.wait_event IS NOT NULL GROUP BY aa.wait_event_type , aa.wait_event UNION SELECT ha.wait_event_type , ha.wait_event FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ha.wait_event IS NOT NULL GROUP BY ha.wait_event_type , ha.wait_event ) SELECT wait_event_type , wait_event FROM hist GROUP BY wait_event_type , wait_event ORDER BY 1 ASC,2 ASC ---------------------------------------------------------------------- WITH hist AS ( SELECT aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid , count(*) * interval '1 second' AS duration FROM activity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid UNION SELECT ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid, count(*) * interval '1 second' AS duration FROM activity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid ) SELECT query , queryid , SUM( duration ) as duration FROM hist GROUP BY query , queryid ORDER BY 3 DESC

Ein Beispiel:
 ------------------------------------------------ | WAITINGS FOR QUERIES +----------------------------------------------- | wait_event_type = Client| | wait_event = ClientRead| | Total time = 00:46:56| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 8085340880788646241| 00:40:20 | 2| | 00:03:45 | 3| 5731212217001535134| 00:01:53 | 4| | 00:00:12 | 5| 9150846928388977274| 00:00:09 | 6| 3422818749220588372| 00:00:08 | 7| 1237430309438971376| 00:00:06 | 8| 28942442626229688| 00:00:05 | 9| 4710212362688288619| 00:00:05 | 10|-5730801771815999400| 00:00:03 | 11| 8304755792398128062| 00:00:02 | 12|-6572922443698419129| 00:00:02 | 13|-1473395109729441239| 00:00:02 | 14| 2404820632950544954| 00:00:02 | 15| 180077086776069052| 00:00:01 | 16| 2369289265278398647| 00:00:01 +----------------------------------------------- | wait_event_type = IO| | wait_event = BufFileRead| | Total time = 00:00:38| ------------------------------------------------ | #| queryid| duration +-----+--------------------+-------------------- | 1| 28942442626229688| 00:00:38 +-----------------------------------------------

GESCHICHTE FÜR GESPERRTE PROZESSE


Anfrage:
 SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.archive_locking al WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype UNION SELECT MIN(date_trunc('second',timepoint)) AS started , count(*) * interval '1 second' as duration , pid , blocking_pids , relation , mode , locktype FROM activity_hist.history_locking WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND NOT granted AND locktype = 'relation' GROUP BY pid , blocking_pids , relation , mode , locktype ORDER BY 1

Ein Beispiel:
  -------------------------------------------------- -------------------------------------------------- ---------------------------------
 |  GESCHICHTE FÜR GESPERRTE PROZESSE
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 |  # |  pid |  gestartet |  Dauer |  blockierende_pids |  Beziehung |  Modus |  Locktyp
 + ----- + ---------- + -------------------- + ---------- + -------------------- + -------------------- + -------- ------------ + --------------------
 |  1 |  26224 |  2019-09-02 19: 32: 16 |  00: 01: 45 |  {26211} |  16541 |  AccessShareLock |  Beziehung
 |  2 |  26390 |  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541 |  AccessShareLock |  Beziehung
 |  3 |  26391 |  2019-09-02 19: 34: 03 |  00: 00: 53 |  {26211} |  16541 |  AccessShareLock |  Beziehung
 |  4 |  26531 |  2019-09-02 19: 35: 27 |  00: 00: 12 |  {26211} |  16541 |  AccessShareLock |  Beziehung
 |  5 |  27284 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  Beziehung
 |  6 |  27283 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  Beziehung
 |  7 |  27286 |  2019-09-02 19: 44: 02 |  00: 00: 19 |  {27276} |  16541 |  AccessShareLock |  Beziehung
 |  8 |  27423 |  2019-09-02 19:45:24 |  00: 00: 12 |  {27394} |  16541 |  AccessShareLock |  Beziehung
 |  9 |  27648 |  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541 |  AccessShareLock |  Beziehung
 |  10 |  27650 |  2019-09-02 19: 48: 06 |  00: 00: 20 |  {27647} |  16541 |  AccessShareLock |  Beziehung
 |  11 |  27735 |  2019-09-02 19: 49: 08 |  00: 00: 06 |  {27650} |  16541 |  AccessExclusiveLock |  Beziehung
 |  12 |  28380 |  2019-09-02 19: 56: 03 |  00: 01: 56 |  {28379} |  16541 |  AccessShareLock |  Beziehung
 |  13 |  28379 |  2019-09-02 19: 56: 03 |  00: 00: 01 |  28377 |  16541 |  AccessExclusiveLock |  Beziehung
 |  |  |  |  |  28376 |  |

BLOCKING PROCESSES GESCHICHTE


Anfragen:
 SELECT blocking_pids FROM activity_hist.archive_locking al WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND NOT granted AND locktype = 'relation' GROUP BY blocking_pids UNION SELECT blocking_pids FROM activity_hist.history_locking WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND NOT granted AND locktype = 'relation' GROUP BY blocking_pids ORDER BY 1 --------------------------------------------------------------- SELECT pid , usename , application_name , datname , MIN(date_trunc('second',timepoint)) as started , count(*) * interval '1 second' as duration , state , query FROM activity_hist.archive_pg_stat_activity WHERE pid= current_pid AND timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') GROUP BY pid , usename , application_name , datname , state_change, state , query UNION SELECT pid , usename , application_name , datname , MIN(date_trunc('second',timepoint)) as started , count(*) * interval '1 second' as duration , state , query FROM activity_hist.history_pg_stat_activity_for_reports WHERE pid= current_pid AND timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') GROUP BY pid , usename , application_name , datname , state_change, state , query ORDER BY 5 , 1

Ein Beispiel:
  -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
 BLOCKING PROCESSES GESCHICHTE
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  # |  pid |  Benutzername |  Anwendungsname |  datname |  gestartet |  Dauer |  Zustand |  Abfrage
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  1 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 54 |  00: 00: 04 |  Leerlauf |
 |  2 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 58 |  00: 00: 06 |  Leerlauf in Transaktion |  beginnen;
 |  3 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 32: 16 |  00: 01: 45 |  Leerlauf in Transaktion |  Sperrtabelle wafer_data;
 |  4 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 35: 54 |  00: 01: 23 |  Leerlauf |  begehen;
 |  5 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 46 |  00: 00: 02 |  Leerlauf in Transaktion |  beginnen;
 |  6 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 54 |  00: 00: 08 |  Leerlauf in Transaktion |  Sperrtabelle wafer_data;
 |  7 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 39: 08 |  00: 42: 42 |  Leerlauf |  begehen;
 |  8 |  26211 |  tuser |  psql |  tdb1 |  2019-09-03 07: 12: 07 |  00: 00: 52 |  aktiv |  wähle test_del ();


Entwicklung.


Die angezeigten grundlegenden Abfragen und erhaltenen Berichte vereinfachen bereits die Lebensdauer bei der Analyse von Leistungsvorfällen erheblich.
Basierend auf grundlegenden Abfragen können Sie einen Bericht erhalten, der ungefähr an Oracle AWR erinnert.
Beispielzusammenfassungsbericht
  + --------------------------------------------- -----------------------------------
 |  KONSOLIDIERTER BERICHT ÜBER AKTIVITÄT UND WARTEN.  DATETIME: 09/03/2019 14:08
 | --------------------------------------------- -----------------------------------
 |  Host: XXXX
 |  BEGIN_SNAPSHOT: 09/02/2019 14:08 END_SNAPSHOT: 09/03/2019 14:00
 | --------------------------------------------- -----------------------------------
 |  AKTUELLE DATENBANKGRÖSSE:
 |  DATENBANK: Monitor
 |  GRÖSSE (MB): 1370,00
 | --------------------------------------------- -----------------------------------
 |  CLUSTER-CPU-ZEIT: 19:44:22
 |  CLUSTER WAITINGS TIME: 78:49:16
 |
 |  SQL DBTIME: 65:53:09
 |  SQL-CPU-ZEIT: 19:05:21
 |  SQL WAITINGS TIME: 21:50:46
 |  SQL IOTIME: 20:53:00
 |  SQL-LESEZEIT: 20:52:55
 |  SQL-SCHREIBZEIT: 00:00:05
 |
 |  SQL CALLS: 311293
 -------------------------------------------------- -----------
 |  SQL SHARED BLOCKS LESEN: 13351563334
 |  SQL SHARED BLOCKS HITS: 2775427045
 |  SQL SHARED BLOCKS HITS / READS%: 20,79
 |  SQL SHARED BLOCKS DIRTED: 21105
 |  SQL SHARED BLOCKS SCHRIFTLICH: 3656
 |
 |  SQL TEMPORARY BLOCKS READS: 7464932
 |  SQL TEMPORARY BLOCKS SCHRIFTLICH: 10176024
 -------------------------------------------------- -----------
 |
 |  WARTUNGSSTATIK
 |
 + --------------------------------------------- -----------------------------------
 |  TOP 10 WARTUNGEN NACH GESAMTWARTUNG FÜR SYSTEMPROZESSE
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  wait_event |  Dauer
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Aktivität |  LogicalLauncherMain |  11:21:01
 |  2 |  Aktivität |  CheckpointerMain |  11:20:35
 |  3 |  Aktivität |  AutoVacuumMain |  11:20:31
 |  4 |  Aktivität |  WalWriterMain |  11:19:35
 |  5 |  Aktivität |  BgWriterMain |  10:14:19
 |  6 |  Aktivität |  BgWriterHibernate |  01.06.04
 |  7 |  Aktivität |  WalSenderMain |  00:04:05
 |  8 |  Client |  ClientWrite |  00:04:00
 |  9 |  IO |  BufFileWrite |  00:02:45
 |  10 |  LWLock |  buffer_mapping |  00:02:14
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  TOP 10 WARTUNGEN NACH GESAMTWARTUNG FÜR KUNDENPROZESSE
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  Dauer |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Sperre |  Transaktions-ID |  11: 55: 37 |  18.1
 |  2 |  IO |  DataFileRead |  07: 19: 43 |  12/12
 |  3 |  Client |  ClientRead |  00: 46: 54 |  1.19
 |  4 |  Sperre |  Beziehung |  00: 40: 37 |  1,03
 |  5 |  LWLock |  buffer_mapping |  00: 31: 08 |  0,79
 |  6 |  LWLock |  buffer_io |  00: 22: 12 |  0,56
 |  7 |  Zeitüberschreitung |  PgSleep |  00: 10: 58 |  0,28
 |  8 |  Sperre |  Tupel |  00: 01: 30 |  0,04
 |  9 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 |  10 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  WARTETYPEN NACH GESAMTWARTZEIT FÜR SYSTEMPROZESSE
 + ----- + ------------------------------ + ------------ --------
 |  # |  wait_event_type |  Dauer
 + ----- + ------------------------------ + ------------ --------
 |  1 |  Aktivität |  56:46:10
 |  2 |  IO |  00:05:13
 |  3 |  Client |  00:04:00
 |  4 |  LWLock |  00:03:07
 + ----- + ------------------------------ + ------------ --------
 |  WARTETYPEN NACH GESAMTWARTUNG FÜR KUNDENPROZESSE
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  Dauer |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Sperre |  12: 37: 44 |  19.17
 |  2 |  IO |  07: 21: 40 |  11.17
 |  3 |  LWLock |  00: 53: 26 |  1,35
 |  4 |  Client |  00: 46: 54 |  1.19
 |  5 |  Zeitüberschreitung |  00: 10: 58 |  0,28
 |  6 |  IPC |  00: 00: 04 |  0
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  WARTEN AUF SYSTEMPROZESSE
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  # |  backend_type |  Datenbankname |  wait_event_type |  wait_event |  Dauer
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  logischer Replikationsstarter |  |  Aktivität |  LogicalLauncherMain |  11:21:01
 |  2 |  Checkpointer |  |  Aktivität |  CheckpointerMain |  11:20:35
 |  3 |  Autovakuum-Starter |  |  Aktivität |  AutoVacuumMain |  11:20:31
 |  4 |  Walwriter |  |  Aktivität |  WalWriterMain |  11:19:35
 |  5 |  Hintergrundschreiber |  |  Aktivität |  BgWriterMain |  10:14:19
 |  6 |  Hintergrundschreiber |  |  Aktivität |  BgWriterHibernate |  01.06.04
 |  7 |  Walsender |  |  Aktivität |  WalSenderMain |  00:04:05
 |  8 |  Walsender |  |  Client |  ClientWrite |  00:04:00
 |  9 |  Parallelarbeiter |  tdb1 |  IO |  BufFileWrite |  00:02:45
 |  10 |  Parallelarbeiter |  tdb1 |  LWLock |  buffer_mapping |  00:02:05
 |  11 |  Parallelarbeiter |  tdb1 |  IO |  DataFileRead |  00:01:10
 |  12 |  Parallelarbeiter |  tdb1 |  IO |  BufFileRead |  00:01:05
 |  13 |  Parallelarbeiter |  tdb1 |  LWLock |  buffer_io |  00:00:45
 |  14 |  Autovakuumarbeiter |  tdb1 |  LWLock |  buffer_mapping |  00:00:09
 |  15 |  Walwriter |  |  IO |  WALWrite |  00:00:08
 |  16 |  Walwriter |  |  LWLock |  WALWriteLock |  00:00:04
 |  17 |  Hintergrundschreiber |  |  LWLock |  WALWriteLock |  00:00:03
 |  18 |  Hintergrundschreiber |  |  IO |  WALWrite |  00:00:02
 |  19 |  Hintergrundschreiber |  |  IO |  DataFileWrite |  00:00:02
 |  20 |  Checkpointer |  |  IO |  ControlFileSyncUpdate |  00:00:01
 |  21 |  Autovakuumarbeiter |  tdb1 |  LWLock |  buffer_io |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  WARTEN AUF SQL
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  # |  queryid |  Datenbankname |  wait_event_type |  wait_event |  Dauer |  % dbtime
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  1 |  389015618226997618 |  tdb1 |  Sperre |  Transaktions-ID |  09: 47: 43 |  14.87
 |  2 |  389015618226997618 |  tdb1 |  IO |  DataFileRead |  05: 47: 07 |  8,78
 |  3 |  |  tdb1 |  Sperre |  Transaktions-ID |  02: 07: 54 |  3.24
 |  4 |  |  tdb1 |  IO |  DataFileRead |  01: 30: 24 |  2.29
 |  5 |  8085340880788646241 |  tdb1 |  Client |  ClientRead |  00: 40: 20 |  1,02
 |  6 |  389015618226997618 |  tdb1 |  LWLock |  buffer_mapping |  00: 20: 41 |  0,52
 |  7 |  389015618226997618 |  tdb1 |  LWLock |  buffer_io |  00: 17: 30 |  0,44
 |  8 |  2649515222348904837 |  tdb1 |  Zeitüberschreitung |  PgSleep |  00: 10: 58 |  0,28
 |  9 |  4710212362688288619 |  tdb1 |  Sperre |  Beziehung |  00: 10: 44 |  0,27
 |  10 |  9150846928388977274 |  tdb1 |  Sperre |  Beziehung |  00: 10: 24 |  0,26
 |  11 |  28942442626229688 |  tdb1 |  Sperre |  Beziehung |  00: 07: 48 |  0,2
 |  12 |  1237430309438971376 |  tdb1 |  Sperre |  Beziehung |  00: 07: 32 |  0,19
 |  13 |  |  tdb1 |  LWLock |  buffer_mapping |  00: 04: 32 |  0,11
 |  14 |  |  tdb1 |  LWLock |  buffer_io |  00: 04: 13 |  0,11
 |  15 |  |  tdb1 |  Client |  ClientRead |  00: 03: 57 |  0,1
 |  16 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_mapping |  00: 02: 26 |  0,06
 |  17 |  3167065002719415275 |  tdb1 |  Sperre |  Beziehung |  00: 02: 20 |  0,06
 |  18 |  5731212217001535134 |  tdb1 |  Client |  ClientRead |  00: 01: 53 |  0,05
 |  19 |  1237430309438971376 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 42 |  0,04
 |  20 |  389015618226997618 |  tdb1 |  Sperre |  Tupel |  00: 01: 30 |  0,04
 |  21 |  8304755792398128062 |  tdb1 |  Sperre |  Beziehung |  00: 01: 29 |  0,04
 |  22 |  28942442626229688 |  tdb1 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 |  23 |  9150846928388977274 |  tdb1 |  IO |  DataFileRead |  00: 01: 07 |  0,03
 |  24 |  28942442626229688 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 03 |  0,03
 |  25 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_mapping |  00: 00: 44 |  0,02
 |  26 |  28942442626229688 |  tdb1 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 |  27 |  28942442626229688 |  tdb1 |  LWLock |  buffer_io |  00: 00: 25 |  0,01
 |  28 |  1237430309438971376 |  tdb1 |  IO |  DataFileRead |  00: 00: 24 |  0,01
 |  29 |  28942442626229688 |  tdb1 |  IO |  DataFileRead |  00: 00: 22 |  0,01
 |  30 |  5731212217001535134 |  tdb1 |  Sperre |  Beziehung |  00: 00: 20 |  0,01
 |  31 |  4710212362688288619 |  tdb1 |  IO |  DataFileRead |  00: 00: 19 |  0,01
 |  32 |  9150846928388977274 |  tdb1 |  Client |  ClientRead |  00: 00: 09 |  0
 |  33 |  3422818749220588372 |  tdb1 |  Client |  ClientRead |  00: 00: 08 |  0
 |  34 |  1237430309438971376 |  tdb1 |  Client |  ClientRead |  00: 00: 06 |  0
 |  35 |  389015618226997618 |  tdb1 |  LWLock |  buffer_content |  00: 00: 05 |  0
 |  36 |  4710212362688288619 |  tdb1 |  Client |  ClientRead |  00: 00: 05 |  0
 |  37 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_io |  00: 00: 04 |  0
 |  38 |  28942442626229688 |  tdb1 |  Client |  ClientRead |  00: 00: 04 |  0
 |  39 |  28942442626229688 |  tdb1 |  IPC |  ParallelFinish |  00: 00: 03 |  0
 |  40 |  389015618226997618 |  tdb1 |  IO |  DataFileWrite |  00: 00: 02 |  0
 |  41 |  -5730801771815999400 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  42 |  2404820632950544954 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  43 |  -6572922443698419129 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  44 |  8304755792398128062 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  45 |  -1473395109729441239 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  46 |  |  tdb1 |  LWLock |  buffer_content |  00: 00: 01 |  0
 |  47 |  180077086776069052 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |  0
 |  48 |  |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0
 |  49 |  28942442626229688 |  tdb1 |  IPC |  MessageQueueReceive |  00: 00: 01 |  0
 |  50 |  2369289265278398647 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |  0
 |  51 |  9150846928388977274 |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |
 |  CLIENT SQL STATICTICS
 |
 + --------------------------------------------- -----------------------------------
 |  CLIENT SQL sortiert nach verstrichener Zeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  verstrichene Zeit |  Anrufe |  % dbtime |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  06: 43: 19 |  36 |  10.2 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  02: 06: 53 |  715 |  3.21 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  01: 52: 07 |  720 |  2,84 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 39: 03 |  357 |  0,99 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 22: 00 |  8 |  0,56 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  CLIENT SQL geordnet nach CPU-Zeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CPU-Zeit |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  16: 14: 38 |  36 |  10.2 |  06: 43: 19 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  00: 13: 38 |  720 |  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 11: 39 |  357 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 10: 58 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 |  00: 09: 44 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL sortiert nach Benutzer-E / A-Wartezeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  io_wait time |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  05: 47: 09 |  36 |  10.2 |  06: 43: 19 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  00: 02: 15 |  357 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 00: 24 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  00: 00: 19 |  720 |  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL sortiert nach Shared Buffers Reads
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Puffer liest |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  2562353244 |  36 |  10.2 |  06: 43: 19 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  11041689 |  357 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  3303551 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  3242892 |  720 |  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  0 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL sortiert nach Disk Reads Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Lesezeit |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  03: 37: 46 |  36 |  10.2 |  06: 43: 19 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  00: 04: 07 |  357 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 00: 59 |  720 |  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 00: 42 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL sortiert nach Ausführungen
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Anrufe |  Zeilen |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  720 |  720 |  2,84 |  01: 52: 07 |  1,19 |  0,08 |  tdb1 |  4710212362688288619
 |  715 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  357 |  0 |  0,99 |  00: 39: 03 |  1,02 |  0,33 |  tdb1 |  28942442626229688
 |  36 |  36 |  10.2 |  06: 43: 19 |  85. September |  17.38 |  tdb1 |  389015618226997618
 |  8 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Vollständige Liste des SQL-Textes
 ----------------------------------------------
 ...

Fortsetzung folgt. Der nächste Schritt ist die Bildung des Sperrverlaufs (pg_stat_locks), eine detailliertere Beschreibung des Prozesses zum Füllen von Tabellen.

Source: https://habr.com/ru/post/de467181/

More articles:


All Articles