{"id":84082,"date":"2020-06-05T07:42:28","date_gmt":"2020-06-05T05:42:28","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij"},"modified":"2020-06-05T07:42:28","modified_gmt":"2020-06-05T05:42:28","slug":"linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij","title":{"rendered":"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>Dekodimi i raportit t\u00eb vitit 2015 nga Ilya Kosmodemyansky \"Rregullimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL\"<\/p>\n<p><\/p>\n<p>Sh\u00ebnim: Dua t\u00eb theksoj se ky raport daton nga n\u00ebntori i vitit 2015 \u2014 ka kaluar m\u00eb shum\u00eb se 4 vjet dhe jan\u00eb zhvilluar shum\u00eb gj\u00ebra. Versioni i shqyrtuar n\u00eb raportin 9.4 nuk mb\u00ebshtetet m\u00eb. Gjat\u00eb k\u00ebtyre 4 viteve jan\u00eb l\u00ebshuar 5 versione t\u00eb reja t\u00eb PostgreSQL dhe 15 versione b\u00ebrthamash Linux. N\u00ebse do t\u00eb rishkruaja k\u00ebto pika, do t\u00eb rezultonte nj\u00eb raport tjet\u00ebr. Megjithat\u00eb, k\u00ebtu \u00ebsht\u00eb shqyrtuar tunimi themelor i Linux p\u00ebr PostgreSQL, i cili \u00ebsht\u00eb aktual edhe tani.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/8292f74a7d004a9c13ff5f1393816340.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\n<center><div class=\"youtube-placeholder\" data-id=\"V0M6YwWmMYM\" onclick=\"loadVideo(this)\">\r\n        <img decoding=\"async\" src=\"https:\/\/img.youtube.com\/vi\/V0M6YwWmMYM\/hqdefault.jpg\" alt=\"Luaj videon\" loading=\"lazy\" width=\"480\" height=\"360\" style=\"width:100%;height:auto;\">\r\n        <div class=\"play-button\"><\/div>\r\n    <\/div><\/center><\/p>\n<p>M\u00eb quajn\u00eb Ilya Kosmodemyansky. Un\u00eb punoj n\u00eb kompanin\u00eb PostgreSQL-Consulting. Tani do t\u00eb flas pak p\u00ebr at\u00eb se \u00e7far\u00eb t\u00eb b\u00ebjm\u00eb me Linux n\u00eb lidhje me bazat e t\u00eb dh\u00ebnave n\u00eb p\u00ebrgjith\u00ebsi dhe me PostgreSQL n\u00eb ve\u00e7anti, sepse parimet jan\u00eb mjaft t\u00eb ngjashme.<\/p>\n<p><\/p>\n<p>P\u00ebr \u00e7far\u00eb do flasim? N\u00ebse komunikoni me PostgreSQL, at\u00ebher\u00eb n\u00eb nj\u00eb far\u00eb m\u00ebnyre duhet t\u00eb jeni administrator UNIX-i. \u00c7far\u00eb do t\u00eb thot\u00eb kjo? N\u00ebse e krahasojm\u00eb Oracle dhe PostgreSQL, n\u00eb Oracle duhet t\u00eb jesh 80% DBA dhe 20% administrator Linux.<\/p>\n<p><\/p>\n<p>Me PostgreSQL \u00ebsht\u00eb pak m\u00eb e komplikuar. Me PostgreSQL duhet t\u00eb keni nj\u00eb kuptim m\u00eb t\u00eb mir\u00eb se si funksionon Linux. Dhe p\u00ebr k\u00ebt\u00eb, duhet t\u00eb ndjekim zhvillimet, sepse koh\u00ebt e fundit shum\u00eb gj\u00ebra p\u00ebrdit\u00ebsohen. Po l\u00ebshohen b\u00ebrthama t\u00eb reja, funksionalitet t\u00eb ri, performanca p\u00ebrmir\u00ebsohet etj. <\/p>\n<p><\/p>\n<p>Pse flasim p\u00ebr Linux? Jo p\u00ebr faktin se jemi n\u00eb konferenc\u00ebn Linux n\u00eb Piter, por sepse n\u00eb kushtet moderne nj\u00eb nga sistemet operative m\u00eb t\u00eb justifikuara p\u00ebr eksploatimin e bazave t\u00eb t\u00eb dh\u00ebnave n\u00eb p\u00ebrgjith\u00ebsi dhe p\u00ebr PostgreSQL n\u00eb ve\u00e7anti \u00ebsht\u00eb Linux. Sepse FreeBSD, fatkeq\u00ebsisht, po zhvillohet n\u00eb nj\u00eb drejtim shum\u00eb t\u00eb \u00e7uditsh\u00ebm. Dhe do t\u00eb ket\u00eb probleme me performanc\u00ebn, si dhe me shum\u00eb gj\u00ebra t\u00eb tjera. <strong>Performanca e PostgreSQL n\u00eb Windows \u00ebsht\u00eb nj\u00eb tem\u00eb totale e ve\u00e7ant\u00eb, q\u00eb shqet\u00ebson faktin se Windows-i nuk ka t\u00eb till\u00eb memorie t\u00eb p\u00ebrbashk\u00ebt si UNIX, dhe e gjith\u00eb kjo tek PostgreSQL lidhet me k\u00ebt\u00eb, sepse \u00ebsht\u00eb nj\u00eb sistem me shum\u00eb procese.<\/strong> <\/p>\n<p><\/p>\n<p>Dhe ekstravaganca si Solaris, mendoj se intereson m\u00eb pak t\u00eb tjer\u00ebt, ndaj le t\u00eb vazhdojm\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/990133933bd1816895fc8f73edaf900e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nj\u00eb distribucion modern i Linux ka m\u00eb shum\u00eb se 1,000 parametra syctl, var\u00ebsisht nga m\u00ebnyra se si p\u00ebrgatitet b\u00ebrthama. P\u00ebrve\u00e7 k\u00ebsaj, n\u00ebse shohim n\u00eb ndryshime t\u00eb tjera, ka pasoja t\u00eb tjera q\u00eb mund t\u00eb rregullohen. Ekzistojn\u00eb parametra t\u00eb sistemeve t\u00eb skedar\u00ebve, si t\u00eb montoni. N\u00ebse ka pyetje, si t\u00eb lansoni: \u00e7far\u00eb t\u00eb aktivizoni n\u00eb BIOS, si t\u00eb konfiguroni harduerin etj.<\/p>\n<p><\/p>\n<p>Ky \u00ebsht\u00eb nj\u00eb sasi shum\u00eb e madhe informacioni, p\u00ebr t\u00eb cilin mund t\u00eb flitet p\u00ebr disa dit\u00eb, dhe jo p\u00ebr nj\u00eb prezantim t\u00eb shkurt\u00ebr, por tani do t\u00eb ndalem te gj\u00ebrat e r\u00ebnd\u00ebsishme, si t\u00eb shmangim ato momente q\u00eb n\u00eb m\u00ebnyr\u00eb t\u00eb garantuar nuk do t'ju lejojn\u00eb t\u00eb shfryt\u00ebzoni mir\u00eb baz\u00ebn e t\u00eb dh\u00ebnave n\u00eb Linux, n\u00ebse nuk i korrigjoni ato. Dhe nj\u00eb pik\u00eb e r\u00ebnd\u00ebsishme \u00ebsht\u00eb se shum\u00eb parametra jan\u00eb aktivizuar n\u00eb konfigurime q\u00eb nuk jan\u00eb t\u00eb duhura p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave. K\u00ebshtu, p\u00ebr sa koh\u00eb q\u00eb \u00ebsht\u00eb p\u00ebrpar\u00ebsi, do t\u00eb funksionoj\u00eb keq ose nuk do t\u00eb funksionoj\u00eb fare. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/189f6497c795a5e5fd5b458edfadb22f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cilat jan\u00eb targetet tradicionale t\u00eb tuning n\u00eb Linux? Mendoj se, pasi q\u00eb t\u00eb gjith\u00eb merremi me administrimin e Linux, nuk ka nevoj\u00eb t\u00eb shpjegojm\u00eb ve\u00e7anti rreth targeteve. <\/p>\n<p><\/p>\n<p>Mund t\u00eb b\u00ebjm\u00eb tuning:<\/p>\n<p><\/p>\n<ul>\n<li>CPU.<\/li>\n<li>Memoria.<\/li>\n<li>Ruajtja.<\/li>\n<li>T\u00eb tjera. Rreth k\u00ebsaj do t\u00eb flasim n\u00eb fund si nj\u00eb larmi. Edhe, p\u00ebr shembull, parametrat si politika e kursimit t\u00eb energjis\u00eb mund t\u00eb ndikojn\u00eb n\u00eb performanc\u00ebn n\u00eb m\u00ebnyr\u00eb shum\u00eb t\u00eb papritur dhe jo t\u00eb k\u00ebndshme. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/92916aadaf123a3f836bed3a2c1bd95a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cila \u00ebsht\u00eb specifika e PostgreSQL dhe e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb p\u00ebrgjith\u00ebsi? Problemi \u00ebsht\u00eb se nuk mund t\u00eb b\u00ebni tuning p\u00ebr nj\u00eb ndihm\u00ebs t\u00eb ve\u00e7ant\u00eb dhe t\u00eb shihni se performanca ka p\u00ebrmir\u00ebsuar ndjesh\u00ebm. <\/p>\n<p><\/p>\n<p>Po, ndihm\u00ebs t\u00eb till\u00eb ka, por baza e t\u00eb dh\u00ebnave \u00ebsht\u00eb nj\u00eb gj\u00eb komplekse. Ajo nd\u00ebrvepron me t\u00eb gjitha burimet q\u00eb ka serveri dhe preferon t\u00eb nd\u00ebrveproj\u00eb n\u00eb m\u00ebnyr\u00eb t\u00eb plot\u00eb. N\u00ebse shikoni rekomandimet moderne t\u00eb Oracle mbi p\u00ebrdorimin e sistemit operativ host, do t\u00eb jet\u00eb si n\u00eb anekdot\u00ebn p\u00ebr astronautin mongol \u2013 ushqeja qenushin dhe mos e preku asgj\u00eb. Le t\u00eb japim t\u00eb gjitha burimet e baz\u00ebs, ajo do ta menaxhoj\u00eb vet\u00eb. <\/p>\n<p><\/p>\n<p>N\u00eb thelb, deri n\u00eb nj\u00ebfar\u00eb shkalle situata me PostgreSQL \u00ebsht\u00eb e nj\u00ebjt\u00eb. Diferenca \u00ebsht\u00eb se baza nuk di t\u00eb marr\u00eb vet\u00eb t\u00eb gjitha burimet, dmth, disa gj\u00ebra duhet menaxhuar vet\u00eb n\u00eb nivelin e Linux. <\/p>\n<p><\/p>\n<p>Ideja kryesore \u00ebsht\u00eb t\u00eb mos zgjidhni ndonj\u00eb target t\u00eb vet\u00ebm dhe t\u00eb filloni ta tunoni at\u00eb, p\u00ebr shembull, memorjen, CPU-n\u00eb apo di\u00e7ka t\u00eb till\u00eb, por t\u00eb analizoni ngarkes\u00ebn e pun\u00ebs dhe t\u00eb p\u00ebrpiqeni t\u00eb p\u00ebrmir\u00ebsoni sa m\u00eb shum\u00eb kapacitetin e kalimit, q\u00eb ngarkesa q\u00eb programuesit tan\u00eb t\u00eb shk\u00eblqyer dhe p\u00ebrdoruesit tan\u00eb e krijojn\u00eb t\u00eb kaloj\u00eb sa m\u00eb efektivisht p\u00ebrmes baz\u00ebs son\u00eb t\u00eb t\u00eb dh\u00ebnave. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/2db6c11a6f612b8aecccfe126be4fd7f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb nj\u00eb pamje p\u00ebr t\u00eb shpjeguar se \u00e7far\u00eb \u00ebsht\u00eb. Ka nj\u00eb buffer t\u00eb OS Linux, ka memorie t\u00eb p\u00ebrbashk\u00ebt dhe ka buffers t\u00eb p\u00ebrbashk\u00ebt t\u00eb PostgreSQL. PostgreSQL, ndryshe nga Oracle, punon direkt vet\u00ebm me bufferin e kernelit, pra q\u00eb nj\u00eb faqe nga disku t\u00eb kaloj\u00eb n\u00eb memorien e tij t\u00eb p\u00ebrbashk\u00ebt, ajo duhet t\u00eb kaloj\u00eb p\u00ebrmes bufferit t\u00eb kernelit dhe situata \u00ebsht\u00eb sakt\u00ebsisht e nj\u00ebjt\u00eb n\u00eb an\u00ebn tjet\u00ebr. <\/p>\n<p><\/p>\n<p>N\u00ebn k\u00ebt\u00eb sistem jetojn\u00eb disk\u00ebt. Un\u00eb e kam vizatuar si disk\u00eb. N\u00eb t\u00eb v\u00ebrtet\u00eb, mund t\u00eb ket\u00eb nj\u00eb kontrollor RAID etj. <\/p>\n<p><\/p>\n<p>Dhe ky input-output ndodh k\u00ebshtu ose ashtu p\u00ebrmes k\u00ebsaj.<\/p>\n<p><\/p>\n<p>PostgreSQL \u00ebsht\u00eb nj\u00eb baz\u00eb e dh\u00ebnash klasike. Brenda saj ka faqe. Dhe gjith\u00eb input-output ndodh p\u00ebrmes faqeve. Ne ngrem\u00eb blloqet n\u00eb memorie me faqe. Dhe n\u00ebse nuk ka ndodhur asgj\u00eb, thjesht i lexojm\u00eb ato, gradualisht ato shp\u00ebrndahen nga ky cache, nga buffers t\u00eb p\u00ebrbashk\u00ebt dhe kthehen p\u00ebrs\u00ebri n\u00eb disk. <\/p>\n<p><\/p>\n<p>N\u00ebse ne z\u00ebvend\u00ebsojm\u00eb ndonj\u00eb gj\u00eb, gjith\u00eb faqeja sh\u00ebnohet si e ndotur. Un\u00eb i kam sh\u00ebnuar k\u00ebtu me ngjyr\u00eb blu. Dhe kjo do t\u00eb thot\u00eb se kjo faqe duhet t\u00eb sinkronizohet me magazin\u00ebn bllokuese. Pra, kur e b\u00ebjm\u00eb at\u00eb t\u00eb ndotur, b\u00ebjm\u00eb nj\u00eb regjistrim n\u00eb WAL. Dhe n\u00eb nj\u00eb moment t\u00eb bukur, ndodh nj\u00eb fenomen i quajtur checkpoint. Dhe n\u00eb k\u00ebt\u00eb log u regjistrua informata q\u00eb ai erdhi. Kjo do t\u00eb thot\u00eb se t\u00eb gjitha faqet e ndotura, q\u00eb ishin k\u00ebtu n\u00eb at\u00eb moment n\u00eb k\u00ebto buffers t\u00eb p\u00ebrbashk\u00ebt, u sinkronizuan me disku i magazin\u00ebs p\u00ebrmes fsync p\u00ebrmes bufferit t\u00eb kernelit.<\/p>\n<p><\/p>\n<p>P\u00ebrse b\u00ebhet kjo? N\u00ebse na humbet energjia, at\u00ebher\u00eb nuk kemi situat\u00ebn q\u00eb t\u00eb gjith\u00eb t\u00eb dh\u00ebnat humbasin. Memoria persistente, p\u00ebr t\u00eb cil\u00ebn na treguan t\u00eb gjith\u00eb, p\u00ebr momentin ekziston n\u00eb teorin\u00eb e bazave t\u00eb t\u00eb dh\u00ebnave \u2013 \u00ebsht\u00eb nj\u00eb t\u00eb ardhme e ndritshme, p\u00ebr t\u00eb cil\u00ebn sigurisht q\u00eb synojm\u00eb dhe na p\u00eblqen, por p\u00ebr momentin ata jetojn\u00eb ende n\u00eb -20 vjet. Dhe, natyrisht, duhet t\u00eb vigjilojm\u00eb p\u00ebr k\u00ebt\u00eb.<\/p>\n<p><\/p>\n<p>Dhe detyra e maksimizimit t\u00eb kapacitetit \u00ebsht\u00eb t\u00eb optimizojm\u00eb n\u00eb t\u00eb gjitha k\u00ebto etapa, p\u00ebr ta b\u00ebr\u00eb q\u00eb gjith\u00e7ka t\u00eb ec\u00eb shpejt. Memoria e p\u00ebrbashk\u00ebt \u00ebsht\u00eb kryesisht cache faqe. N\u00eb PostgreSQL ne d\u00ebrguam nj\u00eb k\u00ebrkes\u00eb select di\u00e7ka, ai e mori at\u00eb t\u00eb dh\u00ebn\u00ebn nga disku. Ato arrit\u00ebn n\u00eb buffers t\u00eb p\u00ebrbashk\u00ebt. P\u00ebrshtatshm\u00ebria e tij p\u00ebr t\u00eb punuar m\u00eb mir\u00eb do t\u00eb thot\u00eb se duhet t\u00eb ket\u00eb shum\u00eb memorie.<\/p>\n<p><\/p>\n<p>P\u00ebr t\u00eb b\u00ebr\u00eb q\u00eb kjo t\u00eb funksionoj\u00eb mir\u00eb dhe shpejt, ju nevojitet t\u00eb konfiguroni n\u00eb m\u00ebnyr\u00eb t\u00eb duhur sistemin operativ n\u00eb t\u00eb gjitha fazat. Gjithashtu, duhet t\u00eb zgjidhni nj\u00eb harduer t\u00eb ekuilibruar, sepse n\u00ebse ka nj\u00eb disbalanc\u00eb diku, mund t\u00eb keni shum\u00eb memorie, por do t\u00eb sh\u00ebrbehet me shpejt\u00ebsi t\u00eb pamjaftueshme. <\/p>\n<p><\/p>\n<p>Dhe do t\u00eb shkojm\u00eb p\u00ebrmes secil\u00ebs nga k\u00ebto pika.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/fe031218be39cd727f3a76b22563e101.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u00ebr t\u00eb b\u00ebr\u00eb q\u00eb k\u00ebto faqe t\u00eb udh\u00ebtojn\u00eb m\u00eb shpejt, duhet t\u00eb arrijm\u00eb t\u00eb b\u00ebjm\u00eb t\u00eb siguiente:<\/p>\n<p><\/p>\n<ul>\n<li>S\u00eb pari, duhet t\u00eb punojm\u00eb m\u00eb efikas me memorien.<\/li>\n<li>S\u00eb dyti, duhet t\u00eb jet\u00eb m\u00eb efikase kjo kalim, kur faqet kalojn\u00eb nga memoria n\u00eb disk.<\/li>\n<li>Dhe, s\u00eb treti, duhet t\u00eb kemi disqe t\u00eb mira. <\/li>\n<\/ul>\n<p><\/p>\n<p>N\u00ebse keni 512 GB memorie RAM n\u00eb <a class=\"wpil_keyword_link\" href=\"https:\/\/prohoster.info\/sq\/server\/dts-dronten\/\"   title=\"server\" data-wpil-keyword-link=\"linked\"  data-wpil-monitor-id=\"2587\">server<\/a> dhe gjith\u00eb kjo p\u00ebrfundon n\u00eb nj\u00eb hard disk SATA pa asnj\u00eb cache, at\u00ebher\u00eb gjith\u00eb serveri i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave shnd\u00ebrrohet jo vet\u00ebm n\u00eb nj\u00eb kungull, por n\u00eb nj\u00eb kungull me nd\u00ebrfaqe SATA. Ju do t\u00eb p\u00ebrballeni drejtp\u00ebrdrejt me k\u00ebt\u00eb. Dhe asgj\u00eb nuk do t'ju shp\u00ebtoj\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/6630e445c96e94621ae670bc4aee8492.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Sa i p\u00ebrket pik\u00ebs s\u00eb par\u00eb me memorien, ka tri gj\u00ebra q\u00eb mund ta komplikojn\u00eb shum\u00eb jet\u00ebn. <\/p>\n<p><\/p>\n<p>E para nga ato \u00ebsht\u00eb NUMA. NUMA \u00ebsht\u00eb nj\u00eb koncept i b\u00ebr\u00eb p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn. Sipas ngarkes\u00ebs, mund t\u00eb optimizoni gj\u00ebra t\u00eb ndryshme. N\u00eb form\u00ebn e saj aktuale, p\u00ebr aplikacione t\u00eb tilla si baza e t\u00eb dh\u00ebnave q\u00eb p\u00ebrdorin intensivisht cache-n\u00eb e faqeve, nuk \u00ebsht\u00eb shum\u00eb e mir\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/8fcf2af82a96bd1ac52fb0a36bf0b0b5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Me fjal\u00eb t\u00eb tjera. Si t\u00eb kuptoni q\u00eb di\u00e7ka nuk shkon me NUMA? Ju ndjeni nj\u00eb goditje t\u00eb pak\u00ebndshme, papritmas ndonj\u00eb CPU del n\u00eb ngarkes\u00eb. Duke analizuar k\u00ebrkesat n\u00eb PostgreSQL, shihni se nuk ka asgj\u00eb q\u00eb ngjan me k\u00ebt\u00eb. K\u00ebto k\u00ebrkesa nuk duhet t\u00eb konsumojn\u00eb kaq intensisht CPU-n\u00eb. Mund ta kapni k\u00ebt\u00eb p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb. \u00cbsht\u00eb m\u00eb leht\u00eb t\u00eb p\u00ebrdorni q\u00eb nga fillimi rekomandimin e duhur, si t\u00eb konfiguroheni NUMA p\u00ebr PostgreSQL.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/424b8677e5ae1382b9b49b288e045a3f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodh n\u00eb fakt? NUMA \u00ebsht\u00eb Non-Uniform Memory Access. \u00c7far\u00eb do t\u00eb thot\u00eb? Keni nj\u00eb CPU, pran\u00eb tij \u00ebsht\u00eb memoria e tij lokale. Dhe kjo memorie mund t\u00eb t\u00ebrheq\u00eb memorien nga CPU t\u00eb tjer\u00eb.<\/p>\n<p><\/p>\n<p>N\u00ebse ekzekutoni <code>numactl --hardware<\/code>, do t\u00eb shihni nj\u00eb list\u00eb t\u00eb madhe. Mes t\u00eb tjerash, do t\u00eb ket\u00eb nj\u00eb fush\u00eb distances. Do t\u00eb jen\u00eb numra \u2013 10-20, di\u00e7ka e till\u00eb. K\u00ebta numra jan\u00eb asgj\u00eb tjet\u00ebr ve\u00e7se numri i hopave p\u00ebr t\u00eb t\u00ebrhequr k\u00ebt\u00eb memorie t\u00eb larg\u00ebt dhe p\u00ebr ta p\u00ebrdorur lokal. N\u00eb parim, nj\u00eb ide e mir\u00eb. Kjo ndihmon shum\u00eb n\u00eb p\u00ebrmir\u00ebsimin e performanc\u00ebs n\u00ebn disa ngarkesa.<\/p>\n<p><\/p>\n<p>Tani, imagjinoni se keni nj\u00eb CPU q\u00eb s\u00eb pari p\u00ebrpiqet t\u00eb p\u00ebrdor\u00eb memorjen e tij lokale, pastaj p\u00ebrpiqet t\u00eb t\u00ebrheq\u00eb memorje tjet\u00ebr p\u00ebrmes interconnect p\u00ebr ndonj\u00eb gj\u00eb. Dhe n\u00eb k\u00ebt\u00eb CPU arrin e gjith\u00eb memoria juaj e cache t\u00eb faqeve t\u00eb PostgreSQL - t\u00eb gjitha ato, ndonj\u00ebher\u00eb disa gigabajt. Ju gjithmon\u00eb merrni rastin m\u00eb t\u00eb keq, sepse zakonisht ka pak memory n\u00eb k\u00ebt\u00eb modul memorie. Dhe e gjith\u00eb memoria q\u00eb sh\u00ebrbehet, kalon p\u00ebrmes k\u00ebtyre interconnects. K\u00ebshtu b\u00ebhet ngadal\u00eb dhe me trishtim. Dhe ju keni nj\u00eb procesor q\u00eb sh\u00ebrben k\u00ebt\u00eb nyje, gjithmon\u00eb tejet i ngarkuar. Dhe koha e qasjes n\u00eb k\u00ebt\u00eb memorie \u00ebsht\u00eb e dob\u00ebt, e ngadalt\u00eb. Kjo \u00ebsht\u00eb situata q\u00eb nuk doni, n\u00ebse e p\u00ebrdorni k\u00ebt\u00eb gj\u00eb p\u00ebr nj\u00eb baz\u00eb t\u00eb dh\u00ebnash. <\/p>\n<p><\/p>\n<p>Prandaj, nj\u00eb variant m\u00eb i sakt\u00eb p\u00ebr nj\u00eb baz\u00eb t\u00eb dh\u00ebnash \u00ebsht\u00eb q\u00eb sistemi operativ Linux t\u00eb mos dij\u00eb fare \u00e7far\u00eb po ndodh atje. Q\u00eb ajo t\u00eb qaset n\u00eb memorie si\u00e7 duhet. <\/p>\n<p><\/p>\n<p>Pse k\u00ebshtu? Duke menduar se duhet t\u00eb ishte nj\u00ebsoj. Kjo ndodh p\u00ebr nj\u00eb shkak t\u00eb thjesht\u00eb, q\u00eb na nevojitet shum\u00eb memorie p\u00ebr cache-n e faqeve - dhjet\u00ebra, qindra gigabajt. <\/p>\n<p><\/p>\n<p>Dhe n\u00ebse ne e kemi gjith\u00eb at\u00eb t\u00eb dedikuar dhe kemi cache-uar t\u00eb dh\u00ebnat tona aty, p\u00ebrfitimi nga p\u00ebrdorimi i caches do t\u00eb jet\u00eb shum\u00eb m\u00eb i madh se p\u00ebrfitimi nga nj\u00eb qasje e till\u00eb n\u00eb memorie. Dhe n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb ne do t\u00eb fitojm\u00eb n\u00eb m\u00ebnyr\u00eb t\u00eb paqenueshme krahasuar me at\u00eb q\u00eb do t\u00eb jemi m\u00eb efikas n\u00eb qasjen n\u00eb memorie duke p\u00ebrdorur NUMA.<\/p>\n<p><\/p>\n<p>Prandaj, deri tani ka dy qasje, derisa e ardhmja e ndritur t\u00eb ket\u00eb ardhur dhe baza e t\u00eb dh\u00ebnave t\u00eb dij\u00eb vet\u00eb n\u00eb cilin CPU po punon dhe nga ku i nevojitet di\u00e7ka. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/218513d407ea77320057d57b447c54d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><strong>Prandaj, qasja e duhur \u00ebsht\u00eb t'i fikim plot\u00ebsisht NUMA-n.<\/strong>, p\u00ebr shembull, gjat\u00eb rindezjes. N\u00eb shumic\u00ebn e rasteve, fitimet jan\u00eb t\u00eb tilla q\u00eb nuk ka fare pyetje se si \u00ebsht\u00eb m\u00eb mir\u00eb. <\/p>\n<p><\/p>\n<p>Ka nj\u00eb variant tjet\u00ebr. Ne e p\u00ebrdorim at\u00eb m\u00eb shpesh se variantin e par\u00eb, sepse kur nj\u00eb klient na vjen p\u00ebr mb\u00ebshtetje, p\u00ebr t\u00eb rindezur serverin \u00ebsht\u00eb nj\u00eb \u00e7\u00ebshtje e madhe. Ai ka biznesin e tij aty. Dhe ata p\u00ebrjetojn\u00eb probleme nga NUMA. Prandaj ne p\u00ebrpiqemi ta fikim n\u00eb m\u00ebnyra m\u00eb pak invazive se rindezja, por k\u00ebtu duhet t\u00eb kontrolloni me kujdes q\u00eb ajo t\u00eb ket\u00eb q\u00ebn\u00eb fikur. Sepse, si\u00e7 tregon eksperienca, kur ne e fikim NUMA-n p\u00ebr procesin prind t\u00eb PostgreSQL, kjo \u00ebsht\u00eb mir\u00eb, por nuk \u00ebsht\u00eb e domosdoshme q\u00eb kjo t\u00eb funksionoj\u00eb. Duhet t\u00eb kontrolloni dhe t\u00eb shihni se ajo me t\u00eb v\u00ebrtet\u00eb \u00ebsht\u00eb fikur. <\/p>\n<p><\/p>\n<p>Ka nj\u00eb post t\u00eb shk\u00eblqyer nga Robert Haas. Ai \u00ebsht\u00eb nj\u00eb nga kontribuesit e PostgreSQL. Nj\u00eb nga zhvilluesit ky\u00e7 t\u00eb t\u00eb gjitha brend\u00ebsive t\u00eb nivelit t\u00eb ul\u00ebt. Dhe n\u00ebse ndiheni t\u00eb interesuar pas lidhjeve nga ky post, atje p\u00ebrshkruhen disa histori interesante p\u00ebr m\u00ebnyr\u00ebn se si NUMA i v\u00ebshtir\u00ebson jet\u00ebn njer\u00ebzve. Shikoni, studioni list\u00ebn kontroluese p\u00ebr administrator\u00ebt e sistemeve, q\u00eb duhet t\u00eb konfiguroni n\u00eb server p\u00ebr t\u00eb siguruar q\u00eb baza e t\u00eb dh\u00ebnave t\u00eb funksionoj\u00eb mir\u00eb. K\u00ebto konfigurations duhet t\u00eb ruhen dhe t\u00eb kontrollohen, sepse p\u00ebrndryshe do t\u00eb jet\u00eb jo shum\u00eb mir\u00eb. <\/p>\n<p><\/p>\n<p>Dua t\u00eb theksoj se kjo e b\u00ebn fjal\u00eb p\u00ebr t\u00eb gjitha konfigurimet, p\u00ebr t\u00eb cilat do t\u00eb flas. Por zakonisht, bazat e t\u00eb dh\u00ebnave organizohen n\u00eb modin master-slave p\u00ebr q\u00ebllime q\u00ebndrueshm\u00ebrie. Mos harroni t\u00eb b\u00ebni k\u00ebto konfigurime n\u00eb slave, sepse n\u00eb nj\u00eb moment t\u00eb caktuar do t\u00eb keni nj\u00eb katastrof\u00eb dhe do t\u00eb kaloni te slave, i cili do t\u00eb b\u00ebhet master. <\/p>\n<p><\/p>\n<p>N\u00eb nj\u00eb situat\u00eb emergjente, kur gjith\u00e7ka \u00ebsht\u00eb shum\u00eb keq, telefoni juaj bie vazhdimisht dhe shefi vjen me nj\u00eb shkop t\u00eb madh, nuk do t\u00eb keni koh\u00eb t\u00eb mendoni p\u00ebr kontrollime. Dhe rezultatet mund t\u00eb jen\u00eb shum\u00eb trondit\u00ebse.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/d2fdda7ad4570554b0e134758295f83e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Momenti tjet\u00ebr \u00ebsht\u00eb pagesat e m\u00ebdha (huge pages). Pagesat e m\u00ebdha jan\u00eb t\u00eb v\u00ebshtira p\u00ebr t'u testuar ve\u00e7mas, dhe nuk ka shum\u00eb kuptim p\u00ebr ta b\u00ebr\u00eb k\u00ebt\u00eb, edhe pse ka benchmarking q\u00eb di t'i b\u00ebj\u00eb k\u00ebto. Ato jan\u00eb t\u00eb lehta p\u00ebr t'u gjetur n\u00eb internet. <\/p>\n<p><\/p>\n<p>Cili \u00ebsht\u00eb kuptimi? Keni nj\u00eb server jo shum\u00eb t\u00eb shtrenjt\u00eb, ku ka shum\u00eb memorie RAM, p\u00ebr shembull, m\u00eb shum\u00eb se 30 GB. Ju nuk po p\u00ebrdorni pagesat e m\u00ebdha. Kjo do t\u00eb thot\u00eb se ju patjet\u00ebr keni overhead n\u00eb p\u00ebrdorimin e memories. Dhe ky overhead nuk \u00ebsht\u00eb aspak i k\u00ebndsh\u00ebm. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/28c3c94390a6afef815f712ac9189c2d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pse ndodh k\u00ebshtu? \u00c7far\u00eb po ndodh? Sistemi operativ ndan memorin\u00eb n\u00eb copa t\u00eb vogla. Kjo \u00ebsht\u00eb e p\u00ebrshtatshme, k\u00ebshtu ka ndodhur historikisht. Dhe n\u00ebse shqyrtohet me holl\u00ebsi, OS duhet t\u00eb p\u00ebrkthej\u00eb adresat virtuale n\u00eb ato fizike. Dhe ky proces nuk \u00ebsht\u00eb aspak i thjesht\u00eb, prandaj OS rezultatet e k\u00ebsaj operacioni i ruan n\u00eb Translation Lookaside Buffer (TLB).<\/p>\n<p><\/p>\n<p>Dhe ngase TLB \u00ebsht\u00eb nj\u00eb cache, n\u00eb nj\u00eb situat\u00eb t\u00eb till\u00eb lindin t\u00eb gjitha problemet q\u00eb lidhen me caching. S\u00eb pari, n\u00ebse keni shum\u00eb memorie RAM dhe ajo \u00ebsht\u00eb e gjitha e ndar\u00eb n\u00eb copa t\u00eb vogla, at\u00ebher\u00eb ky buffer b\u00ebhet shum\u00eb i madh. Dhe n\u00ebse cache \u00ebsht\u00eb i madh, k\u00ebrkimi n\u00eb t\u00eb b\u00ebhet m\u00eb i ngadalsh\u00ebm. Overhead-i \u00ebsht\u00eb i madh dhe ai vet\u00eb merr vend, dmth. memorizimi i fizikisht ndar\u00eb \u00ebsht\u00eb di\u00e7ka jo e sakt\u00eb. Kjo \u00ebsht\u00eb nj\u00ebra nga arsyet. <\/p>\n<p><\/p>\n<p>Dy \u2013 sa m\u00eb shum\u00eb q\u00eb rritet cache n\u00eb k\u00ebt\u00eb situat\u00eb, aq m\u00eb e madhe \u00ebsht\u00eb probabiliteti i humbjeve t\u00eb cache. Dhe efektiviteti i k\u00ebtij cache bie shpejt me rritjen e madh\u00ebsis\u00eb s\u00eb tij. Prandaj, n\u00eb sistemet operative u shpik nj\u00eb qasje e thjesht\u00eb. N\u00eb Linux ajo tashm\u00eb p\u00ebrdoret prej koh\u00ebsh. N\u00eb FreeBSD kjo u shfaq jo shum\u00eb koh\u00eb m\u00eb par\u00eb. Por ne po flasim p\u00ebr Linux. K\u00ebto jan\u00eb huge pages.<\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu duhet t\u00eb theksohet se idea e huge pages fillimisht ishte promovuar nga komunitete q\u00eb p\u00ebrfshinin Oracle dhe IBM, pra prodhuesit e bazave t\u00eb t\u00eb dh\u00ebnave mendonin thell\u00eb se do t'u nevojiteshin, p\u00ebrfshir\u00eb p\u00ebr bazat e t\u00eb dh\u00ebnave. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/39a24536929fbcf551d8ba1c8e7faf32.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe si t'i lidhim ato me PostgreSQL? S\u00eb pari, n\u00eb b\u00ebrtham\u00ebn e Linux duhet t\u00eb aktivizohen huge pages.<\/p>\n<p><\/p>\n<p>S\u00eb dyti, ato duhet t\u00eb specifikohen qart\u00eb me parametrin sysctl \u2013 sa shum\u00eb prej tyre. Numrat k\u00ebtu vijn\u00eb nga nj\u00eb server i vjet\u00ebr. Ju mund t\u00eb llogaritni se sa ashp\u00ebrsisht keni shared buffers, q\u00eb huge pages t\u00eb mund t\u00eb hyjn\u00eb atje. <\/p>\n<p><\/p>\n<p>Dhe n\u00ebse t\u00ebr\u00eb serveri \u00ebsht\u00eb i dedikuar p\u00ebr PostgreSQL, nj\u00eb pik\u00eb e mir\u00eb fillestare \u00ebsht\u00eb t\u00eb jepni ose 25% t\u00eb memories operuese p\u00ebr buffered ndarjen, ose 75%, n\u00ebse jeni t\u00eb sigurt se n\u00eb k\u00ebto 75% baza juaj e dh\u00ebnash me siguri do t\u00eb p\u00ebrshtatet. Kjo \u00ebsht\u00eb pika e par\u00eb fillestare. Dhe llogaritni, n\u00ebse keni 256 GB memorie operuese, at\u00ebher\u00eb p\u00ebrkat\u00ebsisht, 64 GB do t\u00eb jen\u00eb buffers t\u00eb ndara. Llogaritni ndoshta me nj\u00eb rezerv\u00eb \u2013 \u00e7far\u00eb duhet t\u00eb jet\u00eb kjo num\u00ebr.<\/p>\n<p><\/p>\n<p>Para versionit 9.2 (n\u00ebse nuk gaboj, q\u00eb nga versioni 8.2) kishte mund\u00ebsi t\u00eb lidhni PostgreSQL me huge pages p\u00ebrmes nj\u00eb bibliotek\u00eb t\u00eb tret\u00eb. Dhe kjo gjithmon\u00eb duhet b\u00ebr\u00eb. S\u00eb pari, ju nevojitet q\u00eb b\u00ebrthama t\u00eb dij\u00eb si t\u00eb alokoj\u00eb huge pages n\u00eb m\u00ebnyr\u00eb t\u00eb duhur. Dhe, s\u00eb dyti, aplikacioni q\u00eb punon me to duhet t\u00eb mund t\u00eb p\u00ebrfitoj\u00eb nga ato. Thjesht ashtu nuk do t\u00eb p\u00ebrfitoj\u00eb. Sepse PostgreSQL alokonte memorie n\u00eb stilin e sistemit 5, k\u00ebshtu q\u00eb kjo mund t\u00eb b\u00ebhej p\u00ebrmes libhugetlbfs \u2013 ky \u00ebsht\u00eb emri i plot\u00eb i bibliotek\u00ebs.<\/p>\n<p><\/p>\n<p>N\u00eb versionin 9.3, performanca e PostgreSQL p\u00ebr menaxhimin e memories u p\u00ebrmir\u00ebsua dhe metodi i alokimit t\u00eb memories system 5 u hoq. T\u00eb gjith\u00eb ishin shum\u00eb t\u00eb k\u00ebnaqur, sepse p\u00ebrndryshe, kur p\u00ebrpiqesh t\u00eb lancosh dy instance t\u00eb PostgreSQL n\u00eb t\u00eb nj\u00ebjtin server, ai thot\u00eb se ka munges\u00eb t\u00eb memories s\u00eb ndar\u00eb. Ai tregon se duhet t\u00eb rregullohet sysctl. Dhe aty ka nj\u00eb sysctl q\u00eb k\u00ebrkon edhe nj\u00eb restart, etj. Pra, t\u00eb gjith\u00eb u g\u00ebzuan. Megjithat\u00eb, alokimi i memories mmap prishi p\u00ebrdorimin e pages t\u00eb m\u00ebdha. shumica e klient\u00ebve tan\u00eb p\u00ebrdorin buffers t\u00eb m\u00ebdha t\u00eb ndar\u00eb. Kemi rekomanduar me ngulm q\u00eb t\u00eb mos kaloni n\u00eb 9.3, sepse atje overhead-i fillonte t\u00eb llogaritej n\u00eb p\u00ebrqindje t\u00eb mira.<\/p>\n<p><\/p>\n<p>Megjithat\u00eb, komuniteti e vuri re k\u00ebt\u00eb problem dhe n\u00eb 9.4 e ristrukturuan shum\u00eb mir\u00eb k\u00ebt\u00eb aspekt. N\u00eb 9.4 u shfaq nj\u00eb parametr n\u00eb postgresql.conf, ku mund t\u00eb aktivizoni try, on ose off.<\/p>\n<p><\/p>\n<p>Try \u2013 \u00ebsht\u00eb parametri m\u00eb i sigurt. Gjat\u00eb startit t\u00eb PostgreSQL, kur ai alokon memory t\u00eb ndar\u00eb, p\u00ebrpiqet t\u00eb marr\u00eb at\u00eb memory nga pages t\u00eb m\u00ebdha. Dhe n\u00ebse nuk ia del, kthehet n\u00eb alokimin e zakonsh\u00ebm. Po ashtu, n\u00ebse keni FreeBSD ose Solaris, mund ta vendosni try, gjithmon\u00eb \u00ebsht\u00eb e sigurt. <\/p>\n<p><\/p>\n<p>N\u00ebse \u00ebsht\u00eb on, ai thjesht nuk starton n\u00ebse nuk arrin t\u00eb alokoj\u00eb nga pages t\u00eb m\u00ebdha. K\u00ebtu tashm\u00eb varet nga preferencat tuaja. Por n\u00ebse keni vendosur try, sigurohuni se ajo \u00e7far\u00eb nevojitet \u00ebsht\u00eb v\u00ebrtet alokuar, sepse ka shum\u00eb hap\u00ebsir\u00eb p\u00ebr gabime. Aktualisht, ky funksionalitet punon vet\u00ebm n\u00eb Linux.<\/p>\n<p><\/p>\n<p>Nj\u00eb shk\u00ebputje tjet\u00ebr e vog\u00ebl, para se t\u00eb vazhdojm\u00eb m\u00eb tej. Transparent huge pages \u2013 kjo nuk \u00ebsht\u00eb akoma p\u00ebr PostgreSQL. Ai nuk mund ta shfryt\u00ebzoj\u00eb normalisht. Dhe n\u00eb rastin e Transparent huge pages p\u00ebr nj\u00eb ngarkes\u00eb pune, kur nevojitet nj\u00eb cop\u00eb e madhe memories s\u00eb ndar\u00eb, p\u00ebrfitimet ndodhin vet\u00ebm n\u00eb volume shum\u00eb t\u00eb m\u00ebdha. N\u00ebse keni terabajt\u00eb memories, at\u00ebher\u00eb kjo mund t\u00eb ket\u00eb r\u00ebnd\u00ebsi. N\u00ebse flasim p\u00ebr aplikacione m\u00eb t\u00eb zakonshme, kur keni 32, 64, 128, 256 GB memories n\u00eb server, at\u00ebher\u00eb pages t\u00eb zakonshme t\u00eb m\u00ebdha jan\u00eb t\u00eb mira, nd\u00ebrsa Transparent thjesht fiket. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/18cf3ace8876e55b42e66f617a24f1bc.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe gj\u00ebja e fundit, q\u00eb lidhet me memory-n\u00eb, nuk \u00ebsht\u00eb drejtp\u00ebrdrejt e lidhur me fruputin, por mund t\u00eb prish\u00eb shum\u00eb jet\u00ebn. I gjith\u00eb kapaciteti i kalimit do t\u00eb ndikohet r\u00ebnd\u00eb nga fakti q\u00eb serveri vazhdimisht swap-on. <\/p>\n<p><\/p>\n<p>Dhe do t\u00eb jet\u00eb shum\u00eb e pak\u00ebndshme n\u00eb disa momente. Dhe problemi kryesor \u00ebsht\u00eb se n\u00eb b\u00ebrthamat moderne, sjellja \u00ebsht\u00eb paksa ndryshe nga b\u00ebrthamat m\u00eb t\u00eb vjetra Linux. Dhe kjo \u00ebsht\u00eb di\u00e7ka n\u00eb t\u00eb cil\u00ebn \u00ebsht\u00eb mjaft e pak\u00ebndshme t\u00eb shkel\u00ebsh, sepse kur flasim p\u00ebr ndonj\u00eb pun\u00eb me swap, p\u00ebrfundon me vones\u00ebn e OOM-killer. Dhe OOM-killer, q\u00eb nuk erdhi n\u00eb koh\u00eb dhe hodhi PostgreSQL, \u00ebsht\u00eb shum\u00eb e pak\u00ebndshme. Kjo do t\u00eb merret vesh nga t\u00eb gjith\u00eb, dometh\u00ebn\u00eb, deri te p\u00ebrdoruesi m\u00eb i fundit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/c7c46ff0f4cd8cfcfb21978dd1da4c08.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodh? Keni atje nj\u00eb sasi t\u00eb madhe memorjeje, gjith\u00e7ka funksionon mir\u00eb. Por p\u00ebr nj\u00eb arsye, serveri ngec n\u00eb swap dhe ngadal\u00ebson p\u00ebr k\u00ebt\u00eb arsye. Duke u dukur, ka shum\u00eb memorje, por k\u00ebshtu ndodh. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/24d19929f1506cd9768a836095a24dfb.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00eb par\u00eb ne rekomandonim q\u00eb vm.swappiness t\u00eb vendoset n\u00eb zero, dometh\u00ebn\u00eb, t\u00eb \u00e7aktivizohet swap. M\u00eb par\u00eb dukej se 32 GB memorje dhe buferat p\u00ebrkat\u00ebs t\u00eb shp\u00ebrndar\u00eb ishin nj\u00eb sasi e madhe. Q\u00ebllimi kryesor i swap-it \u00ebsht\u00eb t\u00eb ket\u00eb nj\u00eb vend ku t\u00eb vendoset thelbi, n\u00ebse zhvillohemi. Dhe kjo tashm\u00eb nuk po realizohej. Dhe \u00e7far\u00eb do t\u00eb b\u00ebsh me k\u00ebt\u00eb thelb? Kjo \u00ebsht\u00eb nj\u00eb detyr\u00eb, kur nuk \u00ebsht\u00eb shum\u00eb e qart\u00eb pse ne kemi nevoj\u00eb p\u00ebr swap, sidomos t\u00eb till\u00eb q\u00eb jan\u00eb t\u00eb m\u00ebdhenj. <\/p>\n<p><\/p>\n<p>Por n\u00eb b\u00ebrthamat m\u00eb moderne, dometh\u00ebn\u00eb n\u00eb versionet e tret\u00eb, sjellja ka ndryshuar. Dhe n\u00ebse vendosni swap-in n\u00eb zero, dometh\u00ebn\u00eb, ta \u00e7aktivizoni, her\u00ebt a von\u00eb, madje edhe kur ka ende nj\u00eb sasi t\u00eb caktuar memorjeje, OOM-killer do t'ju vij\u00eb p\u00ebr t\u00eb vrar\u00eb p\u00ebrdoruesit m\u00eb intensiv\u00eb. Sepse ai do t\u00eb mendoj\u00eb se me nj\u00eb ngarkes\u00eb t\u00eb till\u00eb, na ka mbetur pak dhe ne do t\u00eb rr\u00ebzohemi, dometh\u00ebn\u00eb, jo t\u00eb eliminojm\u00eb procesin sistemor, por t\u00eb vrasim di\u00e7ka m\u00eb pak t\u00eb r\u00ebnd\u00ebsishme. Kjo do t\u00eb rezultoj\u00eb t\u00eb jet\u00eb p\u00ebrdoruesi intensiv i memorjes s\u00eb shp\u00ebrndar\u00eb, pra postmaster. Dhe pas k\u00ebsaj, do t\u00eb jet\u00eb mir\u00eb n\u00ebse nuk do t\u00eb jet\u00eb e nevojshme t\u00eb rikuperoni baz\u00ebn. <\/p>\n<p><\/p>\n<p>Prandaj tani, sipas kujtimit tim, shumica e distribucioneve baz\u00eb jan\u00eb diku rreth 6, dometh\u00ebn\u00eb, n\u00eb cilin moment duhet t\u00eb filloni t\u00eb p\u00ebrdorni swap n\u00eb var\u00ebsi t\u00eb sasis\u00eb s\u00eb memorjes q\u00eb ka mbetur. <strong>Tani ne rekomandojm\u00eb t\u00eb vendoset vm.swappiness = 1, sepse kjo praktikisht e \u00e7aktivizon, por nuk ka efekte t\u00eb tilla si me nj\u00eb OOM-killer q\u00eb papritmas vjen dhe e eliminon t\u00eb gjith\u00eb k\u00ebt\u00eb.<\/strong> <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/20f01dca0aff819abcbb687cea69ac74.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodh m\u00eb pas? Kur flasim p\u00ebr performanc\u00ebn e bazave t\u00eb t\u00eb dh\u00ebnave dhe gradualisht arrijm\u00eb te disqet, t\u00eb gjith\u00eb fillojn\u00eb t\u00eb kapin kok\u00ebn. Sepse e v\u00ebrteta q\u00eb disku \u00ebsht\u00eb i ngadalsh\u00ebm dhe memoria e shpejt\u00eb \u00ebsht\u00eb di\u00e7ka q\u00eb t\u00eb gjith\u00ebve iu njoh q\u00eb f\u00ebmij\u00eb. T\u00eb gjith\u00eb e din\u00eb se n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave do t\u00eb ket\u00eb probleme me performanc\u00ebn e diskut.<\/p>\n<p><\/p>\n<p>Problemi kryesor me performanc\u00ebn e PostgreSQL, q\u00eb lidhet me shp\u00ebrthimet e checkpoints, nuk lind nga fakti se disku \u00ebsht\u00eb i ngadalsh\u00ebm. Ajo \u00ebsht\u00eb m\u00eb shum\u00eb p\u00ebr shkak t\u00eb papajtueshm\u00ebris\u00eb midis kapacitetit t\u00eb memorjes dhe diskut. Nd\u00ebrsa, ato mund t\u00eb jen\u00eb t\u00eb papajtueshme n\u00eb vende t\u00eb ndryshme. PostgreSQL nuk \u00ebsht\u00eb konfiguruar, sistemi operativ nuk \u00ebsht\u00eb konfigurur, hardueri nuk \u00ebsht\u00eb i sakt\u00eb dhe hardueri \u00ebsht\u00eb i gabuar. Ky problem ndodh vet\u00ebm n\u00eb rast se gjith\u00e7ka ndodh si\u00e7 duhet, dometh\u00ebn\u00eb ose nuk ka ngarkes\u00eb, ose konfigurimet dhe hardueri jan\u00eb mir\u00ebsisht t\u00eb p\u00ebrshtatura. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/c4d841f9ed48bb5bb1b29bd9f189753f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb \u00ebsht\u00eb dhe si duket kjo? Zakonisht, njer\u00ebzit q\u00eb punojn\u00eb me PostgreSQL e ndiejn\u00eb veten n\u00eb k\u00ebt\u00eb situat\u00eb shpesh. Do ta shpjegoj. Si\u00e7 thash\u00eb, PostgreSQL her\u00eb pas here b\u00ebn checkpoints p\u00ebr t\u00eb kaluar faqet e ndotura nga memoria e ndar\u00eb n\u00eb disk. N\u00ebse kemi nj\u00eb volum t\u00eb madh memorjeje t\u00eb ndar\u00eb, at\u00ebher\u00eb checkpoint fillon t\u00eb ngarkoj\u00eb diskun me intensitet, sepse kalon k\u00ebto faqe fsync. Ai arrin n\u00eb buffer-in e kernel-it dhe shkruhet n\u00eb disqe me an\u00eb t\u00eb fsync. Dhe, n\u00ebse volumi i k\u00ebtij procesi \u00ebsht\u00eb i madh, mund t\u00eb v\u00ebrejm\u00eb nj\u00eb efekt t\u00eb pak\u00ebndsh\u00ebm, dmth. nj\u00eb shfryt\u00ebzim t\u00eb madh t\u00eb disqeve.<\/p>\n<p><\/p>\n<p>K\u00ebtu kam dy grafik\u00eb. Tani do ta shpjegoj se \u00e7far\u00eb jan\u00eb. K\u00ebto jan\u00eb dy grafika t\u00eb korrelacionuar n\u00eb koh\u00eb. Grafik\u00eb i par\u00eb \u00ebsht\u00eb shfryt\u00ebzimi i diskut. K\u00ebtu ai arrin pothuajse deri n\u00eb 90% n\u00eb k\u00ebt\u00eb moment. N\u00ebse ju keni nj\u00eb baz\u00eb t\u00eb dh\u00ebnash me disqe fizike, me nj\u00eb kontrollues RAID me shfryt\u00ebzim rreth 90%, at\u00ebher\u00eb kjo \u00ebsht\u00eb nj\u00eb lajm i keq. Kjo tregon se pak m\u00eb shum\u00eb dhe do t\u00eb arrij\u00eb 100 dhe hyrja-dalja do t\u00eb ndaloj\u00eb. <\/p>\n<p><\/p>\n<p>N\u00ebse keni nj\u00eb grumbull disqesh, at\u00ebher\u00eb aty \u00ebsht\u00eb nj\u00eb histori pak m\u00eb ndryshe. Ajo varet nga m\u00ebnyra se si \u00ebsht\u00eb konfiguruar, \u00e7far\u00eb lloji grumbulli \u00ebsht\u00eb etj. <\/p>\n<p><\/p>\n<p>Nd\u00ebrkoh\u00eb, k\u00ebtu \u00ebsht\u00eb konfiguruar nj\u00eb grafik nga pamja e brendshme e Postgres, e cila tregon se si ndodh checkpoint. Dhe me ngjyr\u00eb t\u00eb gjelb\u00ebr k\u00ebtu \u00ebsht\u00eb e shfaqur sasia e bufeve, k\u00ebtyre faqeve t\u00eb ndotura, q\u00eb ka arritur n\u00eb k\u00ebt\u00eb moment n\u00eb k\u00ebt\u00eb checkpoint p\u00ebr sinkronizim. Dhe kjo \u00ebsht\u00eb gj\u00ebja kryesore q\u00eb duhet t\u00eb dini. Ne shohim se kemi shum\u00eb faqe k\u00ebtu dhe n\u00eb nj\u00eb moment jemi bllokuar n\u00eb disk, dmth. kemi shkruar-shkruar, k\u00ebtu \u00ebsht\u00eb e qart\u00eb q\u00eb sistemi i diskut \u00ebsht\u00eb shum\u00eb i angazhuar. Dhe checkpoint-i yn\u00eb ka nj\u00eb ndikim t\u00eb madh n\u00eb disk. N\u00eb m\u00ebnyr\u00eb ideale, situata duhet t\u00eb duket, m\u00eb shum\u00eb k\u00ebshtu, dmth. kemi pasur m\u00eb pak shkarkime. Dmth. utilit\u00ebzimi \u00ebsht\u00eb i vog\u00ebl, por ndonj\u00ebher\u00eb kemi shkarkime k\u00ebtu. <\/p>\n<p><\/p>\n<p>\u00c7far\u00eb duhet t\u00eb b\u00ebni p\u00ebr t\u00eb zgjidhur k\u00ebt\u00eb problem? N\u00ebse IO-ja n\u00ebn baz\u00ebn e t\u00eb dh\u00ebnave \u00ebsht\u00eb ndalur, at\u00ebher\u00eb kjo do t\u00eb thot\u00eb se t\u00eb gjith\u00eb p\u00ebrdoruesit q\u00eb vijn\u00eb p\u00ebr t\u00eb ekzekutuar k\u00ebrkesat e tyre do t\u00eb presin. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/3efac5fe79443c32f56ec8da2f418116.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00ebse e shihni nga k\u00ebndv\u00ebshtrimi i Linux-it, n\u00ebse keni marr\u00eb hardware t\u00eb mir\u00eb, e keni konfiguruar sakt\u00eb, keni konfiguruar PostgreSQL-n\u00eb n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb b\u00ebj\u00eb k\u00ebto checkpoints m\u00eb rrall\u00eb, t\u00eb shp\u00ebrndaj\u00eb ato n\u00eb koh\u00eb nj\u00ebra pas tjetr\u00ebs, at\u00ebher\u00eb ju arrini n\u00eb parametrat default t\u00eb Debian. P\u00ebr shumic\u00ebn e distribucioneve t\u00eb Linux-it, kjo \u00ebsht\u00eb pamja: vm.dirty_ratio=20, vm.dirty_background_ratio=10.<\/p>\n<p><\/p>\n<p>\u00c7far\u00eb do t\u00eb thot\u00eb kjo? Me kernelin 2.6 u shfaq nj\u00eb demon p\u00ebr pastrimin. Pgflush, n\u00eb var\u00ebsi t\u00eb atij q\u00eb e p\u00ebrdor, merret me heqjen e faqeve t\u00eb ndotura nga buffer-i i kernelit dhe heqjen, kur \u00ebsht\u00eb e nevojshme, pa marr\u00eb parasysh, kur heqja e pasme nuk ndihmon m\u00eb. <\/p>\n<p><\/p>\n<p>Kur ndodh heqja e pasme? Kur 10% e gjith\u00eb memories operativ\u00eb q\u00eb \u00ebsht\u00eb n\u00eb server \u00ebsht\u00eb e z\u00ebn\u00eb me faqe t\u00eb ndotura n\u00eb buffer-in e kernelit, at\u00ebher\u00eb thirret nj\u00eb funksion special q\u00eb shkarkon n\u00eb background. Pse \u00ebsht\u00eb e pasme? Ajo merr si paramet\u00ebr sa faqe t\u00eb heq\u00eb. Dhe, p\u00ebr shembull, heq N faqe. Dhe p\u00ebr nj\u00eb koh\u00eb t\u00eb caktuar, ajo fle. M\u00eb pas ajo vjen p\u00ebrs\u00ebri dhe heq nj\u00eb sasi tjet\u00ebr faqesh. <\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb nj\u00eb histori ekstremisht e thjesht\u00eb. K\u00ebtu, detyra \u00ebsht\u00eb si me nj\u00eb pishin\u00eb, kur n\u00eb nj\u00eb tub derdhet, n\u00eb tjetrin mbushet. Na erdhi checkpoint dhe n\u00ebse ai d\u00ebrgoi pak faqe t\u00eb ndotura p\u00ebr shkarkim, ngadal\u00eb nga buferi i kernelit pgflush do ta zgjidh\u00eb k\u00ebt\u00eb situat\u00eb. <\/p>\n<p><\/p>\n<p>N\u00ebse k\u00ebto faqe t\u00eb pista vazhdojn\u00eb t\u00eb grumbullohen, ato do t\u00eb grumbullohen deri n\u00eb 20%, pas k\u00ebsaj n\u00eb prioritetin e OS \u00ebsht\u00eb t\u00eb shkruaj\u00eb gjith\u00e7ka n\u00eb disk, sepse do t\u00eb ket\u00eb nj\u00eb nd\u00ebrprerje t\u00eb energjis\u00eb dhe do t\u00eb kemi probleme. Do t\u00eb humbasim k\u00ebto t\u00eb dh\u00ebna, p\u00ebr shembull. <\/p>\n<p><\/p>\n<p>\u00c7far\u00eb \u00ebsht\u00eb triku? <strong>Triku q\u00ebndron n\u00eb at\u00eb se k\u00ebta parametra n\u00eb bot\u00ebn moderne, 20 dhe 10% e t\u00ebr\u00eb memorjes operative q\u00eb keni n\u00eb makin\u00ebn tuaj, jan\u00eb krejt\u00ebsisht t\u00eb tmerrsh\u00ebm nga k\u00ebndv\u00ebshtrimi i kapacitetit t\u00eb \u00e7do sistemi disku q\u00eb keni.<\/strong> <\/p>\n<p><\/p>\n<p>Imagjinoni se keni 128 GB memorie operative. 12,8 GB arrijn\u00eb n\u00eb sistemin tuaj t\u00eb diskut. Dhe cilido kesh q\u00eb mund t\u00eb keni atje, \u00e7far\u00ebdo grupi q\u00eb keni atje, ata nuk do ta mbajn\u00eb aq. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/1e4bab2fad7d3a05dc76cdd9d153f46c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><strong>Prandaj ne rekomandojm\u00eb q\u00eb k\u00ebto numra t\u00eb konfigurohen menj\u00ebher\u00eb n\u00eb p\u00ebrputhje me mund\u00ebsit\u00eb e kontrolluesit tuaj RAID.<\/strong> K\u00ebtu kam dh\u00ebn\u00eb nj\u00eb rekomandim p\u00ebr nj\u00eb kontrollues q\u00eb ka 512 MB kesh. <\/p>\n<p><\/p>\n<p>Kontrollohet gjith\u00e7ka shum\u00eb thjesht\u00eb. Mund t\u00eb vendosni vm.dirty_background n\u00eb byte. Dhe k\u00ebto cil\u00ebsime anullojn\u00eb dy t\u00eb parat. Ose ratio baz\u00eb, ose ato q\u00eb jan\u00eb aktivizuar me byte do t\u00eb funksionojn\u00eb. Por q\u00eb p\u00ebr shkak se un\u00eb jam nj\u00eb konsulent DBA dhe punoj me klient\u00eb t\u00eb ndrysh\u00ebm, p\u00ebrpiqem t\u00eb jem i kujdessh\u00ebm dhe prandaj, n\u00ebse \u00ebsht\u00eb n\u00eb byte, at\u00ebher\u00eb n\u00eb byte. Askush nuk dha ndonj\u00eb garanci q\u00eb nj\u00eb admin i mir\u00eb nuk do t\u00eb shtoj\u00eb memorie n\u00eb server, nuk do ta rindez\u00eb at\u00eb, nd\u00ebrsa numri do t\u00eb mbetet i nj\u00ebjt\u00eb. Thjesht llogaritni k\u00ebto numra q\u00eb gjith\u00e7ka t\u00eb hyj\u00eb me garanci. <\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodh n\u00ebse nuk arrini? Kam shkruar se \u00e7do flushing ndalet efektivisht, por n\u00eb t\u00eb v\u00ebrtet\u00eb kjo \u00ebsht\u00eb nj\u00eb figur\u00eb e fjal\u00ebve. Sistemi operativ ka nj\u00eb problem t\u00eb madh - ka shum\u00eb faqe t\u00eb pista, prandaj ndalet efektivisht IO q\u00eb gjenerojn\u00eb klient\u00ebt tuaj, dmth, aplikacioni q\u00eb d\u00ebrgon nj\u00eb k\u00ebrkes\u00eb SQL n\u00eb databaz\u00eb, ai pret. \u00c7do hyrje-dali n\u00eb t\u00eb \u00ebsht\u00eb n\u00eb prioritetin m\u00eb t\u00eb ul\u00ebt, sepse databaza \u00ebsht\u00eb e angazhuar me checkpoint-in. Dhe kur do ta p\u00ebrfundoj\u00eb at\u00eb \u00ebsht\u00eb krejt\u00ebsisht e paqart\u00eb. Dhe kur t\u00eb arrini flushing jo n\u00eb sfond, kjo do t\u00eb thot\u00eb q\u00eb t\u00eb gjitha IO-t\u00eb jan\u00eb e angazhuar me t\u00eb. Dhe derisa t\u00eb p\u00ebrfundoj\u00eb, nuk do t\u00eb b\u00ebni asgj\u00eb. <\/p>\n<p><\/p>\n<p>K\u00ebtu ka edhe dy momente t\u00eb r\u00ebnd\u00ebsishme q\u00eb e kalojn\u00eb k\u00ebt\u00eb raport. K\u00ebto cil\u00ebsime duhet t\u00eb p\u00ebrputhen me cil\u00ebsimet n\u00eb postgresql.conf, dmth, cil\u00ebsimet e checkpoints. Dhe sistemi juaj disk duhet t\u00eb jet\u00eb i konfiguruar n\u00eb m\u00ebnyr\u00eb adekuate. <strong>N\u00ebse keni nj\u00eb RAID me cache, at\u00ebher\u00eb duhet t\u00eb ket\u00eb nj\u00eb bateri mbi t\u00eb.<\/strong> Njer\u00ebzit blejn\u00eb RAID me nj\u00eb cache t\u00eb mir\u00eb pa bateri. <strong>N\u00ebse keni SSD n\u00eb RAID, ato duhet t\u00eb jen\u00eb serverike, duhet t\u00eb ken\u00eb kondensator\u00eb.<\/strong> K\u00ebtu \u00ebsht\u00eb nj\u00eb list\u00eb e detajuar kontrolli. N\u00eb k\u00ebt\u00eb lidhje ka raportin tim mbi si t\u00eb konfiguroni performanc\u00ebn e disqeve n\u00eb PostgreSQL. Aty jan\u00eb t\u00eb gjitha k\u00ebto lista kontrolli. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/0ca13d550cb9eec4163f886467b2b3ea.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb tjet\u00ebr mund ta komplikoj\u00eb shum\u00eb jet\u00ebn? K\u00ebto jan\u00eb dy parametra. Ato jan\u00eb relativisht t\u00eb reja. Ato mund t\u00eb jen\u00eb t\u00eb aktivizuara si parazgjedhje n\u00eb aplikacione t\u00eb ndryshme. Dhe mund t\u00eb komplikojn\u00eb jet\u00ebn po aq, n\u00ebse aktivizohen gabimisht. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/35aa98d67ff1f089a751a5fd808bd1b6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ekzistojn\u00eb dy gj\u00ebra relativisht t\u00eb reja. Ato tashm\u00eb jan\u00eb paraqitur n\u00eb b\u00ebrtham\u00ebn e tret\u00eb. K\u00ebto jan\u00eb sched_migration_cost n\u00eb nanosekonda dhe sched_autogroup_enabled, e cila \u00ebsht\u00eb aktivizuar si parazgjedhje. <\/p>\n<p><\/p>\n<p>Dhe si i \u00e7rregullojn\u00eb ata jet\u00ebn? \u00c7far\u00eb \u00ebsht\u00eb sched_migration_cost? N\u00eb Linux scheduler mund t\u00eb migroj\u00eb nj\u00eb proces nga nj\u00eb CPU n\u00eb nj\u00eb tjet\u00ebr. Dhe p\u00ebr PostgreSQL, i cili ekzekuton k\u00ebrkesa, migrimi n\u00eb nj\u00eb CPU tjet\u00ebr nuk kuptohet aspak p\u00ebr \u00e7far\u00eb duhej. <strong>P\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave \u2013 kjo \u00ebsht\u00eb shum\u00eb keq.<\/strong> <strong>Prandaj, nj\u00eb politik\u00eb e arsyeshme \u00ebsht\u00eb t\u00eb vendos\u00ebsh migration_cost n\u00eb nj\u00eb vler\u00eb t\u00eb madhe, t\u00eb pakt\u00ebn disa mij\u00ebra nanosekonda.<\/strong> <\/p>\n<p><\/p>\n<p>\u00c7far\u00eb do t\u00eb thot\u00eb kjo p\u00ebr scheduler? Do t\u00eb mendohet se p\u00ebr nj\u00eb periudh\u00eb t\u00eb caktuar koha, ky proces \u00ebsht\u00eb ende i nxeht\u00eb. K\u00ebshtu q\u00eb, n\u00ebse keni nj\u00eb transaksion t\u00eb gjat\u00eb q\u00eb merret me di\u00e7ka gjat\u00eb, scheduler do ta kuptoj\u00eb k\u00ebt\u00eb. Ai do t\u00eb mendoj\u00eb se derisa t\u00eb kaloj\u00eb ky timeout, nuk ka nevoj\u00eb ta migroj\u00eb k\u00ebt\u00eb proces askund. N\u00ebse n\u00eb at\u00eb moment procesi po b\u00ebn di\u00e7ka, ai nuk do t\u00eb migrohet askund, ai do t\u00eb p\u00ebrfundoj\u00eb pun\u00ebn n\u00eb at\u00eb CPU q\u00eb iu \u00ebsht\u00eb caktuar. Dhe rezultati \u00ebsht\u00eb i shk\u00eblqyer. <\/p>\n<p><\/p>\n<p>Momentin tjet\u00ebr \u2013 \u00ebsht\u00eb autogroup. Ka nj\u00eb ide t\u00eb mir\u00eb p\u00ebr ngarkesa specifike, q\u00eb nuk kan\u00eb t\u00eb b\u00ebjn\u00eb me bazat moderne t\u00eb t\u00eb dh\u00ebnave \u2013 kjo \u00ebsht\u00eb grupimi i proceseve sipas terminalit virtual nga i cili jan\u00eb nisur. Kjo \u00ebsht\u00eb e p\u00ebrshtatshme p\u00ebr disa detyra. <strong>N\u00eb praktik\u00eb, PostgreSQL \u00ebsht\u00eb nj\u00eb sistem me shum\u00eb procese me prefork, i cili aktivizohet nga nj\u00eb terminal. Keni nj\u00eb lock writer, nj\u00eb checkpoint dhe gjitha k\u00ebrkesat tuaja nga klient\u00ebt do t\u00eb grumbullohen n\u00eb nj\u00eb scheduler, n\u00eb nj\u00eb CPU. Dhe atje do t\u00eb presin s\u00eb bashku p\u00ebrsa koh\u00eb q\u00eb ai do t\u00eb lirohet, duke penguar nj\u00ebri-tjetrin dhe duke z\u00ebn\u00eb rastin e tij p\u00ebr m\u00eb gjat\u00eb. Kjo \u00ebsht\u00eb nj\u00eb histori q\u00eb nuk ka nevoj\u00eb n\u00eb nj\u00eb ngarkes\u00eb t\u00eb till\u00eb dhe prandaj duhet t\u00eb \u00e7aktivizohet.<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/55e829d1c1b69b7f6eaf5fc89dad30d3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kolegu im, Aleksej Lesovski, b\u00ebri teste me nj\u00eb pgbench t\u00eb thjesht\u00eb, ku rriti me nj\u00eb rend migrimin e kostos dhe \u00e7aktivizoi autogroup. <strong>Dallimi n\u00eb nj\u00eb pajisje t\u00eb keqe doli pothuajse 10%.<\/strong>. Ka nj\u00eb diskutim n\u00eb list\u00ebn e postave t\u00eb postgres, ku njer\u00ebzit sjellin rezultate se si ndryshimet e tilla kan\u00eb ndikuar n\u00eb shpejt\u00ebsin\u00eb e k\u00ebrkes\u00ebs. <strong>kan\u00eb ndikuar n\u00eb 50%.<\/strong>. Ka shum\u00eb histori t\u00eb till\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/b3d1983ec2a37f8b85129c93f2373644.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe p\u00ebrfundimisht, p\u00ebr politik\u00ebn e kursimit t\u00eb energjis\u00eb. \u00cbsht\u00eb mir\u00eb q\u00eb tani Linux mund t\u00eb p\u00ebrdoret n\u00eb laptop. Dhe ai do t\u00eb konsumohet duksh\u00ebm mir\u00eb energjin\u00eb e bateris\u00eb. Por papritur zbulon se kjo mund t\u00eb ndodh\u00eb edhe n\u00eb server. <\/p>\n<p><\/p>\n<p>M\u00eb tej, n\u00ebse merrni server\u00eb me qira nga ndonj\u00eb ofrues hostimi, at\u00ebher\u00eb \"mir\u00ebsit\u00eb\" <a class=\"wpil_keyword_link\" href=\"https:\/\/prohoster.info\/sq\/\"   title=\"hoste\" data-wpil-keyword-link=\"linked\"  data-wpil-monitor-id=\"1200\">hoste<\/a> nuk kujdesen q\u00eb ju t\u00eb keni nj\u00eb performanc\u00eb m\u00eb t\u00eb mir\u00eb. Objektivi i tyre \u00ebsht\u00eb q\u00eb t\u00eb sigurojn\u00eb p\u00ebrdorimin maksimal t\u00eb harduerit. Prandaj, ata mund t\u00eb aktivizojn\u00eb automatikisht modin e kursimit t\u00eb energjis\u00eb n\u00eb sistemin operativ.<\/p>\n<p><\/p>\n<p><strong>N\u00ebse p\u00ebrdorni n\u00eb serverin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave me ngarkes\u00eb intensive k\u00ebt\u00eb mir\u00ebsi, zgjedhja juaj \u00ebsht\u00eb acpi_cpufreq + performance. Edhe me ondemand do t\u00eb keni probleme.<\/strong> <\/p>\n<p><\/p>\n<p>Intel_pstate \u00ebsht\u00eb nj\u00eb driver pak m\u00eb ndryshe. Dhe tani preferohet ky, si m\u00eb i fundit dhe m\u00eb i mir\u00eb n\u00eb funksionim.<\/p>\n<p><\/p>\n<p>Dhe, p\u00ebr pasoj\u00eb, guvernatori vet\u00ebm performance. Ondemand, powersave dhe t\u00eb gjitha t\u00eb tjerat - nuk jan\u00eb p\u00ebr ju. <\/p>\n<p><\/p>\n<p>Rezultatet nga explain analyze PostgreSQL mund t\u00eb ndryshojn\u00eb n\u00eb disa renditje, n\u00ebse aktivizoni powersave, sepse praktikisht CPU-ja do t\u00eb planifikohet n\u00eb nj\u00eb m\u00ebnyr\u00eb krejt\u00ebsisht t\u00eb paparashikueshme n\u00ebn baz\u00ebn tuaj.<\/p>\n<p><\/p>\n<p>K\u00ebto gj\u00ebra mund t\u00eb jen\u00eb t\u00eb aktivizuara p\u00ebr defolt. Shikoni me kujdes - a i kan\u00eb aktivizuar ato p\u00ebr defolt. Kjo mund t\u00eb jet\u00eb v\u00ebrtet nj\u00eb problem i madh. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Optimizimi i Linux p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar performanc\u00ebn e PostgreSQL. Ilya Kosmodemyansky\" src=\"\/wp-content\/uploads\/2020\/06\/ef32dafd9c8ea3403dc31c34ee2b5da8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00eb fund, doja t\u00eb fal\u00ebnderoja djemt\u00eb nga ekipi yn\u00eb DBA PosgreSQL-Consulting, ve\u00e7an\u00ebrisht Maksin Bogukun dhe Aleksein Lesovskin, t\u00eb cil\u00ebt \u00e7do dit\u00eb p\u00ebrballen me sfida n\u00eb k\u00ebt\u00eb fush\u00eb. Ne p\u00ebrpiqemi t\u00eb b\u00ebjm\u00eb gjith\u00e7ka sa m\u00eb mir\u00eb p\u00ebr klient\u00ebt tan\u00eb, q\u00eb gjith\u00e7ka t\u00eb funksionoj\u00eb p\u00ebr ta. K\u00ebtu \u00ebsht\u00eb si me udh\u00ebzimet p\u00ebr sigurin\u00eb ajrore. \u00c7do gj\u00eb \u00ebsht\u00eb shkruar me gjak. \u00c7do nj\u00ebra nga k\u00ebto bolta \u00ebsht\u00eb zbuluar gjat\u00eb nj\u00eb problemi t\u00eb caktuar. Me k\u00ebnaq\u00ebsi i ndaj ato me ju.<\/p>\n<p><\/p>\n<p>Pyetje:<\/p>\n<p><\/p>\n<p><em>Faleminderit! N\u00ebse, p\u00ebr shembull, nj\u00eb kompani d\u00ebshiron t\u00eb kursej\u00eb dhe t\u00eb vendos\u00eb baz\u00ebn e t\u00eb dh\u00ebnave dhe logjik\u00ebn e aplikacionit n\u00eb nj\u00eb server t\u00eb vet\u00ebm, ose n\u00ebse kompania ndjek tendenc\u00ebn moderne t\u00eb arkitekturave mikrosh\u00ebrbimesh, ku PostgreSQL ekzekutohet n\u00eb nj\u00eb kontejner. Cila \u00ebsht\u00eb ideja? Sysctl ndikon globalisht n\u00eb gjith\u00eb b\u00ebrtham\u00ebn. Nuk kam d\u00ebgjuar q\u00eb sysctl t\u00eb virtualizohej ndonj\u00ebher\u00eb, q\u00eb t\u00eb funksiononte ve\u00e7mas n\u00eb kontejner. Ka vet\u00ebm cgroup dhe atje ka kontroll vet\u00ebm p\u00ebr nj\u00eb pjes\u00eb. Si mund t\u00eb jetoni me k\u00ebt\u00eb? Ose n\u00ebse d\u00ebshironi performanc\u00eb, at\u00ebher\u00eb ekzekutoni PostgreSQL n\u00eb nj\u00eb server fizike t\u00eb ve\u00e7ant\u00eb dhe optimizojeni at\u00eb?<\/em><\/p>\n<p><\/p>\n<p>Ne e kemi p\u00ebrgjigjur pyetjen tuaj n\u00eb tre m\u00ebnyra. N\u00ebse flasim p\u00ebr nj\u00eb server fizik, q\u00eb mund t\u00eb optimizohet etj., at\u00ebher\u00eb relaksohuni, gjith\u00e7ka do t\u00eb funksionoj\u00eb mir\u00eb pa k\u00ebto konfigurime. N\u00ebse keni nj\u00eb ngarkes\u00eb t\u00eb till\u00eb q\u00eb k\u00ebrkon k\u00ebto konfigurime, at\u00ebher\u00eb do t\u00eb shkoni te serveri fizik m\u00eb shpejt se sa te k\u00ebto konfigurime.<\/p>\n<p><\/p>\n<p>Cila \u00ebsht\u00eb problemi? N\u00ebse \u00ebsht\u00eb nj\u00eb VM, at\u00ebher\u00eb me shum\u00eb mund\u00ebsi do t\u00eb keni shum\u00eb probleme, p\u00ebr shembull, me at\u00eb se n\u00eb shumic\u00ebn e VMs ka nj\u00eb latenc\u00eb disku q\u00eb \u00ebsht\u00eb mjaft inconsistente. Edhe n\u00ebse kapaciteti i kalimit t\u00eb t\u00eb dh\u00ebnave \u00ebsht\u00eb i mir\u00eb, nj\u00eb transaksion i vet\u00ebm i d\u00ebshtuar n\u00eb operacionin e hyrjes-daljes, q\u00eb nuk ndikon shum\u00eb n\u00eb kapacitetin mesatar t\u00eb kalimit, q\u00eb ndodhi n\u00eb momentin e checkpoint-it ose n\u00eb momentin e shkrimit n\u00eb WAL, do t'i shkaktoj\u00eb shum\u00eb problemet baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Dhe do ta vini re k\u00ebt\u00eb p\u00ebrpara se t\u00eb p\u00ebrballeni me k\u00ebto probleme. <\/p>\n<p><\/p>\n<p>N\u00ebse keni NGINX n\u00eb t\u00eb nj\u00ebjtin server, do t\u00eb keni t\u00eb nj\u00ebjtin problem. Ai do t\u00eb garoj\u00eb p\u00ebr memorien e ndar\u00eb. Dhe nuk do t\u00eb arrini te ato probleme q\u00eb p\u00ebrmenden k\u00ebtu.<\/p>\n<p><\/p>\n<p>Por otro lado, algunos de estos par\u00e1metros seguir\u00e1n siendo relevantes para usted. Por ejemplo, con sysctl establecer dirty_ratio para que no sea tan extremo; de todos modos, esto ayudar\u00e1. De una forma u otra, tendr\u00e1 interacci\u00f3n con el disco. Y ser\u00e1 de una manera incorrecta. Estos son realmente los par\u00e1metros predeterminados que mostr\u00e9. Y en cualquier caso, es mejor cambiarlos. <\/p>\n<p><\/p>\n<p>Con NUMA pueden surgir problemas. VmWare, por ejemplo, funciona bien con NUMA con configuraciones completamente opuestas. Y aqu\u00ed hay que elegir: servidor f\u00edsico o no f\u00edsico. <\/p>\n<p><\/p>\n<p><em>Tengo una pregunta relacionada con Amazon AWS. Tienen im\u00e1genes preconfiguradas. Una de ellas se llama Amazon RDS. \u00bfHay alguna configuraci\u00f3n personalizada para su sistema operativo?<\/em><\/p>\n<p><\/p>\n<p>Hay configuraciones, pero son otras configuraciones. Aqu\u00ed estamos configurando el sistema operativo en funci\u00f3n de c\u00f3mo la base de datos usar\u00e1 esto. Y all\u00ed hay par\u00e1metros que determinan hacia d\u00f3nde ir en este momento, una especie de shaping. Es decir, necesitamos tantos recursos, y los utilizaremos ahora. Despu\u00e9s de eso, Amazon RDS incorpora esos recursos, y ah\u00ed la productividad cae. Hay historias separadas sobre c\u00f3mo algunas personas comienzan a experimentar con esto. A veces con bastante \u00e9xito. Pero esto no tiene relaci\u00f3n con la configuraci\u00f3n del sistema operativo. Es una especie de hackeo de nube. Es otra historia.<\/p>\n<p><\/p>\n<p><em>\u00bfPor qu\u00e9 las Transparent huge pages no tienen efecto en comparaci\u00f3n con Huge TLB?<\/em><\/p>\n<p><\/p>\n<p>No tienen. Se puede explicar de muchas maneras. Pero en realidad, simplemente no lo tienen. \u00bfCu\u00e1l es la historia de PostgreSQL? Al iniciar, asigna un gran bloque de memoria compartida. Transparent o no transparent, eso no importa en absoluto. El hecho de que se asignen al inicio lo explica todo. Y si hay mucha memoria y se necesita reconstruir el segmento shared_memory, entonces Transparent huge pages ser\u00e1 relevante. En PostgreSQL, simplemente se asigna un gran bloque al inicio y ya est\u00e1; no pasa nada especial despu\u00e9s. Claro, se puede utilizar, pero hay un riesgo de corrupci\u00f3n en shared_memory cuando se vuelve a asignar algo. PostgreSQL no sabe nada de eso.<\/p>\n<p>Burimi: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/505108\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0420\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u0430 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 2015 \u0433\u043e\u0434\u0430 \u0418\u043b\u044c\u0438 \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u043e\u0433\u043e &quot;Linux tuning to improve PostgreSQL performance&quot; Disclaimer: \u0417\u0430\u043c\u0435\u0447\u0443 \u0447\u0442\u043e \u0434\u043e\u043a\u043b\u0430\u0434 \u044d\u0442\u043e\u0442 \u0434\u0430\u0442\u0438\u0440\u043e\u0432\u0430\u043d \u043d\u043e\u044f\u0431\u0440\u0435\u043c 2015 \u0433\u043e\u0434\u0430 \u2014 \u043f\u0440\u043e\u0448\u043b\u043e \u0431\u043e\u043b\u044c\u0448\u0435 4 \u043b\u0435\u0442 \u0438 \u043f\u0440\u043e\u0448\u043b\u043e \u043c\u043d\u043e\u0433\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0438. \u0420\u0430\u0441\u0441\u043c\u0430\u0442\u0440\u0438\u0432\u0430\u0435\u043c\u0430\u044f \u0432 \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u0432\u0435\u0440\u0441\u0438\u044f 9.4 \u0443\u0436\u0435 \u043d\u0435 \u043f\u043e\u0434\u0434\u0435\u0440\u0436\u0438\u0432\u0430\u0435\u0442\u0441\u044f. \u0417\u0430 \u043f\u0440\u043e\u0448\u0435\u0434\u0448\u0438\u0435 4 \u0433\u043e\u0434\u0430 \u0432\u044b\u0448\u043b\u043e 5 \u043d\u043e\u0432\u044b\u0445 \u0440\u0435\u043b\u0438\u0437\u043e\u0432 PostgreSQL \u0432\u044b\u0448\u043b\u043e \u0438 15 \u0432\u0435\u0440\u0441\u0438\u0439 \u044f\u0434\u0440\u0430 Linux. \u0415\u0441\u043b\u0438 \u043f\u0435\u0440\u0435\u043f\u0438\u0441\u044b\u0432\u0430\u0442\u044c [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":84083,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-84082","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.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0420\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u0430 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 2015 \u0433\u043e\u0434\u0430 \u0418\u043b\u044c\u0438 \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u043e\u0433\u043e &quot;Linux tuning to improve PostgreSQL performance&quot; Disclaimer: \u0417\u0430\u043c\u0435\u0447\u0443 \u0447\u0442\u043e \u0434\u043e\u043a\u043b\u0430\u0434 \u044d\u0442\u043e\u0442 \u0434\u0430\u0442\u0438\u0440\u043e\u0432\u0430\u043d \u043d\u043e\u044f\u0431\u0440\u0435\u043c 2015 \u0433\u043e\u0434\u0430 \u2014 \u043f\u0440\u043e\u0448\u043b\u043e \u0431\u043e\u043b\u044c\u0448\u0435 4 \u043b\u0435\u0442 \u0438 \u043f\u0440\u043e\u0448\u043b\u043e \u043c\u043d\u043e\u0433\u043e.\" \/>\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\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2.1\" \/>\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\udd47Linux tuning to improve PostgreSQL performance. \u0418\u043b\u044c\u044f \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u0438\u0439 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0420\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u0430 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 2015 \u0433\u043e\u0434\u0430 \u0418\u043b\u044c\u0438 \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u043e\u0433\u043e &quot;Linux tuning to improve PostgreSQL performance&quot; Disclaimer: \u0417\u0430\u043c\u0435\u0447\u0443 \u0447\u0442\u043e \u0434\u043e\u043a\u043b\u0430\u0434 \u044d\u0442\u043e\u0442 \u0434\u0430\u0442\u0438\u0440\u043e\u0432\u0430\u043d \u043d\u043e\u044f\u0431\u0440\u0435\u043c 2015 \u0433\u043e\u0434\u0430 \u2014 \u043f\u0440\u043e\u0448\u043b\u043e \u0431\u043e\u043b\u044c\u0448\u0435 4 \u043b\u0435\u0442 \u0438 \u043f\u0440\u043e\u0448\u043b\u043e \u043c\u043d\u043e\u0433\u043e.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij\" \/>\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=\"2020-06-05T05:42:28+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-06-05T05:42:28+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\udd47Ajuste de Linux para mejorar el rendimiento de PostgreSQL. Ilya Kosmodemyansky | ProHoster","description":"Transcripci\u00f3n de la conferencia de 2015 de Ilya Kosmodemyansky \"Ajuste de Linux para mejorar el rendimiento de PostgreSQL\" Disclaimer: Cabe mencionar que esta conferencia est\u00e1 fechada en noviembre de 2015, ha pasado m\u00e1s de 4 a\u00f1os y ha habido muchas novedades.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij","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\udd47Linux tuning to improve PostgreSQL performance. \u0418\u043b\u044c\u044f \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u0438\u0439 | ProHoster","og:description":"\u0420\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u0430 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 2015 \u0433\u043e\u0434\u0430 \u0418\u043b\u044c\u0438 \u041a\u043e\u0441\u043c\u043e\u0434\u0435\u043c\u044c\u044f\u043d\u0441\u043a\u043e\u0433\u043e &quot;Linux tuning to improve PostgreSQL performance&quot; Disclaimer: \u0417\u0430\u043c\u0435\u0447\u0443 \u0447\u0442\u043e \u0434\u043e\u043a\u043b\u0430\u0434 \u044d\u0442\u043e\u0442 \u0434\u0430\u0442\u0438\u0440\u043e\u0432\u0430\u043d \u043d\u043e\u044f\u0431\u0440\u0435\u043c 2015 \u0433\u043e\u0434\u0430 \u2014 \u043f\u0440\u043e\u0448\u043b\u043e \u0431\u043e\u043b\u044c\u0448\u0435 4 \u043b\u0435\u0442 \u0438 \u043f\u0440\u043e\u0448\u043b\u043e \u043c\u043d\u043e\u0433\u043e.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/linux-tuning-to-improve-postgresql-performance-ilya-kosmodemyanskij","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":"2020-06-05T05:42:28+00:00","article:modified_time":"2020-06-05T05:42:28+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"84082","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":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 15:05:32","updated":"2026-02-09 21:38:01","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\/84082","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=84082"}],"version-history":[{"count":2,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/84082\/revisions"}],"predecessor-version":[{"id":159869,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/84082\/revisions\/159869"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/84083"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=84082"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=84082"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=84082"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}