Innealtóir - aistrithe ón Laidin - spreag.
Is féidir le hinnealtóir rud ar bith a dhéanamh. (c) R. Díosal.
Epigraphs.
Nó scéal faoin bhfáth ar gá do riarthóir bunachar sonraí cuimhneamh ar an am atá caite a bhí aige le cláir.
Réamhrá
Athraíodh gach ainm. Tá cluichí randamach. Is é tuairim phearsanta an údair amháin an t-ábhar.
Barántas a shéanadh: sa tsraith alt atá beartaithe ní bheidh aon chur síos mionsonraithe cruinn ar na táblaí agus na scripteanna a úsáidtear. Ní féidir ábhair a úsáid láithreach "MAR ATÁ".
Gcéad dul síos, mar gheall ar an méid mór ábhar,
sa dara háit, mar gheall ar an géire le bonn táirgeachta fíorchustaiméir.
Mar sin, ní thabharfar ach smaointe agus tuairiscí san fhoirm is ginearálta sna hailt.
B'fhéidir sa todhchaí go dtiocfaidh méadú ar an gcóras go dtí an leibhéal postála ar GitHub, nó b'fhéidir nach mbeidh. Taispeánfaidh am.
Tús an scéil -
Cad a tharla mar thoradh air sin, sna téarmaí is ginearálta - "
Cén fáth a dteastaíonn seo go léir uaim?
Bhuel, ar dtús, ionas nach ndéanfaidh tú dearmad ort féin, ag cuimhneamh ar na laethanta glórmhara ar scor.
Ar an dara dul síos, an méid a scríobhadh a chórasú. Maidir liom féin cheana féin, uaireanta tosaíonn mé ag mearbhall agus déanann mé dearmad ar chodanna ar leithligh.
Bhuel, agus is tábhachtaí - go tobann is féidir leis teacht i handy do dhuine agus cabhrú gan a reinvent an roth agus gan a bhailiú raca. I bhfocail eile, feabhas a chur ar do karma (ní Khabrovsky). Is é an rud is luachmhaire sa saol seo ná smaointe. Is é an rud is mó ná smaoineamh a aimsiú. Agus is ceist theicniúil amháin í an smaoineamh a chur i gcrích cheana féin.
Mar sin déanaimis tosú go mall ...
Foirmiú na faidhbe.
Ar fáil:
PostgreSQL(10.5), ualach measctha (OLTP+DSS), ualach meánach go solas, arna óstáil sa scamall AWS.
Níl aon mhonatóireacht ar bhunachar sonraí, cuirtear monatóireacht bonneagair i láthair mar uirlisí caighdeánacha AWS i gcumraíocht íosta.
Riachtanach:
Monatóireacht a dhéanamh ar fheidhmíocht agus ar stádas an bhunachair shonraí, aimsigh agus bíodh faisnéis tosaigh agat chun ceisteanna troma bunachar sonraí a bharrfheabhsú.
Réamhrá gearr nó anailís ar réitigh
Chun tús a chur leis, déanaimis iarracht anailís a dhéanamh ar na roghanna chun an fhadhb a réiteach ó thaobh anailís chomparáideach ar na buntáistí agus na trioblóidí don innealtóir, agus lig dóibh siúd atá ceaptha a bheith ar an liosta foirne déileáil leis na buntáistí agus na caillteanais. na bainistíochta.
Rogha 1 - "Oibriú ar éileamh"
Fágann muid gach rud mar atá sé. Mura bhfuil an custaiméir sásta le rud éigin i sláinte, feidhmíocht an bhunachair shonraí nó an iarratais, cuirfidh sé in iúl do na hinnealtóirí DBA trí r-phost nó trí eachtra a chruthú sa bhosca ticéad.
Tuigfidh innealtóir, tar éis fógra a fháil, an fhadhb, tairgfidh sé réiteach, nó cuirfidh sé an fhadhb ar leataobh, ag súil go réiteoidh gach rud é féin, agus mar sin féin, déanfar dearmad ar gach rud go luath.
Sinséar agus donuts, bruises agus bumpsArán Sinséir agus donuts:
1. Níl aon rud breise le déanamh
2. Bíonn deis ann i gcónaí éirí amach agus dul salach.
3. Go leor ama is féidir leat a chaitheamh i do chuid féin.
Bruises agus bumps:
1. Luath nó mall, beidh an custaiméir smaoineamh ar an croílár a bheith agus ceartas uilíoch sa saol seo agus arís eile a chur air féin an cheist - cén fáth a bhfuil mé ag íoc leo mo chuid airgid? Is é an iarmhairt i gcónaí mar an gcéanna - is í an cheist ach amháin nuair a éiríonn an custaiméir leamh agus waved beannacht. Agus tá an friothálacha folamh. Tá sé brónach.
2. Tá forbairt innealtóra nialas.
3. Deacrachtaí maidir le sceidealú oibre agus luchtú
Rogha 2 - “Damhsa le tambóirín, cuir ort agus cuir ort bróga”
Alt 1-Cén fáth a bhfuil gá againn le córas monatóireachta, gheobhaidh muid gach iarratas. Cuirimid gach cineál fiosrúchán chuig an bhfoclóir sonraí agus tuairimí dinimiciúla, cuirimid gach cineál áiritheoirí ar siúl, cuirimid gach rud isteach i dtáblaí, déanaimid anailís go tréimhsiúil ar liostaí agus táblaí, mar a bhí. Mar thoradh air sin, ní mór dúinn álainn nó nach bhfuil an-graif, táblaí, tuarascálacha. An rud is mó - go mbeadh níos mó, níos mó.
Alt 2-Gin gníomhaíocht-rith an anailís ar seo go léir.
Alt 3-Táimid ag ullmhú doiciméad áirithe, tugaimid an doiciméad seo, go simplí - "conas is féidir linn a threalmhú an mbunachar sonraí."
Alt 4- Tá muinín naive childish ag an gcustaiméir, ag féachaint ar an magnificance seo go léir de ghraif agus figiúirí - anois beidh gach rud ag obair dúinn, go luath. Agus, go héasca agus painless páirteach lena n-acmhainní airgeadais. Tá an bhainistíocht cinnte freisin go bhfuil ár n-innealtóirí ag obair go dian. Uaslódáil.
Alt 5- Déan céim 1 go rialta.
Sinséar agus donuts, bruises agus bumpsArán Sinséir agus donuts:
1. Tá saol na mbainisteoirí agus na n-innealtóirí simplí, intuartha agus líonta le gníomhaíocht. Tá gach rud buzzing, tá gach duine gnóthach.
2. Níl saol an chustaiméara dona freisin - tá sé cinnte i gcónaí go gcaithfidh tú a bheith foighneach beagán agus oibreoidh gach rud amach. Gan éirí níos fearr, go maith, go maith - tá an saol seo éagórach, sa chéad saol eile - ádh.
Bruises agus bumps:
1. Luath nó mall, beidh soláthraí níos cliste ar sheirbhís den chineál céanna a dhéanfaidh an rud céanna, ach beagán níos saoire. Agus má tá an toradh mar an gcéanna, cén fáth a íoc níos mó. A bheidh mar thoradh arís ar an cealú an friothálacha.
2. Tá sé leadránach. Cé chomh leadránach aon ghníomhaíocht beag brí.
3. Mar atá sa leagan roimhe seo - gan aon fhorbairt. Ach d'innealtóir, is é an lúide, murab ionann agus an chéad rogha, anseo ní mór duit IDB a ghiniúint i gcónaí. Agus tógann sé sin am. Cé acu is féidir a chaitheamh ar mhaithe le do grá amháin. Toisc nach féidir leat aire a thabhairt duit féin, tá cúram ar gach duine fút.
Rogha 3-Ní gá rothar a chumadh, ní mór duit é a cheannach agus a thiomána.
Innealtóirí ó chuideachtaí eile a ithe go feasach pizza le beoir (OH, amanna glórmhar St Petersburg sna 90s). Bainimis úsáid as córais mhonatóireachta a dhéantar, a dhífhabhtaítear agus a oibríonn, agus go ginearálta, tugann siad buntáistí (go maith, dá gcuid cruthaitheoirí ar a laghad).
Sinséar agus donuts, bruises agus bumpsArán Sinséir agus donuts:
1. Ní gá am a chur amú ag cumadh cad atá invented cheana féin. Tóg agus úsáid.
2. Níl córais mhonatóireachta scríofa ag amaidí, agus ar ndóigh tá siad úsáideach.
3. De ghnáth soláthraíonn córais mhonatóireachta oibre faisnéis úsáideach scagtha.
Bruises agus bumps:
1. Ní innealtóir é an t-innealtóir sa chás seo, ach úsáideoir táirge duine éigin eile nó úsáideoir.
2. Ní mór don chustaiméir a bheith cinnte go bhfuil gá le rud éigin a cheannach nach bhfuil sé ag iarraidh a thuiscint go ginearálta, agus níor cheart dó, agus go ginearálta tá buiséad na bliana ceadaithe agus ní athróidh sé. Ansin ní mór duit acmhainn ar leith a leithdháileadh, é a chumrú le haghaidh córas ar leith. Iad siúd. Ar dtús caithfidh tú íoc, íoc agus íoc arís. Agus tá an custaiméir stingy. Is é seo norm an tsaoil seo.
Cad atá le déanamh, Chernyshevsky? Tá do cheist an-ábhartha. (Le)
Sa chás áirithe seo agus an staid reatha, is féidir leat a dhéanamh beagán difriúil - déanaimis ár gcóras monatóireachta féin.
Bhuel, ní córas, ar ndóigh, i gciall iomlán an fhocail, tá sé seo ró-ard agus toimhdeach, ach ar a laghad é a dhéanamh níos éasca duit féin agus tuilleadh eolais a bhailiú chun teagmhais feidhmíochta a réiteach. Chun nach mbeidh tú féin i gcás - "téigh ann, níl a fhios agam cá háit, faigh sin, níl a fhios agam cad é."
Cad iad na buntáistí agus na míbhuntáistí a bhaineann leis an rogha seo:
Son:
1. Tá sé suimiúil. Bhuel, ar a laghad níos suimiúla ná an tairiseach "Laghdaigh datafile, athraigh spás tábla, etc."
2. Is scileanna nua agus forbairt nua iad seo. Cé acu sa todhchaí luath nó mall a thabhairt tuillte go maith Sinséir agus donuts.
CONS:
1. A bheith ag obair. Oibrigh go leor.
2. Beidh ort brí agus peirspictíochtaí na gníomhaíochta go léir a mhíniú go rialta.
3. Beidh rud éigin a bheith íobairt, toisc go bhfuil an acmhainn amháin atá ar fáil don innealtóir - am - teoranta ag na Cruinne.
4. An ceann is measa agus is míthaitneamhach - mar thoradh air sin, d'fhéadfadh go dtiocfadh truflais cosúil le "Ní luch, ní frog, ach ainmhí beag anaithnid".
An té nach bhfuil i mbaol ní ólann sé Champagne.
Mar sin, tosaíonn an spraoi.
Smaoineamh ginearálta - scéimreach
(Léaráid a tógadh ón alt «
Míniú:
- Tá an bunachar sonraí sprice suiteáilte leis an síneadh caighdeánach PostgreSQL “pg_stat_statements”.
- Sa bhunachar sonraí monatóireachta, cruthaímid sraith táblaí seirbhíse chun an stair pg_stat_statements a stóráil ag an gcéim tosaigh agus chun méadracht agus monatóireacht a chumrú sa todhchaí
- Ar an óstaigh monatóireachta, cruthaímid sraith scripteanna bash, lena n-áirítear iad siúd chun teagmhais a ghiniúint sa chóras ticéad.
Táblaí seirbhíse
Ar dtús, ERD atá simplithe go scéimreach, cad a tharla sa deireadh:
Cur síos gairid ar na táblaícríochphointe - óstach, pointe nasctha leis an ásc
bunachar - roghanna bunachar sonraí
pg_stat_stair - tábla stairiúil chun pictiúir shealadacha a stóráil den amharc pg_stat_statements den spriocbhunachar sonraí
méadrach_gluais - Foclóir méadrachta feidhmíochta
méadrach_config - cumraíocht na méadrachta aonair
méadrach - méadracht shonrach don iarratas a bhfuil faireachán á dhéanamh uirthi
méadrach_airde_stair - stair na rabhaidh feidhmíochta
log_cheist - tábla seirbhíse chun taifid pharsáilte a stóráil ó logchomhad PostgreSQL íoslódáilte ó AWS
bunlíne - paraiméadair na tréimhse ama a úsáidtear mar bhonn
seicphointe - cumraíocht na méadrachta chun stádas an bhunachair shonraí a sheiceáil
checkpoint_alert_stair - stair rabhaidh na méadrachta seiceála stádais bunachar sonraí
pg_stat_db_ceisteanna — tábla na n-iarratas gníomhach a sheirbheáil
loga gníomhaíochta — tábla seirbhíse loga gníomhaíochta
trap_oid - tábla seirbhíse cumraíochta gaiste
Céim 1 - staitisticí feidhmíochta a bhailiú agus tuarascálacha a fháil
Úsáidtear tábla chun faisnéis staidrimh a stóráil. pg_stat_stair
struchtúr tábla pg_stat_history
Tábla "public.pg_stat_history" Colún | cineál | Mionathraitheoirí-------------------+----------------------+---- ------------------------------ id | slánuimhir | ní null réamhshocrú nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | stampa ama gan crios ama | bunachar sonraí_id | slánuimhir | dbid | oid | úsáideora | oid | ceisteach | mór | ceist | téacs | glaonna | mór | iomlán_am | cruinneas dúbailte | nóim_am | cruinneas dúbailte | uas_am | cruinneas dúbailte | meán_am | cruinneas dúbailte | stddev_am | cruinneas dúbailte | sraitheanna | mór | roinnte_blks_buail | mór | roinnte_blks_léamh | mór | roinnte_blks_dirtied | mór | roinnte_blks_scríofa | mór | logánta_blks_buail | mór | logánta_blks_léamh | mór | logánta_blks_salach | mór | logánta_blks_scríofa | mór | temp_blks_léamh | mór | temp_blks_scríofa | mór | blk_léamh_am | cruinneas dúbailte | blk_write_am | cruinneas dúbailte | bunlíne_id | slánuimhir | Innéacsanna: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Srianta eochrach-eochair: "bunachar sonraí_fkFER_idx" bunachar sonraí FORENCE (bunachar sonraí_fKFER) ) AR CHAISCÉAL A SCRÍOBH
Mar a fheiceann tú, níl sa tábla ach sonraí amhairc carnach pg_stat_ráitis sa bhunachar sonraí sprice.
Tá úsáid an tábla seo an-simplí.
pg_stat_stair léireoidh sé an staidreamh carntha ar fheidhmiú fiosrúchán in aghaidh gach uaire. Ag tús gach uair an chloig, tar éis an tábla a líonadh, staitisticí pg_stat_ráitis athshocrú le pg_stat_ráitis_athshocrú().
Tabhair faoi deara: Bailítear staitisticí d’iarratais a mhairfidh níos mó ná 1 soicind.
Ag daonra an tábla 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;
Mar thoradh air sin, tar éis tréimhse áirithe ama sa tábla pg_stat_stair beidh sraith pictiúir againn d'ábhar an tábla pg_stat_ráitis bunachar sonraí sprice.
I ndáiríre tuairisciú
Ag baint úsáide as fiosrúcháin shimplí, is féidir leat tuairiscí úsáideacha agus suimiúla a fháil.
Sonraí comhiomlánaithe ar feadh tréimhse áirithe ama
Iarratas
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 ;
Am D.B
to_char(eatramh '1 milleasoicind' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Am I/O
to_char(eatramh '1 milleasoicind' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL de réir total_time
Iarratas
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 DE RÉIR AM FHEIDHMIÚ IOMLÁN | #| ceisteach| glaonna| glaonna %| iomlán_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( 3.44 ms.) | 4 | 655729273| 1| 00000| .00|02:01.869:121869.981( 3.25 ms.) | 5 | 2460318461| 1| 00000| .00|01:33.113:93113.835( 2.48 ms.) | 6. 2194493487 | 4| 00001| 00| .00|17.377:17377.868:46( 7 ms.) | .1053044345 | 1| 00000| 00| .00|06.156:6156.352:16( 8 ms.) | .3644780286 | 1| 00000| 00| .00|01.063:1063.830:03( XNUMX ms.) | .XNUMX
TOP10 SQL de réir am iomlán I/O
Iarratas
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 DE RÉIR AM IOMLÁN I/O | #| ceisteach| glaonna| glaonna %| Am I/O (ms)|db am I/O % +----+----------+---------+----- -----+------------------------------+---------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 Meitheamh | 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( 1.01 ms.) | 8. 3644780286 | 1| 00000| 00| .00|00.436:436.205:03( XNUMX ms.) | .XNUMX
TOP10 SQL faoin am forghníomhaithe uasta
Iarratas
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 DE RÉIR MAX AM A FHEIDHMIÚ | #| pictiúr | snapshotID| ceisteach| max_time (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.)
Léigh/scríobh TOP10 SQL de réir maolán ROINNTE
Iarratas
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 TRÍ Maolán Roinnte LÉIGH/SCRÍOBH | #| pictiúr | snapshotID| ceisteach| bloic roinnte léite| scríobh bloic roinnte +----+-----------------+----------+--------- -+--------------------+-------------------- 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 ---------------------------------------------- -----------------------------------------------
Histeagram de dháileadh fiosrúchán de réir uas-ama rite
Iarratais
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 | Glaonna IOMLÁN: 33851920 | MIN TIME: 00:00:01.063 | MAX TIME: 00:02:01.869 -------------------------------- ------- --------------------------- | min ré| uasfhad| glaonna +-------------------------------+------------ --------------------+--------- | 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 Seatanna de réir Iarratas sa Soicind
Iarratais
--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
|--------------------------------------------- -------------------------------------- | Léimeanna TOP10 ordaithe de réir uimhreacha QueryPerSeconds ----------------------------------- ------ ----------------------------------------- ------ ---------------------------------------- | #| pictiúr | snapshotID| glaonna | dbtime iomlán| QPS | Am I/O | Am I/O % +-----+-----------------+----------+------ ----+--------------------------------+--------- -+---------------------------------+----------- 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:14( 00 ms.)| 4137| 2397326:00:04( 43.033 ms.)| .283033.854 | 665.924| 00/00/00.024 24.505:009| 7| 04.04.2019| 15:00:4139( 2394416 ms.)| 00| 04:51.435:291435.010( 665.116 ms.)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04 ms.)| 26.791. 266791.988| 659.179:00:00( 00.064 ms.)| 64.261. 024 | 9| 05.04.2019/01/03 4167:4387191 | 00| 06| 51.380:411380.293:609.332( 00 ms.)| 05. 18.847| 318847.407:77.507:10( 04.04.2019 ms.)| .18 | 01| 4157/1145596/00 01:19.217| 79217.372| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX. XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX. XNUMX
Stair Forghníomhaithe in aghaidh na huaire le QueryPerSeconds agus Am I/O
Iarratas
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
Roghnaíonn téacs gach SQL
Iarratas
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
Iomlán na
Mar a fheiceann tú, trí bhealaí simplí go leor, is féidir leat a lán eolais úsáideach a fháil faoin ualach oibre agus faoi staid an bhunachair sonraí.
Nóta:Má shocraíonn tú an queryid sna fiosrúcháin, gheobhaidh muid an stair le haghaidh iarratas ar leith (chun spás a shábháil, fágtar tuarascálacha ar iarratas ar leith ar lár).
Mar sin, tá sonraí staidrimh ar fheidhmíocht fiosrúchán ar fáil agus bailítear iad.
Críochnaítear an chéad chéim "bailiú sonraí staidrimh".
Is féidir leat dul ar aghaidh go dtí an dara céim - "méadracht feidhmíochta a chumrú".
Ach scéal iomlán difriúil é sin.
Le leanúint ...
Foinse: will.com