Inżynier — tłumacząc z łaciny — jest natchniony.
Inżynier potrafi wszystko. (c) R.Dizel.
Epigrafy.
Lub historia o tym, dlaczego administrator baz danych powinien przypominać sobie swoje programistyczne przeszłość.
Wstęp
Wszystkie imiona zostały zmienione. Zbiegi okoliczności są przypadkowe. Materiał przedstawia wyłącznie osobiste zdanie autora.
Zastrzeżenie dotyczące gwarancji: w planowanym cyklu artykułów nie będzie szczegółowego i dokładnego opisu używanych tabel i skryptów. Materiały nie będą mogły być od razu użyte "TAK JAK SĄ".
Po pierwsze, z powodu dużej objętości materiału,
po drugie, z powodu dostosowania do produkcyjnej bazy rzeczywistego klienta.
Dlatego w artykułach będą podane tylko pomysły i opisy w najbardziej ogólnym ujęciu.
Może w przyszłości system osiągnie poziom publikacji na GitHubie, a może i nie. Czas pokaże.
Początek historii – „».
Co powstało w wyniku, w najbardziej ogólnych zarysach – „»
Po co mi to wszystko?
No, po pierwsze, aby samemu nie zapomnieć, wspominając na emeryturze dawne, chwalebne dni.
Po drugie, aby usystematyzować to, co napisałem. Bowiem już sam, czasami, zaczynam się mylić i zapominać o poszczególnych częściach.
No i najważniejsze — nagle może to komuś się przyda i pomoże nie wynajdywać na nowo koła ani nie zbierać łopat. Innymi słowy, poprawić swoją karmę (nie hubrowską). Bo najcenniejsze w tym świecie to idee. Najważniejsze to znaleźć pomysł. A zrealizować pomysł w rzeczywistości to już kwestia czysto techniczna.
Tak więc, zaczynamy, powoli...
Postawienie zadania.
Mamy:
Baza danych PostgreSQL (10.5), mieszany typ obciążenia (OLTP+DSS), średnie-niskie obciążenie, umieszczona w chmurze AWS.
Monitoring bazy danych nie istnieje, monitoring infrastruktury przedstawiony jest w postaci standardowych narzędzi AWS w minimalnej konfiguracji.
Wymagane:
Monitorowanie wydajności i stanu bazy danych, znajdowanie i posiadanie początkowych informacji do optymalizacji ciężkich zapytań do bazy danych.
Krótka przedmowa lub analiza opcji rozwiązania
Na początek spróbujemy przeanalizować możliwości rozwiązania zadania z punktu widzenia analizy porównawczej korzyści i niedogodności dla inżyniera, a korzyściami i stratami w zarządzaniu niech zajmą się ci, którzy są do tego wyznaczeni zgodnie z etatem.
Opcja 1 – "Praca na żądanie"
Zostawiamy wszystko jak jest. Jeśli zleceniodawca ma jakieś zastrzeżenia co do funkcjonowania, wydajności bazy danych lub aplikacji, powiadomi inżynierów DBA za pomocą e-maila lub tworząc incydent w systemie zgłoszeń.
Inżynier, otrzymawszy powiadomienie, zajmie się problemem, zaproponuje rozwiązanie lub odłoży sprawę na później, mając nadzieję, że sama się rozwiąże, a i tak wkrótce wszystko zostanie zapomniane.
Pierniki i pączki, siniaki i guzkiPierniki i pączki:
1. Nie ma potrzeby robić nic zbędnego
2. Zawsze można się wykręcić i odpuścić.
3. Mnóstwo czasu, który można wykorzystać według własnego uznania.
Siniaki i guzki:
1. Prędzej czy później zleceniodawca zacznie się zastanawiać nad istotą bytu i kosmiczną sprawiedliwością w tym świecie i po raz kolejny zada sobie pytanie — za co płacę im swoje pieniądze? Skutek zawsze jest ten sam — chodzi tylko o to, kiedy zleceniodawca się znudzi i machnie ręką na pożegnanie. A żłób się opróżni. To smutne.
2. Rozwój inżyniera — zero.
3. Trudności w planowaniu pracy i obciążenia
Opcja 2 - „Tańczymy z bębnem, wciskamy i obuwamy”
Punkt 1-Po co nam system monitorowania, będziemy wszystko dostawać poprzez zapytania. Uruchamiamy mnóstwo różnych zapytań do słownika danych i dynamicznych widoków, włączamy różne liczniki, zestawiamy wszystko w tabelach, okresowo analizujemy listy i tabele. W rezultacie mamy ładne lub mniej ładne wykresy, tabele, raporty. Ważne, żeby było jak najwięcej.
Punkt 2-Generujemy aktywność - uruchamiamy analizę tego wszystkiego.
Punkt 3-Przygotowujemy jakiś dokument, nazywamy go po prostu - „jak mamy urządzić bazę danych”.
Punkt 4-Zleceniodawca, widząc to wszystko wspaniałość wykresów i liczb jest w dziecięcej naiwnej pewności — oto teraz wszystko zacznie działać, wkrótce. I łatwo i bezboleśnie rozstaje się ze swoimi zasobami finansowymi. Zarząd również jest pewien — inżynierowie u nas pracują na pełnych obrotach. Obciążenie na maksymalnym poziomie.
Punkt 5-Regularnie powtórzyć Punkt 1.
Pierniki i pączki, siniaki i guzkiPierniki i pączki:
1. Życie menedżerów i inżynierów — proste, przewidywalne i pełne aktywności. Wszystko brzęczy, wszyscy zajęci.
2. Życie zleceniodawcy też nie jest złe — zawsze jest pewny, że wystarczy trochę poczekać i wszystko się ułoży. Nie układa się, no coż — świat jest niesprawiedliwy, w następnym życiu się uda.
Siniaki i guzki:
1. Prędzej czy później znajdzie się szybszy dostawca podobnej usługi, który zrobi to samo, ale za nieco niższą cenę. A jeśli wynik jest ten sam, to po co płacić więcej? To znowu doprowadzi do zniknięcia źródła dochodu.
2. To nudne. Jak każda mało sensowna aktywność.
3. Jak w poprzedniej wersji — brak jakiegokolwiek rozwoju. Ale dla inżyniera minusem jest to, że w przeciwieństwie do pierwszej opcji, tutaj trzeba nieustannie generować bazę danych. A to zajmuje czas. Który można wykorzystać dla siebie. Bo jeśli sam o siebie nie zadbasz, nikt się o ciebie nie troszczy.
Opcja 3 — nie trzeba wymyślać koła na nowo, wystarczy je kupić i jeździć.
Inżynierowie z innych firm nie bez powodu jedzą pizzę popijając piwem (ach, wspaniałe czasy w Petersburgu lat 90.). Użyjmy systemów monitorujących, które są już stworzone, dopracowane i działają, i przynoszą korzyści (przynajmniej ich twórcom).
Pierniki i pączki, siniaki i guzkiPierniki i pączki:
1. Nie trzeba tracić czasu na wymyślanie czegoś, co już zostało wymyślone. Weź i korzystaj.
2. Systemy monitorowania nie są pisaną przez idiotów i na pewno są użyteczne.
3. Działające systemy monitorowania zazwyczaj dostarczają przydatne, przefiltrowane informacje.
Siniaki i guzki:
1. Inżynier w tej sytuacji nie jest inżynierem, a jedynie użytkownikiem cudzej produkcji. Albo użytkownikiem.
2. Klienta trzeba przekonać do konieczności zakupu czegoś, w co w ogóle nie chce się zagłębiać, a nie powinien. Budżet na rok został zatwierdzony i się nie zmieni. Następnie trzeba przypisać osobny zasób, skonfigurować pod konkretny system. Tzn. najpierw trzeba płacić, płacić i jeszcze raz płacić. A klient jest skąpy. To norma życia.
Co zrobić - Czernyszewski? Twoje pytanie jest bardzo zasadne. (c)
W tej konkretnej sytuacji można postąpić trochę inaczej — a może zróbmy nasz własny system monitorowania.
No nie system oczywiście, w pełnym tego słowa znaczeniu, to zbyt patetyczne i zuchwałe, ale przynajmniej w jakiś sposób ułatwmy sobie zadanie i zbierzmy jak najwięcej informacji do rozwiązania incydentów wydajności. Żeby nie znaleźć się w sytuacji — "idź tam, nie wiem gdzie, znajdź to, nie wiem co".
Jakie są plusy i minusy tej opcji:
Zalety:
1. To ciekawe. Przynajmniej ciekawsze niż ciągłe „shrink datafile, alter tablespace, itp.”
2. To nowe umiejętności i nowe możliwości rozwoju. Co w przyszłości da zasłużone nagrody i przyjemności.
Wady:
1. Będzie trzeba pracować. Pracować dużo.
2. Będzie trzeba regularnie wyjaśniać sens i możliwości całej aktywności.
3. Czegoś będzie trzeba się wyrzec, ponieważ jedynym zasobem dostępnym inżynierowi – czasem – jest ograniczony przez Wszechświat.
4. Najgorsze i najbardziej nieprzyjemne – w wyniku może powstać coś w rodzaju "Nie mysz, nie żaba, a nieznane zwierzątko".
Kto nie ryzykuje, ten nie pije szampana.
I tak – zaczyna się to, co najciekawsze.
Ogólna idea – schematycznie
(Ilustracja pochodzi z artykułu «»)
Wyjaśnienie:
- W docelowej bazie instalowane jest standardowe rozszerzenie PostgreSQL – "pg_stat_statements".
- W bazie danych monitorującej tworzymy zestaw tabel serwisowych do przechowywania historii pg_stat_statements na wczesnym etapie oraz do konfigurowania metryk i monitorowania później.
- Na hoście monitorującym tworzymy zestaw skryptów bash, w tym do generowania incydentów w systemie zgłoszeń.
Tabele serwisowe
Na początek schematyczna uproszczona ERD, co nam ostatecznie wyszło:
Krótki opis tabelendpoint – host, punkt połączenia z instancją
database – parametry bazy danych
pg_stat_history – tabela historyczna do przechowywania czasowych zrzutów widoku pg_stat_statements docelowej bazy danych
metric_glossary – słownik metryk wydajności
metric_config – konfiguracja poszczególnych metryk
metric – konkretna metryka dla monitorowanego zapytania
metric_alert_history – historia ostrzeżeń wydajności
log_query – tabela serwisowa do przechowywania sparsowanych wpisów z pliku dziennika PostgreSQL wgrywanego z AWS
baseline – parametry okresu czasu używanego jako bazowy
checkpoint – konfiguracja metryk sprawdzających stan bazy danych
checkpoint_alert_history – historia ostrzeżeń metryk sprawdzających stan bazy danych
pg_stat_db_queries – tabela serwisowa aktywnych zapytań
activity_log – tabela serwisowa dziennika aktywności
trap_oid – tabela serwisowa konfiguracji pułapki
Etap 1 – zbieramy informacje statystyczne o wydajności i uzyskujemy raporty
Do przechowywania informacji statystycznych służy tabela pg_stat_history
Struktura tabeli pg_stat_history
Tabela "public.pg_stat_history"
Kolumna | Typ | Modyfikatory
---------------------+-----------------------------+-------------------------------------------
id | liczba całkowita | not null domyślnie nextval('pg_stat_history_id_seq'::regclass)
snapshot_timestamp | znacznik czasu bez strefy |
database_id | liczba całkowita |
dbid | oid |
userid | oid |
queryid | bigint |
query | tekst |
calls | bigint |
total_time | podwójna precyzja |
min_time | podwójna precyzja |
max_time | podwójna precyzja |
mean_time | podwójna precyzja |
stddev_time | podwójna precyzja |
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 | podwójna precyzja |
blk_write_time | podwójna precyzja |
baseline_id | liczba całkowita |
Indeksy:
"pg_stat_history_pkey" KLUCZ GŁÓWNY, btree (id)
"database_idx" btree (database_id)
"queryid_idx" btree (queryid)
"snapshot_timestamp_idx" btree (snapshot_timestamp)
Ograniczenia kluczy obcych:
"database_id_fk" KLUCZ OBCE (database_id) ODNIESIENIA database(id) W USUŃ KASKADOWOJak widać, tabela jest jedynie akumulacją danych widoku pg_stat_statements w docelowej bazie danych.
Użycie tej tabeli jest bardzo proste
pg_stat_history będzie stanowić skumulowaną statystykę wykonania zapytań dla każdej godziny. Na początku każdej godziny, po wypełnieniu tabeli, statystyka pg_stat_statements jest resetowana za pomocą pg_stat_statements_reset().
Uwaga: statystyka jest zbierana dla zapytań o czasie wykonania przekraczającym 1 sekundę.
Wypełnienie tabeli 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 'NOWY ZRÓDŁO JEST TWORZONE';
--Połącz z docelową bazą danych
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % i dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Tworzenie zrzutu pg_stat_statements dla bazy danych %',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 'Tworzenie zrzutu pg_stat_statements dla zapytań z min_time większym niż 1000ms';
FOR pg_stat_snapshot IN
--Wszystkie zapytania z max_time większym niż 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;W rezultacie, po pewnym okresie czasu w tabeli pg_stat_history będziemy mieli zestaw zrzutów zawartości tabeli pg_stat_statements docelowej bazy danych.
Faktyczne raportowanie
Używając prostych zapytań, można uzyskać całkiem przydatne i interesujące raporty.
Zaggregowane dane za podany okres czasu
Zapytanie
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 ;Czas DB
to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Czas 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 według total_time
Zapytanie
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 WEDŁUG CAŁKOWITEGO CZASU WYKONANIA | #| queryid| calls| 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 według całkowitego czasu I/O
Zapytanie
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 WEDŁUG CAŁKOWYTEGO CZASU I/O | #| queryid| wywołania| % wywołań| czas I/O (ms)|% czasu I/O bazy danych +----+-----------+-----------+-----------+--------------------------------+------------- | 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 według maksymalnego czasu wykonania
Zapytanie
WYBIERZ
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
max_time
Z
pg_stat_history
GDZIE
queryid NIE JEST NULL I
database_id = DATABASE_ID I
snapshot_timestamp POMIĘDZY BEGIN_TIMEPOINT A END_TIMEPOINT
ZAMÓW WEDŁUG 4 DESC
LIMIT 10----------------------------------------------------------------------------------------- | TOP10 SQL WEDŁUG MAKSYMALNEGO CZASU WYKONANIA | #| 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 według odczytu/zapisu bufora SHARED
Zapytanie
WYBIERZ
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
Z
pg_stat_history
GDZIE
queryid NIE JEST NULL I
database_id = DATABASE_ID I
snapshot_timestamp POMIĘDZY BEGIN_TIMEPOINT A END_TIMEPOINT I
( shared_blks_read > 0 LUB shared_blks_written > 0 )
ZAMÓW WEDŁUG 4 DESC , 5 DESC
LIMIT 10-------------------------------------------------------------------------------------------- | TOP10 SQL ZA ODCZYT/ZAPIS BUFRA WDZIELONEGO | #| zrzut| snapshotID| queryid| odczytane bloki współdzielone| zapisane bloki współdzielone +----+------------------+-----------+-----------+---------------------+--------------------- | 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 --------------------------------------------------------------------------------------------
Histogram rozkładu zapytań według maksymalnego czasu wykonania
Zapytania
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 ) ;
|----------------------------------------------------------------------------------------------- | HISTOGRAM CZASU MAX_TIME | ŁĄCZNA LICZBA ZDAŃ : 33851920 | MIN CZAS : 00:00:01.063 | MAKS CZAS : 00:02:01.869 --------------------------------------------------------------------------------- | minimalny czas trwania| maksymalny czas trwania| liczba zdań +----------------------------------+----------------------------------+---------- | 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 Zrzutów według Zapytania na Sekundę
Zapytania
--pg_qps.sql
--Oblicz zapytania na sekundę
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 'BŁĄD - Nie znaleziono pg_stat_history dla 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 Zrzutów zamówionych według liczby QPS ----------------------------------------------------------------------------------------------------------------------------------------------- | #| zrzut| ID zrzutu| wywołania| całkowity czas db| QPS| Czas I/O| % Czasu I/O +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 2| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 3| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 4| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 5| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 6| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 7| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 8| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 9| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 10| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666
Godzinowa historia wykonania z QPS i czasem I/O
Zapytanie
SELECT
id ,
timestamp_zrzutu ,
wywołania ,
całkowity_czas ,
( select pg_qps( id )) AS QPS ,
czas_czytania_blk ,
czas_zapisu_blk
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
timestamp_zrzutu BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HISTORIA WYKONANIA CO GODZINĘ Z QueryPerSeconds i Czasem I/O ----------------------------------------------------------------------------------------------------------------------------------------------- | HISTORIA ZAPYTANIA NA SEKUNDĘ | #| zrzut| snapshotID| wywołania| całkowity czas db| QPS| czas I/O| % czasu 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
Tekst wszystkich zapytań SQL
Zapytanie
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
Podsumowanie
Jak widać, stosunkowo prostymi środkami można uzyskać stosunkowo dużo użytecznych informacji o obciążeniu i stanie bazy.
Uwaga:Jeśli w zapytaniach zarejestrujemy queryid, otrzymamy historię dla poszczególnych zapytań (w celu zaoszczędzenia miejsca, raporty dla osobnych zapytań zostały pominięte).
Tak więc, dane statystyczne dotyczące wydajności zapytań są dostępne i zbierane.
Pierwszy etap "zbierania danych statystycznych" — zakończony.
Można przejść do drugiego etapu - "konfiguracja metryk wydajności".
Ale to już zupełnie inna historia.
Ciąg dalszy nastąpi…
Źródło: habr.com
