{"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\/sq\/blog\/administrirovanie\/monitoring-proizvoditelnosti-zaprosov-postgresql-chast-1-reporting","title":{"rendered":"Monitorimi i performanc\u00ebs s\u00eb k\u00ebrkesave PostgreSQL. Pjesa 1 \u2014 raportimi.","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Inxhinier \u2014 n\u00eb p\u00ebrkthim nga latinishtja \u2014 i frym\u00ebzuar.
\nInxhinieri mund t\u00eb b\u00ebj\u00eb gjith\u00e7ka. (c) R.Dizel.
\nEpigraf\u00ebt.<\/i>
\n\"Monitorimi
\nOse historia se p\u00ebrse nj\u00eb administrator i databaz\u00ebs duhet t\u00eb kujtoj\u00eb t\u00eb kaluar\u00ebn e tij si programues. <\/i><\/p>\n

Parath\u00ebnie<\/h2>\n

\nT\u00eb gjitha emrat jan\u00eb t\u00eb ndryshuar. Rast\u00ebsit\u00eb jan\u00eb t\u00eb rastit. Materiali p\u00ebrb\u00ebn ekskluzivisht mendimin personal t\u00eb autorit.<\/p>\n

P\u00ebrgjegj\u00ebsia e garantive:<\/b> n\u00eb ciklin e planifikuar t\u00eb artikujve nuk do t\u00eb ket\u00eb p\u00ebrshkrime t\u00eb detajuara dhe t\u00eb sakta t\u00eb tabelave dhe skripteve t\u00eb p\u00ebrdorura. Materialet nuk do t\u00eb mund t\u00eb p\u00ebrdoren menj\u00ebher\u00eb \"SIQ\".
\nS\u00eb pari, p\u00ebr shkak t\u00eb volumit t\u00eb madh t\u00eb materialit,
\ns\u00eb dyti p\u00ebr shkak t\u00eb fokusit me baz\u00ebn e prodhimit t\u00eb klientit real.
\nPrandaj, n\u00eb artikuj do t\u00eb jepen vet\u00ebm ide dhe p\u00ebrshkrime n\u00eb nj\u00eb form\u00eb t\u00eb p\u00ebrgjithshme.
\nNdoshta n\u00eb t\u00eb ardhmen sistemi do t\u00eb rritet n\u00eb nivelin e publikimit n\u00eb GitHub, ndoshta jo. Koha do ta tregoj\u00eb.<\/i><\/p><\/blockquote>\n

\nFillimi i historis\u00eb - \"A e mban mend si filloi gjith\u00e7ka<\/a><\/noindex>\u00bb.
\n\u00c7far\u00eb doli si rezultat, n\u00eb m\u00ebnyr\u00eb t\u00eb p\u00ebrgjithshme - \"Sinteza si nj\u00eb nga metodat p\u00ebr p\u00ebrmir\u00ebsimin e performanc\u00ebs s\u00eb PostgreSQL<\/a><\/noindex>\u00bb<\/p>\n

P\u00ebrse gjith kjo m\u00eb intereson?<\/h2>\n

\nE para, p\u00ebr t\u00eb mos harruar, duke kujtuar dit\u00ebt e bukura n\u00eb pension.
\nE dyta, p\u00ebr t\u00eb sistematizuar at\u00eb q\u00eb kam shkruar. Sepse ndonj\u00ebher\u00eb vet\u00eb, filloj t\u00eb ngat\u00ebrrohem dhe harroj disa pjes\u00eb. <\/p>\n

E dhe e r\u00ebnd\u00ebsishmja \u2014 ndoshta do t'i ndihmoj\u00eb dikujt t\u00eb mos shpik\u00eb rrot\u00ebn dhe t\u00eb mos p\u00eblcitet me t\u00eb. Me fjal\u00eb t\u00eb tjera, t\u00eb p\u00ebrmir\u00ebsoj\u00eb karma e tij (jo ajo e Habr). Sepse, gj\u00ebja m\u00eb e \u00e7muar n\u00eb k\u00ebt\u00eb bot\u00eb jan\u00eb idet\u00eb. E r\u00ebnd\u00ebsishme \u00ebsht\u00eb t\u00eb gjejm\u00eb nj\u00eb ide. Realizimi i saj n\u00eb realitet \u00ebsht\u00eb nj\u00eb \u00e7\u00ebshtje strikt teknike.<\/p>\n

Pra, le t\u00eb fillojm\u00eb, ngadal\u00eb...<\/p>\n

Formulimi i detyr\u00ebs.<\/h2>\n

<\/p>\n

Shk\u00ebputja p\u00ebrmbledh\u00ebse: <\/h3>\n

\nBaza e t\u00eb dh\u00ebnave PostgreSQL (10.5), me ngarkes\u00eb t\u00eb p\u00ebrzier (OLTP+DSS), me ngarkes\u00eb mesatare-t\u00eb vog\u00ebl, e vendosur n\u00eb cloud AWS.
\nMonitorimi i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave mungon, monitorimi i infrastruktur\u00ebs \u00ebsht\u00eb i p\u00ebrfaq\u00ebsuar nga mjetet standarde t\u00eb AWS n\u00eb konfigurimin minimal.<\/p>\n

K\u00ebrkohet:<\/h3>\n

\nT\u00eb monitorosh performanc\u00ebn dhe gjendjen e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave, t\u00eb gjesh dhe t\u00eb kesh informacion fillestar p\u00ebr optimizimin e k\u00ebrkesave t\u00eb r\u00ebnda ndaj DB.
\n<\/a><\/noindex><\/p>\n

Nj\u00eb parath\u00ebnie e shkurt\u00ebr ose analiza e mund\u00ebsive t\u00eb zgjidhjes<\/h2>\n

\nFillimisht, do t\u00eb p\u00ebrpiqemi t\u00eb analizojm\u00eb mund\u00ebsit\u00eb p\u00ebr zgjidhjen e detyr\u00ebs nga k\u00ebndv\u00ebshtrimi i analiz\u00ebs krahasuese t\u00eb p\u00ebrfitimeve dhe shqet\u00ebsimeve p\u00ebr inxhinierin, nd\u00ebrsa p\u00ebrfitimet dhe humbjet p\u00ebr menaxhimin le t\u00eb merret me ato q\u00eb i takon sipas orarit t\u00eb pun\u00ebs.<\/p>\n

Mund\u00ebsia 1 - \u00abPun\u00eb n\u00eb k\u00ebrkes\u00eb\u00bb<\/h3>\n

\nD\u00ebrrmojm\u00eb gjith\u00e7ka si\u00e7 \u00ebsht\u00eb. N\u00ebse klienti nuk \u00ebsht\u00eb i k\u00ebnaqur me di\u00e7ka n\u00eb funksionimin, performanc\u00ebn e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave ose aplikacionit, ai do t'i njoftoj\u00eb inxhinier\u00ebt DBA p\u00ebrmes e-mailit ose duke krijuar nj\u00eb incident n\u00eb sistemin e bileta.
\nInxhinieri, pasi t\u00eb marr\u00eb njoftimin, do t\u00eb hetoj\u00eb problemin, do t\u00eb propozoj\u00eb nj\u00eb zgjidhje ose do ta shtyj\u00eb problemin p\u00ebr m\u00eb von\u00eb, duke shpresuar q\u00eb gjith\u00e7ka t\u00eb zgjidhet vet\u00eb, dhe gjithsesi, shum\u00eb shpejt do t\u00eb harrohet.
\nP\u00ebrsh\u00ebndetje dhe bullgura, kall\u00ebzime dhe goditje<\/b>P\u00ebrsh\u00ebndetje dhe bullgura:<\/b>
\n1. Nuk nevojitet t\u00eb b\u00ebni ndonj\u00eb gj\u00eb t\u00eb tep\u00ebrt
\n2. Gjithmon\u00eb ka mund\u00ebsi p\u00ebr t'u justifikuar dhe p\u00ebr t\u00eb shp\u00ebtuar nga puna.
\n3. Nj\u00eb shum\u00eb koh\u00eb q\u00eb mund t\u00eb shpenzohet sipas d\u00ebshir\u00ebs.
\nKall\u00ebzime dhe goditje:<\/b>
\n1. Von\u00eb a her\u00ebt, klienti do t\u00eb mendoj\u00eb p\u00ebr qenien dhe drejt\u00ebsin\u00eb universale n\u00eb k\u00ebt\u00eb bot\u00eb dhe do t\u00eb pyes\u00eb veten p\u00ebrs\u00ebri \u2014 p\u00ebr \u00e7far\u00eb po paguaj parat\u00eb e tij? Pasojat gjithmon\u00eb jan\u00eb t\u00eb nj\u00ebjta \u2014 pyetje \u00ebsht\u00eb vet\u00ebm kur klienti do t\u00eb m\u00ebrzitet dhe do t\u00eb thot\u00eb lamtumir\u00eb. Dhe burimi do t\u00eb zbrazet. Kjo \u00ebsht\u00eb e trishtueshme.
\n2. Zhvillimi i inxhinierit \u2014 zero.
\n3. V\u00ebshtir\u00ebsi n\u00eb planifikimin e pun\u00ebs dhe ngarkes\u00ebs <\/p>\n

Opsioni 2 - \"K\u00ebrkojm\u00eb me tambur, shesim dhe veshim\"<\/h3>\n

\nPika 1<\/b>-Pse na duhet nj\u00eb sistem monitorimi, ne do t\u00eb marrim gjith\u00e7ka p\u00ebrmes k\u00ebrkesave. D\u00ebrgojm\u00eb nj\u00eb s\u00ebr\u00eb k\u00ebrkesash n\u00eb fjalorin e t\u00eb dh\u00ebnave dhe prezantimet dinamike, aktivizojm\u00eb llogarit\u00ebs t\u00eb ndrysh\u00ebm, p\u00ebrmbledhim gjith\u00e7ka n\u00eb tabela, dhe p\u00ebr nj\u00eb koh\u00eb t\u00eb shkurt\u00ebr analizojm\u00eb listat dhe tabelat. Si rezultat, kemi grafike t\u00eb bukura ose jo aq t\u00eb bukura, tabela, raporte. E r\u00ebnd\u00ebsishmja \u00ebsht\u00eb q\u00eb t\u00eb kemi sa m\u00eb shum\u00eb, sa m\u00eb shum\u00eb.
\nPika 2<\/b>-Gjenerojm\u00eb aktivitet \u2013 fillojm\u00eb analiz\u00ebn e gjith\u00e7kaje.
\nPika 3<\/b>-P\u00ebrgatitemi nj\u00eb dokument, e quajm\u00eb k\u00ebt\u00eb dokument, thjesht - \u2018si t\u00eb organizojm\u00eb baz\u00ebn e t\u00eb dh\u00ebnave\u2019.
\nPika 4<\/b>-Konsumatori, duke par\u00eb gjith\u00eb k\u00ebt\u00eb madh\u00ebshti grafik\u00ebsh dhe shifrash, ndodhet n\u00eb nj\u00eb besim naiv f\u00ebmij\u00ebror - ja, tani gjith\u00e7ka do t\u00eb punoj\u00eb. Ai leht\u00ebsisht dhe pa dhimbje l\u00eb vizitat e tij financiare. Menaxhmenti gjithashtu \u00ebsht\u00eb i sigurt - inxhinier\u00ebt tan\u00eb po punojn\u00eb fort. Ngarkesa \u00ebsht\u00eb maksimale.
\nPika 5<\/b>-Rip\u00ebrs\u00ebrisni rregullisht Pik\u00ebn 1.
\nP\u00ebrsh\u00ebndetje dhe bullgura, kall\u00ebzime dhe goditje<\/b>P\u00ebrsh\u00ebndetje dhe bullgura: <\/b>
\n1. Jeta e menaxher\u00ebve dhe inxhinier\u00ebve \u00ebsht\u00eb e thjesht\u00eb, e parashikueshme dhe e mbushur me aktivitet. Gjith\u00e7ka b buzzing, t\u00eb gjith\u00eb jan\u00eb t\u00eb z\u00ebn\u00eb.
\n2. Jeta e klientit \u00ebsht\u00eb gjithashtu e mir\u00eb \u2014 ai gjithmon\u00eb \u00ebsht\u00eb i sigurt se duhet t\u00eb pres\u00eb pak m\u00eb shum\u00eb dhe gjith\u00e7ka do t\u00eb rregullohet. N\u00ebse nuk rregullohet, \u00e7far\u00eb t\u00eb b\u00ebjm\u00eb \u2014 ky \u00ebsht\u00eb nj\u00eb bot\u00eb e padrejt\u00eb, ndoshta n\u00eb jet\u00ebn e ardhshme do t\u00eb ken\u00eb fat.
\nKall\u00ebzime dhe goditje:<\/b>
\n1. Von\u00eb a her\u00ebt, do t\u00eb gjendet nj\u00eb ofrues m\u00eb i shpejt\u00eb i sh\u00ebrbimeve t\u00eb ngjashme, i cili do t\u00eb b\u00ebj\u00eb t\u00eb nj\u00ebjt\u00ebn gj\u00eb, por pak m\u00eb lir\u00eb. Dhe n\u00ebse rezultati \u00ebsht\u00eb i nj\u00ebjt\u00eb, pse t\u00eb paguash m\u00eb shum\u00eb. Kjo p\u00ebrs\u00ebri do t\u00eb \u00e7oj\u00eb n\u00eb zhdukjen e burimeve.
\n2. \u00cbsht\u00eb e m\u00ebrzitshme. Si \u00e7do aktivitet i paq\u00ebndruesh\u00ebm, q\u00eb ka pak kuptim.
\n3. Si n\u00eb variantin e m\u00ebparsh\u00ebm \u2014 nuk ka asnj\u00eb zhvillim. Por p\u00ebr inxhinierin, shqet\u00ebsimi \u00ebsht\u00eb se, ndryshe nga varianti i par\u00eb, k\u00ebtu duhet t\u00eb gjenerosh vazhdimisht t\u00eb dh\u00ebnat e mbrojtjes. Kjo merr koh\u00eb. E cila mund t\u00eb p\u00ebrdoret p\u00ebr dobi personale. Sepse n\u00ebse nuk kujdesesh p\u00ebr veten, askush tjet\u00ebr nuk do t\u00eb kujdeset p\u00ebr ty.<\/p>\n

Varianti 3\u2014Nuk \u00ebsht\u00eb e nevojshme t\u00eb shpikni bi\u00e7iklet\u00ebn, duhet ta blini at\u00eb dhe t\u00eb ridezoni.<\/h3>\n

\nInxhinier\u00ebt e kompanive t\u00eb tjera nuk e b\u00ebjn\u00eb pa shkak q\u00eb t\u00eb han\u00eb pizz\u00eb duke e shoq\u00ebruar me birr\u00eb (ah, koh\u00eb t\u00eb bukura n\u00eb Sh\u00ebn Petersburg n\u00eb vitet '90). Le t\u00eb p\u00ebrdorim sisteme monitorimi q\u00eb jan\u00eb krijuar, t\u00eb testuar dhe funksionojn\u00eb, dhe q\u00eb sjellin ndihm\u00eb, qoft\u00eb edhe minimalisht p\u00ebr krijuesit t\u00eb tyre.
\nP\u00ebrsh\u00ebndetje dhe bullgura, kall\u00ebzime dhe goditje<\/b>P\u00ebrsh\u00ebndetje dhe bullgura:<\/b>
\n1. Nuk e nevojitet t\u00eb humbni koh\u00eb duke menduar p\u00ebr at\u00eb q\u00eb tashm\u00eb \u00ebsht\u00eb menduar. Merrni dhe p\u00ebrdorni.
\n2. Sistemet e monitorimit nuk jan\u00eb krijuar nga budallenj dhe, sigurisht, ato jan\u00eb t\u00eb dobishme.
\n3. Sistemet e monitorimit q\u00eb funksionojn\u00eb n\u00eb p\u00ebrgjith\u00ebsi ofrojn\u00eb informacion t\u00eb filtruar dhe t\u00eb dobish\u00ebm.
\nKall\u00ebzime dhe goditje:<\/b>
\n1. Inxhinieri n\u00eb k\u00ebt\u00eb rast nuk \u00ebsht\u00eb nj\u00eb inxhinier, por thjesht nj\u00eb p\u00ebrdorues i produktit t\u00eb t\u00eb tjer\u00ebve. Ose nj\u00eb p\u00ebrdorues.
\n2. Klienti duhet t\u00eb bindet p\u00ebr nevoj\u00ebn p\u00ebr t\u00eb bler\u00eb di\u00e7ka p\u00ebr t\u00eb cil\u00ebn ai nuk d\u00ebshiron t\u00eb kuptoj\u00eb, dhe as nuk duhet, dhe buxheti p\u00ebr vitin \u00ebsht\u00eb miratuar dhe nuk do t\u00eb ndryshoj\u00eb. M\u00eb pas, duhen ndar\u00eb burime t\u00eb ve\u00e7anta, t\u00eb konfigurohen p\u00ebr sistemin e caktuar. Pra, fillimisht duhet t\u00eb paguani, t\u00eb paguani dhe p\u00ebrs\u00ebri t\u00eb paguani. Dhe klienti \u00ebsht\u00eb i kursyer. Kjo \u00ebsht\u00eb norma e jet\u00ebs.<\/p>\n

\u00c7far\u00eb t\u00eb b\u00ebjm\u00eb - \u00c7ernysh\u00ebvski? Pyetja jote \u00ebsht\u00eb shum\u00eb n\u00eb vend. (c)<\/h2>\n

\nN\u00eb k\u00ebt\u00eb rast t\u00eb ve\u00e7ant\u00eb dhe n\u00eb situat\u00ebn e krijuar, mund t\u00eb veprojm\u00eb pak ndryshe \u2014 le t\u00eb b\u00ebjm\u00eb sistemin ton\u00eb t\u00eb monitorimit. <\/b>
\n\"Monitorimi
\nSigurisht, nuk \u00ebsht\u00eb nj\u00eb sistem n\u00eb kuptimin e plot\u00eb t\u00eb fjal\u00ebs, kjo do t\u00eb ishte shum\u00eb e z\u00ebshme dhe vet\u00ebbesuese, por ndonj\u00eb m\u00ebnyr\u00eb p\u00ebr ta lehtesuar veten dhe p\u00ebr t\u00eb mbledhur m\u00eb shum\u00eb informacion p\u00ebr zgjidhjen e incidenteve t\u00eb performanc\u00ebs. P\u00ebr t\u00eb mos u gjetur n\u00eb situat\u00ebn - \"shkonte andej nuk di ku, gjeje at\u00eb, nuk di \u00e7far\u00eb.\"<\/p>\n

Cilat jan\u00eb p\u00ebrfitimet dhe disavantazhet e k\u00ebtij varianti:<\/h4>\n

Avantazhet:<\/b>
\n1. \u00cbsht\u00eb interesante. T\u00eb pakt\u00ebn \u00ebsht\u00eb m\u00eb interesante sesa vazhdim\u00ebsia \"shrink datafile, alter tablespace, etj.\"
\n2. K\u00ebto jan\u00eb aft\u00ebsi t\u00eb reja dhe zhvillim i ri. \u00c7ka n\u00eb perspektiv\u00eb do t\u00eb sjell\u00eb her\u00ebt a von\u00eb shp\u00ebrblime t\u00eb merituara.
\nDisavantazhet:<\/b>
\n1. Do t\u00eb duhet t\u00eb punosh. T\u00eb punosh shum\u00eb.
\n2. Do t\u00eb duhet rregullisht t\u00eb shpjegosh kuptimin dhe perspektivat e t\u00eb gjith\u00eb aktivitetit.
\n3. Do t\u00eb duhet t\u00eb sakrifikosh ndonj\u00eb gj\u00eb, sepse burimi i vet\u00ebm i disponuesh\u00ebm p\u00ebr inxhinierin - koha - \u00ebsht\u00eb e kufizuar nga Universi.
\n4. Gj\u00ebja m\u00eb e frikshme dhe m\u00eb e pak\u00ebndshme<\/b> \u00ebsht\u00eb se n\u00eb rezultat mund t\u00eb dal\u00eb di\u00e7ka si \"As nj\u00eb mi, as nj\u00eb bretkoc\u00eb, por nj\u00eb krijes\u00eb e panjohur.\"<\/p>\n

Kush nuk rrezikon, nuk pi shampanj\u00eb.<\/b>
\nPra, fillon pjesa m\u00eb interesante.<\/p>\n

Ideja e p\u00ebrgjithshme \u00ebsht\u00eb n\u00eb m\u00ebnyr\u00eb skematike<\/h2>\n

\n\"Monitorimi
\n(Imazhi \u00ebsht\u00eb marr\u00eb nga artikulli<\/i> \u00abSinteza si nj\u00eb nga metodat p\u00ebr p\u00ebrmir\u00ebsimin e performanc\u00ebs s\u00eb PostgreSQL<\/a><\/noindex>\u00bb)<\/p>\n

Shpjegim:<\/p>\n