{"id":32353,"date":"2019-10-31T21:46:32","date_gmt":"2019-10-31T18:46:32","guid":{"rendered":"https:\/\/prohoster.info\/blog\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting\/"},"modified":"2019-10-31T21:46:32","modified_gmt":"2019-10-31T18:46:32","slug":"monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","status":"publish","type":"post","link":"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","title":{"rendered":"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>In\u017eenjer \u2014 na latinskom zna\u010di inspirisan.<br \/>\nIn\u017eenjer mo\u017ee sve. (c) R.Dizel.<br \/>\n<i>Epigrafi.<\/i><br \/>\n<img decoding=\"async\" alt=\"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje\" src=\"\/wp-content\/uploads\/2019\/04\/9a2640267ea05006b94e03642b864ddc.jpeg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<i>Ili pri\u010da o tome za\u0161to administrator baza podataka treba da se seti svoje programerske pro\u0161losti. <\/i><\/p>\n<h2>Predgovor<\/h2>\n<p>\nSva imena su promenjena. Slu\u010dajna poklapanja. Materijal predstavlja isklju\u010divo li\u010dno mi\u0161ljenje autora.<\/p>\n<blockquote><p><b>Odricanje od garancija:<\/b> <i>u planiranom ciklusu \u010dlanaka ne\u0107e biti detaljnog i ta\u010dnog opisa kori\u0161\u0107enih tabela i skripti. Materijal se ne mo\u017ee odmah koristiti 'KAKO JESTE'. <br \/>\nPrvo, zbog velike koli\u010dine materijala, <br \/>\ndrugo, zbog specifi\u010dnosti proizvodne baze stvarnog naru\u010dioca. <br \/>\nZato \u0107e \u010dlanci sadr\u017eati samo ideje i opise u najop\u0161tijem smislu. <br \/>\nMo\u017eda \u0107e sistem u budu\u0107nosti napredovati do nivoa objavljivanja na GitHub-u, a mo\u017eda i ne. Vreme \u0107e pokazati.<\/i><\/p><\/blockquote>\n<p>\nPo\u010detak pri\u010de - '<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/icl_services\/blog\/446314\/\">Se\u0107a\u0161 se kako je sve po\u010delo<\/a><\/noindex>\u00bb.<br \/>\n\u0160ta je na kraju ispalo, u najop\u0161tijim crtama - '<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/444988\/\">Sinteza kao jedna od metoda za unapre\u0111enje performansi PostgreSQL-a<\/a><\/noindex>\u00bb<\/p>\n<h2>Za\u0161to mi je sve ovo potrebno?<\/h2>\n<p>\nPrvo, da ne zaboravim, se\u0107aju\u0107i se svojih dobrih dana u penziji. <br \/>\nDrugo, da sistematizujem napisanog. Jer ve\u0107 sam po\u010deo da se gubim i zaboravljam pojedine delove. <\/p>\n<p>I najva\u017enije \u2014 mo\u017eda \u0107e nekome koristiti i pomo\u0107i da ne izmi\u0161lja bicikl i ne sakuplja grablje. Drugim re\u010dima, da pobolj\u0161a svoju karmu (ne Habr-ovu). Jer, najvrednije u ovom svetu su ideje. Klju\u010dno je prona\u0107i ideju. A realizovati ideju u stvarnost je ve\u0107 isklju\u010divo tehni\u010dko pitanje.<\/p>\n<p>Dakle, hajde da po\u010dnemo, polako...<\/p>\n<h2>Postavljanje zadatka.<\/h2>\n<p><\/p>\n<h3>Imamo: <\/h3>\n<p>\nBaza podataka PostgreSQL (10.5), me\u0161ovitog tipa optere\u0107enja (OLTP+DSS), srednje-male optere\u0107enosti, sme\u0161tena u oblaku AWS. <br \/>\nMonitoring baze podataka nije prisutan, monitoring infrastrukture je predstavljen u vidu standardnih sredstava AWS u minimalnoj konfiguraciji.<\/p>\n<h3>Potrebno je:<\/h3>\n<p>\nPratiti performanse i stanje baze podataka, nalaziti i imati osnovne informacije za optimizaciju te\u0161kih upita ka BDP.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2>Kratak uvod ili analiza opcija re\u0161enja<\/h2>\n<p>\nZa po\u010detak poku\u0161ajmo razmotriti opcije re\u0161enja zadatka sa stanovi\u0161ta komparativne analize koristi i neprijatnosti za in\u017eenjera, dok se koristi i gubici menad\u017ementa prepu\u0161taju onima kojima to pripada po radnoj biografiji.<\/p>\n<h3>Opcija 1 - 'Rad na zahtev'<\/h3>\n<p>\nOstavimo sve kako jeste. Ako naru\u010ditelj nije zadovoljan ne\u010dim u funkcionalnosti, performansama baze podataka ili aplikacije, obavestit \u0107e DBA in\u017eenjere putem e-maila ili kreirati incident u sistemu za pra\u0107enje. <br \/>\nIn\u017eenjer, primiv\u0161i obave\u0161tenje, razmotri\u0107e problem, predlo\u017eiti re\u0161enje ili odlo\u017eiti problem u dalju budu\u0107nost, nadaju\u0107i se da \u0107e se sve samo re\u0161iti, jer \u0107e ionako brzo zaboraviti.<br \/>\n<b class=\"spoiler_title\">Medenjaci i krofne, modrice i kvrge<\/b><b>Medenjaci i krofne:<\/b><br \/>\n1. Ne treba raditi ni\u0161ta suvi\u0161no<br \/>\n2. Uvek postoji mogu\u0107nost izbegavanja obaveza. <br \/>\n3. Puno vremena koje se mo\u017ee potro\u0161iti po vlastitom naho\u0111enju.<br \/>\n<b>Modrice i kvrge:<\/b><br \/>\n1. Pre ili kasnije, naru\u010ditelj \u0107e se zapitati o su\u0161tini postojanja i univerzalnoj pravdi u ovom svetu i jo\u0161 jednom postaviti pitanje \u2014 za\u0161to pla\u0107am svoje novce? Posledstvo je uvek isto \u2014 pitanje je samo kada \u0107e se naru\u010ditelj zasititi i odustati. A hranili\u0161te \u0107e se isprazniti. To je \u017ealosno.<br \/>\n2. Razvoj in\u017eenjera \u2014 nula.<br \/>\n3. Te\u0161ko\u0107e u planiranju rada i optere\u0107enja <\/p>\n<h3>Opcija 2 - \u201ePles sa bubnjevima, prodajemo i obuvamo\u201c<\/h3>\n<p>\n<b>Ta\u010dka 1<\/b>-Za\u0161to nam je potreban sistem monitoringa, dobijat \u0107emo sve putem zahteva. Pustamo gomilu raznih upita ka re\u010dniku podataka i dinami\u010dkim prikazima, uklju\u010dujemo razne broja\u010de, sve svodimo u tabele, povremeno kao da analiziramo liste i tabele. Kao rezultat imamo lepe ili ne ba\u0161 lepe grafike, tabele, izve\u0161taje. Glavno je \u2014 da bude \u0161to vi\u0161e, \u0161to vi\u0161e.<br \/>\n<b>Ta\u010dka 2<\/b>-Generi\u0161emo aktivnost \u2014 pokre\u0107emo analizu svega ovoga.<br \/>\n<b>Ta\u010dka 3<\/b>-Pripremamo neki dokument, nazivamo ga jednostavno \u2014 \u201ekako da uredimo bazu podataka\u201c.<br \/>\n<b>Ta\u010dka 4<\/b>-Naru\u010dilac, vide\u0107i svu tu veli\u010danstvenost grafika i brojeva, preuzima detinjastu naivnost \u2014 sada \u0107e sve raditi, uskoro. I lako i bezbolno se odri\u010de svojih finansijskih sredstava. Menad\u017ement tako\u0111e veruje \u2014 na\u0161i in\u017eenjeri rade odli\u010dno. Optere\u0107enje je na maksimumu. <br \/>\n<b>Ta\u010dka 5<\/b>-Redovno ponavljati Ta\u010dku 1.<br \/>\n<b class=\"spoiler_title\">Medenjaci i krofne, modrice i kvrge<\/b><b>Medenjaci i krofne: <\/b><br \/>\n1. \u017divot menad\u017eera i in\u017eenjera \u2014 jednostavan, predvidiv i ispunjen aktivnostima. Sve zuji, svi su zaposleni. <br \/>\n2. \u017divot naru\u010ditelja tako\u0111e nije lo\u0161 \u2014 uvek je siguran da treba jo\u0161 malo strpljenja i sve \u0107e se srediti. Ne sre\u0111uje se, pa, \u0161ta da se radi \u2014 svet je nepravedan, u slede\u0107em \u017eivotu \u2014 mo\u017eda \u0107e imati sre\u0107e.<br \/>\n<b>Modrice i kvrge:<\/b><br \/>\n1. Pr sooner or later, a faster provider of a similar service will emerge, doing the same thing but slightly cheaper. If the result is the same, why pay more? Again, this will lead to the disappearance of the feeding source.<br \/>\n2. It's boring. Just as boring as any meaningless activity.<br \/>\n3. Like the previous option, there's no development here. But for an engineer, the downside is that, unlike the first option, IBD needs to be constantly generated here. And that takes time. Time that could be spent on something more beneficial for yourself. Because if you don't take care of yourself, no one else will.<\/p>\n<h3>Option 3 - There's no need to reinvent the wheel; just buy one and ride.<\/h3>\n<p>\nEngineers from other companies don\u2019t eat pizza washed down with beer for nothing (ah, the glorious times of St. Petersburg in the 90s). Let\u2019s use monitoring systems that are built, debugged, and functional, which, generally speaking, bring value (at least to their creators).<br \/>\n<b class=\"spoiler_title\">Medenjaci i krofne, modrice i kvrge<\/b><b>Medenjaci i krofne:<\/b><br \/>\n1. You don't need to waste time on inventing what has already been invented. Just take it and use it.<br \/>\n2. Monitoring systems are created not by fools, and they are certainly useful.<br \/>\n3. Working monitoring systems usually provide useful filtered information. <br \/>\n<b>Modrice i kvrge:<\/b><br \/>\n1. In this case, the engineer is not an engineer but merely a user of someone else's product. Or a user.<br \/>\n2. You have to convince the client of the necessity to buy something that, frankly speaking, they don\u2019t want to deal with, and should not, and the annual budget has been approved and will not change. Then it requires allocating a separate resource to configure under a specific system. So, first, you have to pay, pay again, and pay once more. And the client is stingy. That's the norm of life.<\/p>\n<h2>What to do - Chernyshevsky? Your question is quite relevant. (c)<\/h2>\n<p>\nIn this particular case and the existing situation, we can act a bit differently \u2014 <b>let's create our own monitoring system. <\/b><br \/>\n<img decoding=\"async\" alt=\"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje\" src=\"\/wp-content\/uploads\/2019\/04\/b59a3ad9e16b68c0fbc962a61d571c52.jpeg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\nWell, not a system in the full sense of the word, that's too grand and presumptuous, but at least make it easier for ourselves and gather more information for resolving performance incidents. So we won\u2019t find ourselves in a situation of 'go there, I don\u2019t know where, find that, I don\u2019t know what.'<\/p>\n<h4>What are the pros and cons of this option:<\/h4>\n<p><b>Prednosti:<\/b><br \/>\n1. It's interesting. At least it's more interesting than constant 'shrink datafile, alter tablespace, etc.' <br \/>\n2. Ovo su nove vje\u0161tine i nova razvijanja. \u0160to \u0107e na duge staze kona\u010dno donijeti zaslu\u017eene nagrade i slastice.<br \/>\n<b>Mane:<\/b><br \/>\n1. Morat \u0107ete raditi. Raditi puno. <br \/>\n2. Morat \u0107ete redovno obja\u0161njavati smisao i perspektive svih aktivnosti.<br \/>\n3. Ne\u0161to \u0107ete morati \u017ertvovati, jer je jedini resurs dostupan in\u017eenjeru \u2013 vrijeme \u2013 ograni\u010deno svemirskim pravilima. <br \/>\n<b>4. Najstra\u0161nija i najneugodnija stvar<\/b> \u2014 u rezultatima bi se moglo pojaviti ne\u0161to \u0161to nije ni mi\u0161, ni \u017eaba, ve\u0107 neka nepoznata \u017eivotinja.<\/p>\n<p><b>Ko ne rizikuje, ne pije \u0161ampanjac.<\/b><br \/>\nDakle, sada po\u010dinje najzanimljivije.<\/p>\n<h2>Op\u0107a ideja \u2013 u shematskoj formi<\/h2>\n<p>\n<img decoding=\"async\" alt=\"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje\" src=\"\/wp-content\/uploads\/2019\/04\/d1ae2ddc1d4c43353fe93eebdaa8b81a.jpeg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n(<i>Ilustracija je preuzeta iz \u010dlanka<\/i> \u00ab<noindex><a rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/444988\/\">Sinteza kao jedna od metoda za unapre\u0111enje performansi PostgreSQL-a<\/a><\/noindex>\u00bb)<\/p>\n<p>Obja\u0161njenje:<\/p>\n<ul>\n<li> U ciljnoj bazi se postavlja standardno pro\u0161irenje PostgreSQL \u2013 \u201cpg_stat_statements\u201d. <\/li>\n<li>U bazi podataka za monitoring kreiramo skup servisnih tabela za pohranu historije pg_stat_statements u po\u010detnoj fazi i za pode\u0161avanje metrika i monitoringa u budu\u0107nosti<\/li>\n<li> Na hostu za monitoring kreiramo skup bash skripti, uklju\u010duju\u0107i one za generisanje incidenata u sistemu tiketa. <\/li>\n<\/ul>\n<h2>Servisne tabele<\/h2>\n<p>\nZa po\u010detak, shematski pojednostavljena ERD, \u0161ta je na kraju rezultat:<br \/>\n<img decoding=\"async\" alt=\"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje\" src=\"\/wp-content\/uploads\/2019\/04\/03d29f4d6932d470d9d88651cbf0c715.jpeg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<b class=\"spoiler_title\">Kratak opis tabela<\/b><b>endpoint <\/b> \u2014 host, ta\u010dka povezivanja s instancom<br \/>\n<b>database <\/b> \u2014 parametri baze podataka<br \/>\n<b>pg_stat_history <\/b> \u2014 istorijska tabela za pohranu vremenskih snimaka prikaza pg_stat_statements ciljane baze podataka<br \/>\n<b>metric_glossary <\/b> \u2014 re\u010dnik metrika performansi<br \/>\n<b>metric_config <\/b> \u2014 konfiguracija pojedina\u010dnih metrika<br \/>\n<b>metric <\/b> \u2014 konkretna metrika za pra\u0107eni upit<br \/>\n<b>metric_alert_history <\/b> \u2014 istorija upozorenja iz performansi<br \/>\n<b>log_query <\/b> \u2014 pomo\u0107na tabela za pohranu razra\u0111enih zapisa iz loga PostgreSQL preuzetih sa AWS-a<br \/>\n<b>baseline <\/b> \u2014 parametri vremenskog perioda kori\u0161\u0107eni kao osnovni <br \/>\n<b>checkpoint <\/b> \u2014 konfiguracija metrika za provjeru stanja baze podataka<br \/>\n<b>checkpoint_alert_history <\/b> \u2014 istorija upozorenja metrika provjere stanja baze podataka<br \/>\n<b>pg_stat_db_queries <\/b> \u2014 pomo\u0107na tabela aktivnih upita <br \/>\n<b>activity_log <\/b> \u2014 pomo\u0107na tabela dnevnika aktivnosti <br \/>\n<b>trap_oid <\/b> \u2014 pomo\u0107na tabela konfiguracije zamke<\/p>\n<p><\/p>\n<h2>Faza 1 \u2014 prikupljanje statisti\u010dkih informacija o performansama i dobijanje izve\u0161taja<\/h2>\n<p>\nZa pohranu statisti\u010dkih informacija slu\u017ei tabela <b>pg_stat_history<\/b><br \/>\n<b class=\"spoiler_title\">Struktura tabele pg_stat_history<\/b><\/p>\n<pre>\n                                          Tabela \"public.pg_stat_history\"\n       Kolona        |            Tip             |                          Modifikatori\n---------------------+-----------------------------+-------------------------------------------\n id                  | celi broj                 | ne null podrazumijeva nextval('pg_stat_history_id_seq'::regclass)\n snapshot_timestamp  | vremenska oznaka bez vremenske zone |\n database_id         | celi broj                 |\n dbid                | oid                         |\n userid              | oid                         |\n queryid             | veliki broj                |\n query               | tekst                      |\n calls               | veliki broj                |\n total_time          | dvostruka preciznost       |\n min_time            | dvostruka preciznost       |\n max_time            | dvostruka preciznost       |\n mean_time           | dvostruka preciznost       |\n stddev_time         | dvostruka preciznost       |\n rows                | veliki broj                |\n shared_blks_hit     | veliki broj                |\n shared_blks_read    | veliki broj                |\n shared_blks_dirtied | veliki broj                |\n shared_blks_written | veliki broj                |\n local_blks_hit      | veliki broj                |\n local_blks_read     | veliki broj                |\n local_blks_dirtied  | veliki broj                |\n local_blks_written  | veliki broj                |\n temp_blks_read      | veliki broj                |\n temp_blks_written   | veliki broj                |\n blk_read_time       | dvostruka preciznost       |\n blk_write_time      | dvostruka preciznost       |\n baseline_id         | celi broj                 |\nIndeksi:\n    \"pg_stat_history_pkey\" PRIMARNI KLJU\u010c, btree (id)\n    \"database_idx\" btree (database_id)\n    \"queryid_idx\" btree (queryid)\n    \"snapshot_timestamp_idx\" btree (snapshot_timestamp)\nStrani klju\u010devi:\n    \"database_id_fk\" STRANI KLJU\u010c (database_id) REFERENCIRA database(id) KADA SE IZLIJE<\/pre>\n<p>\nKao \u0161to vidimo, tabela predstavlja samo kumulativne podatke prikaza <b>pg_stat_statements <\/b> u ciljnoj bazi podataka.<\/p>\n<h3>Kori\u0161tenje ove tabele je vrlo jednostavno<\/h3>\n<p>\n<b>pg_stat_history<\/b> predstavlja\u0107e nagomilanu statistiku izvr\u0161avanja upita za svaku sedmicu. Na po\u010detku svake sedmice, nakon \u0161to se tabela popuni, statistika <b>pg_stat_statements<\/b> se resetuje pomo\u0107u <b>pg_stat_statements_reset()<\/b>.<br \/>\nNapomena: <i>statistika se prikuplja za upite \u010dije vrijeme izvr\u0161enja traje vi\u0161e od 1 sekunde.<\/i><br \/>\n<b class=\"spoiler_title\">Popunjavanje tabele pg_stat_history<\/b><\/p>\n<pre><code class=\"plaintext\">--pg_stat_history.sql\nCREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$\nDECLARE\n  endpoint_rec record ;\n  database_rec record ;\n  pg_stat_snapshot record ;\n  current_snapshot_timestamp timestamp without time zone;\nBEGIN\n  current_snapshot_timestamp = date_trunc('minute',now());  \n  \n  FOR endpoint_rec IN SELECT * FROM endpoint \n  LOOP\n    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id \n\t  LOOP\n\t    \n\t\tRAISE NOTICE 'NOVI SNIMAK SE STVARA';\n\t\t\n\t\t--Povezivanje na ciljnu bazu podataka\t  \n\t    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';\n \n        RAISE NOTICE 'host % i dbname % ',endpoint_rec.host,database_rec.name;\n\t\tRAISE NOTICE 'Stvaranje snimka pg_stat_statements za bazu podataka %',database_rec.name;\n\t\t\n\t\tSELECT \n\t      *\n\t\tINTO \n\t\t  pg_stat_snapshot\n\t    FROM dblink('LINK1',\n\t      'SELECT \n\t       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , \n           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)\n\t       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) \n\t\t   GROUP BY dbid\n  \t      '\n\t               )\n\t      AS t\n\t       ( dbid oid , calls bigint , \n  \t         total_time double precision , \n\t         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written\t bigint ,\n             local_blks_hit\t bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,\n             temp_blks_read\t bigint ,temp_blks_written bigint ,\n             blk_read_time double precision , blk_write_time double precision\t  \n\t       );\n\t\t \n\t\tINSERT INTO pg_stat_history\n          ( \n\t\t    snapshot_timestamp  ,database_id  ,\n\t\t\tdbid , calls  ,total_time ,\n            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , \t \n            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, \t\n            blk_read_time, blk_write_time \n\t\t  )\t\t  \n\t    VALUES\n\t      (\n\t       current_snapshot_timestamp ,\n\t\t   database_rec.id ,\n\t       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,\n\t       pg_stat_snapshot.total_time,\n\t       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 , \n           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , \n\t       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time \t   \n\t      );\t\t   \n\t\t  \n        RAISE NOTICE 'Stvaranje snimka pg_stat_statements za upite sa min_time ve\u0107im od 1000ms';\n\t\n        FOR pg_stat_snapshot IN\n          --Svi upiti sa max_time ve\u0107im od 1000 ms\n\t      SELECT \n\t        *\n\t      FROM dblink('LINK1',\n\t        'SELECT \n\t         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,\n\t\t\t shared_blks_read ,shared_blks_dirtied ,shared_blks_written , \n             local_blks_hit , local_blks_read , local_blks_dirtied , \n\t\t\t local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , \n\t\t\t blk_write_time\n\t         FROM pg_stat_statements \n\t\t\t WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time &gt;= 1000 ) \n  \t        '\n\n\t                  )\n\t        AS t\n\t         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , \n  \t           total_time double precision ,min_time double precision\t ,max_time double precision\t , mean_time double precision\t ,  stddev_time double precision\t , \n\t           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written\t bigint ,\n               local_blks_hit\t bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,\n               temp_blks_read\t bigint ,temp_blks_written bigint ,\n               blk_read_time double precision , blk_write_time double precision\t  \n\t         )\n\t    LOOP\n\t\t  INSERT INTO pg_stat_history\n          ( \n\t\t    snapshot_timestamp  ,database_id  ,\n\t\t\tdbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,\n            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , \t \n            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, \t\n            blk_read_time, blk_write_time \n\t\t  )\t\t  \n\t      VALUES\n\t      (\n\t       current_snapshot_timestamp ,\n\t\t   database_rec.id ,\n\t       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,\n\t       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 ,\n\t       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 , \n           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , \n\t       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time \t   \n\t      );\n\t\t  \n        END LOOP;\n\n        PERFORM dblink_disconnect('LINK1');  \n\t\t\t\t\n\t  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id \n    \n  END LOOP;\n\nRETURN TRUE;  \nEND\n$$ LANGUAGE plpgsql;<\/code><\/pre>\n<p>Kao rezultat, nakon nekog vremenskog perioda u tabeli <b>pg_stat_history<\/b> ima\u0107emo skup uzoraka sadr\u017eaja tabele <b>pg_stat_statements <\/b>ciljane baze podataka. <\/p>\n<h2>Sam izve\u0161taj<\/h2>\n<p>\nKoriste\u0107i jednostavne upite, mo\u017eemo dobiti prili\u010dno korisne i zanimljive izve\u0161taje.<\/p>\n<h2>Agregirani podaci za dati vremenski period<\/h2>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  database_id , \n  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,\n  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,\n  SUM(shared_blks_read) AS shared_blks_read ,\n  SUM(shared_blks_dirtied) AS shared_blks_dirtied,\n  SUM(shared_blks_written) AS shared_blks_written , \n  SUM(local_blks_hit) AS local_blks_hit , \n  SUM(local_blks_read) AS local_blks_read , \n  SUM(local_blks_dirtied) AS local_blks_dirtied , \n  SUM(local_blks_written)  AS local_blks_written,\n  SUM(temp_blks_read) AS temp_blks_read, \n  SUM(temp_blks_written) temp_blks_written , \n  SUM(blk_read_time) AS blk_read_time , \n  SUM(blk_write_time) AS blk_write_time\nFROM \n  pg_stat_history\nWHERE \n  queryid IS NULL AND\n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT\nGROUP BY database_id ;<\/code><\/pre>\n<p><\/p>\n<h4>DB Vreme<\/h4>\n<blockquote><p>to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')<\/p><\/blockquote>\n<p><\/p>\n<h4>I\/O Vreme<\/h4>\n<blockquote><p>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')<\/p><\/blockquote>\n<h3>TOP10 SQL po ukupnom vremenu<\/h3>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  queryid , \n  SUM(calls) AS calls ,\n  SUM(total_time)  AS total_time  \t\nFROM \n  pg_stat_history\nWHERE \n  queryid IS NOT NULL AND \n  database_id = DATABASE_ID AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT \nGROUP BY queryid \nORDER BY 3 DESC \nLIMIT 10<\/code><\/pre>\n<pre>-------------------------------------------------------------------------------------\n| TOP10 SQL PO UKUPNOJ VREMENU IZVR\u0160ENJA\n|   #|    queryid|      calls|    procenat poziva|                ukupno vreme (ms) |  procenat dbvremena\n+----+-----------+-----------+-----------+--------------------------------+----------\n|   1|  821760255|          2|     .00001|00:03:23.141(    203141.681 ms.)|      5.42\n|   2| 4152624390|          2|     .00001|00:03:13.929(    193929.215 ms.)|      5.17\n|   3| 1484454471|          4|     .00001|00:02:09.129(    129129.057 ms.)|      3.44\n|   4|  655729273|          1|     .00000|00:02:01.869(    121869.981 ms.)|      3.25\n|   5| 2460318461|          1|     .00000|00:01:33.113(     93113.835 ms.)|      2.48\n|   6| 2194493487|          4|     .00001|00:00:17.377(     17377.868 ms.)|       .46\n|   7| 1053044345|          1|     .00000|00:00:06.156(      6156.352 ms.)|       .16\n|   8| 3644780286|          1|     .00000|00:00:01.063(      1063.830 ms.)|       .03\n<\/pre>\n<h4>TOP10 SQL po ukupnom I\/O vremenu<\/h4>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  queryid , \n  SUM(calls) AS calls ,\n  SUM(blk_read_time + blk_write_time)  AS io_time\nFROM \n  pg_stat_history\nWHERE \n  queryid IS NOT NULL AND \n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT\nGROUP BY  queryid \nORDER BY 3 DESC \nLIMIT 10<\/code><\/pre>\n<pre>----------------------------------------------------------------------------------------\n| TOP10 SQL PO UKUPNOM VREMENU I\/O\n|   #|    queryid|      pozivi|    % poziva|                   Vreme I\/O (ms)|% vremena db I\/O\n+----+-----------+-----------+-----------+--------------------------------+-------------\n|   1| 4152624390|          2|     .00001|00:08:31.616(    511616.592 ms.)|        31.06\n|   2|  821760255|          2|     .00001|00:08:27.099(    507099.036 ms.)|        30.78\n|   3|  655729273|          1|     .00000|00:05:02.209(    302209.137 ms.)|        18.35\n|   4| 2460318461|          1|     .00000|00:04:05.981(    245981.117 ms.)|        14.93\n|   5| 1484454471|          4|     .00001|00:00:39.144(     39144.221 ms.)|         2.38\n|   6| 2194493487|          4|     .00001|00:00:18.182(     18182.816 ms.)|         1.10\n|   7| 1053044345|          1|     .00000|00:00:16.611(     16611.722 ms.)|         1.01\n|   8| 3644780286|          1|     .00000|00:00:00.436(       436.205 ms.)|          .03\n<\/pre>\n<h4>TOP10 SQL po maksimalnom vremenu izvr\u0161enja<\/h4>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  id AS snapshotid , \n  queryid , \n  snapshot_timestamp ,  \n  max_time \nFROM \n  pg_stat_history \nWHERE \n  queryid IS NOT NULL AND \n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT\nORDER BY 4 DESC \nLIMIT 10<\/code><\/pre>\n<p><\/p>\n<pre>-----------------------------------------------------------------------------------------\n| TOP10 SQL PO MAKSMALNOM VREMENU IZVR\u0160ENJA\n|   #|          snapshot| snapshotID|    queryid|                           max_time (ms)\n+----+------------------+-----------+-----------+----------------------------------------\n|   1|  05.04.2019 01:03|       4169|  655729273|        00:02:01.869(    121869.981 ms.)\n|   2|  04.04.2019 17:00|       4153|  821760255|        00:01:41.570(    101570.841 ms.)\n|   3|  04.04.2019 16:00|       4146|  821760255|        00:01:41.570(    101570.841 ms.)\n|   4|  04.04.2019 16:00|       4144| 4152624390|        00:01:36.964(     96964.607 ms.)\n|   5|  04.04.2019 17:00|       4151| 4152624390|        00:01:36.964(     96964.607 ms.)\n|   6|  05.04.2019 10:00|       4188| 1484454471|        00:01:33.452(     93452.150 ms.)\n|   7|  04.04.2019 17:00|       4150| 2460318461|        00:01:33.113(     93113.835 ms.)\n|   8|  04.04.2019 15:00|       4140| 1484454471|        00:00:11.892(     11892.302 ms.)\n|   9|  04.04.2019 16:00|       4145| 1484454471|        00:00:11.892(     11892.302 ms.)\n|  10|  04.04.2019 17:00|       4152| 1484454471|        00:00:11.892(     11892.302 ms.)\n<\/pre>\n<h4>TOP10 SQL po DELJENOM \u010ditanju\/pisanu bafera<\/h4>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  id AS snapshotid , \n  queryid ,\n  snapshot_timestamp , \n  shared_blks_read , \n  shared_blks_written \nFROM \n  pg_stat_history\nWHERE \n  queryid IS NOT NULL AND \n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND\n  ( shared_blks_read &gt; 0 OR shared_blks_written &gt; 0 )\nORDER BY 4 DESC  , 5 DESC \nLIMIT 10<\/code><\/pre>\n<pre>--------------------------------------------------------------------------------------------\n| TOP10 SQL PO DELJENOM BUFERSKOM \u010cITANJU\/PI\u0160U\n|   #|          snapshot| snapshotID|    queryid|   deljeni blokovi \u010ditana|  deljeni blokovi pisanja\n+----+------------------+-----------+-----------+---------------------+---------------------\n|   1|  04.04.2019 17:00|       4153|  821760255|               797308|                    0\n|   2|  04.04.2019 16:00|       4146|  821760255|               797308|                    0\n|   3|  05.04.2019 01:03|       4169|  655729273|               797158|                    0\n|   4|  04.04.2019 16:00|       4144| 4152624390|               756514|                    0\n|   5|  04.04.2019 17:00|       4151| 4152624390|               756514|                    0\n|   6|  04.04.2019 17:00|       4150| 2460318461|               734117|                    0\n|   7|  04.04.2019 17:00|       4155| 3644780286|                52973|                    0\n|   8|  05.04.2019 01:03|       4168| 1053044345|                52818|                    0\n|   9|  04.04.2019 15:00|       4141| 2194493487|                52813|                    0\n|  10|  04.04.2019 16:00|       4147| 2194493487|                52813|                    0\n--------------------------------------------------------------------------------------------\n<\/pre>\n<h4>Histogram distribucije upita po maksimalnom vremenu izvr\u0161avanja<\/h4>\n<p><b class=\"spoiler_title\">Upiti<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT  \n  MIN(max_time) AS hist_min  , \n  MAX(max_time) AS hist_max , \n  (( MAX(max_time) - MIN(min_time) ) \/ hist_columns ) as hist_width\nFROM \n  pg_stat_history \nWHERE \n  queryid IS NOT NULL AND\n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;\n\nSELECT \n  SUM(calls) AS calls\nFROM \n  pg_stat_history \nWHERE \n  queryid IS NOT NULL AND\n  database_id =DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND \n  ( max_time &gt;= hist_current_min AND  max_time &lt; hist_current_max ) ;\n<\/code><\/pre>\n<pre>|-----------------------------------------------------------------------------------------------\n| MAX_TIME HISTOGRAM\n| UKUPNO POZIVA : 33851920\n| MIN VREME  : 00:00:01.063\n| MAX VREME  : 00:02:01.869\n---------------------------------------------------------------------------------\n|                      min trajanje|                      max trajanje|     pozivi\n+----------------------------------+----------------------------------+----------\n| 00:00:01.063(      1063.830 ms.) | 00:00:13.144(     13144.445 ms.) | 9\n| 00:00:13.144(     13144.445 ms.) | 00:00:25.225(     25225.060 ms.) | 0\n| 00:00:25.225(     25225.060 ms.) | 00:00:37.305(     37305.675 ms.) | 0\n| 00:00:37.305(     37305.675 ms.) | 00:00:49.386(     49386.290 ms.) | 0\n| 00:00:49.386(     49386.290 ms.) | 00:01:01.466(     61466.906 ms.) | 0\n| 00:01:01.466(     61466.906 ms.) | 00:01:13.547(     73547.521 ms.) | 0\n| 00:01:13.547(     73547.521 ms.) | 00:01:25.628(     85628.136 ms.) | 0\n| 00:01:25.628(     85628.136 ms.) | 00:01:37.708(     97708.751 ms.) | 4\n| 00:01:37.708(     97708.751 ms.) | 00:01:49.789(    109789.366 ms.) | 2\n| 00:01:49.789(    109789.366 ms.) | 00:02:01.869(    121869.981 ms.) | 0\n<\/pre>\n<h4>TOP10 Snapsotova po Upitu po Sekundi<\/h4>\n<p><b class=\"spoiler_title\">Upiti<\/b><\/p>\n<pre><code class=\"plaintext\">--pg_qps.sql\n--Izra\u010dunaj Upite Po Sekundi \nCREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$\nDECLARE\n pg_stat_history_rec record ;\n prev_pg_stat_history_id integer ;\n prev_pg_stat_history_rec record;\n total_seconds double precision ;\n result double precision;\nBEGIN \n  result = 0 ;\n  \n  SELECT *\n  INTO pg_stat_history_rec\n  FROM \n    pg_stat_history\n  WHERE id = pg_stat_history_id ;\n\n  IF pg_stat_history_rec.snapshot_timestamp IS NULL \n  THEN\n    RAISE EXCEPTION 'GRESKA - Nije prona\u0111ena pg_stat_history za id = %',pg_stat_history_id;\n  END IF ;  \n  \n --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , \n pg_stat_history_rec.snapshot_timestamp ;\n  \n  SELECT \n    MAX(id)   \n  INTO\n    prev_pg_stat_history_id\n  FROM\n    pg_stat_history\n  WHERE \n    database_id = pg_stat_history_rec.database_id AND\n\tqueryid IS NULL AND\n\tid  0 \n  THEN\n    result = pg_stat_history_rec.calls \/ total_seconds ;\n  ELSE\n   result = 0 ; \n  END IF;\n   \n RETURN result ;\nEND\n$$ LANGUAGE plpgsql;\n\n\nSELECT \n  id , \n  snapshot_timestamp ,\n  calls , \t\n  total_time , \n  ( select pg_qps( id )) AS QPS ,\n  blk_read_time ,\n  blk_write_time\nFROM \n  pg_stat_history\nWHERE \n  queryid IS NULL AND \n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND\n  ( select pg_qps( id )) IS NOT NULL \nORDER BY 5 DESC \nLIMIT 10\n<\/code><\/pre>\n<pre>|-----------------------------------------------------------------------------------------------\n| TOP10 Snimci poredani po broju upita po sekundi\n-----------------------------------------------------------------------------------------------------------------------------------------------\n|    #|          snimak| snimakID|      pozivi|                      ukupno vreme u bazi|        QPS|                          I\/O vreme| I\/O vreme %\n+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------\n|    1|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376\n|    2|  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\n|    3|  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\n|    4|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134\n|    5|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732\n|    6|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009\n|    7|  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\n|    8|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024\n|    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\n|   10|  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\n<\/pre>\n<h4>Historiya izvr\u0161enja po satu sa brojem upita po sekundi i vremenom I\/O<\/h4>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  id , \n  vreme_snimanja ,\n  pozivi , \t\n  ukupno_vreme , \n  ( select pg_qps( id )) AS QPS ,\n  vreme_\u010ditanja_blk ,\n  vreme_pisanja_blk\nFROM \n  pg_stat_history\nWHERE \n  queryid IS NULL AND \n  database_id = DATABASE_ID  AND\n  vreme_snimanja BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT\nORDER BY 2\n<\/code><\/pre>\n<pre>|-----------------------------------------------------------------------------------------------\n| HISTORIJA IZVR\u0160AVANJA NA SAT  SA QueryPerSeconds i I\/O Vremenom\n-----------------------------------------------------------------------------------------------------------------------------------------------\n| HISTORIJA UPITA PO SEKUNDI\n|    #|          trenutna slika| IDsnapshot|      pozivi|                      ukupno dbvrijeme|        QPS|                          I\/O vreme| I\/O vreme %\n+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------\n|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000\n|    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\n|    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\n|    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\n|    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\n|    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\n|    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\n|    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\n|    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\n|   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\n|   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\n|   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\n|   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\n|   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\n|   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\n|   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\n|   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\n|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006\n|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007\n|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001\n|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008\n|   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\n<\/pre>\n<h4>Tekst svih SQL-izbora<\/h4>\n<p><b class=\"spoiler_title\">Upit<\/b><\/p>\n<pre><code class=\"plaintext\">SELECT \n  queryid , \n  query \nFROM \n  pg_stat_history\nWHERE \n  queryid IS NOT NULL AND \n  database_id = DATABASE_ID  AND\n  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT\nGROUP BY queryid , query\n<\/code><\/pre>\n<h2>Zaklju\u010dak<\/h2>\n<p>\nKao \u0161to se mo\u017ee vidjeti, prili\u010dno jednostavnim sredstvima, mo\u017ee se dobiti dosta korisnih informacija o optere\u0107enju i stanju baze. <\/p>\n<p><b>Napomena:<\/b>Ako u upitima zabilje\u017eimo queryid, dobijamo historiju po pojedina\u010dnom upitu (radi u\u0161tede prostora, izvje\u0161taji po pojedina\u010dnom upitu su izostavljeni).<\/p>\n<p>Dakle, statisti\u010dki podaci o performansama upita \u2014 postoje i prikupljaju se.<br \/>\nPrva faza 'prikupljanje statisti\u010dkih podataka' \u2014 zavr\u0161ena.<\/p>\n<p>Mo\u017eemo pre\u0107i na drugu fazu \u2014 'pode\u0161avanje metrika performansi'.<br \/>\n<img decoding=\"async\" alt=\"Pra\u0107enje performansi upita PostgreSQL. Dio 1 \u2014 izvje\u0161tavanje\" src=\"\/wp-content\/uploads\/2019\/04\/a893e49280d4cb425e4667a5c51d2397.jpeg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\n<b>Ali to je ve\u0107 sasvim druga pri\u010da.<\/b><\/p>\n<p><i>Nastavak sledi\u2026<\/i><br \/>\n<br \/>Izvor: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/icl_services\/blog\/446734\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u2014 \u0432 \u043f\u0435\u0440\u0435\u0432\u043e\u0434\u0435 \u0441 \u043b\u0430\u0442\u044b\u043d\u0438 \u2014 \u0432\u0434\u043e\u0445\u043d\u043e\u0432\u0435\u043d\u043d\u044b\u0439. \u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u043c\u043e\u0436\u0435\u0442 \u0432\u0441\u0451. (\u0441) \u0420.\u0414\u0438\u0437\u0435\u043b\u044c. \u042d\u043f\u0438\u0433\u0440\u0430\u0444\u044b. \u0418\u043b\u0438 \u0438\u0441\u0442\u043e\u0440\u0438\u044f \u043e \u0442\u043e\u043c, \u0437\u0430\u0447\u0435\u043c \u0430\u0434\u043c\u0438\u043d\u0438\u0441\u0442\u0440\u0430\u0442\u043e\u0440\u0443 \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432\u0441\u043f\u043e\u043c\u0438\u043d\u0430\u0442\u044c \u0441\u0432\u043e\u0435 \u043f\u0440\u043e\u0433\u0440\u0430\u043c\u043c\u0438\u0441\u0442\u0441\u043a\u043e\u0435 \u043f\u0440\u043e\u0448\u043b\u043e\u0435. \u041f\u0440\u0435\u0434\u0438\u0441\u043b\u043e\u0432\u0438\u0435 \u0412\u0441\u0435 \u0438\u043c\u0435\u043d\u0430 \u0438\u0437\u043c\u0435\u043d\u0435\u043d\u044b. \u0421\u043e\u0432\u043f\u0430\u0434\u0435\u043d\u0438\u044f \u0441\u043b\u0443\u0447\u0430\u0439\u043d\u044b. \u041c\u0430\u0442\u0435\u0440\u0438\u0430\u043b \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0441\u043e\u0431\u043e\u0439 \u0438\u0441\u043a\u043b\u044e\u0447\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u043b\u0438\u0447\u043d\u043e\u0435 \u043c\u043d\u0435\u043d\u0438\u0435 \u0430\u0432\u0442\u043e\u0440\u0430. Disclaimer of warranties: \u0432 \u043f\u043b\u0430\u043d\u0438\u0440\u0443\u0435\u043c\u043e\u043c \u0446\u0438\u043a\u043b\u0435 \u0441\u0442\u0430\u0442\u0435\u0439 \u043d\u0435 \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u043e\u0433\u043e \u0438 \u0442\u043e\u0447\u043d\u043e\u0433\u043e \u043e\u043f\u0438\u0441\u0430\u043d\u0438\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446 \u0438 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":24168,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-32353","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.5.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u2014 \u0432 \u043f\u0435\u0440\u0435\u0432\u043e\u0434\u0435 \u0441 \u043b\u0430\u0442\u044b\u043d\u0438 \u2014 \u0432\u0434\u043e\u0445\u043d\u043e\u0432\u0435\u043d\u043d\u044b\u0439. \u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u043c\u043e\u0436\u0435\u0442 \u0432\u0441\u0451. (\u0441) \u0420.\u0414\u0438\u0437\u0435\u043b\u044c. \u042d\u043f\u0438\u0433\u0440\u0430\u0444\u044b.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.5.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"bs_BA\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u041c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 PostgreSQL. \u0427\u0430\u0441\u0442\u044c 1 \u2014 \u0440\u0435\u043f\u043e\u0440\u0442\u0438\u043d\u0433 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u2014 \u0432 \u043f\u0435\u0440\u0435\u0432\u043e\u0434\u0435 \u0441 \u043b\u0430\u0442\u044b\u043d\u0438 \u2014 \u0432\u0434\u043e\u0445\u043d\u043e\u0432\u0435\u043d\u043d\u044b\u0439. \u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u043c\u043e\u0436\u0435\u0442 \u0432\u0441\u0451. (\u0441) \u0420.\u0414\u0438\u0437\u0435\u043b\u044c. \u042d\u043f\u0438\u0433\u0440\u0430\u0444\u044b.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2019-10-31T18:46:32+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T18:46:32+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Pra\u0107enje performansi upita PostgreSQL. Deo 1 \u2014 izve\u0161tavanje | ProHoster","description":"In\u017eenjer \u2014 na latinskom \u2014 inspirisan. In\u017eenjer mo\u017ee sve. (c) R.Dizel. Epigrafi.","canonical_url":"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"bs_BA","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u041c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 PostgreSQL. \u0427\u0430\u0441\u0442\u044c 1 \u2014 \u0440\u0435\u043f\u043e\u0440\u0442\u0438\u043d\u0433 | ProHoster","og:description":"\u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u2014 \u0432 \u043f\u0435\u0440\u0435\u0432\u043e\u0434\u0435 \u0441 \u043b\u0430\u0442\u044b\u043d\u0438 \u2014 \u0432\u0434\u043e\u0445\u043d\u043e\u0432\u0435\u043d\u043d\u044b\u0439. \u0418\u043d\u0436\u0435\u043d\u0435\u0440 \u043c\u043e\u0436\u0435\u0442 \u0432\u0441\u0451. (\u0441) \u0420.\u0414\u0438\u0437\u0435\u043b\u044c. \u042d\u043f\u0438\u0433\u0440\u0430\u0444\u044b.","og:url":"https:\/\/prohoster.info\/bs\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2019-10-31T18:46:32+00:00","article:modified_time":"2019-10-31T18:46:32+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"32353","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":"2026-01-21 10:28:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 19:13:42","updated":"2026-01-21 10:28:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/posts\/32353","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/comments?post=32353"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/posts\/32353\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/media\/24168"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/media?parent=32353"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/categories?post=32353"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/bs\/wp-json\/wp\/v2\/tags?post=32353"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}