ʻEnekinia - unuhi ʻia mai ka ʻōlelo Latina - hoʻoulu ʻia.
Hiki i ka ʻenekinia ke hana i kekahi mea. (c) R. Diesel.
Epigraphs.
A i ʻole he moʻolelo e pili ana i ke kumu e hoʻomanaʻo ai ka luna waihona waihona i kāna papahana i hala.
Kauwehe
Ua hoʻololi ʻia nā inoa a pau. Hoʻopaʻa like ʻole. ʻO ka manaʻo pilikino wale nō ka manaʻo o ka mea kākau.
Hōʻole i nā palapala hoʻohiki: ma ka moʻolelo i hoʻolālā ʻia, ʻaʻohe wehewehe kikoʻī a pololei o nā papa a me nā palapala i hoʻohana ʻia. ʻAʻole hiki ke hoʻohana koke ʻia nā mea "AS IS".
ʻO ka mea mua, ma muli o ka nui o nā mea,
ʻO ka lua, ma muli o ka ʻoi me ka waihona hana o kahi mea kūʻai maoli.
No laila, e hāʻawi ʻia nā manaʻo a me nā wehewehe ma ke ʻano maʻamau ma nā ʻatikala.
Malia paha i ka wā e hiki mai ana e ulu ka ʻōnaehana i ka pae o ka hoʻouna ʻana ma GitHub, a ʻaʻole paha. Hōʻike ka manawa.
Ka hoomaka ana o ka moolelo-".
He aha ka hopena, ma nā ʻōlelo maʻamau - "»
No ke aha e pono ai iaʻu kēia mau mea a pau?
ʻAe, ʻo ka mea mua, i ʻole e poina iā ʻoe iho, e hoʻomanaʻo ana i nā lā hanohano i ka hoʻomaha.
ʻO ka lua, e hoʻonohonoho i ka mea i kākau ʻia. Noʻu iho, i kekahi manawa hoʻomaka wau e huikau a poina i nā ʻāpana kaʻawale.
ʻAe, a ʻo ka mea nui loa - hiki koke ke hele mai i ka lima no kekahi a kōkua ʻaʻole e hana hou i ka huila a ʻaʻole e hōʻiliʻili i kahi rake. I nā huaʻōlelo ʻē aʻe, e hoʻomaikaʻi i kāu karma (ʻaʻole ʻo Khabrovsky). No ka mea, ʻo ka manaʻo ka mea waiwai loa ma kēia ao. ʻO ka mea nui ka loaʻa ʻana o kahi manaʻo. A ʻo ka unuhi ʻana i ka manaʻo i loko o ka ʻoiaʻiʻo, he pilikia ʻenehana maʻemaʻe.
No laila e hoʻomaka mālie kākou...
Hoʻokumu i ka pilikia.
Loaʻa:
ʻO PostgreSQL(10.5), haʻawe hui ʻia (OLTP+DSS), haʻahaʻa haʻahaʻa a māmā, mālama ʻia ma ke ao AWS.
ʻAʻohe nānā ʻikepili, hōʻike ʻia ka nānā ʻana i nā ʻōnaehana ma ke ʻano he mau mea hana AWS maʻamau i kahi hoʻonohonoho liʻiliʻi.
Kōkua:
E nānā i ka hana a me ke kūlana o ka waihona, ʻimi a loaʻa ka ʻike mua e hoʻopaʻa i nā nīnau ʻikepili koʻikoʻi.
Hoʻopuka pōkole a i ʻole ka nānā ʻana i nā hoʻonā
No ka hoʻomaka ʻana, e hoʻāʻo mākou e kālailai i nā koho no ka hoʻoponopono ʻana i ka pilikia mai ka manaʻo o ka loiloi hoʻohālikelike o nā pōmaikaʻi a me nā pilikia no ka ʻenekinia, a na ka poʻe i manaʻo ʻia ma ka papa inoa o nā limahana e hana i nā pōmaikaʻi a me nā poho. o ka hooponopono ana.
Koho 1 - "Ke hana nei ma ke koi"
Waiho mākou i nā mea a pau e like me ia. Inā ʻaʻole ʻoluʻolu ka mea kūʻai aku i kekahi mea i ke olakino, ka hana o ka waihona a i ʻole ka noi, e hoʻolaha ʻo ia i nā ʻenekini DBA ma ka leka uila a i ʻole ma ka hana ʻana i kahi hanana i ka pahu tiketi.
ʻO ka mea ʻenekinia, i loaʻa iā ia ka leka hoʻomaopopo, e hoʻomaopopo ʻo ia i ka pilikia, hāʻawi i kahi hopena, a i ʻole e hoʻopaʻa i ka pilikia, me ka manaʻo e hoʻoponopono nā mea a pau iā ia iho, a ʻoiai, e poina koke nā mea āpau.
Gingerbread a me nā donuts, nā ʻeha a me nā ʻōpūGingerbread a me nā donuts:
1. ʻAʻohe mea ʻē aʻe e hana ai
2. Loaʻa mau ka manawa e puka ai a haumia.
3. Nui ka manawa hiki iā ʻoe ke hoʻolilo iā ʻoe iho.
ʻO nā ʻōpala a me nā ʻōpala:
1. Ma hope a ma hope paha, e noʻonoʻo ka mea kūʻai aku i ke ʻano o ka noho ʻana a me ka hoʻoponopono honua i kēia ao a nīnau hou iā ia iho i ka nīnau - no ke aha wau e uku ai iā lākou i kaʻu kālā? Hoʻokahi ka hopena - ʻo ka nīnau wale nō i ka wā e hōʻoluʻolu ai ka mea kūʻai aku a hoʻolele aloha. A nele ka mea hānai. He mea kaumaha.
2. ʻO ka hoʻomohala ʻana o ka ʻenekinia he ʻole.
3. Paʻakikī i ka hoʻonohonoho ʻana i ka hana a me ka hoʻouka ʻana
Koho 2 - "Hula me nā pahu kani, hoʻokomo a hoʻokomo i nā kāmaʻa"
Paukū 1-No ke aha mākou e pono ai i kahi ʻōnaehana nānā, e loaʻa iā mākou nā noi āpau. Hoʻomaka mākou i kahi hui o nā ʻano nīnau āpau i ka puke wehewehe ʻikepili a me nā hiʻohiʻona ikaika, hoʻohuli i nā ʻano helu like ʻole, lawe i nā mea āpau i loko o nā papa, e nānā i nā papa inoa a me nā papa, e like me ia. ʻO ka hopena, loaʻa iā mākou nā kiʻi nani a maikaʻi ʻole paha, nā papa, nā hōʻike. ʻO ka mea nui - ʻoi aku ka nui, ʻoi aku.
Paukū 2-E hana i ka hana-holo i ka loiloi o kēia mau mea a pau.
Paukū 3-Ke hoʻomākaukau nei mākou i kekahi palapala, kapa mākou i kēia palapala, maʻalahi - "pehea mākou e hoʻolako ai i ka waihona."
Paukū 4- ʻO ka mea kūʻai aku, ke ʻike nei i kēia nani o nā kiʻi a me nā kiʻi, aia i loko o kahi hilinaʻi kamaliʻi - i kēia manawa e hana nā mea āpau iā mākou, koke. A, maʻalahi a me ka ʻeha ʻole i kā lākou waiwai kālā. Manaʻo nō hoʻi ka hoʻokele e hana ikaika ana kā mākou ʻenekinia. Hoʻouka ʻoi aku.
Paukū 5- E hana mau i ka hana 1.
Gingerbread a me nā donuts, nā ʻeha a me nā ʻōpūGingerbread a me nā donuts:
1. He maʻalahi ke ola o nā mana a me nā ʻenekinia, hiki ke wānana a piha i ka hana. Kūleʻa nā mea a pau, hana nā mea a pau.
2. ʻAʻole maikaʻi ke ola o ka mea kūʻai - ua maopopo mau ʻo ia e pono ʻoe e hoʻomanawanui iki a e holo nā mea a pau. ʻAʻole maikaʻi, maikaʻi - maikaʻi ʻole kēia ao, i ke ola aʻe - laki.
ʻO nā ʻōpala a me nā ʻōpala:
1. Ma hope a ma hope paha, e loaʻa ana kahi mea hoʻolako akamai o kahi lawelawe like e hana like i ka mea like, akā ʻoi aku ka liʻiliʻi. A inā like ka hopena, no ke aha e uku hou aku ai. ʻO ia ka mea e alakaʻi ai i ka nalowale o ka mea hānai.
2. He luuluu. Nani ka luuluu o kekahi hana ano nui.
3. E like me ka mana mua - ʻaʻohe hoʻomohala. Akā no ka mea ʻenekinia, ʻo ka mea liʻiliʻi, ʻaʻole like me ka koho mua, pono ʻoe e hana mau i kahi IDB. A pono ka manawa. Hiki ke lilo no ka pono o kāu mea aloha. No ka mea, ʻaʻole hiki iā ʻoe ke mālama iā ʻoe iho, mālama nā kānaka a pau iā ʻoe.
Koho 3-ʻAʻole pono e hana i ka paikikala, pono ʻoe e kūʻai a holo.
ʻAi ʻike nā ʻenekinia mai nā hui ʻē aʻe i ka pizza me ka pia (ʻo, nā manawa hanohano o St. Petersburg i nā makahiki 90). E hoʻohana kākou i nā ʻōnaehana nānā i hana ʻia, hoʻopau ʻia a hana, a ma ka ʻōlelo maʻamau, lawe mai lākou i nā pōmaikaʻi (maikaʻi, ma ka liʻiliʻi loa i kā lākou mea hana).
Gingerbread a me nā donuts, nā ʻeha a me nā ʻōpūGingerbread a me nā donuts:
1. ʻAʻole pono e hoʻonele i ka manawa no ka haku ʻana i nā mea i haku mua ʻia. Lawe a hoʻohana.
2. ʻAʻole kākau ʻia nā ʻōnaehana kiaʻi e ka naʻaupō, a he mea pono lākou.
3. Hāʻawi nā ʻōnaehana nānā hana i ka ʻike pono kānana.
ʻO nā ʻōpala a me nā ʻōpala:
1. ʻO ka ʻenekinia ma kēia hihia ʻaʻole ia he ʻenekinia, akā he mea hoʻohana wale i ka huahana a haʻi, a he mea hoʻohana paha.
2. Pono ka mea kūʻai e hoʻomaopopo i ka pono e kūʻai i kahi mea āna i makemake ʻole ai e hoʻomaopopo, a ʻaʻole pono, a ma ke ʻano nui ua ʻae ʻia ka waihona kālā no ka makahiki a ʻaʻole e loli. A laila pono ʻoe e hoʻokaʻawale i kahi kumuwaiwai kaʻawale, hoʻonohonoho iā ia no kahi ʻōnaehana kikoʻī. ʻO kēlā mau. Pono mua ʻoe e uku, uku a uku hou. A lili ka mea kūʻai. ʻO kēia ka mea maʻamau o kēia ola.
He aha kāu e hana ai, e Chernyshevsky? Pili loa kāu nīnau. (Me)
I kēia hihia kūikawā a me ke kūlana o kēia manawa, hiki iā ʻoe ke hana i kahi ʻano ʻokoʻa - e hana mākou i kā mākou ʻōnaehana nānā ponoʻī.
ʻAe, ʻaʻole ia he ʻōnaehana, ʻoiaʻiʻo, ma ke ʻano piha o ka huaʻōlelo, he leo nui kēia a me ka haʻaheo, akā ma ka liʻiliʻi e maʻalahi iā ʻoe iho a hōʻiliʻili i nā ʻike hou aʻe e hoʻoponopono i nā hanana hana. I ʻole e ʻike iā ʻoe iho i kahi kūlana - "hele i laila, ʻaʻole wau ʻike i hea, ʻike i kēlā, ʻaʻole wau ʻike i ka mea."
He aha nā pōmaikaʻi a me nā pōʻino o kēia koho:
Kākoʻo:
1. He hoihoi. ʻAe, ʻoi aku ka hoihoi ma mua o ka "shrink datafile, alter tablespace, etc."
2. He mau mākau hou a hoʻomohala hou kēia. ʻO ka mea i ka wā e hiki mai ana e hāʻawi i ka gingerbread kūpono a me nā donuts.
ʻAla:
1. Pono e hana. Hana nui.
2. Pono ʻoe e wehewehe mau i ke ʻano a me nā hiʻohiʻona o nā hana a pau.
3. Pono e kaumaha ʻia kekahi mea, no ka mea, ʻo ka waiwai wale nō i loaʻa i ka ʻenekinia - manawa - ua kaupalena ʻia e ka Universe.
4. ʻO ka meaʻino a maikaʻiʻole - ʻo ka hopena, ʻo ka ʻōpala e like me "ʻAʻole he ʻiole, ʻaʻole he rana, akā he holoholona liʻiliʻi i ʻike ʻole ʻia".
ʻO wai ka mea ʻaʻole pilikia i kekahi mea ʻaʻole inu i ka champagne.
No laila, hoʻomaka ka leʻaleʻa.
Manaʻo laulā - schematic
(Kiʻi i lawe ʻia mai ka ʻatikala «»)
ʻO ka wehewehe:
- Hoʻokomo ʻia ka waihona ʻikepili me ka hoʻonui PostgreSQL maʻamau "pg_stat_statements".
- I loko o ka ʻikepili nānā, hana mākou i kahi papaʻaina lawelawe e mālama i ka mōʻaukala pg_stat_statements ma ka pae mua a e hoʻonohonoho i nā metric a me ka nānā ʻana i ka wā e hiki mai ana.
- Ma ka hale kiaʻi, hana mākou i kahi hoʻonohonoho o nā palapala bash, me nā mea no ka hana ʻana i nā hanana i ka ʻōnaehana tiketi.
Nā papa lawelawe
I ka hoʻomaka ʻana, kahi ERD i hoʻohālikelike ʻia, ka mea i hana i ka hopena:
ʻO ka wehewehe pōkole o nā papahopena kūlike - host, wahi pili i ka laʻana
hōkeoʻikepili - nā koho waihona
pg_stat_history - papa mōʻaukala no ka mālama ʻana i nā kiʻi paʻi pōkole o ka nānā ʻana i nā pg_stat_statements o ka waihona ʻikepili
metric_glossary - ʻO ka wehewehe ʻana o nā ana hana
metric_config - ka hoʻonohonoho ʻana o nā ana pākahi
mika - he ana kiko'ī no ka noi e nānā ʻia nei
metric_alert_history - moʻolelo o nā ʻōlelo aʻo hana
log_query - papaʻaina lawelawe no ka mālama ʻana i nā moʻolelo i hoʻopaʻa ʻia mai ka faila log PostgreSQL i hoʻoiho ʻia mai AWS
ka hopena - nā ʻāpana o ka manawa i hoʻohana ʻia ma ke ʻano he kumu
wahi huli - ka hoʻonohonoho ʻana o nā metric no ka nānā ʻana i ke kūlana o ka waihona
checkpoint_alert_history - ka mōʻaukala ʻōlelo aʻoaʻo o nā anana nānā kūlana waihona
pg_stat_db_queries — papa lawelawe o na noi ikaika
moʻolelo_hana — papa hana mooolelo lawelawe
trap_oid - papaʻaina lawelawe hoʻonohonoho pahele
Papa 1 - e hōʻiliʻili i nā helu hana a loaʻa nā hōʻike
Hoʻohana ʻia kahi pākaukau e mālama i ka ʻikepili helu. pg_stat_history
pg_stat_history hale papaʻaina
Papa "public.pg_stat_history" kolamu | ʻano | Nā mea hoʻololi--------------------+--------------------- ------------- -------------------------------- id | huinahelu | 'a'ole null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | hoailona manawa me ka palena manawa ole | waihona_id | huinahelu | dbid | oid | mea hoohana | oid | hulina | nui | ninau | kikokikona | kahea | nui | huina_manawa | ka pololei papalua | min_manawa | ka pololei papalua | max_time | ka pololei papalua | mean_time | ka pololei papalua | stddev_time | ka pololei papalua | lalani | nui | shared_blks_hit | nui | shared_blks_read | nui | shared_blks_dirtied | nui | shared_blks_written | nui | local_blks_hit | nui | local_blks_read | nui | local_blks_dirtied | nui | local_blks_written | nui | temp_blks_heluhelu | nui | temp_blks_written | nui | blk_read_time | ka pololei papalua | blk_write_time | ka pololei papalua | baseline_id | huinahelu | Nā papa kuhikuhi: "pg_stat_history_pkey" KĀNUI KĀMĀ, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Nā kī kī haole: "Kiddatabase_idx_data_data_data_keʻikena" ) MA KA HELE KAI
E like me kāu e ʻike ai, ʻo ka papaʻaina he ʻikepili ʻike kumulative wale nō pg_stat_statements ma ka waihona waihona.
He maʻalahi loa ka hoʻohana ʻana i kēia pākaukau.
pg_stat_history e hōʻike i nā ʻikepili i hōʻiliʻili ʻia o ka hoʻokō ʻana i nā nīnau no kēlā me kēia hola. I ka hoʻomaka ʻana o kēlā me kēia hola, ma hope o ka hoʻopiha ʻana i ka papaʻaina, ʻikepili helu pg_stat_statements hoʻonohonoho hou me pg_stat_statements_reset().
'Ōlelo Aʻo: ʻOhi ʻia nā ʻikepili no nā noi me ka lōʻihi o ka 1 kekona.
Ke hoʻokomo nei i ka papa pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( dbid oid , userid oid , queryid bigint ,query text , calls bigint ,
total_time double precision ,min_time double precision ,max_time double precision , mean_time double precision , stddev_time double precision ,
rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written bigint ,
local_blks_hit bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
temp_blks_read bigint ,temp_blks_written bigint ,
blk_read_time double precision , blk_write_time double precision
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;ʻO ka hopena, ma hope o kekahi manawa i ka papaʻaina pg_stat_history e loaʻa iā mākou kahi paʻi kiʻi o nā mea o ka papaʻaina pg_stat_statements waihona waihona.
Hōʻike maoli
Ke hoʻohana nei i nā nīnau maʻalahi, hiki iā ʻoe ke loaʻa nā hōʻike pono a hoihoi.
ʻIke i hui ʻia no kekahi manawa i hāʻawi ʻia
Noi
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 ;D.B. Manawa
to_char(wawa '1 milikekona' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
Manawa I/O
to_char(wā '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL ma ka huina_manawa
Noi
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 MA KA TOTAL HANA HOOKO | #| queryid| kahea| kahea %| huina_manawa (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 ma ka huina I/O manawa
Noi
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 MA KA TOTAL I/O MANAWA | #| queryid| kahea| kahea %| I/O manawa (ms)|db I/O manawa % +----+-----------+-----------+------ --------------------+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| Iune 31.06 | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 ms.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 18.35 | 4| 2460318461| 1| .00000|00:04:05.981( 245981.117 ms.)| 14.93 | 5| 1484454471| 4| .00001|00:00:39.144( 39144.221 ms.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 ms.)| .03
TOP10 SQL ma ka nui o ka manawa o ka hoʻokō
Noi
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 BY MAX KA MANA HOOKO | #| kiʻi paʻi| 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 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00( 01 ms.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150 ms.) | 7| 04.04.2019/17/00 4150:2460318461 | 00| 01| 33.113:93113.835:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16( 00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)
TOP10 SQL e SHARED buffer heluhelu/kākau
Noi
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC , 5 DESC
LIMIT 10------------------------------------------------- ------------------------------------ | TOP10 SQL BY SHARED BUFFER HELUHELU/KAKAU | #| kiʻi paʻi| snapshotID| queryid| heluhelu ʻia nā poloka kaʻana| palapala kaʻana like +----+-----+-----------+---------- ---------------------------------- | 1| 04.04.2019/17/00 4153:821760255| 797308| 0| 2| 04.04.2019 | 16| 00/4146/821760255 797308:0| 3| 05.04.2019| 01| 03 | 4169| 655729273/797158/0 4:04.04.2019| 16| 00| 4144| 4152624390 | 756514| 0/5/04.04.2019 17:00| 4151| 4152624390| 756514| 0 | 6| 04.04.2019/17/00 4150:2460318461| 734117| 0| 7| 04.04.2019 | 17| 00/4155/3644780286 52973:0| 8| 05.04.2019| 01| 03 | 4168| 1053044345/52818/0 9:04.04.2019| 15| 00| 4141| 2194493487 | 52813| 0/10/04.04.2019 16:00| 4147| 2194493487| 52813| 0 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX ------------------------------------------------- -------------------------------------------------
Histogram o ka hāʻawi ʻana i ka nīnau e ka manawa hoʻokō kiʻekiʻe
Nā noi
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 | NUI KELE: 33851920 | MIN MANAWA : 00:00:01.063 | MAX MANAWA : 00:02:01.869 ------------------------------------ -------- ---------------------------- | min lōʻihi| lōʻihi lōʻihi| kelepona +---------------------------------+------------------------- ---------------------+---------- | 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
Nā Kiʻi kiʻi kiʻi kiʻi kiʻi kiʻi kiʻi kiʻi ma ka Huli i kēlā me kēia Kekona
Nā noi
--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 i kauoha ʻia e nā helu QueryPerSeconds -------------------------------------- ------ --------------------------------------------- ------ ------------------------------------------- | #| kiʻi paʻi| snapshotID| kahea| huina dbtime| QPS | I/O manawa | I/O manawa % +-----+---------------------------------+------- ----+----------------------------------+---------- ------------------------------------------------ | 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 ms.)| 00.064 | 64.261| 024/9/05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| .77.507 | 10| 04.04.2019/18/01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX
Moʻolelo Hoʻokō i kēlā me kēia hola me QueryPerSeconds a me I/O Time
Noi
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
kikokiko o nā koho SQL a pau
Noi
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
ʻO ka hopena
E like me kāu e ʻike ai, ma ke ʻano maʻalahi, hiki iā ʻoe ke loaʻa i nā ʻike he nui e pili ana i ka hana a me ke kūlana o ka waihona.
Nānā:Inā hoʻoponopono ʻoe i ka queryid i nā nīnau, a laila e loaʻa iā mākou ka mōʻaukala no kahi noi ʻokoʻa (i mea e mālama ai i ka lewa, waiho ʻia nā hōʻike no kahi noi ʻokoʻa).
No laila, loaʻa a hōʻiliʻili ʻia ka ʻikepili helu e pili ana i ka hana noiʻi.
Hoʻopau ʻia ka pae mua "ʻohi ʻikepili helu".
Hiki iā ʻoe ke hoʻomau i ka pae ʻelua - "hoʻonohonoho i nā metric hana".
Akā he moʻolelo ʻē aʻe kēia.
E hoʻomau 'ia…
Source: www.habr.com