Inisinia - faaliliuina mai le Latina - musuia.
E mafai e se inisinia ona faia soo se mea. (c) R. Diesel.
Epigraphs.
Po'o se tala e uiga i le mafua'aga e mana'omia ai e le pule fa'amaumauga ona manatua lana polokalame ua tuana'i.
Upu Tomua
Ua suia uma igoa. O fa'alavelave fa'afuase'i. O mea o loʻo faʻatusalia naʻo le manatu patino o le tusitala.
Fa'ate'aina o fa'amaoniga: O le faasologa fuafuaina o tala o le a le aofia ai se faʻamatalaga auiliili ma saʻo o laulau ma faʻamatalaga na faʻaaogaina. E le mafai ona fa'aogaina vave mea "AS IS".
Muamua, ona o le tele o meafaitino,
lona lua, ona o le sootaga vavalalata ma le gaosiga faavae o se tagata faatau moni.
O le mea lea, o tala o le a aofia ai na o manatu ma faʻamatalaga i le tulaga sili ona lautele.
Masalo i le lumanaʻi o le a tupu aʻe le faiga i le tulaga o le lafoina i luga o GitHub, pe leai. O le a faʻaalia le taimi.
Le amataga o le tala-"".
O le a le mea na tupu o se taunuuga, i le tele o faaupuga lautele - "»
Aisea ua ou manaomia ai nei mea uma?
Ia, muamua, ina ia aua nei galo, manatua aso mamalu i le litaea.
Lona lua, ia fa'atulagaina mea na tusia. Aua o nisi taimi e amata ona ou le mautonu ma galo ai nisi vaega.
Ia, ma o le mea pito sili ona taua o le mafai lea ona aoga mo se tasi ma fesoasoani ia i latou e aloese mai le toe faʻafouina o le uili ae le o le aoina o se rake. I se isi faaupuga, faʻaleleia lau karma (e le o Khabrov's). Aua o le mea e sili ona taua i lenei lalolagi o manatu. O le mea autu o le sailia o se manatu. Ae o le liliuina o se manatu i le mea moni o se fesili faʻapitoa faʻapitoa.
O lea la, tatou amata, sina mea itiiti ...
Fa'atulagaina o le fa'afitauli.
Avanoa:
PostgreSQL(10.5) fa'amaumauga, ituaiga uta fefiloi (OLTP + DSS), uta mama-mama, o lo'o i totonu o le ao AWS.
E leai se mata'ituina o fa'amaumauga; o lo'o tu'uina atu le mata'ituina o atina'e i le tulaga o meafaigaluega AWS masani i se fa'atulagaga la'ititi.
E manaʻomia:
Mataʻituina le faʻatinoga ma le tulaga o faʻamaumauga, saili ma maua faʻamatalaga muamua mo le faʻamalieina o fesili mamafa faʻamaumauga.
Fa'atomuaga pu'upu'u po'o le au'ili'iliga o filifiliga fofo
Muamua, sei o tatou taumafai e iloilo filifiliga mo le foia o le faafitauli mai le vaaiga o se iloiloga faatusatusa o faamanuiaga ma le le lelei mo le inisinia, ma ia tuu atu ia i latou e agavaa i ai e tusa ai ma le faasologa o le aufaigaluega e feagai ma faamanuiaga ma gau o le pulega.
Filifiliga 1 - "Galue pe a mana'omia"
Matou te tuua mea uma e pei ona i ai. Afai e le faamalieina le tagata faʻatau i se mea i le faʻatinoga, faʻatinoga o le database poʻo le talosaga, o le a ia logoina le inisinia DBA e ala i le imeli poʻo le fatuina o se faʻalavelave i le fata pepa.
O le inisinia, ina ua maua le faʻamatalaga, o le a malamalama i le faʻafitauli, ofoina atu se fofo poʻo le tuʻuina o le faʻafitauli i le pito i tua, ma le faʻamoemoe o mea uma o le a foia e ia lava, ma o le a le pine ae galo mea uma.
Falaoa Sina ma donut, manu'a ma patupatuGingerbread ma donuts:
1. E leai se mana'oga e fai se mea fa'aopoopo.
2. E i ai lava le avanoa e fai ai 'alofaga ma fa'afefe.
3. Le tele o le taimi e mafai ona e faʻaaluina i lau lava filifiliga.
Manu’a ma patupatu:
1. I se taimi mulimuli ane, o le a mafaufau le tagata faʻatau e uiga i le ute o le ola ma le faʻamasinoga lautele i lenei lalolagi ma toe fesili ifo ia te ia lava le fesili - aisea ua ou totogiina ai aʻu tupe? O le taunuuga e tutusa lava - pau le fesili o le a le fiafia le tagata faatau ma talotalo faatofa. Ma o le a gaogao le fafaga. E faanoanoa.
2. Atina'e inisinia - zero.
3. Faigata i le fuafuaina o galuega ma uta
Filifiliga 2- “Siva ma topa, ausa ma ofuina seevae”
Palakalafa 1-Aisea tatou te manaʻomia ai se faiga mataʻituina, o le a tatou mauaina mea uma ma talosaga. Matou te fa'atautaia le tele o ituaiga fesili uma i le lomifefiloi fa'amaumauga ma fa'amatalaga mata'ina, fa'aola so'o se fa'atau, tu'u mea uma i luga o laulau, ma su'esu'e i lea taimi ma lea taimi lisi ma laulau. O se taunuuga, e iai a matou kalafi matagofie pe le matagofie, laulau, lipoti. O le mea autu o le maua atili, sili atu.
Palakalafa 2-Matou te faʻatupuina gaioiga ma faʻalauiloa le auiliiliga o nei mea uma.
Palakalafa 3-O loʻo matou saunia se pepa faʻapitoa, matou te taʻua lenei pepa faigofie - "faʻafefea ona matou setiina se faʻamaumauga."
Palakalafa 4-O le tagata faʻatau, vaʻai i nei matagofie uma o kalafi ma numera, o loʻo i totonu o se tamaititi, faʻatuatuaina le mautonu - o le taimi nei o le a aoga mea uma mo i tatou, lata mai. Ma, e faigofie ma leai se tiga na te tuʻuina atu ana punaoa tau tupe. E talitonu foi le pulega e lelei le galuega a tatou inisinia. utaina i le maualuga.
Palakalafa 5- Toe fai le Laasaga 1 e le aunoa.
Falaoa Sina ma donut, manu'a ma patupatuGingerbread ma donuts:
1. O le olaga o pule ma inisinia e faigofie, vaʻaia ma faʻatumuina i gaioiga. Ua pisapisao mea uma, ua pisi tagata uma.
2. E le leaga foi le olaga o le tagata faʻatau - na te mautinoa i taimi uma naʻo le manaʻomia o le onosaʻi ma o le a lelei mea uma. E le o lelei, lelei, e le fetaui le lalolagi, i le isi olaga e te laki.
Manu’a ma patupatu:
1. Ise taimi mulimuli ane, o le a i ai se tagata e tuʻuina atu vave se auaunaga faʻapea o le a faia le mea lava e tasi, ae e sili atu le taugofie. Ae afai e tutusa le iʻuga, aisea e te totogia atili ai. Lea o le a toe taitai atu ai i le mou atu o le fafaga.
2. E le manaia. E ese le manaia o soo se gaoioiga leai se uiga.
3. E pei o le lomiga muamua, e leai se atinae. Ae mo se inisinia, o le pito i lalo o le, e le pei o le filifiliga muamua, e tatau ona e faʻatupuina pea se IBD. Ma o lenei mea e umi se taimi. Le mea e mafai ona e faʻaaluina mo le manuia o lau pele. Talu ai e le mafai ona e tausia oe lava, e leai se tasi na te faʻaleagaina oe.
Filifiliga 3 - E te le manaʻomia le fatuina o se uila, naʻo lou manaʻomia e faʻatau ma tietie ai.
E le o se mea e 'ai e inisinia mai isi kamupani pizza ma pia (oi, o aso mamalu o St. Petersburg i le 90s). Se'i o tatou fa'aogaina faiga e mata'ituina ai ua faia, fa'asalaina ma galue, ma fa'amanino fa'amanuiaga (lelei, a itiiti ifo i latou foafoa).
Falaoa Sina ma donut, manu'a ma patupatuGingerbread ma donuts:
1. E leai se mea e tatau ona faamaimaua le taimi e sau ai i se mea ua uma ona fai. Ave ma faaaoga.
2. O faiga mata'itū e le'o tusia e tagata valea ma e moni, e aoga.
3. O faiga mata'ituina galuega e masani ona maua ai fa'amatalaga fa'amama aoga.
Manu’a ma patupatu:
1. O le inisinia i lenei tulaga e le o se inisinia, ae na o se tagata e faʻaaogaina oloa a se isi tagata poʻo se tagata faʻaoga.
2. E tatau i le tagata fa'atau ona fa'amaonia le mana'oga e fa'atau se mea, e masani lava, na te le fia malamalama, ma e le tatau, ma i se tulaga lautele o le paketi mo le tausaga ua fa'amaonia ma o le a le suia. Ona e manaʻomia lea e tuʻufaʻatasia se isi punaoa ma faʻapipiʻi mo se faiga faʻapitoa. O na. muamua e tatau ona e totogi, totogi ma toe totogi. Ma e pipii le tagata faatau. O le masani lea o lenei olaga.
O le a le mea e fai - Chernyshevsky? E taua tele lau fesili. (Ma)
I lenei tulaga faʻapitoa ma le tulaga o loʻo i ai nei, e mafai ona e faia i se tulaga ese - sei fai a tatou lava faiga e mataituina ai.
Ia, e le o se faiga, ioe, i le uiga atoa o le upu, e leo tele ma faʻamaualuga, ae o le mea sili e faafaigofie ai lau galuega ma aoina atili faʻamatalaga e foia ai faʻalavelave faʻatinoga. Ina ia aua nei e maua oe i se tulaga - "alu iina, ou te le iloa po o fea, saili se mea, ou te le iloa po o le a."
O le a le lelei ma le le lelei o lenei filifiliga:
Pros:
1. E manaia lenei. Ia, o le mea sili e sili atu le manaia nai lo le "faʻaitiitia faʻamaumauga, sui laulau, ma isi."
2. O tomai fou ia ma atina'e fou. O le a, i se taimi mulimuli ane, o le a avatu ai ia te oe le falaoamata ma le donuts.
Cons:
1. E tatau ona e galue. Galue malosi.
2. E tatau ona e fa'amatalaina e le aunoa le uiga ma fa'amoemoega o gaioiga uma.
3. O se mea e tatau ona ositaulagaina, aua e na o le pau lava le punaoa o loʻo avanoa mo se inisinia - taimi - faʻatapulaʻaina e le Atulaulau.
4. Le mea sili ona leaga ma sili ona le fiafia - o le i'uga atonu o le taufaaleaga pei o le "E le o se isumu, e le o se rane, ae o se manu e le iloa."
O i latou e le faia ni tulaga lamatia e le inu vaiinu.
O lea - ua amata le fiafia.
Manatu lautele - schematically
(Faʻataʻitaʻiga na maua mai le tusiga «")
Faamatalaga:
- O le faʻaopoopoga PostgreSQL masani "pg_stat_statements" o loʻo faʻapipiʻiina i totonu o faʻamaumauga autu.
- I totonu o faʻamaumauga mataʻituina, matou te fatuina se seti o laulau tautua mo le teuina o le pg_stat_statements talafaasolopito i le tulaga muamua ma mo le faʻatulagaina o metric ma mataʻituina i le lumanaʻi
- I luga o le talimalo mataʻituina, matou te fatuina se seti o tusitusiga o le bash, e aofia ai mo le faʻatupuina o faʻalavelave i le faiga tiketi.
Lau'au tautua
Muamua, o se schematic faafaigofieina ERD, o le a le mea na tupu i le faaiuga:
Fa'amatalaga puupuu o laulaupito i luga — talimalo, tulaga o le sootaga i le faataitaiga
utuofaamatalaga - fa'amaumauga fa'amaumauga
pg_stat_history - se laulau fa'asolopito mo le teuina o ata le tumau o le pg_stat_statements va'aiga o le fa'amaumauga autu
metric_glossary - lomifefiloi o fua fa'atinoga
metric_config - fa'atulagaina o fuataga ta'itasi
nati - se fua fa'atatau mo le talosaga o lo'o mata'ituina
metric_alert_history - tala fa'asolopito o lapataiga fa'atinoga
log_query - se laulau tautua mo le teuina o faʻamaumauga faʻasalalau mai se faila ogalaau PostgreSQL na sii mai le AWS
laina faavae - tapula'a o vaitau taimi fa'aaoga e fai ma fa'avae
siaki siaki - faʻatulagaina o metrics mo le siakiina o le tulaga o le database
checkpoint_alert_history - lapata'iga tala'aga o fa'amaumauga o su'esu'ega fa'alesoifua maloloina
pg_stat_db_queries — laulau tautua o talosaga malosi
activity_log — laulau auaunaga o faamaumauga o gaoioiga
trap_oid — laulau o auaunaga faatulagaina mailei
Laasaga 1 - aoina faʻamatalaga faʻamaumauga e uiga i faʻatinoga ma maua lipoti
E fa'aoga se laulau e teu ai fa'amatalaga fa'afuainumera pg_stat_history
pg_stat_history fausaga laulau
Laulau "public.pg_stat_history" Koluma | Ituaiga | Suia--------+-------------------------------------- - ------------------------------------------ id | numera tasi | e le o null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | faailoga taimi e aunoa ma sone taimi | database_id | numera tasi | dbid | oid | userid | oid | fesili fesili | tele | fesili | tusitusiga | valaau | tele | atoa_taimi | sa'o fa'alua | min_taimi | sa'o fa'alua | max_time | sa'o fa'alua | mean_time | sa'o fa'alua | stddev_time | sa'o fa'alua | laina | tele | shared_blks_hit | tele | shared_blks_read | tele | shared_blks_dirtied | tele | shared_blks_written | tele | local_blks_hit | tele | local_blks_read | tele | local_blks_dirtied | tele | local_blks_written | tele | temp_blks_read | tele | temp_blks_written | tele | blk_read_time | sa'o fa'alua | blk_write_time | sa'o fa'alua | faavae_id | numera tasi | Fa'asino igoa: "pg_stat_history_pkey" KEY UTAMA, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Foreign-key fa'agata: (Yfdatabase_IDRENEI) ) IA TA'E LE TAIMI
E pei ona e vaʻaia, o le laulau e naʻo le faʻasologa o faʻamatalaga faʻamatalaga pg_stat_statements i totonu o fa'amaumauga fa'atatau.
O le faʻaaogaina o lenei laulau e matua faigofie lava
pg_stat_history o le a fa'atusalia fa'amaumauga fa'aputuina o fa'atinoga o fesili mo itula ta'itasi. I le amataga o itula taitasi, pe a uma ona faʻatumu le laulau, fuainumera pg_stat_statements toe setiina ma pg_stat_statements_reset().
Manatua: E aoina fa'amaumauga mo fa'amatalaga ma le umi ole fa'atinoga e sili atu ile 1 sekone.
Fa'atumu le laulau pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( dbid oid , userid oid , queryid bigint ,query text , calls bigint ,
total_time double precision ,min_time double precision ,max_time double precision , mean_time double precision , stddev_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;O se taunuuga, ina ua mavae sina taimi i le laulau pg_stat_history o le a matou maua se seti o ata o mea o loʻo i totonu o le laulau pg_stat_statements fa'amaumauga autu.
Tala moni
I le fa'aaogaina o fesili faigofie, e mafai ona e maua ai ni lipoti aoga ma manaia.
Fa'amaumauga tu'ufa'atasia mo se vaitaimi
Suesuega
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 ;Taimi DB
to_char(vala '1 milisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Taimi I/O
to_char(vala '1 milisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL ile aofaiga_taimi
Suesuega
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 I LE AOFAIGA TAIMI FAATINO | #| queryid| valaau| valaau %| atoa_taimi (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 ile taimi atoa I/O
Suesuega
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 IA TOTAL I/O TAIMI | #| queryid| valaau| valaau %| Taimi I/O (ms)|db Taimi I/O % +----+-----------+-----------+------ -------------------------+--------------------------------+----- ------ -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 ms.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 ms.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 ms.)| .03
TOP10 SQL ile maualuga ole taimi ole fa'atinoga
Suesuega
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 I LE MAX TAIMI FAATINO | #| ata | snapshotID| queryid| maualuga_taimi (ms) +---------------------------------------+--------- ------------------------------------------ | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00(01 ms.) | 36.964| 96964.607/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:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16(00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)
TOP10 SQL e SHARED pate faitau/tusi
Suesuega
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 BY SHARED BUFFER FAITAU/TUSI | #| ata | snapshotID| queryid| fa'asoa poloka faitau| poloka fa'asoa tusi +--------------------------------------------------- ------------------------------------------------ | 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 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX --------------------------------------------------- ------------------------------------------
Histogram o talosaga tufatufaina i le maualuga o le taimi e faatino ai
Talosaga
SELECT
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;
SELECT
SUM(calls) AS calls
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id =DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( max_time >= hist_current_min AND max_time < hist_current_max ) ;
|------------------------------------------------ - --------------------------------------------------- | MAX_TIME HISTOGRAM | TELE AOFI: 33851920 | MIN TAIMI: 00:00:01.063 | TAIMI TOTOGI: 00:02:01.869 ------------------------------------------ -------------------------------------- | min umi| umi umi| valaau +---------------------------------+------------------------ ----------------------+---------- | 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 by Query per Second
Talosaga
--pg_qps.sql
--Calculate Query Per Second
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
pg_stat_history_rec record ;
prev_pg_stat_history_id integer ;
prev_pg_stat_history_rec record;
total_seconds double precision ;
result double precision;
BEGIN
result = 0 ;
SELECT *
INTO pg_stat_history_rec
FROM
pg_stat_history
WHERE id = pg_stat_history_id ;
IF pg_stat_history_rec.snapshot_timestamp IS NULL
THEN
RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
END IF ;
--RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id ,
pg_stat_history_rec.snapshot_timestamp ;
SELECT
MAX(id)
INTO
prev_pg_stat_history_id
FROM
pg_stat_history
WHERE
database_id = pg_stat_history_rec.database_id AND
queryid IS NULL AND
id < pg_stat_history_rec.id ;
IF prev_pg_stat_history_id IS NULL
THEN
RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
RETURN NULL ;
END IF;
SELECT *
INTO prev_pg_stat_history_rec
FROM
pg_stat_history
WHERE id = prev_pg_stat_history_id ;
--RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;
total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
--RAISE NOTICE 'total_seconds = % ', total_seconds ;
--RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;
IF total_seconds > 0
THEN
result = pg_stat_history_rec.calls / total_seconds ;
ELSE
result = 0 ;
END IF;
RETURN result ;
END
$$ LANGUAGE plpgsql;
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( select pg_qps( id )) IS NOT NULL
ORDER BY 5 DESC
LIMIT 10
|------------------------------------------------ - --------------------------------------------------- | TOP10 Snapshots okaina e QueryPerSeconds numera -------------------------------------------- --------------------------------------------------- ------------------------------------------------- | #| ata | snapshotID| valaau| atoa dbtime| QPS| I/O taimi| I/O taimi % +------+--------------------------------+------- ----+----------------------------------+---------- --------------------------+----------------------- | 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 ms.)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03(4163 ms.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755 776.979:00| 00| 01.441| 1441.386:732:6( 04.04.2019 ms.)| 14| 00:4137:2397326( 00 ms.)| .04 | 43.033| 283033.854/665.924/00 00:00.024| 24.505| 009| 7:04.04.2019:15( 00 ms.)| 4139| 2394416:00:04(51.435 ms.)| .291435.010 | 665.116| 00/00/12.025 12025.895:4.126| 8| 04.04.2019| 13:00:4135( 2373043 ms.)| 00| 04:26.791:266791.988( 659.179 ms.)| 00 | 00| 00.064 64.261:024| 9| 05.04.2019| 01:03:4167( 4387191 ms.)| 00| 06:51.380:411380.293( 609.332 ms.)| .00 | 05| 18.847/318847.407/77.507 10:04.04.2019| 18| 01| 4157:1145596:00( 01 ms.)| 19.217| 79217.372:313.004:00( 00 ms.)| 01.319 | 1319.676| 1.666/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX
Talafa'asolopito o Fa'atinoga i Itula ma QueryPerSeconds ma I/O Taimi
Suesuega
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HOURLY EXECUTION HISTORY WITH QueryPerSeconds and I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | QUERY PER SECOND HISTORY | #| snapshot| snapshotID| calls| total dbtime| QPS| I/O time| I/O time % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 11:00| 4131| 3747| 00:00:00.835( 835.374 ms.)| 1.041| 00:00:00.000( .000 ms.)| .000 | 2| 04.04.2019 12:00| 4133| 1002722| 00:01:52.419( 112419.376 ms.)| 278.534| 00:00:00.149( 149.105 ms.)| .133 | 3| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 4| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 5| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 6| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 7| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 8| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666 | 9| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 10| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 11| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 12| 04.04.2019 23:03| 4165| 1443155| 00:01:34.467( 94467.539 ms.)| 200.438| 00:00:00.015( 15.287 ms.)| .016 | 13| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 14| 05.04.2019 02:03| 4171| 189852| 00:00:10.989( 10989.899 ms.)| 52.737| 00:00:00.539( 539.110 ms.)| 4.906 | 15| 05.04.2019 03:01| 4173| 3627| 00:00:00.103( 103.000 ms.)| 1.042| 00:00:00.004( 4.131 ms.)| 4.010 | 16| 05.04.2019 04:00| 4175| 3627| 00:00:00.085( 85.235 ms.)| 1.025| 00:00:00.003( 3.811 ms.)| 4.471 | 17| 05.04.2019 05:00| 4177| 3747| 00:00:00.849( 849.454 ms.)| 1.041| 00:00:00.006( 6.124 ms.)| .721 | 18| 05.04.2019 06:00| 4179| 3747| 00:00:00.849( 849.561 ms.)| 1.041| 00:00:00.000( .051 ms.)| .006 | 19| 05.04.2019 07:00| 4181| 3747| 00:00:00.839( 839.416 ms.)| 1.041| 00:00:00.000( .062 ms.)| .007 | 20| 05.04.2019 08:00| 4183| 3747| 00:00:00.846( 846.382 ms.)| 1.041| 00:00:00.000( .007 ms.)| .001 | 21| 05.04.2019 09:00| 4185| 3747| 00:00:00.855( 855.426 ms.)| 1.041| 00:00:00.000( .065 ms.)| .008 | 22| 05.04.2019 10:00| 4187| 3797| 00:01:40.150( 100150.165 ms.)| 1.055| 00:00:21.845( 21845.217 ms.)| 21.812
Tusitusiga o SQL-filifiliga uma
Suesuega
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
Le iʻuga
E pei ona e vaʻaia, faʻaaogaina auala faigofie, e mafai ona e mauaina le tele o faʻamatalaga aoga e uiga i le mamafa o galuega ma le tulaga o le database.
Fa'aaliga:Afai tatou te faamaumauina le fesili i fesili, o le a tatou maua le tala faasolopito mo se isi fesili (ina ia sefe avanoa, lipoti mo se isi fesili e le o iai).
O lea la, o fa'amaumauga fa'afuainumera i luga o fa'atinoga o fesili e maua ma aoina.
O le laasaga muamua "aoina o faʻamaumauga faʻamaumauga" ua maeʻa.
E mafai ona e agai i luma i le laasaga lona lua - "faʻatulagaina fua faʻatinoga".
Ae o se tala e matua ese lava.
Ia toe faauau…
puna: www.habr.com