{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimizimi i k\u00ebrkesave t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb shembujt B2B p\u00ebr nd\u00ebrtuesit","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Si t\u00eb rritet 10 her\u00eb numri i k\u00ebrkesave n\u00eb DB pa kaluar n\u00eb nj\u00eb server m\u00eb t\u00eb fuqish\u00ebm dhe t\u00eb ruhet funksionaliteti i sistemit? Do t\u00eb flas p\u00ebr m\u00ebnyrat si luftuam me r\u00ebnien e performanc\u00ebs s\u00eb baz\u00ebs son\u00eb t\u00eb t\u00eb dh\u00ebnave, si e optimizuam SQL k\u00ebrkesat p\u00ebr t\u00eb sh\u00ebrbyer sa m\u00eb shum\u00eb p\u00ebrdorues dhe p\u00ebr t\u00eb mos rritur shpenzimet p\u00ebr burimet llogaritore.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nUn\u00eb po zhvilloj nj\u00eb sh\u00ebrbim p\u00ebr menaxhimin e proceseve biznesore n\u00eb kompanit\u00eb e nd\u00ebrtimit. Me ne punojn\u00eb rreth 3 mij\u00eb kompani. M\u00eb shum\u00eb se 10 mij\u00eb njer\u00ebz punojn\u00eb \u00e7do dit\u00eb me sistemin ton\u00eb p\u00ebr 4-10 or\u00eb. Ai zgjidh nj\u00eb gam\u00eb t\u00eb gjer\u00eb detyrash planifikimi, njoftimi, paralajm\u00ebrimi, validimi... Ne p\u00ebrdorim PostgreSQL 9.6. N\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave kemi rreth 300 tabela dhe \u00e7do dit\u00eb pranojm\u00eb deri n\u00eb 200 milion k\u00ebrkesa (10 mij\u00eb t\u00eb ndryshme). N\u00eb mesatare, kemi 3-4 mij\u00eb k\u00ebrkesa p\u00ebr sekond\u00eb, n\u00eb momentet m\u00eb aktive m\u00eb shum\u00eb se 10 mij\u00eb k\u00ebrkesa p\u00ebr sekond\u00eb. Pjesa m\u00eb e madhe e k\u00ebrkesave \u00ebsht\u00eb OLAP. Shtesat, modifikimet dhe fshirjet jan\u00eb shum\u00eb m\u00eb pak, q\u00eb do t\u00eb thot\u00eb se ngarkesa OLTP \u00ebsht\u00eb relativisht e vog\u00ebl. T\u00eb gjitha k\u00ebto numra i p\u00ebrmenda, q\u00eb t\u00eb mund t\u00eb vler\u00ebsoni shtrirjen e projektit ton\u00eb dhe t\u00eb kuptoni se sa e r\u00ebnd\u00ebsishme mund t\u00eb jet\u00eb p\u00ebr ju p\u00ebrvoja jon\u00eb.<\/p>\n<h3>Piktura e par\u00eb. Lirike<\/h3>\n<p>\nKur filluam zhvillimin, nuk menduam shum\u00eb p\u00ebr ngarkes\u00ebn q\u00eb do t\u00eb r\u00ebndonte mbi DB dhe \u00e7far\u00eb do t\u00eb b\u00ebnim n\u00ebse serveri do t\u00eb ndalte s\u00eb funksionuari. Gjat\u00eb projektimit t\u00eb DB ndjek\u00ebm rekomandimet e p\u00ebrgjithshme dhe u munduam t\u00eb mos na b\u00ebjm\u00eb vet\u00eb t\u00eb k\u00ebqija, por nuk shkuam p\u00ebrtej k\u00ebshillave t\u00eb zakonshme si \"mos p\u00ebrdorni modelin <noindex><a rel=\"nofollow\" href=\"https:\/\/en.wikipedia.org\/wiki\/Entity%E2%80%93attribute%E2%80%93value_model\">Entity Attribute Values<\/a><\/noindex> ne nuk shkuam. E projektuam duke u bazuar n\u00eb parimet e normalizimit duke evituar tepric\u00ebn e t\u00eb dh\u00ebnave dhe nuk u shqet\u00ebsuam p\u00ebr shpejtimin e k\u00ebrkesave t\u00eb ndryshme. Sapo erdh\u00ebn p\u00ebrdoruesit e par\u00eb, u p\u00ebrball\u00ebm me problemin e performanc\u00ebs. Ashtu si zakonisht, ishim krejt\u00ebsisht t\u00eb pap\u00ebrgatitur p\u00ebr k\u00ebt\u00eb. Problemet e para ishin t\u00eb thjeshta. Si rregull, gjith\u00e7ka zgjidhej duke shtuar nj\u00eb indeks t\u00eb ri. Por erdhi nj\u00eb moment kur zgjidhjet e thjeshta nuk funksiononin m\u00eb. Kur kuptuam se na mungonte p\u00ebrvoja dhe po b\u00ebhej gjithnj\u00eb e m\u00eb e v\u00ebshtir\u00eb t\u00eb kuptonim shkakun e problemeve, angazhova ekspert\u00eb q\u00eb na ndihmuan t\u00eb konfigurojm\u00eb si\u00e7 duhet serverin, t\u00eb lidhim monitorimin dhe na treguan se ku t\u00eb shihnim p\u00ebr t\u00eb marr\u00eb <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/9.6\/pgstatstatements.html\">statistika<\/a><\/noindex>.<\/p>\n<h3>Piktura e dyt\u00eb. Statistike<\/h3>\n<p>\nK\u00ebshtu q\u00eb kemi rreth 10 mij\u00eb k\u00ebrkesa t\u00eb ndryshme q\u00eb kryhen n\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave \u00e7do dit\u00eb. Nga k\u00ebto 10 mij\u00eb, ka disa monst\u00ebr q\u00eb kryhen 2-3 milion her\u00eb me nj\u00eb koh\u00eb mesatare ekzekutimi prej 0.1-0.3 ms dhe ka k\u00ebrkesa me nj\u00eb koh\u00eb mesatare ekzekutimi prej 30 sekondash, t\u00eb cilat thirren 100 her\u00eb n\u00eb dit\u00eb.<\/p>\n<p>Optimizimi i t\u00eb gjitha 10 mij\u00eb k\u00ebrkesave nuk ishte i mundur, prandaj vendos\u00ebm t\u00eb kuptojm\u00eb se ku t\u00eb drejtojm\u00eb p\u00ebrpjekjet p\u00ebr t\u00eb rritur performanc\u00ebn e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb m\u00ebnyr\u00eb t\u00eb drejt\u00eb. Pas disa iteracionesh, filluam t\u00eb ndan\u00eb k\u00ebrkesat n\u00eb lloje.<\/p>\n<h4>K\u00ebrkesat TOP<\/h4>\n<p>\nK\u00ebto jan\u00eb k\u00ebrkesat m\u00eb t\u00eb r\u00ebnda, t\u00eb cilat z\u00ebn\u00eb m\u00eb shum\u00eb koh\u00eb (koha totale). K\u00ebto jan\u00eb k\u00ebrkesa q\u00eb ose thirren shum\u00eb shpesh ose k\u00ebrkesa q\u00eb ekzekutohen shum\u00eb ngadal\u00eb (k\u00ebrkesat e ngadalta dhe t\u00eb shpeshta jan\u00eb optimizuar q\u00eb n\u00eb iteracionet e para p\u00ebr shpejt\u00ebsin\u00eb). Si rezultat, serveri shpenzon m\u00eb shum\u00eb koh\u00eb p\u00ebr ekzekutimin e tyre. \u00cbsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb ndahen k\u00ebrkesat kryesore sipas koh\u00ebs totale t\u00eb ekzekutimit dhe ve\u00e7mas sipas koh\u00ebs IO. Metodat e optimizimit t\u00eb k\u00ebtyre k\u00ebrkesave jan\u00eb pak t\u00eb ndryshme.<\/p>\n<p>Praktika e zakonshme e t\u00eb gjitha kompanive - t\u00eb punojn\u00eb me k\u00ebrkesat TOP. Ato jan\u00eb t\u00eb pakta, optimizimi i madhe t\u00eb pakt\u00ebn nj\u00eb k\u00ebrkese mund t\u00eb liroj\u00eb 5-10% t\u00eb burimeve. Megjithat\u00eb, nd\u00ebrsa projekti \u201crritet\u201d, optimizimi i k\u00ebrkesave TOP b\u00ebhet nj\u00eb detyr\u00eb gjithnj\u00eb e m\u00eb e komplikuar. T\u00eb gjitha metodat e thjeshta jan\u00eb zbatuar tashm\u00eb, dhe vet\u00eb k\u00ebrkesa m\u00eb \u201ce r\u00ebnd\u00eb\u201d ndalon \u201cvet\u00ebm\u201d 3-5% t\u00eb burimeve. N\u00ebse k\u00ebrkesat TOP n\u00eb total z\u00ebn\u00eb m\u00eb pak se 30-40% t\u00eb koh\u00ebs, at\u00ebher\u00eb me siguri keni b\u00ebr\u00eb p\u00ebrpjekje q\u00eb ato t\u00eb punojn\u00eb shpejt dhe ka ardhur koha p\u00ebr t\u00eb kaluar n\u00eb optimizimin e k\u00ebrkesave nga grupi tjet\u00ebr.<br \/>\nTani mbetet t\u00eb p\u00ebrgjigjemi n\u00eb pyetjen se sa k\u00ebrkesa kryesore t\u00eb p\u00ebrfshijm\u00eb n\u00eb k\u00ebt\u00eb grup. Un\u00eb zakonisht marr jo m\u00eb pak se 10, por jo m\u00eb shum\u00eb se 20. Mundohem q\u00eb koha e ekzekutimit t\u00eb par\u00eb dhe t\u00eb fundit n\u00eb grupin TOP t\u00eb ndryshoj\u00eb jo m\u00eb shum\u00eb se 10 her\u00eb. Pra, n\u00ebse koha e ekzekutimit t\u00eb k\u00ebrkesave bie ndjesh\u00ebm nga vendi i par\u00eb n\u00eb t\u00eb dhjetin, at\u00ebher\u00eb marr TOP-10, n\u00ebse r\u00ebnia \u00ebsht\u00eb m\u00eb e but\u00eb, at\u00ebher\u00eb e rris madh\u00ebsin\u00eb e grupit n\u00eb 15 ose 20.<br \/>\n<img decoding=\"async\" alt=\"Optimizimi i k\u00ebrkesave t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb shembujt B2B p\u00ebr nd\u00ebrtuesit\" src=\"\/wp-content\/uploads\/2019\/08\/2a9d9e6053d1aebb71aa213757bd2393.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<\/p>\n<h4>K\u00ebrkesat mesatare (medium)<\/h4>\n<p>\nAto jan\u00eb t\u00eb gjitha k\u00ebrkesat q\u00eb vijn\u00eb menj\u00ebher\u00eb pas TOP-it, p\u00ebrve\u00e7 5-10% t\u00eb fundit. Zakonisht, optimizimi i k\u00ebtyre k\u00ebrkesave p\u00ebrmban mund\u00ebsin\u00eb p\u00ebr t\u00eb ngritur ndjesh\u00ebm performanc\u00ebn e serverit. K\u00ebto k\u00ebrkesa mund t\u00eb \u201cpeshojn\u00eb\u201d deri n\u00eb 80%. Por edhe n\u00ebse pjes\u00ebmarrja e tyre kalon 50%, at\u00ebher\u00eb \u00ebsht\u00eb koha p\u00ebr t\u2019i shqyrtuar ato m\u00eb me kujdes.<\/p>\n<h4>Bisht (tail)<\/h4>\n<p>\nSi e tha, k\u00ebto k\u00ebrkesa vijn\u00eb n\u00eb fund dhe k\u00ebrkojn\u00eb 5-10% t\u00eb koh\u00ebs. Mund t'i harrosh ato, n\u00ebse nuk po p\u00ebrdor mjete automatike p\u00ebr analizimin e k\u00ebrkesave, at\u00ebher\u00eb optimizimi i tyre gjithashtu mund t\u00eb jet\u00eb i lir\u00eb.<\/p>\n<p>Si t\u00eb vler\u00ebsosh \u00e7do grup?<\/p>\n<p>Un\u00eb p\u00ebrdor nj\u00eb k\u00ebrkes\u00eb SQL q\u00eb ndihmon n\u00eb vler\u00ebsimin e till\u00eb p\u00ebr PostgreSQL (jam i sigurt q\u00eb p\u00ebr shum\u00eb DBMS t\u00eb tjera mund t\u00eb shkruhet nj\u00eb k\u00ebrkes\u00eb e ngjashme)<\/p>\n<p><b class=\"spoiler_title\">K\u00ebrkesa SQL p\u00ebr vler\u00ebsimin e madh\u00ebsis\u00eb s\u00eb grupeve TOP-MEDIUM-TAIL<\/b><\/p>\n<pre><code class=\"sql\">SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail)\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800              THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n<p>Rezultati i k\u00ebrkes\u00ebs - tre kolona, secila prej t\u00eb cilave p\u00ebrmban p\u00ebrqindjen e koh\u00ebs q\u00eb shpenzohet p\u00ebr p\u00ebrpunimin e k\u00ebrkesave nga ky grup. Brenda k\u00ebrkes\u00ebs ndodhen dy numra (n\u00eb rastin tim, k\u00ebto jan\u00eb 20 dhe 800), q\u00eb ndajn\u00eb k\u00ebrkesat e nj\u00eb grupi nga tjetri.<\/p>\n<p>K\u00ebshtu, p\u00ebrfshirja e k\u00ebrkesave n\u00eb fillim t\u00eb pun\u00ebs p\u00ebr optimizim dhe tani, \u00ebsht\u00eb rreth k\u00ebsaj.<\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i k\u00ebrkesave t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb shembujt B2B p\u00ebr nd\u00ebrtuesit\" src=\"\/wp-content\/uploads\/2019\/08\/9c70ad9aba8b835c94729c9dda656eab.jpg\" style=\"display:block;margin: 0 auto;\" \/><br \/>\n<br \/>\nNga diagrami, duket se p\u00ebrqindja e k\u00ebrkesave TOP u ul ndjesh\u00ebm, nd\u00ebrsa u rrit\u00ebn \"mesatar\u00ebt\".<br \/>\nFillimisht, n\u00eb k\u00ebrkesat TOP hynin gabime t\u00eb hapura. Me kalimin e koh\u00ebs, t\u00eb s\u00ebmurat e f\u00ebmij\u00ebris\u00eb u zhduk\u00ebn, p\u00ebrqindja e k\u00ebrkesave TOP u pak\u00ebsua, duke k\u00ebrkuar p\u00ebrpjekje gjithnj\u00eb e m\u00eb t\u00eb m\u00ebdha p\u00ebr t\u00eb p\u00ebrshpejtuar k\u00ebrkesat e r\u00ebnda. <\/p>\n<p><b class=\"spoiler_title\">P\u00ebr t\u00eb marr\u00eb tekstet e k\u00ebrkesave, ne p\u00ebrdorim k\u00ebt\u00eb k\u00ebrkes\u00eb.<\/b><\/p>\n<pre><code class=\"sql\">SELECT * FROM (\n  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n<p>Ja lista e teknikave m\u00eb t\u00eb p\u00ebrdorura q\u00eb na ndihmuan t\u00eb shpejtojm\u00eb k\u00ebrkesat TOP:<\/p>\n<ul>\n<li>Ridisajnimi i sistemit, p\u00ebr shembull, ri-nd\u00ebrton logjik\u00ebn e njoftimeve n\u00eb broker mesazh n\u00eb vend t\u00eb k\u00ebrkesave periodike n\u00eb DB.<\/li>\n<li>Shtimi ose ndryshimi i indekseve.<\/li>\n<li>Rishkrimi i k\u00ebrkesave ORM n\u00eb SQL t\u00eb past\u00ebr.<\/li>\n<li>Rishkrimi i logjik\u00ebs s\u00eb ngarkimit lenjues t\u00eb t\u00eb dh\u00ebnave.<\/li>\n<li>Keshimi p\u00ebrmes denormalizimit t\u00eb t\u00eb dh\u00ebnave. P\u00ebr shembull, ne kemi lidhjen e tabelave Transporti -&gt; Llogaria -&gt; K\u00ebrkesa -&gt; Aplikimi. Kjo do t\u00eb thot\u00eb se \u00e7do transport \u00ebsht\u00eb i lidhur me aplikimin p\u00ebrmes tabelave t\u00eb tjera. P\u00ebr t\u00eb mos lidhur t\u00eb gjitha tabelat n\u00eb \u00e7do k\u00ebrkes\u00eb, ne e kopjojm\u00eb lidhjen me aplikimin n\u00eb tabel\u00ebn e Transportit.<\/li>\n<li>Marrja e t\u00eb dh\u00ebnave statike nga tabela me referenca dhe tabela q\u00eb ndryshojn\u00eb rrall\u00eb n\u00eb memorien e programit.<\/li>\n<\/ul>\n<p>\nNdonj\u00ebher\u00eb, ndryshimet k\u00ebrkonin nj\u00eb rip\u00ebrdorim t\u00eb konsideruesh\u00ebm, por jepnin nj\u00eb shkarkim prej 5-10% t\u00eb sistemit dhe ishin t\u00eb justifikuara. Me kalimin e koh\u00ebs, rezultati b\u00ebhej gjithnj\u00eb e m\u00eb i vog\u00ebl, dhe rip\u00ebrdorimi k\u00ebrkonte nj\u00eb p\u00ebrmir\u00ebsim m\u00eb serioz.<\/p>\n<p>At\u00ebher\u00eb ne kemi v\u00ebn\u00eb re grupin e dyt\u00eb t\u00eb k\u00ebrkesave - grupin e t\u00eb mes\u00ebm. N\u00eb k\u00ebt\u00eb grup kishte shum\u00eb m\u00eb tep\u00ebr k\u00ebrkesa dhe dukej se do t\u00eb shkonte shum\u00eb koh\u00eb p\u00ebr t\u00eb analizuar t\u00ebr\u00eb grupin. Megjithat\u00eb, shumica e k\u00ebrkesave ishin shum\u00eb t\u00eb thjeshta p\u00ebr t'u optimizuar dhe shum\u00eb probleme p\u00ebrs\u00ebriteshin dhjet\u00ebra her\u00eb n\u00eb variacione t\u00eb ndryshme. Ja disa shembuj t\u00eb optimizimeve tipike q\u00eb ne aplikonim p\u00ebr dhjet\u00ebra k\u00ebrkesa t\u00eb ngjashme dhe \u00e7do grup i k\u00ebrkesave t\u00eb optimizuara shkarkonte DB-n\u00eb me 3-5%.<\/p>\n<ul>\n<li> N\u00eb vend t\u00eb kontrollit t\u00eb pranis\u00eb s\u00eb regjistrimeve duke p\u00ebrdorur COUNT dhe skanimin e plot\u00eb t\u00eb tabel\u00ebs, filluam t\u00eb p\u00ebrdornim EXISTS.\n <\/li>\n<li>Na u hoq DISTINCT (nuk ka nj\u00eb recet\u00eb t\u00eb p\u00ebrbashk\u00ebt, por ndonj\u00ebher\u00eb mund t\u00eb hiqet leht\u00ebsisht, duke p\u00ebrshpejtuar k\u00ebrkes\u00ebn nga 10-100 her\u00eb).\n<p>P\u00ebr shembull, n\u00eb vend t\u00eb k\u00ebrkes\u00ebs p\u00ebr marrjen e t\u00eb gjith\u00eb drejtuesve nga nj\u00eb tabel\u00eb e madhe d\u00ebrgimesh (DELIVERY) <\/p>\n<pre><code class=\"sql\">SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME\nFROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID\n<\/code><\/pre>\n<p>\nb\u00ebm\u00eb nj\u00eb k\u00ebrkes\u00eb n\u00eb nj\u00eb tabel\u00eb relativisht t\u00eb vog\u00ebl PERSON<\/p>\n<pre><code class=\"sql\">SELECT P.ID, P.FIRST_NAME, P.LAST_NAME\nFROM PERSON\nWHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)\n<\/code><\/pre>\n<p>\nDuket se ne p\u00ebrdor\u00ebm nj\u00eb n\u00ebnk\u00ebrkes\u00eb t\u00eb korreluar, por ajo jep nj\u00eb p\u00ebrshpejtim m\u00eb shum\u00eb se 10 her\u00eb.\n <\/li>\n<li>N\u00eb shum\u00eb raste e eliminuam plot\u00ebsisht COUNT dhe <br \/>\n<noindex><a rel=\"nofollow\" href=\"https:\/\/www.citusdata.com\/blog\/2016\/10\/12\/count-performance\/#dup_counts_estimated_filtered\">e z\u00ebvend\u00ebsuam me nj\u00eb llogaritje t\u00eb vler\u00ebs af\u00ebrsisht<\/a><\/noindex>\n <\/li>\n<li>n\u00eb vend t\u00eb\n<pre><code class=\"sql\">UPPER(s) LIKE JOHN% \n<\/code><\/pre>\n<p>\np\u00ebrdorim <\/p>\n<pre><code class=\"sql\">s ILIKE \u201cJohn%\u201d\n<\/code><\/pre>\n<p>\n <\/li>\n<\/ul>\n<p>\n\u00c7do k\u00ebrkes\u00eb specifike arriti t\u00eb p\u00ebrshpejtohej disa her\u00eb nga 3 deri n\u00eb 1000 her\u00eb. Pavar\u00ebsisht nga rezultatet mbres\u00ebl\u00ebn\u00ebse, n\u00eb fillim na duket se nuk kishte kuptim optimizimi i nj\u00eb k\u00ebrkese q\u00eb kryhet n\u00eb 10 ms, \u00ebsht\u00eb n\u00eb rreth 300 k\u00ebrkesat m\u00eb t\u00eb r\u00ebnda dhe n\u00eb p\u00ebrgjith\u00ebsi koh\u00ebn e ngarkes\u00ebs n\u00eb DB z\u00eb nj\u00eb p\u00ebrqindje t\u00eb vog\u00ebl. Por duke aplikuar t\u00eb nj\u00ebjt\u00ebn recet\u00eb p\u00ebr grupin e k\u00ebrkesave t\u00eb ngjashme ne arrinim t\u00eb p\u00ebrfitonim disa p\u00ebrqindje. P\u00ebr t\u00eb mos humbur koh\u00eb me kontrollin manual t\u00eb t\u00eb gjith\u00eb qindra k\u00ebrkesave, ne shkruam disa skript\u00eb t\u00eb thjesht\u00eb q\u00eb me ndihm\u00ebn e shprehjeve t\u00eb rregullta gjenin k\u00ebrkesa t\u00eb ngjashme. Si rezultat, k\u00ebrkimi automatizuar i grupeve t\u00eb k\u00ebrkesave na lejoi t\u00eb p\u00ebrmir\u00ebsonim edhe m\u00eb shum\u00eb performanc\u00ebn ton\u00eb, duke shpenzuar p\u00ebrpjekje modeste.<\/p>\n<p>Pas rezultatit, ne kemi punuar tashm\u00eb p\u00ebr tre vjet me t\u00eb nj\u00ebjt\u00ebn pajisje. Ngarkesa mesatare ditore \u00ebsht\u00eb rreth 30%, dhe n\u00eb pikat m\u00eb t\u00eb larta arrin deri n\u00eb 70%. Numri i k\u00ebrkesave ashtu si dhe numri i p\u00ebrdoruesve \u00ebsht\u00eb rritur rreth 10 her\u00eb. Dhe e gjith\u00eb kjo fal\u00eb monitorimit t\u00eb vazhduesh\u00ebm t\u00eb grupeve t\u00eb k\u00ebrkesave TOP-MEDIUM. Sa her\u00eb q\u00eb nj\u00eb k\u00ebrkes\u00eb e re shfaqet n\u00eb grupin TOP, ne e analizojm\u00eb menj\u00ebher\u00eb dhe p\u00ebrpiqemi ta p\u00ebrshpejtojm\u00eb. Grupin MEDIUM e shqyrtojm\u00eb nj\u00eb her\u00eb n\u00eb jav\u00eb duke p\u00ebrdorur skenar\u00eb analize. N\u00ebse hasim k\u00ebrkesa t\u00eb reja, t\u00eb cilat dihen si t\u00eb optimizohen, ne i ndryshojm\u00eb ato shpejt. Ndonj\u00ebher\u00eb gjejm\u00eb m\u00ebnyra t\u00eb reja optimizimi, t\u00eb cilat mund t\u00eb aplikohen menj\u00ebher\u00eb p\u00ebr disa k\u00ebrkesa. <\/p>\n<p>Sipas parashikimeve tona, serveri aktual do t\u00eb mbaj\u00eb rritjen e numrit t\u00eb p\u00ebrdoruesve edhe 3-5 her\u00eb t\u00eb tjera. Megjithat\u00eb, ne kemi edhe nj\u00eb as n\u00eb m\u00ebng\u00eb - nuk i kemi kaluar ende k\u00ebrkesat SELECT n\u00eb nj\u00eb pasqyr\u00eb, si\u00e7 rekomandohet. Por ne nuk e b\u00ebjm\u00eb k\u00ebt\u00eb me q\u00ebllim, pasi duam s\u00eb pari t\u00eb shfryt\u00ebzojm\u00eb deri n\u00eb fund mund\u00ebsit\u00eb e optimizimit 'inteligjent', para se t\u00eb aktivizojm\u00eb 'artillerin\u00eb e r\u00ebnd\u00eb'.<br \/>\nNj\u00eb v\u00ebshtrim kritik mbi pun\u00ebn e kryer mund t\u00eb sugjeroj\u00eb p\u00ebrdorimin e skalimit vertikal. T\u00eb blini nj\u00eb server m\u00eb t\u00eb fuqish\u00ebm, n\u00eb vend q\u00eb t\u00eb humbni koh\u00eb specialist\u00ebsh. Nj\u00eb server mund t\u00eb mos kushtoj\u00eb shum\u00eb, sidomos duke ditur se kufijt\u00eb e skalimit vertikal nuk jan\u00eb shfryt\u00ebzuar ende. Megjithat\u00eb, numri i k\u00ebrkesave \u00ebsht\u00eb rritur vet\u00ebm 10 her\u00eb. Pas disa vitesh, funksionaliteti i sistemit \u00ebsht\u00eb zgjeruar dhe tani ka m\u00eb shum\u00eb lloje k\u00ebrkesash. Funksionaliteti q\u00eb ishte, p\u00ebrmes memorizimit, kryhet me nj\u00eb num\u00ebr m\u00eb t\u00eb vog\u00ebl k\u00ebrkesash, dhe gjithashtu k\u00ebrkesa m\u00eb efektive. K\u00ebshtu, mund t\u00eb shum\u00ebzojm\u00eb me qet\u00ebsi edhe me 5, p\u00ebr t\u00eb marr\u00eb nj\u00eb koeficient t\u00eb v\u00ebrtet\u00eb shpejt\u00ebsie. Prandaj, sipas llogaritjeve m\u00eb modeste, mund t\u00eb thuhet se shpejt\u00ebsia \u00ebsht\u00eb rritur 50 her\u00eb e m\u00eb shum\u00eb. T\u00eb rris\u00ebsh vertikalisht serverin 50 her\u00eb do t\u00eb ishte m\u00eb e shtrenjt\u00eb. Sidomos duke pasur parasysh se nj\u00eb optimizim i kryer nj\u00eb her\u00eb punon gjithmon\u00eb, nd\u00ebrsa llogaria p\u00ebr serverin e marr\u00eb me qira vjen \u00e7do muaj.<br \/>\n<br \/>Burimi: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/461071\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b? \u042f \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0443, \u043a\u0430\u043a \u043c\u044b \u0431\u043e\u0440\u043e\u043b\u0438\u0441\u044c \u0441 \u043f\u0430\u0434\u0435\u043d\u0438\u0435\u043c \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u043d\u0430\u0448\u0435\u0439 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445, \u043a\u0430\u043a \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043b\u0438 SQL \u0437\u0430\u043f\u0440\u043e\u0441\u044b, \u0447\u0442\u043e\u0431\u044b \u043e\u0431\u0441\u043b\u0443\u0436\u0438\u0432\u0430\u0442\u044c \u043a\u0430\u043a \u043c\u043e\u0436\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u0435 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0438 \u043d\u0435 \u043f\u043e\u0432\u044b\u0448\u0430\u0442\u044c \u0440\u0430\u0441\u0445\u043e\u0434\u044b \u043d\u0430 \u0432\u044b\u0447\u0438\u0441\u043b\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0440\u0435\u0441\u0443\u0440\u0441\u044b. \u042f \u0434\u0435\u043b\u0430\u044e \u0441\u0435\u0440\u0432\u0438\u0441 \u0434\u043b\u044f \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0431\u0438\u0437\u043d\u0435\u0441 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u043c\u0438 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":27493,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-36704","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.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\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\/sq\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"sq_AL\" \/>\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\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 B2B \u0441\u0435\u0440\u0432\u0438\u0441\u0430 \u0434\u043b\u044f \u0441\u0442\u0440\u043e\u0438\u0442\u0435\u043b\u0435\u0439 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b?\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\" \/>\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-31T19:13:15+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:13:15+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\udd47Optimizimi i k\u00ebrkesave t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave me shembuj t\u00eb sh\u00ebrbimit B2B p\u00ebr nd\u00ebrtuesit | ProHoster","description":"Si t\u00eb rritesh 10 her\u00eb n\u00eb numrin e k\u00ebrkesave n\u00eb Baza t\u00eb Dh\u00ebnash pa u kaluar n\u00eb nj\u00eb server m\u00eb t\u00eb fuqish\u00ebm dhe t\u00eb ruash funksionalitetin e sistemit?","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","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\u041e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u044f \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 B2B \u0441\u0435\u0440\u0432\u0438\u0441\u0430 \u0434\u043b\u044f \u0441\u0442\u0440\u043e\u0438\u0442\u0435\u043b\u0435\u0439 | ProHoster","og:description":"\u041a\u0430\u043a \u0432\u044b\u0440\u0430\u0441\u0442\u0438 \u0432 10 \u0440\u0430\u0437 \u043f\u043e\u0434 \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u0443 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u043a \u0411\u0414 \u043d\u0435 \u043f\u0435\u0440\u0435\u0435\u0437\u0436\u0430\u044f \u043d\u0430 \u0431\u043e\u043b\u0435\u0435 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0441\u0435\u0440\u0432\u0435\u0440 \u0438 \u0441\u043e\u0445\u0440\u0430\u043d\u0438\u0442\u044c \u0440\u0430\u0431\u043e\u0442\u043e\u0441\u043f\u043e\u0441\u043e\u0431\u043d\u043e\u0441\u0442\u044c \u0441\u0438\u0441\u0442\u0435\u043c\u044b?","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","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-31T19:13:15+00:00","article:modified_time":"2019-10-31T19:13:15+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"36704","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-22 04:30:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:40:22","updated":"2026-01-22 04:30:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/36704","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=36704"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/36704\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/27493"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=36704"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=36704"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=36704"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}