Engineer - txhais los ntawm Latin - tshoov siab.
Ib tug engineer tuaj yeem ua txhua yam. (c) R. Diesel.
Epigraphs.
Los yog ib zaj dab neeg hais txog vim li cas tus thawj tswj hwm database yuav tsum nco ntsoov nws qhov programming yav dhau los.
Lus Qhia Tshab
Tag nrho cov npe tau raug hloov lawm. Cov coincidences yog random. Cov ntaub ntawv tsuas yog tus kheej lub tswv yim ntawm tus sau.
Disclaimer ntawm warranty: Cov kab lus uas tau npaj tseg yuav tsis muaj cov ncauj lus kom ntxaws thiab meej ntawm cov lus thiab cov ntawv siv. Cov ntaub ntawv tsis tuaj yeem siv tam sim ntawd "AS YOG".
Firstly, vim lub ntim loj ntawm cov khoom,
Thib ob, vim muaj kev sib raug zoo nrog lub hauv paus ntawm cov neeg siv khoom tiag.
Yog li ntawd, cov ntawv yuav tsuas muaj cov tswv yim thiab cov lus piav qhia nyob rau hauv feem ntau daim ntawv.
Tej zaum yav tom ntej lub kaw lus yuav loj hlob mus rau theem uas tau tshaj tawm ntawm GitHub, lossis tej zaum tsis yog. Lub sij hawm yuav qhia.
Qhov pib ntawm zaj dab neeg - "".
Yuav ua li cas tshwm sim, nyob rau hauv feem ntau cov ntsiab lus - "»
Vim li cas kuv thiaj xav tau tag nrho cov no?
Zoo, ua ntej, yog li tsis txhob hnov qab, nco txog cov hnub tseem ceeb hauv kev so haujlwm.
Qhov thib ob, kom systematize dab tsi tau sau. Vim tias qee zaum kuv pib tsis meej pem thiab tsis nco qab qee yam.
Zoo, thiab qhov tseem ceeb tshaj plaws yog tias nws yuav tuaj yeem ua ke rau ib tus neeg thiab pab lawv kom tsis txhob rov tsim lub log thiab tsis txhob khaws cov rake. Hauv lwm lo lus, txhim kho koj cov karma (tsis yog Khabrov's). Vim qhov tseem ceeb tshaj plaws hauv ntiaj teb no yog cov tswv yim. Qhov tseem ceeb yog nrhiav ib lub tswv yim. Tab sis tig ib lub tswv yim rau hauv kev muaj tiag yog ib lo lus nug ntawm kev txawj ntse.
Yog li, cia peb pib, me ntsis ntawm ...
Formulation ntawm qhov teeb meem.
Muaj:
PostgreSQL(10.5) database, mixed load type (OLTP+DSS), nruab nrab-lub teeb load, nyob rau hauv AWS huab.
Tsis muaj kev saib xyuas cov ntaub ntawv; kev saib xyuas kev tsim kho vaj tse yog muab nyob rau hauv daim ntawv ntawm cov txheej txheem AWS cov cuab yeej hauv kev teeb tsa tsawg heev.
Yuav tsum muaj:
Saib xyuas cov kev ua tau zoo thiab cov xwm txheej ntawm cov ntaub ntawv, nrhiav thiab muaj cov ntaub ntawv thawj zaug rau optimizing cov lus nug hnyav database.
Kev taw qhia luv luv lossis kev txheeb xyuas cov kev xaiv daws teeb meem
Yuav pib nrog, cia peb sim tshuaj xyuas cov kev xaiv los daws qhov teeb meem los ntawm qhov kev pom ntawm kev sib piv ntawm cov txiaj ntsig thiab qhov tsis zoo rau tus engineer, thiab cia cov neeg uas muaj cai rau nws raws li cov neeg ua haujlwm teem sijhawm nrog cov txiaj ntsig thiab poob ntawm kev tswj hwm.
Option 1 - "Ua haujlwm raws li qhov xav tau"
Peb tawm txhua yam raws li yog. Yog tias tus neeg siv khoom tsis txaus siab rau qee yam hauv kev ua haujlwm, kev ua haujlwm ntawm cov ntaub ntawv lossis daim ntawv thov, nws yuav ceeb toom rau DBA engineers los ntawm e-mail lossis los ntawm kev tsim ib qho xwm txheej hauv daim pib tais.
Tus kws tshaj lij, tau txais kev ceeb toom, yuav nkag siab txog qhov teeb meem, muab kev daws teeb meem los yog muab qhov teeb meem rau ntawm lub qhov cub tom qab, vam tias txhua yam yuav daws tau nws tus kheej, thiab txawm li cas los xij, txhua yam yuav tsis ntev tom qab ntawd.
Gingerbread thiab donuts, bruises thiab pobGingerbread thiab donuts:
1. Tsis tas yuav ua dab tsi ntxiv.
2. Muaj ib txwm muaj lub cib fim los zam txim thiab ntsia hlau.
3. Ntau lub sijhawm uas koj tuaj yeem siv ntawm koj tus kheej txiav txim siab.
Bruises thiab pob:
1. Tsis ntev los sis tom qab, tus neeg siv khoom yuav xav txog qhov tseem ceeb ntawm kev muaj sia nyob thiab kev ncaj ncees thoob ntiaj teb hauv ntiaj teb no thiab rov nug nws tus kheej cov lus nug - vim li cas kuv thiaj them kuv cov nyiaj rau lawv? Qhov tshwm sim yog ib txwm zoo ib yam - cov lus nug tsuas yog thaum tus neeg siv khoom yuav tau dhuav thiab yoj goodbye. Thiab lub feeder yuav khoob. Nws yog kev tu siab.
2. Kev tsim kho engineer - xoom.
3. Teeb meem hauv kev npaj ua haujlwm thiab kev thauj khoom
Kev xaiv 2- "Dancing nrog tambourines, steaming thiab tso rau khau"
Nqe lus 1-Vim li cas peb xav tau kev saib xyuas, peb yuav tau txais txhua yam nrog kev thov. Peb khiav ib pawg ntawm txhua hom lus nug rau cov ntaub ntawv phau ntawv txhais lus thiab cov kev xav tsis zoo, tig rau txhua hom txee, muab txhua yam rau hauv cov rooj, thiab ua ntu zus txheeb xyuas cov npe thiab cov rooj. Yog li ntawd, peb muaj cov duab zoo nkauj lossis tsis zoo nkauj, cov ntxhuav, cov ntawv ceeb toom. Qhov tseem ceeb yog kom muaj ntau, ntau dua.
Nqe lus 2-Peb tsim cov kev ua ub no thiab tso tawm kev tsom xam ntawm txhua qhov no.
Nqe lus 3-Peb tab tom npaj ib daim ntawv, peb hu cov ntaub ntawv no yooj yim - "peb yuav tsum teeb tsa lub database li cas."
Nqe lus 4-Tus neeg siv khoom, pom txhua qhov zoo nkauj ntawm cov duab thiab cov lej, yog nyob rau hauv menyuam yaus, tsis muaj kev ntseeg siab - tam sim no txhua yam yuav ua haujlwm rau peb, sai sai. Thiab, nws yooj yim thiab tsis mob siab koom nrog nws cov peev nyiaj txiag. Kev tswj hwm kuj ntseeg tau tias peb cov engineers ua haujlwm zoo. Chaw thau khoom ntawm qhov siab tshaj plaws.
Nqe lus 5- Rov ua kauj ruam 1 tsis tu ncua.
Gingerbread thiab donuts, bruises thiab pobGingerbread thiab donuts:
1. Lub neej ntawm cov thawj coj thiab cov engineers yog qhov yooj yim, kwv yees tau thiab muaj kev ua haujlwm. Txhua yam yog buzzing, txhua leej txhua tus yog tibneeg hu tauj coob.
2. Tus neeg siv khoom lub neej kuj tsis phem - nws yeej ib txwm paub tseeb tias nws tsuas yog yuav tsum ua siab ntev me ntsis thiab txhua yam yuav ua tiav. Nws tsis tau zoo dua, zoo, lub ntiaj teb tsis ncaj ncees, hauv lub neej tom ntej koj yuav muaj hmoo.
Bruises thiab pob:
1. Tsis ntev los sis tom qab ntawd, yuav muaj cov neeg muab kev pabcuam sai dua uas yuav ua ib yam, tab sis pheej yig dua me ntsis. Thiab yog tias qhov tshwm sim zoo ib yam, vim li cas thiaj them nyiaj ntau dua. Uas yuav rov ua rau lub ploj ntawm feeder.
2. Nws dhuav. Yuav ua li cas dhuav ib yam dab tsi tsis muaj nuj nqis.
3. Raws li nyob rau hauv lub dhau los version, tsis muaj kev txhim kho. Tab sis rau tus engineer, qhov downside yog tias, tsis zoo li thawj qhov kev xaiv, koj yuav tsum tau tsim ib qho IBD tas li. Thiab qhov no yuav siv sijhawm. Qhov twg koj tuaj yeem siv rau qhov txiaj ntsig ntawm koj tus hlub. Vim koj tsis tuaj yeem saib xyuas koj tus kheej, tsis muaj leej twg ua phem rau koj.
Kev xaiv 3 - Koj tsis tas yuav tsim lub tsheb kauj vab, koj tsuas yog yuav tsum tau yuav nws thiab caij nws.
Nws tsis yog rau tsis muaj dab tsi uas engineers los ntawm lwm lub tuam txhab noj pizza nrog npias (oh, lub koob meej hnub ntawm St. Petersburg nyob rau hauv lub 90s). Cia peb siv cov kev tshuaj xyuas uas tau tsim, debugged thiab ua haujlwm, thiab feem ntau hais txog cov txiaj ntsig (zoo, tsawg kawg rau lawv cov neeg tsim).
Gingerbread thiab donuts, bruises thiab pobGingerbread thiab donuts:
1. Tsis tas yuav nkim sij hawm los txog tej yam uas twb tau tsim lawm. Coj nws thiab siv nws.
2. Kev soj ntsuam xyuas tsis yog sau los ntawm cov neeg ruam thiab lawv yog qhov tseeb, muaj txiaj ntsig.
3. Kev saib xyuas kev ua haujlwm feem ntau muab cov ntaub ntawv lim dej muaj txiaj ntsig.
Bruises thiab pob:
1. Tus engineer nyob rau hauv cov ntaub ntawv no tsis yog ib tug engineer, tab sis tsuas yog ib tug neeg siv ntawm lwm tus neeg cov khoom. Los yog ib tug neeg siv.
2. Tus neeg siv khoom yuav tsum muaj kev ntseeg siab ntawm qhov yuav tsum tau yuav ib yam dab tsi, feem ntau hais lus, nws tsis xav kom nkag siab, thiab yuav tsum tsis txhob, thiab feem ntau cov peev nyiaj rau xyoo tau pom zoo thiab yuav tsis hloov. Tom qab ntawd koj yuav tsum faib cov peev txheej cais thiab teeb tsa nws rau ib qho system tshwj xeeb. Cov. ua ntej koj yuav tsum them, them thiab them rov qab. Thiab cov neeg siv khoom yog stingy. Qhov no yog tus qauv ntawm lub neej no.
Yuav ua li cas - Chernyshevsky? Koj cov lus nug tseem ceeb heev. (Nrog)
Hauv qhov tshwj xeeb no thiab qhov xwm txheej tam sim no, koj tuaj yeem ua nws txawv me ntsis - cia peb ua peb tus kheej saib xyuas.
Zoo, tsis yog ib qho system, ntawm chav kawm, nyob rau hauv tag nrho cov ntsiab lus ntawm lo lus, uas yog heev nrov thiab presumptuous, tab sis yam tsawg kawg yuav ua rau koj txoj hauj lwm yooj yim dua thiab sau cov ntaub ntawv ntxiv los daws qhov teeb meem kev ua tau zoo. Txhawm rau kom tsis txhob pom koj tus kheej hauv qhov xwm txheej - "mus rau qhov ntawd, kuv tsis paub qhov twg, nrhiav qee yam, kuv tsis paub dab tsi."
Dab tsi yog qhov zoo thiab qhov tsis zoo ntawm qhov kev xaiv no:
Tshaj:
1. Qhov no yog qhov nthuav. Zoo, yam tsawg kawg nws yog qhov nthuav ntau dua li qhov "shrink datafile, hloov tablespace, thiab lwm yam."
2. Cov no yog cov txuj ci tshiab thiab kev txhim kho tshiab. Uas, sai los yog tom qab, yuav muab rau koj zoo-deserved gingerbread thiab donuts.
Txais:
1. Koj yuav tau ua haujlwm. Ua hauj lwm nyuaj.
2. Koj yuav tau piav qhia lub ntsiab lus thiab kev cia siab ntawm txhua yam dej num.
3. Ib yam dab tsi yuav tsum tau txi, vim tsuas yog cov peev txheej muaj rau tus kws tshaj lij - lub sijhawm - yog txwv los ntawm Lub Ntiaj Teb.
4. Qhov phem tshaj plaws thiab tsis kaj siab tshaj plaws - qhov tshwm sim yuav yog bullshit zoo li "Tsis yog nas, tsis yog qav, tab sis yog tsiaj tsis paub."
Cov neeg uas tsis muaj kev pheej hmoo tsis haus champagne.
Yog li - kev lom zem pib.
General tswv yim - schematically
(Cov duab coj los ntawm kab lus «»)
Cov lus piav qhia:
- Tus qauv PostgreSQL txuas ntxiv "pg_stat_statements" tau teeb tsa hauv lub hom phiaj database.
- Hauv kev saib xyuas cov ntaub ntawv, peb tsim cov txheej txheem kev pabcuam rau khaws cia pg_stat_statements keeb kwm ntawm thawj theem thiab rau kev teeb tsa kev ntsuas thiab saib xyuas yav tom ntej
- Ntawm tus tswv tsev saib xyuas, peb tsim cov ntawv sau bash, suav nrog cov tsim teeb meem hauv daim pib.
Cov rooj pabcuam
Ua ntej, schematic simplified ERD, dab tsi tshwm sim thaum kawg:
Cov lus piav qhia luv luv ntawm cov roojxaus - tus tswv tsev, taw tes ntawm kev sib txuas rau qhov piv txwv
database - database parameters
pg_stat_history - cov lus keeb kwm rau khaws cia ib ntus snapshots ntawm pg_stat_statements saib ntawm lub hom phiaj database
metric_glossary - phau ntawv txhais lus ntawm kev ntsuas kev ua haujlwm
metric_config - configuration ntawm tus kheej metrics
metric - ib qho kev ntsuas tshwj xeeb rau qhov kev thov uas raug saib xyuas
metric_alert_history - keeb kwm ntawm kev ceeb toom ua haujlwm
log_query - lub rooj pabcuam rau khaws cov ntaub ntawv txheeb xyuas los ntawm PostgreSQL cov ntaub ntawv rub tawm los ntawm AWS
lub hauv paus - parameters ntawm lub sij hawm lub sij hawm siv raws li lub hauv paus
chaw kuaj mob - configuration ntawm metrics rau xyuas cov xwm txheej ntawm cov ntaub ntawv
checkpoint_alert_history - ceeb toom keeb kwm ntawm database kev kuaj xyuas kev ntsuas
pg_stat_db_queries - lub rooj pabcuam ntawm cov lus thov nquag
activity_log - Cov ntawv teev kev pabcuam kev ua haujlwm
trap_oid - lub rooj pab cuam teeb tsa cuab
Kauj Ruam 1 - sau cov ntaub ntawv txheeb xyuas txog kev ua tau zoo thiab tau txais cov ntawv ceeb toom
Ib lub rooj yog siv los khaws cov ntaub ntawv txheeb cais pg_stat_history
pg_stat_history table structure
Table "public.pg_stat_history" Kem | Hom | Kev hloov pauv---------------------+-------------------------------- ---------------------------------------------------------------- id | tus lej | tsis null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | timestamp tsis muaj sij hawm zone | database_id | tus lej | dbid | oid | siv | oid | queryid | loj | lus nug | ntawv | hu | loj | total_time | dual precision | min_time | dual precision | max_time | dual precision | mean_time | dual precision | stddev_time | dual precision | kab | loj | shared_blks_hit | loj | shared_blks_read | loj | shared_blks_dirtied | loj | shared_blks_written | loj | local_blks_hit | loj | local_blks_read | loj | local_blks_dirtied | loj | local_blks_written | loj | temp_blks_read | loj | temp_blks_written | loj | blk_read_time | dual precision | blk_write_time | dual precision | baseline_id | tus lej | Indexes: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Txawv teb chaws_data:basek_idx" ) REFERENCES database(id ) RAU DELETE CASCADE
Raws li koj tuaj yeem pom, lub rooj tsuas yog cov ntaub ntawv pom ntau ntxiv pg_statements nyob rau hauv lub hom phiaj database.
Siv lub rooj no yooj yim heev
pg_stat_history yuav sawv cev sau cov txheeb cais ntawm cov lus nug ua tiav rau txhua teev. Thaum pib ntawm txhua teev, tom qab sau cov lus, txheeb cais pg_statements pib dua nrog pg_stat_statements_reset().
Nco ntsoov: Cov ntawv txheeb cais tau sau rau cov lus nug nrog lub sijhawm ua tiav ntau dua 1 thib ob.
Populating lub rooj 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;Yog li ntawd, tom qab qee lub sij hawm ntawm lub sij hawm nyob rau hauv lub rooj pg_stat_history peb yuav muaj ib txheej snapshots ntawm lub rooj txheem pg_statements phiaj database.
Qhia tseeb
Siv cov lus nug yooj yim, koj tuaj yeem tau txais cov ntaub ntawv tseem ceeb thiab nthuav dav.
Aggregated cov ntaub ntawv rau ib lub sij hawm
Thov
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 Sijhawm
to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O Sijhawm
to_char(interval '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL los ntawm total_time
Thov
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 LUB SIJ HAWM EXECUTION | #| queryid | hu | hu %| total_time (ms) | dbtime % +----------+-----------+-----------+----------+ --------------------------------------+---------------- | 1| 821760255 | 2| .00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390 | 2| .00001|00:03:13.929(193929.215 ms.)| 5.17 | 3| 1484454471 | 4| .00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273 | 1| .00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5 | 2460318461 | 1| .00000|00:01:33.113( 93113.835 ms.)| 2.48 | 6| 2194493487 | 4| .00001|00:00:17.377(17377.868 ms.)| .46 | 7 | 1053044345 | 1| .00000|00:00:06.156( 6156.352 ms.)| .16 | 8 | 3644780286 | 1| .00000|00:00:01.063(1063.830 ms.)| .03
TOP10 SQL los ntawm tag nrho I / O lub sijhawm
Thov
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 LOS NTAWM TAG NRHO I/O TIME | #| queryid | hu | hu %| Lub sij hawm I/O (ms) | db I/O sij hawm % ++----+----------+----------+-------- -------------------------------------------------------------------- ------ ------ | 1| 4152624390 | 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 Nws 2| 821760255 | 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 Nws 3| 655729273 | 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35 Nws 4| 2460318461 | 1| .00000|00:04:05.981( 245981.117 ms.)| 14.93 Nws 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 los ntawm max lub sijhawm ua haujlwm
Thov
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 LOS NTAWM MAX EXECUTION TIME | #| snapshot | snapshotID| queryid | 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 TSİ 00 | 01 | 41.570:101570.841:3(04.04.2019 ms.) | 16| 00 4146:821760255 TSİ 00 | 01 | 41.570:101570.841:4(04.04.2019 ms.) | 16| 00 4144:4152624390 TSİ 00 | 01 | 36.964:96964.607:5(04.04.2019 ms.) | 17 | 00 4151:4152624390 TSİ 00 | 01 | 36.964:96964.607:6(05.04.2019 ms.) | 10| 00 4188:1484454471 TSİ 00 | 01 | 33.452:93452.150:7(04.04.2019 ms.) | 17 | 00 4150:2460318461 TSİ 00 | 01 | 33.113:93113.835:8(04.04.2019 ms.) | 15 | 00 4140:1484454471 TSİ 00 | 00 | 11.892:11892.302:9(04.04.2019 ms.) | 16 | 00 4145:1484454471 TSİ 00 | 00 | 11.892:11892.302:10(04.04.2019 ms.) | 17 | 00 4152:1484454471 TSİ 00 | 00 | 11.892:11892.302:XNUMX (XNUMX ms.)
TOP10 SQL los ntawm SHARED tsis nyeem / sau
Thov
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 LOS NTAWM SHARED BUFFER READ/SAU | #| snapshot | snapshotID| queryid | qhia blocks nyeem| sib qhia blocks sau ----+---------------------+-----------------+---------- ----------------------+--------------------------------- | 1| 04.04.2019 17:00 TSİ 4153 | 821760255 | 797308 | 0 | 2| 04.04.2019 16:00 TSİ 4146 | 821760255 | 797308 | 0 | 3| 05.04.2019/01/03 4169:655729273 | 797158 | 0 | 4; ib 04.04.2019 | 16| 00 4144:4152624390 TSİ 756514 | 0 | 5 | 04.04.2019 | 17 | 00 4151:4152624390 TSİ 756514 | 0 | 6 | 04.04.2019 | 17| 00 4150:2460318461 TSİ 734117 | 0 | 7 | 04.04.2019 | 17 | 00 4155:3644780286 TSİ 52973 | 0 | 8; ib 05.04.2019 | 01 | 03/4168/1053044345 52818:0 | 9 | 04.04.2019 | 15; ib 00 | 4141 | 2194493487 52813:0 TSİ 10 | 04.04.2019 | 16; ib 00 | 4147 | 2194493487 52813:0 TSİ XNUMX | XNUMX | XNUMX; ib XNUMX ----------------------------------------------------- --------------------------------------------------
Histogram ntawm kev thov faib los ntawm lub sijhawm ua tiav siab tshaj plaws
Thov
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 | TAG NRHO COV LUS: 33851920 | MIN TIME: 00:00:01.063 | MAX SIJ HAWM: 00:02:01.869 --------------------------------------------- --------------------------------------- | min ntev | max ntev | hu xov tooj ---------------------------------------------------- -----------------------+---------------- | 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 los ntawm Query ib Thib Ob
Thov
--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 xaj los ntawm QueryPerSeconds ------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------------------ | #| snapshot | snapshotID| hu | tag nrho dbtime| QPS | Lub sijhawm I/O | I/O lub sij hawm % +-----+---------------------+-----------------+-------- -------------------------------------------- + ------------------------------------ +----------------- | 1| 04.04.2019 20:04 TSİ 4161 | 5758631 | 00:06:30.513(390513.926 ms.)| 1573.396 | 00:00:01.470(1470.110 ms.)| .376 | 2| 04.04.2019 17:00 TSİ 4149 | 3529197 | 00:11:48.830(708830.618 ms.)| 980.332 | 00:12:47.834(767834.052 ms.)| 108.324 | 3| 04.04.2019 16:00 TSİ 4143 | 3525360 | 00:10:13.492(613492.351 ms.)| 979.267 | 00:08:41.396(521396.555 ms.)| 84.988 | 4| 04.04.2019/21/03 4163:2781536 | 00 | 03 | 06.470:186470.979:785.745(00 ms.)| 00 | 00.249:249.865:134(5 ms.)| .04.04.2019 | 19 | 03 4159:2890362 TSİ 00 | 03 | 16.784:196784.755:776.979(00 ms.)| 00 | 01.441:1441.386:732(6 ms.)| .04.04.2019 | 14| 00 4137:2397326 TSİ 00 | 04 | 43.033:283033.854:665.924(00 ms.)| 00 | 00.024:24.505:009(7 ms.)| .04.04.2019 | 15 | 00 4139:2394416 TSİ 00 | 04 | 51.435:291435.010:665.116(00 ms.)| 00 | 12.025:12025.895:4.126(8 ms.)| 04.04.2019 | 13 | 00 4135:2373043 TSİ 00 | 04 | 26.791:266791.988:659.179(00 ms.)| 00 | 00.064:64.261:024(9 ms.)| .05.04.2019 | 01 | 03/4167/4387191 00:06 | 51.380 | 411380.293 | 609.332:00:05(18.847 ms.)| 318847.407 | 77.507:10:04.04.2019(18 ms.)| 01 | 4157 | 1145596/00/01 19.217:79217.372 | 313.004 | 00 | 00:01.319:1319.676(1.666 ms.)| XNUMX | XNUMX:XNUMX:XNUMX(XNUMX ms.)| XNUMX ib
Lub Sijhawm Ua Haujlwm Txhua Sijhawm nrog QueryPerSeconds thiab I / O Sijhawm
Thov
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
Cov ntawv nyeem ntawm txhua SQL-xaiv
Thov
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
Qhov no
Raws li koj tuaj yeem pom, siv txoj kev yooj yim yooj yim, koj tuaj yeem tau txais ntau cov ntaub ntawv tseem ceeb ntawm kev ua haujlwm thiab lub xeev ntawm cov ntaub ntawv.
Nco tseg:Yog tias peb sau cov lus nug hauv cov lus nug, peb yuav tau txais keeb kwm rau cov lus nug cais (kom txuag tau qhov chaw, cov lus ceeb toom rau cov lus nug cais tawm).
Yog li, cov ntaub ntawv txheeb cais ntawm cov lus nug kev ua tau zoo yog muaj thiab sau.
Thawj theem "kev sau cov ntaub ntawv txheeb cais" ua tiav.
Koj tuaj yeem txav mus rau theem thib ob - "kev teeb tsa kev ntsuas kev ua tau zoo".
Tab sis qhov ntawd yog ib zaj dab neeg sib txawv kiag li.
Kom txuas ntxiv…
Tau qhov twg los: www.hab.com