Inĝeniero - tradukita el la latina - inspirita.
Inĝeniero povas fari ĉion. (c) R. Diesel.
Epigrafoj.
Aŭ rakonto pri kial datumbazadministranto devus memori sian pasintecon kiel programisto.
Antaŭparolo
Ĉiuj nomoj estas ŝanĝitaj. Koincidoj estas hazardaj. La materialo reprezentas nur la personan opinion de la aŭtoro.
Malgarantio: La planita serio de artikoloj ne enhavos detalan kaj precizan priskribon de la uzitaj tabeloj kaj skriptoj. La materialoj ne estos tuj uzeblaj "KIELE ESTAS".
Unue, pro la granda kvanto da materialo,
due, pro la fokuso sur la produktadbazo de reala kliento.
Tial, la artikoloj nur enhavos ideojn kaj priskribojn en la plej ĝenerala formo.
Eble estonte la sistemo kreskos ĝis la nivelo de afiŝado en GitHub, aŭ eble ne. La tempo montros.
La komenco de la rakonto - "".
Kio okazis rezulte, plej ĝenerale - "»
Kial mi bezonas ĉion ĉi?
Nu, unue, por ke mi ne forgesu, rememorante la glorajn tagojn en emeritiĝo.
Due, sistemigi tion, kio estas skribita. Ĉar kelkfoje mi mem komencas konfuziĝi kaj forgesi individuajn partojn.
Nu, kaj la plej grava afero - eble ĝi utilos al iu kaj helpos ne reinventi la radon kaj ne kolekti rastojn. Alivorte, plibonigi vian karmon (ne tiun de Habr). Ĉar, la plej valora afero en ĉi tiu mondo estas ideoj. La ĉefa afero estas trovi ideon. Kaj realigi la ideon jam estas pure teknika demando.
Do, ni komencu, iom post iom...
Deklaro de la problemo.
Disponebla:
Datumbazo PostgreSQL (10.5), miksita laborkvanto (OLTP+DSS), meze-malalta ŝarĝo, situanta en la AWS-nubo.
Ne estas datumbazmonitorado; infrastrukturmonitorado estas provizita en la formo de normaj AWS-iloj en minimuma konfiguracio.
Bezonata:
Monitori datumbazan rendimenton kaj sanon, trovi kaj havi komencajn informojn por optimumigi pezajn datumbazajn serĉojn.
Mallonga enkonduko aŭ analizo de solvopcioj
Unue, ni provu analizi la eblojn por solvi la problemon el la vidpunkto de kompara analizo de la avantaĝoj kaj malavantaĝoj por la inĝeniero, kaj lasu tiujn, kiuj supozeble traktas la avantaĝojn kaj perdojn de administrado laŭ la dungitarplano, trakti ilin.
Opcio 1 - "Labori laŭpete"
Ni lasas ĉion kiel ĝi estas. Se la kliento ne estas kontenta pri io en la funkcieco, rendimento de la datumbazo aŭ aplikaĵo, li informos la datbaz-administrantajn inĝenierojn per retpoŝto aŭ kreante okazaĵon en la bileto.
La inĝeniero, ricevinte la sciigon, solvos la problemon, proponos solvon aŭ flankenmetos la problemon, esperante, ke ĉio solviĝos per si mem, kaj ĉiuokaze, ĉio baldaŭ estos forgesita.
Spickuko kaj ringkukoj, kontuziĝoj kaj tuberojSpickuko kaj ringbulkoj:
1. Ne necesas fari ion ajn ekstran
2. Ĉiam ekzistas ŝanco trovi senkulpigojn kaj eviti.
3. Sufiĉe da tempo por elspezi laŭplaĉe.
Kontuziĝoj kaj tuberoj:
1. Pli frue aŭ pli malfrue la kliento pripensos la esencon de ekzisto kaj universala justeco en ĉi tiu mondo kaj denove demandos al si la demandon - por kio mi pagas al ili mian monon? La konsekvenco estas ĉiam la sama - la demando estas nur kiam la kliento enuiĝos kaj adiaŭos. Kaj la trogo estos malplena. Tio estas malĝoja.
2. Inĝeniera disvolviĝo estas nula.
3. Malfacilaĵoj en planado de laboro kaj ŝarĝado
Opcio 2 - "Ni dancas per tamburinoj, vendas kaj ŝuas"
Punkto 1-Kial ni bezonas monitoran sistemon? Ni ricevos ĉion per petoj. Mi lanĉas amason da petoj al la datenvortaro kaj dinamikaj vidoj, ŝaltas ĉiaspecajn nombrilojn, reduktas ĉion al tabeloj, periode analizas la listojn kaj tabelojn. Rezulte, ni havas belajn aŭ ne tiom belajn grafikaĵojn, tabelojn, raportojn. La ĉefa afero estas, ke pli, pli.
Punkto 2-Ni generas agadon - ni lanĉas analizon de ĉio ĉi.
Punkto 3-Ni preparas certan dokumenton, ni nomas ĉi tiun dokumenton simple - "kiel starigi datumbazon".
Punkto 4- La kliento, vidante tiun tutan belecon de grafikaĵoj kaj ciferoj, estas en infaneca naiva memfido - nun ĉio funkcios por ni, baldaŭ. Kaj, facile kaj sendolore, li disiĝas de siaj financaj rimedoj. La estraro ankaŭ estas memfida - niaj inĝenieroj laboras mirinde. La laborkvanto estas je sia maksimumo.
Punkto 5-Ripetu Punkton 1 regule.
Spickuko kaj ringkukoj, kontuziĝoj kaj tuberojSpickuko kaj ringbulkoj:
1. La vivo de manaĝeroj kaj inĝenieroj estas simpla, antaŭvidebla kaj plena de aktiveco. Ĉio zumas, ĉiuj estas okupataj.
2. La vivo de la kliento ankaŭ ne estas malbona - li ĉiam certas, ke li bezonas pacienci iom da tempo kaj ĉio bone finiĝos. Se ne finiĝos, nu, la mondo estas maljusta, en la sekva vivo - li estos bonŝanca.
Kontuziĝoj kaj tuberoj:
1. Pli frue aŭ pli malfrue, estos pli rapida provizanto de simila servo, kiu faros la samon, sed iom pli malmultekoste. Kaj se la rezulto estas la sama, kial pagi pli? Kio denove kondukos al la malapero de la trogo.
2. Ĝi estas teda. Tiel teda kiel iu ajn agado, kiu havas malmultan signifon.
3. Kiel en la antaŭa opcio - neniu evoluigo. Sed por inĝeniero la malavantaĝo estas, ke, male al la unua opcio, ĉi tie vi bezonas konstante generi IBD. Kaj tio postulas tempon. Kiun vi povas elspezi kun profito por vi mem. Ĉar se vi ne zorgas pri vi mem, neniu zorgas pri vi.
Opcio 3 - Vi ne bezonas inventi biciklon, vi nur bezonas aĉeti ĝin kaj rajdi ĝin.
Inĝenieroj de aliaj kompanioj manĝas picon kaj trinkas ĝin per biero pro kialo (ho, la gloraj tempoj de Sankt-Peterburgo en la 90-aj jaroj). Ni uzu monitorajn sistemojn, kiuj estas faritaj, sencimigitaj, kaj funkcias, kaj ĝenerale alportas avantaĝojn (nu, almenaŭ al iliaj kreintoj).
Spickuko kaj ringkukoj, kontuziĝoj kaj tuberojSpickuko kaj ringbulkoj:
1. Ne necesas perdi tempon inventante ion, kio jam estas inventita. Prenu ĝin kaj uzu ĝin.
2. Monitoradajn sistemojn ne verkis malsaĝuloj kaj ili certe utilas.
3. Funkciantaj monitoraj sistemoj kutime provizas utilajn filtritajn informojn.
Kontuziĝoj kaj tuberoj:
1. La inĝeniero en ĉi tiu kazo ne estas inĝeniero, sed nur uzanto de ies alies produkto. Aŭ uzanto.
2. La kliento devas esti konvinkita pri la bezono aĉeti ion, kion li ĝenerale ne volas kompreni, kaj ne devus, kaj ĝenerale la buĝeto por la jaro estas aprobita kaj ne ŝanĝiĝos. Poste oni devas asigni apartan rimedon, agordi ĝin por specifa sistemo. Tio estas, unue oni devas pagi, pagi kaj pagi denove. Kaj la kliento estas avara. Tio estas la normo de ĉi tiu vivo.
Kion fari - Ĉerniŝevskij? Via demando estas tre konvena. (c)
En ĉi tiu aparta kazo kaj la nuna situacio, vi povas agi iom alimaniere - Ni kreu nian propran monitoran sistemon.
Nu, ne sistemo, kompreneble, en la plena senco de la vorto, tio estas tro laŭte dirita kaj aroganta, sed almenaŭ iel faciligu vian taskon kaj kolektu pli da informoj por solvi funkciajn incidentojn. Por ne trovi vin en situacio - "iru tien, mi ne scias kie, trovu tion, mi ne scias kion."
Kiuj estas la avantaĝoj kaj malavantaĝoj de ĉi tiu opcio:
Pros:
1. Ĝi estas interesa. Nu, almenaŭ pli interesa ol la konstanto "ŝrumpi datumdosieron, ŝanĝi tabelspacon, ktp."
2. Temas pri novaj kapabloj kaj nova evoluo. Kiuj longtempe pli frue aŭ pli malfrue donos la merititajn spickukojn kaj ringbulkojn.
Kons:
1. Vi devos labori. Labori multe.
2. Vi devos regule klarigi la signifon kaj perspektivojn de ĉiuj agadoj.
3. Io devos esti oferita, ĉar la sola rimedo disponebla al inĝeniero - tempo - estas limigita de la Universo.
4. La plej terura kaj la plej malagrabla — rezulte, vi eble ricevos ion similan al “Ne muso, ne rano, sed nekonata besto.”
Kiu ne riskas, tiu ne trinkas ĉampanon.
Do, la plej interesa parto komenciĝas.
Ĝenerala ideo - skematike
(Ilustraĵo prenita el la artikolo «»)
Klarigo:
- La norma PostgreSQL-kromprogramo "pg_stat_statements" estas instalita en la cela datumbazo.
- En la monitorada datumbazo, ni kreas aron da servaj tabeloj por konservi la historion de pg_stat_statements en la komenca stadio kaj por agordi metrikojn kaj monitoradon estonte.
- Sur la monitorada gastiganto, ni kreas aron da bash-skriptoj, inkluzive por generi okazaĵojn en la bileta sistemo.
Servotabloj
Por komenci, skema simpligita ERD, kion ni ricevis fine:
Mallonga priskribo de tabelojfinpunkto — gastiganto, konektopunkto al la instanco
DataBase — datumbazaj parametroj
pg_stat_historio — historia tabelo por konservi provizorajn momentfotojn de la pg_stat_statements vido de la cela datumbazo
metrika_glosaro — vortaro de rendimentaj metrikoj
metrika_agordo — agordo de individuaj metrikoj
metriko — specifa metriko por la serĉmendo, kiu estas monitorata
metrika_alarmhistorio — historio de avertoj pri efikeco
log_demando — utila tabelo por konservi analizitajn rikordojn el PostgreSQL-protokola dosiero elŝutita de AWS
bazlinio — parametroj de la tempoperiodo uzata kiel bazo
kontrolpunkto — agordo de metrikoj por sankontrolo de datumbazo
kontrolpunkta_alarmhistorio — historio de avertoj pri metrikoj de datumbaza sankontrolo
pg_stat_db_queries — serva tabelo de aktivaj petoj
agadregistro — serva tabelo de la agadprotokolo
kaptilo_oido — tabelo de kaptila agordo
Paŝo 1 - Kolektu rendimentajn statistikojn kaj generu raportojn
Tabelo estas uzata por konservi statistikajn informojn. pg_stat_historio
Strukturo de la tabelo pg_stat_history
Tabelo "public.pg_stat_history" Kolumno | Tipo | Modifiloj ---------------------+----------------------------+------------------------------------------ id | entjero | ne nula defaŭlto sekva valoro('pg_stat_history_id_seq'::regclass) momentfota_tempo | tempstampo sen horzono | datumbaza_id | entjero | dbid | oid | uzantoid | oid | demandoid | granda nombro | demando | teksto | vokoj | granda nombro | totala_tempo | duobla precizeco | min_tempo | duobla precizeco | maksimuma_tempo | duobla precizeco | meza_tempo | duobla precizeco | stddev_tempo | duobla precizeco | vicoj | granda nombro | shared_blks_hit | granda nombro | shared_blks_read | granda nombro | shared_blks_dirtied | granda nombro | shared_blks_written | granda nombro | loka_blks_hit | granda nombro | loka_blks_read | granda nombro | loka_blks_dirtied | granda nombro | loka_blks_written | granda nombro | temp_blks_read | granda nombro | provizora_blokoj_skribitaj | granda nombro | blk_lega_tempo | duobla precizeco | blk_skriba_tempo | duobla precizeco | bazlinia_id | entjero | Indeksoj: "pg_stat_history_pkey" PRIMARA ŜLOSILO, btree (id) "datumbazo_idx" btree (datumbazo_id) "serĉidentigilo_idx" btree (serĉidentigilo) "momentfoto_tempstampo_idx" btree (momentfoto_tempstampo) Fremdŝlosilaj limigoj: "datumbazo_id_fk" FREMDŜLOSILO (datumbazo_id) REFERENCAS datumbazon(id) ĈE FORIGO KASKADEKiel vi vidas, la tabelo estas nur akumula vido de la datumoj. pg_stat_statements en la cela datumbazo.
Uzi ĉi tiun tabelon estas tre simple.
pg_stat_historio reprezentos la akumulitajn statistikojn de serĉekzekuto por ĉiu horo. Komence de ĉiu horo, post kiam la tabelo estas plenigita, la statistikoj pg_stat_statements rekomencigi kun pg_stat_deklaroj_restarigo().
Notu: Statistikoj estas kolektitaj por serĉoj, kiuj bezonas pli ol 1 sekundon por plenumiĝi.
Plenigante la tabelon 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;Rezulte, post iom da tempo en la tabelo pg_stat_historio ni havos aron da momentfotoj de la enhavo de la tabelo pg_stat_statements cela datumbazo.
La efektiva raportado
Uzante simplajn serĉdemandojn, vi povas akiri tre utilajn kaj interesajn raportojn.
Agregitaj datumoj por difinita tempoperiodo
Peto
SELECT
database_id ,
SUM(calls) AS calls ,SUM(total_time) AS total_time ,
SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,
SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit ,
SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied ,
SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read,
SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time ,
SUM(blk_write_time) AS blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;DB Tempo
al_kar(intervalo '1 milisekundo' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O Tempo
al_karo(intervalo '1 milisekundo' * (pg_total_stat_history_rec.blk_lega_tempo + pg_total_stat_history_rec.blk_skriba_tempo), 'HH24:MI:SS.MS')
TOP10 SQL laŭ totala_tempo
Peto
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----------------------------------------------------------------------- | PLEJ BONAJ SQL LAŬ TOTALA PLENUMTEMPO | #| demando-ID| vokoj| vokoj %| totala_tempo (ms) | datumbaza_tempo % +-----------+-----------+-----------+--------------------------------+----------- | 10| 1| 821760255| .2|00001:00:03( 23.141 ms.)| 203141.681 | 5.42| 2| 4152624390| .2|00001:00:03( 13.929 ms.)| 193929.215 | 5.17| 3| 1484454471| .4|00001:00:02( 09.129 ms.)| 129129.057 | 3.44| 4| 655729273| .1|00000:00:02( 01.869 ms.)| 121869.981 | 3.25| 5| 2460318461| .1|00000:00:01( 33.113 ms.)| 93113.835 | 2.48| 6| 2194493487| .4|00001:00:00( 17.377 ms.)| .17377.868 | 46| 7| 1053044345| .1|00000:00:00( 06.156 ms.)| .6156.352 | 16| 8| 3644780286| .1|00000:00:00( 01.063 ms.)| .1063.830
TOP10 SQL laŭ totala I/O-tempo
Peto
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------------------------------------------------------------------------ | PLEJ BONAJ SQL LAŬ TOTALA I/O-TEMPO | #| demando-ID| vokoj| vokoj %| I/O-tempo (ms)|db I/O-tempo % +----+-----------+-----------+-----------+-------------------------+------------ | 10| 1| 4152624390| .2|00001:00:08( 31.616 ms.)| 511616.592 | 31.06| 2| 821760255| .2|00001:00:08( 27.099 ms.)| 507099.036 | 30.78| 3| 655729273| .1|00000:00:05( 02.209 ms.)| 302209.137 | 18.35| 4| 2460318461| 1|00000:00:04( 05.981 ms.)| 245981.117 | 14.93| 5| 1484454471| 4|00001:00:00( 39.144 ms.)| 39144.221 | 2.38| 6| 2194493487| 4|00001:00:00( 18.182 ms.)| 18182.816 | 1.10| 7| 1053044345| 1|00000:00:00( 16.611 ms.)| 16611.722 | 1.01| 8| 3644780286| .1|00000:00:00( 00.436 ms.)| .436.205
TOP10 SQL laŭ maksimuma ekzekuttempo
Peto
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-------------------------------------------------------------------------- | PLEJ BONAJ 10 SQL LAŬ MAKSIMUMA PLENUMTEMPO | #| momentfoto| momentfotoID| demandoID| maksimuma_tempo (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 per KOMUNA bufrolegado/skribo
Peto
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 LAŬ KOMUNA BUFRO LEGADO/SKRIBADO | #| momentfoto| momentfotoID| demandoID| komunaj blokoj legitaj| komunaj blokoj skribi +----+-------------------+-----------+----------+---------------------+--------------------- | 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 --------------------------------------------------------------------------------------------
Histogramo de distribuo de serĉdemandoj laŭ maksimuma ekzekuttempo
Petoj
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 ) ;
|------------------------------------------------------------------------------------------------ | MAKSIMUMA_TEMPO HISTOGRAMO | TOTALAJ VOKOJ: 33851920 | MINIMUMA TEMPO: 00:00:01.063 | MAKSIMUMA TEMPO: 00:02:01.869 --------------------------------------------------------------------------------- | min. daŭro| maksimuma daŭro| vokoj +----------------------------------+----------------------------------+---------- | 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
LA 10 PLEJ BONAJ Momentfotoj laŭ Demando po Sekundo
Petoj
--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 Momentfotoj ordigitaj laŭ QueryPerSeconds-nombroj ------------------------------------------------------------------------------------------------------------------------------- | #| momentfoto| momentfotoID| vokoj| totala datumbazotempo| QPS| I/O-tempo| I/O-tempo % +-----+-----------+-----------+-------------------+-----------+-------------------+------------ | 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
Hora Ekzekuta Historio kun QueryPerSeconds kaj I/O Tempo
Peto
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
Teksto de ĉiuj SQL-selektoj
Peto
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
La rezulto
Kiel vi vidas, per sufiĉe simplaj rimedoj, vi povas akiri multajn utilajn informojn pri la laborkvanto kaj stato de la datumbazo.
Noto:Se vi registras la identigilon de la demando en petoj, vi ricevos historion por aparta peto (por ŝpari spacon, raportoj por aparta peto estas preterlasitaj).
Do, statistikaj datumoj pri la efikeco de serĉpetoj estas haveblaj kaj kolektitaj.
La unua etapo, "kolektado de statistikaj datumoj", estas kompleta.
Vi povas pluiri al la dua etapo - "starigo de rendimentaj metrikoj".
Sed tio estas tute alia historio.
Daŭrigota…
fonto: www.habr.com
