{"id":78742,"date":"2020-04-21T19:42:46","date_gmt":"2020-04-21T17:42:46","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov"},"modified":"2020-04-21T19:42:46","modified_gmt":"2020-04-21T17:42:46","slug":"promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","title":{"rendered":"Qasja industriale ndaj tuning-ut t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave\u00bb. Nikolaj Samohvalov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Ju ofroj nj\u00eb p\u00ebrmbledhje t\u00eb raportit t\u00eb Nikolay Samohvalov \"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave\"<\/strong><\/p>\n<p><\/p>\n<p>Shared_buffers = 25% \u2013 \u00ebsht\u00eb shum\u00eb apo pak? A \u00ebsht\u00eb sakt\u00ebsisht ashtu? Si t\u00eb kuptoni n\u00ebse ky rekomandim \u2013 mjaft i vjet\u00ebruar \u2013 \u00ebsht\u00eb i p\u00ebrshtatsh\u00ebm p\u00ebr rastin tuaj t\u00eb ve\u00e7ant\u00eb?<\/p>\n<p><\/p>\n<p>Ka ardhur koha t\u00eb qasemi n\u00eb \u00e7\u00ebshtjen e p\u00ebrcaktimit t\u00eb parametrave postgresql.conf \"si\u00e7 duhet\". Jo me ndihm\u00ebn e \"autotuner\u00ebve\" t\u00eb verb\u00ebr ose k\u00ebshillave t\u00eb vjetra nga artikuj dhe blogje, por n\u00eb baz\u00eb t\u00eb:<\/p>\n<p><\/p>\n<ol>\n<li>eksperiment\u00ebve t\u00eb sakta mbi bazat e t\u00eb dh\u00ebnave, q\u00eb kryhen automatikisht, n\u00eb sasi t\u00eb m\u00ebdha dhe n\u00eb kushte, sa m\u00eb af\u00ebr \"luft\u00ebs\",<\/li>\n<li>kuptimit t\u00eb thell\u00eb t\u00eb ve\u00e7orive t\u00eb funksionimit t\u00eb DBMS dhe OS.<\/li>\n<\/ol>\n<p><\/p>\n<p>Duke p\u00ebrdorur Nancy CLI (<noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres.ai\/nancy\">https:\/\/gitlab.com\/postgres.ai\/nancy<\/a><\/noindex>), ne do t\u00eb shqyrtojm\u00eb nj\u00eb shembull konkret \u2013 t\u00eb njohurit shared_buffers \u2013 n\u00eb situata t\u00eb ndryshme, n\u00eb projekte t\u00eb ndryshme dhe do t\u00eb p\u00ebrpiqemi t\u00eb kuptojm\u00eb se si t\u00eb p\u00ebrcaktojm\u00eb parametrin optimal p\u00ebr infrastruktur\u00ebn ton\u00eb, BD dhe ngarkes\u00ebn.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a15b93734b6563abaec4a713c975732a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>B\u00ebhet fjal\u00eb p\u00ebr eksperimentet mbi bazat e t\u00eb dh\u00ebnave. Kjo \u00ebsht\u00eb nj\u00eb histori q\u00eb vazhdon pak m\u00eb shum\u00eb se gjasht\u00eb muaj. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/e77255c4abaa2a01ae37fe888fe67db7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pak p\u00ebr mua. Kam m\u00eb shum\u00eb se 14 vjet p\u00ebrvoj\u00eb me Postgres. Kam themeluar disa kompani n\u00eb rrjete sociale. Kudo \u00ebsht\u00eb p\u00ebrdorur dhe p\u00ebrdoret Postgres.<\/p>\n<p><\/p>\n<p>Po ashtu, grupi RuPostgres n\u00eb Meetup, vendi i dyt\u00eb n\u00eb bot\u00eb. Po afrohemi ngadal\u00eb drejt 2,000 an\u00ebtar\u00ebve. RuPostgres.org.<\/p>\n<p><\/p>\n<p>Dhe n\u00eb konferenca t\u00eb ndryshme, duke p\u00ebrfshir\u00eb Highload, un\u00eb jam p\u00ebrgjegj\u00ebs p\u00ebr bazat e t\u00eb dh\u00ebnave, ve\u00e7an\u00ebrisht p\u00ebr Postgres q\u00eb nga fillimi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/fa009b50f1c0293eee80fc03fd2c05f0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00eb vitet e fundit kam rinisur praktik\u00ebn time t\u00eb konsulenc\u00ebs p\u00ebr Postgres n\u00eb 11 zona t\u00eb koh\u00ebs nga k\u00ebtu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/550c3ddcd516adca14d2b3de9c34b7ff.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe kur e b\u00ebra k\u00ebt\u00eb disa vjet m\u00eb par\u00eb, pata nj\u00eb pushim n\u00eb pun\u00ebn aktive manuale me Postgres, ndoshta q\u00eb nga viti 2010. U habit\u00ebm sa pak kan\u00eb ndryshuar dit\u00ebt e pun\u00ebs s\u00eb DBA, sa shum\u00eb pun\u00eb manuale duhet akoma t\u00eb p\u00ebrdoret. Dhe menj\u00ebher\u00eb m'u duk se kishte di\u00e7ka t\u00eb gabuar, duhej t\u00eb automatizoja m\u00eb shum\u00eb gj\u00ebra.<\/p>\n<p><\/p>\n<p>Dhe pasi q\u00eb gjith\u00e7ka ndodhte n\u00eb distanc\u00eb, shumica e klient\u00ebve ishte n\u00eb cloud. Dhe shum\u00eb \u00ebsht\u00eb automatizuar tashm\u00eb. P\u00ebr k\u00ebt\u00eb do flasim m\u00eb von\u00eb. K\u00ebshtu q\u00eb kjo kaloi n\u00eb ide, se duhet t\u00eb ket\u00eb disa mjete, pra nj\u00eb platform\u00eb q\u00eb do t\u00eb automatizonte praktisht t\u00eb gjitha veprimet e DBA, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mund t\u00eb menaxhonte numra t\u00eb m\u00ebdha t\u00eb bazave.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b02097f0bb23fa9d7c30e38ab9f3de7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb raport nuk do t\u00eb ket\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>\u00abPlumba t\u00eb argjendt\u00eb\u00bb dhe deklarata si \u2013 vendosni 8 GB ose 25 % shared_buffers dhe do t\u00eb jeni mir\u00eb. P\u00ebr shared_buffers nuk do t\u00eb flasim shum\u00eb. <\/li>\n<li>Hardcore \"interiors\". <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ccea359ec78a967c150ddcedc3e50a88.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>And what will happen?<\/p>\n<p><\/p>\n<ul>\n<li>There will be optimization principles that we apply and develop. There will be various ideas that come up along the way and different tools we mostly create in Open Source, meaning we build the foundation in Open Source. Moreover, we have tickets, and almost all communication happens in Open Source. You can see what we are currently doing, what will be in the next release, and so on. <\/li>\n<li>There will also be some experience using these principles and tools in a number of companies: from small startups to large corporations. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86e6f379726dec814aca616a30d36a2f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>How is all this developing?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c1422d9f2945e717c6ebb31f5fef6329.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>First of all, the main task of a DBA, besides ensuring the creation of instances, deploying backups, etc., is identifying bottlenecks and optimizing performance.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8e682ca8666e94d6688348ebe4540327.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Currently, it's organized this way. We look at monitoring, we see something, but we lack some details. We start digging a little deeper, usually by hand, and understand what to do with it one way or another.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/04599422ea6b6d57c79a4e48ba2db8b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>And there are two approaches. Pg_stat_statements is the standard default solution for identifying slow queries. And analyzing Postgres logs with pgBadger.<\/p>\n<p><\/p>\n<p>Each approach has serious drawbacks. The first approach discards all parameters. If we see groups like SELECT * FROM table where column equals sign \"?\" or \"$\" starting with Postgres version 10. We don't know if it's an index scan or a sequential scan. It heavily depends on the parameter. If you insert a rarely occurring value, there will be an index scan. If you insert a value that represents 90% of the table, it will obviously be a sequential scan, because Postgres knows the statistics. And that's a significant drawback of pg_stat_statements, although some work is being done on it.<\/p>\n<p><\/p>\n<p>The main drawback of log analysis is that you can't usually afford \"log_min_duration_statement = 0\". We'll also talk about this. Accordingly, you don't see the full picture. A very fast query could consume a huge amount of resources, but you won\u2019t notice it because it\u2019s below your threshold.<\/p>\n<p><\/p>\n<p><strong>How do DBAs solve identified problems?<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/5e57fefdec6256f82045aaac037b516c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u00ebr shembull, ne gjet\u00ebm ndonj\u00eb problem. \u00c7far\u00eb b\u00ebhet zakonisht? N\u00ebse jeni zhvillues, do t\u00eb b\u00ebni di\u00e7ka n\u00eb ndonj\u00eb instance, q\u00eb nuk \u00ebsht\u00eb aq shum\u00eb e madhe. N\u00ebse jeni DBA, at\u00ebher\u00eb keni staging. Dhe mund t\u00eb ket\u00eb vet\u00ebm nj\u00eb. Dhe ai ka mbetur pas gjysm\u00eb viti. Dhe mendoni se do t\u00eb shkoni n\u00eb production. Madhenj DBA t\u00eb eksperienc\u00ebs kontrollojn\u00eb m\u00eb von\u00eb n\u00eb production, n\u00eb replik\u00eb. Disa her\u00eb krijojn\u00eb nj\u00eb indeks temporar, sigurohen q\u00eb ai ndihmon, e heqin dhe ia kthejn\u00eb zhvilluesve, q\u00eb t\u00eb fusin at\u00eb n\u00eb skedar\u00ebt e migrimit. Kjo \u00ebsht\u00eb \u00e7far\u00eb po ndodh tani. Dhe kjo \u00ebsht\u00eb nj\u00eb e keqe.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/402e80c7e5fbca6a054c0c9ec61e5fe3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>T\u00eb tunim konfigurimet.<\/li>\n<li>T\u00eb optimizojm\u00eb grupin e indekseve. <\/li>\n<li>T\u00eb ndryshojm\u00eb vet\u00eb SQL-query (kjo \u00ebsht\u00eb m\u00ebnyra m\u00eb e komplikuar).<\/li>\n<li>T\u00eb shtojm\u00eb kapacitete (m\u00ebnyra m\u00eb e thjesht\u00eb n\u00eb shumic\u00ebn e rasteve).<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8a6b3cd56e0df5c7908714926ba1eb05.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Me k\u00ebto gj\u00ebra ka shum\u00eb p\u00ebr t\u00eb th\u00ebn\u00eb. Ka shum\u00eb kontrolla n\u00eb Postgres. Duhet t\u00eb dish shum\u00eb. Ka shum\u00eb indekse n\u00eb Postgres, fal\u00eb gjithashtu organizator\u00ebve t\u00eb k\u00ebtij konferenc\u00eb. Dhe t\u00eb gjitha k\u00ebto duhet t\u2019i dish, dhe pik\u00ebrisht kjo i jep ndjesin\u00eb q\u00eb DBA po merret me magji t\u00eb zez\u00eb. Dometh\u00ebn\u00eb, duhet t\u00eb kalosh 10 vjet q\u00eb t\u00eb fillosh ta kuptosh dhe kjo sakt\u00eb.<\/p>\n<p><\/p>\n<p>Dhe un\u00eb jam luft\u00ebtar kund\u00ebr k\u00ebsaj magjie t\u00eb zez\u00eb. Dua t\u00eb b\u00ebj gjith\u00e7ka n\u00eb nj\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb ket\u00eb teknologji, e jo intuita n\u00eb t\u00eb gjitha k\u00ebto.<\/p>\n<p><\/p>\n<p><strong>Shembuj nga jeta<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/70b11c3df9c41c66570635d5f309ed24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K\u00ebt\u00eb e kam v\u00ebrejtur t\u00eb pakt\u00ebn n\u00eb dy projekte, duke p\u00ebrfshir\u00eb timin. Nj\u00eb postim tjet\u00ebr n\u00eb blog na informon se vlera 1,000 p\u00ebr default_statistic_target \u2013 \u00ebsht\u00eb e mir\u00eb. E mir\u00eb, le t\u00eb provojm\u00eb n\u00eb production.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0b4c2bd6f73df06967c94d5d78b07279.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu ne, duke p\u00ebrdorur mjetin ton\u00eb dy vjet m\u00eb von\u00eb n\u00ebp\u00ebrmjet eksperimenteve mbi bazat e t\u00eb dh\u00ebnave p\u00ebr t\u00eb cilat flasim sot, mund t\u00eb krahasojm\u00eb \u00e7far\u00eb ishte dhe \u00e7far\u00eb \u00ebsht\u00eb tani. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/3564eade6532003d2279c2736ab6d733.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe p\u00ebr k\u00ebt\u00eb na nevojitet t\u00eb krijojm\u00eb nj\u00eb eksperiment. Ai p\u00ebrb\u00ebhet nga kat\u00ebr pjes\u00eb. <\/p>\n<p><\/p>\n<ul>\n<li>E para \u2013 \u00ebsht\u00eb ambienti. Na duhen pajisjet. Dhe kur shkoj n\u00eb ndonj\u00eb kompani dhe lidh nj\u00eb kontrat\u00eb, at\u00ebher\u00eb i them t\u00eb m\u00eb japin pajisje t\u00eb nj\u00ebjta si n\u00eb production. P\u00ebr secilin nga mjeshtrat tuaj, kam nevoj\u00eb p\u00ebr t\u00eb pakt\u00ebn nj\u00eb pajisje t\u00eb till\u00eb. Ose \u00ebsht\u00eb nj\u00eb makin\u00eb virtuale instance n\u00eb Amazon ose n\u00eb Google, ose m\u00eb duhet sakt\u00ebsisht e nj\u00ebjta pajisje. Dometh\u00ebn\u00eb, dua t\u00eb riprodhoj ambientin. Dhe n\u00eb konceptin e ambientit ne p\u00ebrfshijm\u00eb versionin kryesor t\u00eb Postgres. <\/li>\n<li>Pjesa e dyt\u00eb \u2013 \u00ebsht\u00eb objekti i hulumtimeve tona. Kjo \u00ebsht\u00eb baza e t\u00eb dh\u00ebnave. Ajo mund t\u00eb krijohet n\u00eb disa m\u00ebnyra. Un\u00eb do t\u00eb tregoj si. <\/li>\n<li>Pjesa e tret\u00eb \u2013 \u00ebsht\u00eb ngarkesa. Ky \u00ebsht\u00eb momenti m\u00eb i komplikuar. <\/li>\n<li>Dhe pjesa e kat\u00ebrt \u00ebsht\u00eb ajo q\u00eb ne kontrollojm\u00eb, dmth me \u00e7far\u00eb do ta krahasojm\u00eb. Supozojm\u00eb se mund t\u00eb ndryshojm\u00eb nj\u00eb ose disa parametra n\u00eb konfigurim, ose mund t\u00eb krijojm\u00eb nj\u00eb indeks, etj. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8ccd09ac4cdd336618413375617c10a6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ne po fillojm\u00eb nj\u00eb eksperiment. Ja pg_stat_statements. N\u00eb t\u00eb majt\u00eb \u2013 si ishte. N\u00eb t\u00eb djatht\u00eb \u2013 si \u00ebsht\u00eb b\u00ebr\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/6f82d7e25a77b700f2ddb01f42a861a2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb t\u00eb majt\u00eb default_statistics_target = 100, n\u00eb t\u00eb djatht\u00eb = 1,000. Ne shohim se kjo na ndihmoi. Nga 8% gjith\u00e7ka \u00ebsht\u00eb p\u00ebrmir\u00ebsuar n\u00eb t\u00ebr\u00ebsi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/52dda8fb5fbcb9beecaa1eda8443a374.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por n\u00ebse e rrokullisim posht\u00eb, do t\u00eb ket\u00eb grupe k\u00ebrkesash nga pgBadger ose nga pg_stat_statements. K\u00ebtu ka dy mund\u00ebsi. Ne do t\u00eb shohim se ndonj\u00eb k\u00ebrkes\u00eb ka r\u00ebn\u00eb me 88%. Dhe k\u00ebtu fillon qasja inxhinierike. Ne mund t\u00eb thellohemi m\u00eb tej, sepse \u00ebsht\u00eb interesante se pse ajo ka r\u00ebn\u00eb. Duhet t\u00eb kuptojm\u00eb \u00e7far\u00eb ndodhi me statistikat. Pse m\u00eb shum\u00eb kube n\u00eb statistik\u00eb \u00e7ojn\u00eb n\u00eb nj\u00eb rezultat m\u00eb t\u00eb dob\u00ebt.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4e6dfc04af4b3fc2b626dd46c08bdf66.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ose mund t\u00eb mos thellohemi, por t\u00eb b\u00ebjm\u00eb 'ALTER TABLE \u2026 ALTER COLUMN' dhe t'i kthejm\u00eb 100 kube n\u00eb statistik\u00eb k\u00ebsaj kolone. Dhe pastaj, p\u00ebrmes nj\u00eb eksperimentit, ne mund t\u00eb sigurohemi se kjo zgjidhje ndihmoi. Kaq. Kjo \u00ebsht\u00eb qasja inxhinierike q\u00eb na ndihmon t\u00eb shohim situat\u00ebn dhe t\u00eb marrim vendime mbi baza t\u00eb t\u00eb dh\u00ebnave, e jo mbi intuit\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c87df172b994a4523478beebaf4a0275.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/9e766aa4348bd1a77b697e3179d84bbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Disa shembuj nga fusha t\u00eb tjera. N\u00eb testime, ka CI testime q\u00eb ekzistojn\u00eb prej shum\u00eb vitesh. Dhe asnj\u00eb projekt i sh\u00ebndosh\u00eb n\u00eb mendje nuk do t\u00eb ekzistoj\u00eb pa teste automatike.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b6c21981dc8b2b285973ae42199e2b4c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb industri t\u00eb tjera: n\u00eb aviacion, n\u00eb prodhimin e automjeteve, kur testojm\u00eb aerodinamik\u00ebn, gjithashtu kemi mund\u00ebsin\u00eb t\u00eb kryejm\u00eb eksperimente. Ne nuk do t\u00eb l\u00ebshojm\u00eb di\u00e7ka nga skicimi menj\u00ebher\u00eb n\u00eb hap\u00ebsir\u00eb apo nuk do t\u00eb nxjerrim ndonj\u00eb makin\u00eb menj\u00ebher\u00eb n\u00eb autostrad\u00eb. P\u00ebr shembull, ka nj\u00eb tunel aerodinamik. <\/p>\n<p><\/p>\n<p>Nga vObservimet n\u00eb industri t\u00eb tjera, ne mund t\u00eb nxjerrim p\u00ebrfundime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cef02ea9280e5795d1e593c600f0541b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S\u00eb pari, ne kemi nj\u00eb mjedis t\u00eb ve\u00e7ant\u00eb. Ai \u00ebsht\u00eb af\u00ebr prodhimit, por jo t\u00ebr\u00ebsisht i nj\u00ebjt\u00eb. Karakteristika kryesore e tij \u00ebsht\u00eb se duhet t\u00eb jet\u00eb i lir\u00eb, i p\u00ebrs\u00ebritsh\u00ebm dhe maksimalisht i automatizuar. Dhe gjithashtu duhet t\u00eb ket\u00eb mjete speciale p\u00ebr t\u00eb kryer nj\u00eb analiz\u00eb t\u00eb detajuar.<\/p>\n<p><\/p>\n<p>Mund\u00ebsisht, kur fluturojm\u00eb me avionin, kemi m\u00eb pak mund\u00ebsi t\u00eb studiojm\u00eb \u00e7do milit\u00ebr t\u00eb sip\u00ebrfaqes s\u00eb krahut, se sa kemi n\u00eb tunelin aerodinamik. Ne kemi m\u00eb shum\u00eb mjete p\u00ebr diagnostikim. Mund t\u00eb lejojm\u00eb t\u00eb vendosim m\u00eb shum\u00eb gj\u00ebra t\u00eb r\u00ebnda, t\u00eb cilat nuk mund t'i vendosim n\u00eb avionin n\u00eb aj\u00ebr. Po ashtu edhe me Postgres. N\u00eb disa raste, mund t\u00eb aktivizojm\u00eb logimin e plot\u00eb t\u00eb k\u00ebrkesave gjat\u00eb eksperimenteve. Dhe nuk duam ta b\u00ebjm\u00eb k\u00ebt\u00eb n\u00eb ambientin e prodhimit. Ndoshta madje do ta aktivizojm\u00eb k\u00ebt\u00eb me planet p\u00ebrmes auto_explain.<\/p>\n<p><\/p>\n<p>Dhe si\u00e7 thash\u00eb, nj\u00eb nivel i lart\u00eb automatizimi do t\u00eb thot\u00eb q\u00eb kemi shtypur butonin dhe kemi p\u00ebrs\u00ebritur. K\u00ebshtu duhet t\u00eb jet\u00eb, p\u00ebr t\u00eb pasur shum\u00eb eksperimente, q\u00eb t\u00eb jet\u00eb n\u00eb rrjedh\u00eb.<\/p>\n<p><\/p>\n<p>Nancy CLI \u2013 themeli i \"laboratorit t\u00eb DB\"<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/f8303df21f309fb209929acec260b757.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe ja, ne kemi b\u00ebr\u00eb nj\u00eb gj\u00eb t\u00eb till\u00eb. Do t\u00eb thot\u00eb, kam folur p\u00ebr k\u00ebto ide n\u00eb qershor, pothuajse nj\u00eb vit m\u00eb par\u00eb. Dhe tani kemi n\u00eb Open Source nj\u00eb Nancy CLI t\u00eb quajtur k\u00ebshtu. Ky \u00ebsht\u00eb themeli p\u00ebr t\u00eb nd\u00ebrtuar nj\u00eb laborator t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0ed3c72461123745be4fa9e58455baa0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/nancy\">Nancy<\/a><\/noindex> \u2013 Ky \u00ebsht\u00eb n\u00eb Open Source, n\u00eb Gitlab. Mund ta thoni, mund ta provoni. Kam dh\u00ebn\u00eb nj\u00eb lidhje n\u00eb slidet. Mund t\u00eb klikoni dhe do t\u00eb ket\u00eb <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/nancy\/-\/blob\/master\/help\/nancy_run.md\">help<\/a><\/noindex> p\u00ebr t\u00eb gjitha parametrat.<\/p>\n<p><\/p>\n<p>Sigurisht, ka ende shum\u00eb p\u00ebr t\u00eb zhvilluar. Ka shum\u00eb ide. Por kjo \u00ebsht\u00eb ajo q\u00eb ne e zbatojm\u00eb pothuajse \u00e7do dit\u00eb. Dhe kur na vjen nj\u00eb ide - a \u00e7far\u00eb ndodh kur fshini 40,000,000 rreshta dhe gjith\u00e7ka \u00ebsht\u00eb bllokuar n\u00eb IO, at\u00ebher\u00eb mund t\u00eb b\u00ebjm\u00eb nj\u00eb eksperiment dhe t\u00eb shohim m\u00eb n\u00eb detaje, p\u00ebr t\u00eb kuptuar se \u00e7far\u00eb po ndodh dhe m\u00eb pas t\u00eb p\u00ebrpiqemi ta zgjidhim at\u00eb n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mund t\u00eb vazhdojm\u00eb. Do t\u00eb thot\u00eb, b\u00ebjm\u00eb nj\u00eb eksperiment. P\u00ebr shembull, ndonj\u00eb gj\u00eb e rregullojm\u00eb dhe shohim se \u00e7far\u00eb rezultati marrim. Dhe ne nuk e b\u00ebjm\u00eb k\u00ebt\u00eb n\u00eb prodhim. Ky \u00ebsht\u00eb thelbi i ides\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c22d332de4b45d604dfb9c5a95404fd6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ku mund t\u00eb funksionoj\u00eb kjo? Mund t\u00eb funksionoj\u00eb lokal, do t\u00eb thot\u00eb, mund ta b\u00ebni kudo, madje mund ta nisni n\u00eb MacBook. Keni nevoj\u00eb p\u00ebr docker, fillojm\u00eb. Dhe kaq. Mund t\u00eb ndizni n\u00eb ndonj\u00eb instanc\u00eb n\u00eb hardware, ose n\u00eb nj\u00eb virtual, kudo.<\/p>\n<p><\/p>\n<p>Dhe ka edhe mund\u00ebsin\u00eb e hapjes s\u00eb larg\u00ebt n\u00eb Amazon n\u00eb EC2 Instance, n\u00eb spots. Dhe kjo \u00ebsht\u00eb nj\u00eb mund\u00ebsi shum\u00eb e shk\u00eblqyer. P\u00ebr shembull, dje kemi realizuar m\u00eb shum\u00eb se 500 eksperimente n\u00eb instanc\u00ebn i3, duke filluar nga ajo m\u00eb e vog\u00ebl dhe duke p\u00ebrfunduar n\u00eb i3-16-xlarge. Dhe na kushtoi 500 eksperimente 64 dollar\u00eb. \u00c7do nj\u00ebri zgjati 15 minuta. Dometh\u00ebn\u00eb, fal\u00eb p\u00ebrdorimit t\u00eb spoteve, kjo \u00ebsht\u00eb shum\u00eb e lir\u00eb \u2013 zbritje 70%, tarifimi sipas sekond\u00ebs nga Amazon. Mund t\u00eb b\u00ebni shum\u00eb gj\u00ebra. Mund t\u00eb realizoni nj\u00eb hetim t\u00eb v\u00ebrtet\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/64c61cc9dd7181296b2ef5b815a17493.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe tri versione kryesore t\u00eb Postgres mb\u00ebshteten. Nuk \u00ebsht\u00eb kaq e v\u00ebshtir\u00eb t\u00eb p\u00ebrmir\u00ebsosh disa versione t\u00eb vjetra dhe versionin e ri 12 gjithashtu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ee147c91014ab698cbd3c60e193bef23.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Objektin mund ta p\u00ebrcaktojm\u00eb n\u00eb tri m\u00ebnyra. Ato jan\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>Dump\/sq-fail. <\/li>\n<li>M\u00ebnyra kryesore \u2013 \u00ebsht\u00eb kloni i direktoris\u00eb PGDATA. Zakonisht marr\u00eb nga serveri i backup-it. N\u00ebse keni backup-e binaresh t\u00eb mira, mund t\u00eb b\u00ebni klone prej andej. N\u00ebse keni cloud, at\u00ebher\u00eb kjo do t\u00eb b\u00ebhet automatikisht nga kompania cloud si Amazon dhe Google. Kjo \u00ebsht\u00eb m\u00ebnyra kryesore p\u00ebr klon\u00ebt n\u00eb prodhim t\u00eb v\u00ebrtet\u00eb. K\u00ebshtu zhvillojm\u00eb ne. <\/li>\n<li>Dhe m\u00ebnyra e fundit \u00ebsht\u00eb e p\u00ebrshtatshme p\u00ebr hulumtimet, kur ka d\u00ebshir\u00eb t\u00eb kuptohet se si funksionon di\u00e7ka n\u00eb Postgres. Kjo \u00ebsht\u00eb pgbench. Mund t\u00eb gjeneroni me ndihm\u00ebn e pgbench. Kjo \u00ebsht\u00eb thjesht nj\u00eb opsion \"db-pgbench\". I thua atij, \u00e7far\u00eb scale. Dhe gjith\u00e7ka do t\u00eb gjenerohet n\u00eb cloud, si\u00e7 \u00ebsht\u00eb th\u00ebn\u00eb.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/2bc3e30cb31a19f66c6b55480447126f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe ngarkesa:<\/p>\n<p><\/p>\n<ul>\n<li>Ngarkes\u00ebn mund ta ekzekutojm\u00eb n\u00eb nj\u00eb rrjedh\u00eb SQL. Kjo \u00ebsht\u00eb m\u00ebnyra m\u00eb primitive. <\/li>\n<li>Ose mund t\u00eb emulojm\u00eb ngarkes\u00ebn. Dhe mund ta emulojm\u00eb at\u00eb para s\u00eb gjithash n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb. Na nevojitet t\u00eb mbledhim t\u00eb gjitha logjet. Dhe kjo \u00ebsht\u00eb e dhimbshme. Do t'ju tregoj pse. Dhe me ndihm\u00ebn e pgreplay luajm\u00eb, q\u00eb \u00ebsht\u00eb i integruar n\u00eb Nancy. <\/li>\n<li>Ose nj\u00eb opsion tjet\u00ebr. Nj\u00eb ngarkes\u00eb e quajtur artizanal, q\u00eb ne e b\u00ebjm\u00eb me nj\u00eb sasi t\u00eb caktuar p\u00ebrpjekjeje. Duke analizuar ngarkes\u00ebn ton\u00eb aktuale n\u00eb sistemin e prodhimit, ne nxjerrim grupet m\u00eb t\u00eb mira t\u00eb k\u00ebrkesave. Dhe me ndihm\u00ebn e pgbench mund ta emulojm\u00eb k\u00ebt\u00eb ngarkes\u00eb n\u00eb laborator. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4adba12881faf9ad9d4f9e9f42c0f5c1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Ose duhet t\u00eb ekzekutojm\u00eb ndonj\u00eb SQL, dometh\u00ebn\u00eb kontrollojm\u00eb nj\u00eb migrim, krijojm\u00eb nj\u00eb indeks atje, realizojm\u00eb ANALAZE. Dhe shohim se \u00e7far\u00eb ndodhi para vakuumit dhe pas vakuumit. N\u00eb p\u00ebrgjith\u00ebsi, \u00e7do SQL.<\/li>\n<li>Ose ne e nevoj\u00eb q\u00eb t\u00eb ndryshojm\u00eb nj\u00eb ose disa parametra n\u00eb konfigurim. Mund t\u00eb k\u00ebrkojm\u00eb q\u00eb t\u00eb verifikohen, p\u00ebr shembull, 100 vlera n\u00eb Amazon p\u00ebr baz\u00ebn ton\u00eb me terabajt. Dhe pas disa or\u00ebsh, do t\u00eb keni rezultatin. Si rregull, baza me terabajt do t\u00eb zgjatet disa or\u00eb. Por n\u00eb zhvillim kemi nj\u00eb patch, ku kemi mund\u00ebsin\u00eb p\u00ebr t\u00eb b\u00ebr\u00eb nj\u00eb seri, dometh\u00ebn\u00eb, mund t\u00eb p\u00ebrdorni t\u00eb nj\u00ebjtin pgdata n\u00eb t\u00eb nj\u00ebjtin server dhe t\u00eb verifikoni. Postgres do t\u00eb rindez\u00eb, cache-t do t\u00eb zbrazin. Dhe mund t\u00eb st\u00ebrvitni ngarkes\u00ebn. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/02985b47bf0ab62e8f1c4cc3ea003f26.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Vjen nj\u00eb direktori, ku ka shum\u00eb skedar\u00eb, duke filluar nga snapshots pg.<em>stat<\/em>***. Dhe aty jan\u00eb gj\u00ebrat m\u00eb interesante \u2013 pg_stat_statements, pg_stat_kcacke. K\u00ebto jan\u00eb dy zgjerime q\u00eb analizojn\u00eb k\u00ebrkesat. Dhe pg_stat_bgwriter p\u00ebrmban jo vet\u00ebm statistik\u00ebn e pgwriter, por gjithashtu p\u00ebr checkpoint dhe se si vet\u00eb backendet shkarkojn\u00eb bufferat e papast\u00ebr. Dhe kjo \u00ebsht\u00eb e interesante t\u00eb shikohet. P\u00ebr shembull, kur konfiguroni shared_buffers, \u00ebsht\u00eb shum\u00eb interesante t\u00eb shikoni sa ka shkarkuar kush.<\/li>\n<li>Po ashtu, arrijn\u00eb log-\u00ebt e Postgres. Dy log-e \u2013 log i p\u00ebrgatitjes dhe log i luajtjes s\u00eb ngarkes\u00ebs. <\/li>\n<li>Nj\u00eb tipar relativisht i ri \u2013 jan\u00eb FlameGraphs.<\/li>\n<li>Po ashtu, n\u00ebse keni p\u00ebrdorur pgreplay ose pgbench varianta e luajtjes s\u00eb ngarkes\u00ebs, do t\u00eb keni nativen e tyre n\u00eb daljen e tyre. Dhe do t\u00eb shihni latency dhe TPS. Mund t\u00eb kuptoni se si e kan\u00eb par\u00eb ata. <\/li>\n<li>Informacion rreth sistemit. <\/li>\n<li>Kontrollet bazike CPU dhe IO. Kjo \u00ebsht\u00eb m\u00eb shum\u00eb p\u00ebr instancat EC2 n\u00eb Amazon, kur d\u00ebshironi t\u00eb nisni n\u00eb rrjedh\u00eb 100 instanca identike dhe t'i testoni ato me 100 prova t\u00eb ndryshme, do t\u00eb keni 10,000 eksperimente. Dhe duhet t\u00eb siguroheni q\u00eb nuk keni nj\u00eb instanc\u00eb problematike, t\u00eb cil\u00ebn dikush e ka pak\u00ebsuar. N\u00eb k\u00ebt\u00eb hardware, t\u00eb tjer\u00ebt aktivizohen dhe ju mbetet pak burim. T\u00eb tilla rezultate \u00ebsht\u00eb m\u00eb mir\u00eb t'i eliminoni. Dhe pik\u00ebrisht me ndihm\u00ebn e sysbench nga Aleksei Kopytov ne b\u00ebjm\u00eb disa kontrolle t\u00eb shkurtra, q\u00eb do t\u00eb vij\u00eb dhe mund t\u00eb krahasohet me t\u00eb tjerat, dometh\u00ebn\u00eb, do t\u00eb kuptoni si e menaxhon CPU-ja dhe si e menaxhon IO-ja. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c4ddf5283c3c79e3d4c2c061e7dd9e7d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cilat jan\u00eb komplikimet teknike n\u00eb shembuj t\u00eb kompanive t\u00eb ndryshme?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/959690b464a01a4a3cb67ec497becff1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Supozoni se duam t\u00eb p\u00ebrs\u00ebritim nj\u00eb ngarkes\u00eb t\u00eb v\u00ebrtet\u00eb me an\u00eb t\u00eb log-\u00ebve. Ide e shk\u00eblqyer, n\u00ebse \u00ebsht\u00eb shkruar n\u00eb Open Source pgreplay. Ne e p\u00ebrdorim. Por, q\u00eb t\u00eb punoj\u00eb mir\u00eb, duhet t\u00eb aktivizoni logimin e plot\u00eb t\u00eb k\u00ebrkesave me parametra dhe koh\u00ebmatjen.<\/p>\n<p><\/p>\n<p>Ka there disa v\u00ebshtir\u00ebsi rreth duration dhe timestamp. Ne do ta anashkalojm\u00eb k\u00ebt\u00eb pjes\u00eb. Pyetja kryesore \u00ebsht\u00eb \u2013 a mund ta lejoni veten nj\u00eb gj\u00eb t\u00eb till\u00eb apo jo? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/5d08fadbaf67b8a137212302b6adf48a.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/gist.github.com\/NikolayS\/08d9b7b4845371d03e195a8d8df43408\">https:\/\/gist.github.com\/NikolayS\/08d9b7b4845371d03e195a8d8df43408<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Problemi \u00ebsht\u00eb se kjo mund t\u00eb mos jet\u00eb e aksesueshme. Ju duhet s\u00eb pari t\u00eb kuptoni se \u00e7far\u00eb fluksi do t\u00eb shkruhet n\u00eb log. N\u00ebse keni pg_stat_statements, mund t\u00eb kuptoni, me k\u00ebt\u00eb k\u00ebrkes\u00eb (linku do t\u00eb jet\u00eb i disponuesh\u00ebm n\u00eb slajde), sa p\u00ebraf\u00ebrsisht byte do t\u00eb shkruhet n\u00eb sekond\u00eb.<\/p>\n<p><\/p>\n<p>Ne e shikojm\u00eb gjat\u00ebsi e k\u00ebrkes\u00ebs. Ne injorojm\u00eb faktin se nuk ka parametra, por e dim\u00eb gjat\u00ebsi e k\u00ebrkes\u00ebs dhe dim\u00eb sa her\u00eb n\u00eb sekond\u00eb \u00ebsht\u00eb ekzekutuar. K\u00ebshtu mund t\u00eb p\u00ebrllogarisim sa p\u00ebraf\u00ebrsisht byte n\u00eb sekond\u00eb. Mund t\u00eb gabojm\u00eb dy her\u00eb, por rendi do ta kuptojm\u00eb me siguri k\u00ebshtu.<\/p>\n<p><\/p>\n<p>Mund t\u00eb shohim se kjo k\u00ebrkes\u00eb ekzekutohet 802 her\u00eb n\u00eb sekond\u00eb. Dhe ne shohim se bytes_per sec \u2013 300 kB\/s do t\u00eb shkruhen plus minus. Dhe, zakonisht, mund t\u00eb lejojm\u00eb nj\u00eb fluks t\u00eb till\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/218f905073a8519115adf5c65825b74d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por! \u00c7\u00ebshtja \u00ebsht\u00eb se ka sisteme t\u00eb ndryshme logimi. Dhe zakonisht, njer\u00ebzit kan\u00eb \u2018syslog\u2019 si parazgjedhje.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/66054682083ba2ada174afb5e7ee3973.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00ebse keni syslog, at\u00ebher\u00eb mund t\u00eb keni nj\u00eb pamje t\u00eb till\u00eb. Ne do t\u00eb marrim pgbench, do t\u00eb aktivizojm\u00eb logimin e k\u00ebrkesave dhe do t\u00eb shohim \u00e7far\u00eb ndodh.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a05723dcf10a7733f054ba108bb0f107.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pa logim \u2013 kjo \u00ebsht\u00eb kolona n\u00eb t\u00eb majt\u00eb. At\u00ebher\u00eb kishim 161,000 TPS. Me syslog \u2013 n\u00eb Ubuntu 16.04 n\u00eb Amazon, kemi 37,000 TPS. Nd\u00ebrsa n\u00ebse nd\u00ebrojm\u00eb dy metoda t\u00eb tjera t\u00eb logimit, situata \u00ebsht\u00eb shum\u00eb m\u00eb e mir\u00eb. Kjo \u00ebsht\u00eb, prisnim q\u00eb t\u00eb sh\u00ebnuarit t\u00eb bjer\u00eb, por jo kaq shum\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a7d90af9d29761d3b80eb09bf050760f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00eb CentOS 7, ku merr pjes\u00eb journald, duke e kthyer logun n\u00eb format binary p\u00ebr k\u00ebrkimin e leht\u00eb, at\u00ebher\u00eb ndodhemi n\u00eb nj\u00eb situat\u00eb t\u00eb tmerrshme, 44 her\u00eb ulje n\u00eb TPS.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86d2af18a0e6364f297a62378b6ede56.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe kjo \u00ebsht\u00eb ajo me t\u00eb cil\u00ebn jetojn\u00eb njer\u00ebzit. Dhe shpesh n\u00eb kompani, ve\u00e7an\u00ebrisht n\u00eb t\u00eb m\u00ebdha, \u00ebsht\u00eb shum\u00eb e v\u00ebshtir\u00eb p\u00ebr t'u ndryshuar. N\u00ebse mund t\u00eb largoheni nga syslog, at\u00ebher\u00eb ju lutemi largohuni.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a167a6fac7f28c7541515f7eff30bdb4.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Vler\u00ebsoni IOPS dhe fluksin e shkruar. <\/li>\n<li>Kontrolloni sistemin tuaj t\u00eb logimit. <\/li>\n<li>N\u00ebse ngarkesa e parashikuar \u00ebsht\u00eb tep\u00ebr e madhe, shqyrtoni mund\u00ebsin\u00eb e sampling. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/127fa1296578ba0ba6eac2b44c5c5398.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kemi pg_stat_statements. Si\u00e7 e thash\u00eb, ai duhet patjet\u00ebr t\u00eb jet\u00eb. Dhe mund t\u00eb marim dhe \u00e7do grup k\u00ebrkesash ta p\u00ebrshkruajm\u00eb n\u00eb nj\u00eb skedar special. Pastaj mund t\u00eb p\u00ebrdorim nj\u00eb funksion shum\u00eb t\u00eb dobish\u00ebm n\u00eb pgbench \u2013 mund\u00ebsin\u00eb p\u00ebr t\u00eb p\u00ebrfshir\u00eb disa skedare me opsionin \u2018-f\u2019.<\/p>\n<p><\/p>\n<p>Ai kupton shum\u00eb \u201c-f\u201d. Dhe mund t\u00eb thuash me \u201c@\u201d n\u00eb fund, cila p\u00ebrqindje i takon secilit skedar. Pra, mund t\u00eb themi se ky duhet t\u00eb ekzekutohet n\u00eb 10% t\u00eb rasteve, dhe ky n\u00eb 20%. Dhe kjo do t\u00eb na afroj\u00eb me at\u00eb q\u00eb shohim n\u00eb prodhim.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b91d18a9861abdc36bfda54cd72a142f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Si do ta kuptojm\u00eb se \u00e7far\u00eb kemi n\u00eb prodhim? Cila p\u00ebrqindje dhe \u00e7far\u00eb si? K\u00ebtu kemi nj\u00eb devijim paksi. Kemi nj\u00eb produkt tjet\u00ebr. <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/postgres-checkup\">postgres-checkup<\/a><\/noindex>. Gjithashtu baza \u00ebsht\u00eb n\u00eb Open Source. Dhe ne tani po e zhvillojm\u00eb aktivisht at\u00eb.<\/p>\n<p><\/p>\n<p>Ai lindi p\u00ebr arsye pak t\u00eb ndryshme. P\u00ebr arsye se monitorimi nuk \u00ebsht\u00eb i mjaftuesh\u00ebm. Pra, ju vini, shikoni n\u00eb baz\u00eb, shikoni problemet q\u00eb ekzistojn\u00eb. Dhe, zakonisht, ju b\u00ebni kontrollin e sh\u00ebndetit. N\u00ebse jeni nj\u00eb DBA me p\u00ebrvoj\u00eb, at\u00ebher\u00eb e b\u00ebni kontrollin e sh\u00ebndetit. Shikoni p\u00ebrdorimin e indekseve etj. N\u00ebse keni OKmeter, at\u00ebher\u00eb shk\u00eblqyesh\u00ebm. Ky \u00ebsht\u00eb nj\u00eb monitorim i klasit p\u00ebr Postgres. OKmeter.io \u2013 ju lutem, instalojeni, gjith\u00e7ka \u00ebsht\u00eb b\u00ebr\u00eb shum\u00eb mir\u00eb atje. Ai \u00ebsht\u00eb me pages\u00eb.<\/p>\n<p><\/p>\n<p>N\u00ebse nuk e keni at\u00eb, at\u00ebher\u00eb, zakonisht, nuk keni shum\u00eb p\u00ebr t\u00eb par\u00eb. N\u00eb monitorim zakonisht ka CPU, IO dhe madje me rezervime, dhe gjith\u00eb kjo. Por ne kemi nevoj\u00eb p\u00ebr m\u00eb shum\u00eb. Duhet t\u00eb shohim si funksionon autovakuumi, si funksionon checkpoint, n\u00eb IO duhet t\u00eb ndash checkpoint nga bgwriter dhe nga backend-et etj.<\/p>\n<p><\/p>\n<p>Problemi \u00ebsht\u00eb kur ndihmon nj\u00eb kompani t\u00eb madhe, ato nuk mund t\u00eb zbatojn\u00eb shpejt di\u00e7ka. Nuk mund t\u00eb blejn\u00eb shpejt OKmeter. Mund t\u00eb ken\u00eb mund\u00ebsi ta blejn\u00eb pas gjasht\u00eb muajsh. Nuk mund t\u00eb instalojn\u00eb shpejt disa paketa. <\/p>\n<p><\/p>\n<p>Dhe na lindi ideja se na nevojitet nj\u00eb veg\u00ebl e till\u00eb, e cila nuk k\u00ebrkon asgj\u00eb p\u00ebr instalim, pra ju nuk duhet t\u00eb instaloni asgj\u00eb n\u00eb prodhim. E instaloni n\u00eb laptopin tuaj, ose n\u00eb serverin e v\u00ebzhgimit, nga ku do t\u00eb nisni. Dhe ai do t\u00eb analizoj\u00eb shum\u00eb gj\u00ebra: edhe sistemin operativ, edhe sistemin e skedar\u00ebve, edhe vet\u00eb Postgres-in, duke b\u00ebr\u00eb disa k\u00ebrkesa t\u00eb lehta q\u00eb mund t'i drejtoni drejtp\u00ebrdrejt n\u00eb prodhim dhe asgj\u00eb nuk do t\u00eb bjer\u00eb.<\/p>\n<p><\/p>\n<p>Ne e quajm\u00eb at\u00eb Postgres-checkup. N\u00ebse flasim mjek\u00ebsisht, kjo \u00ebsht\u00eb nj\u00eb kontroll i rregullt i sh\u00ebndetit. N\u00ebse e lidhim me tem\u00ebn automobilistike, \u00ebsht\u00eb si nj\u00eb kontroll teknik. Ju b\u00ebni kontroll teknik p\u00ebr makin\u00ebn \u00e7do gjasht\u00eb muaj ose nj\u00eb vit, n\u00eb var\u00ebsi t\u00eb mark\u00ebs. A b\u00ebni kontroll teknik p\u00ebr baz\u00ebn tuaj? Pra, a b\u00ebni nj\u00eb k\u00ebrkim t\u00eb thell\u00eb rregullisht? Kjo duhet b\u00ebr\u00eb. N\u00ebse b\u00ebni kopje rezerv\u00eb, at\u00ebher\u00eb b\u00ebni edhe kontrollin, kjo \u00ebsht\u00eb po aq e r\u00ebnd\u00ebsishme.<\/p>\n<p><\/p>\n<p>Dhe ne kemi nj\u00eb veg\u00ebl t\u00eb till\u00eb. Ai filloi t\u00eb zhvillohet aktivisht vet\u00ebm tri muaj m\u00eb par\u00eb. \u00cbsht\u00eb ende i ri, por aty ka shum\u00eb gj\u00ebra.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/514d00b5cb710a7997af0a5dde55b5a8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ne pofigurojm\u00eb grupet m\u00eb \"t\u00eb ndikueshme\" t\u00eb k\u00ebrkesave \u2013 raporti K003 n\u00eb Postgres-checkup<\/p>\n<p><\/p>\n<p>Dhe ka nj\u00eb grup raportesh K. Momentalisht ka tre raporte. Dhe ka nj\u00eb raport t\u00eb till\u00eb K003. Aty \u00ebsht\u00eb maje e pg_stat_statements, e renditur sipas total_time.<\/p>\n<p><\/p>\n<p>Kur rendisim grupet e k\u00ebrkesave sipas total_time, n\u00eb krye shohim nj\u00eb grup q\u00eb ngarkon sistemin ton\u00eb m\u00eb shum\u00eb, dmth konsumon m\u00eb shum\u00eb burime. Pse e quaj grupet e k\u00ebrkesave? Sepse ne heqim parametrat. Kjo nuk jan\u00eb m\u00eb k\u00ebrkesa, por grupe k\u00ebrkesash, dmth ato jan\u00eb t\u00eb abstrahuara.<\/p>\n<p><\/p>\n<p>Dhe n\u00ebse do t\u00eb optimizojm\u00eb nga lart\u00eb posht\u00eb, do t\u00eb leht\u00ebsojm\u00eb burimet tona dhe do t\u00eb shtyjm\u00eb momentin kur do t\u00eb duhet t\u00eb b\u00ebjm\u00eb nj\u00eb p\u00ebrmir\u00ebsim. Ky \u00ebsht\u00eb nj\u00eb m\u00ebnyr\u00eb shum\u00eb e mir\u00eb p\u00ebr t\u00eb kursyer para.<\/p>\n<p><\/p>\n<p>Mund t\u00eb mos jet\u00eb ndoshta m\u00ebnyra m\u00eb e mir\u00eb p\u00ebr t\u00eb kujdesur p\u00ebr p\u00ebrdoruesit, sepse ndoshta nuk shohim raste t\u00eb rralla, por shum\u00eb t\u00eb bezdisshme, kur dikush pret 15 sekonda. N\u00eb total, ato jan\u00eb aq t\u00eb rralla sa q\u00eb nuk i shohim, por p\u00ebrndryshe merremi me burimet.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d16abc423573e4d923b0c03683c2efee.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodhi n\u00eb k\u00ebt\u00eb tabel\u00eb? Ne b\u00ebm\u00eb dy snapshot-e. Postgres_checkup do t'ju b\u00ebj\u00eb nj\u00eb delta p\u00ebr \u00e7do metrik\u00eb: p\u00ebr total-time, calls, rows, shared_blks_read etj. E gjith\u00eb, delta \u00ebsht\u00eb llogaritur. Problemi i madh me pg_stat_statements \u00ebsht\u00eb se ai nuk e mban mend kur ka qen\u00eb reset. Nd\u00ebrsa pg_stat_database e mban mend, pg_stat_statements nuk e mban. Shihni, ka 1 000 000 num\u00ebr, por nga e kemi llogaritur, nuk e dim\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c370bf1237675e03c0a3d362c025ac24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu e dim\u00eb, k\u00ebtu kemi dy snapshot-e. E dim\u00eb se delta n\u00eb k\u00ebt\u00eb rast ishte 56 sekonda. Nj\u00eb periudh\u00eb shum\u00eb e vog\u00ebl. E rendit\u00ebm sipas total_time. M\u00eb pas mund t\u00eb diferencojm\u00eb, dometh\u00ebn\u00eb, t\u00eb gjith\u00eb metrikat i ndajm\u00eb me duration. N\u00ebse i ndajm\u00eb \u00e7do metrik\u00eb me duration, do t\u00eb kemi numrin e thirrjeve p\u00ebr sekond\u00eb.<\/p>\n<p><\/p>\n<p>M\u00eb pas total_time p\u00ebr sekond\u00eb \u2013 kjo \u00ebsht\u00eb metrika ime e preferuar. Ajo matet n\u00eb sekonda, n\u00eb sekond\u00eb, dmth sa sekonda i nevojiteshin sistemit ton\u00eb p\u00ebr t\u00eb p\u00ebrfunduar k\u00ebt\u00eb grup k\u00ebrkesash p\u00ebr sekond\u00eb. N\u00ebse shihni aty m\u00eb shum\u00eb se nj\u00eb sekond\u00eb p\u00ebr sekond\u00eb, do t\u00eb thot\u00eb se ju nevojiteshin m\u00eb shum\u00eb se nj\u00eb b\u00ebrtham\u00eb. Kjo \u00ebsht\u00eb nj\u00eb metrik\u00eb shum\u00eb e mir\u00eb. Mund t\u00eb kuptoni se ky person, p\u00ebr shembull, ka nevoj\u00eb p\u00ebr t\u00eb pakt\u00ebn tri b\u00ebrthama.<\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb novatori yn\u00eb, nuk kam par\u00eb asgj\u00eb t\u00eb till\u00eb. Vini re \u2013 kjo \u00ebsht\u00eb nj\u00eb gj\u00eb shum\u00eb e thjesht\u00eb \u2013 sekonda p\u00ebr sekond\u00eb. N sometimes, kur keni CPU 100 %, at\u00ebher\u00eb gjysm\u00eb ore p\u00ebr sekond\u00eb, dmth ju keni kaluar gjysm\u00eb ore vet\u00ebm me k\u00ebto k\u00ebrkesa. <\/p>\n<p><\/p>\n<p>M\u00eb pas, ne shohim rreshta n\u00eb sekond\u00eb. Ne e dim\u00eb sa rreshta u kthyen n\u00eb sekond\u00eb.<\/p>\n<p><\/p>\n<p>Dhe gjithashtu ka nj\u00eb gj\u00eb interesante. Sa shared_buffers kemi lexuar n\u00eb sekond\u00eb nga vet\u00eb shared_buffers. Hit-et kishin qen\u00eb atje, nd\u00ebrsa rreshtat i mor\u00ebm nga cache i sistemit operativ, ose nga disku. Varianti i par\u00eb \u00ebsht\u00eb i shpejt\u00eb, nd\u00ebrsa i dyti, ndoshta \u00ebsht\u00eb i shpejt\u00eb, ndoshta edhe jo, varet nga situata. <\/p>\n<p><\/p>\n<p>Dhe m\u00ebnyra e dyt\u00eb p\u00ebr diferencim \u2013 ne ndajm\u00eb numrin e k\u00ebrkesave n\u00eb k\u00ebt\u00eb grup. N\u00eb kolon\u00ebn e dyt\u00eb do t\u00eb keni gjithmon\u00eb nj\u00eb k\u00ebrkes\u00eb t\u00eb ndar\u00eb nga k\u00ebrkesa. M\u00eb pas \u00ebsht\u00eb interesante \u2013 sa milisekonda ishin n\u00eb k\u00ebt\u00eb k\u00ebrkes\u00eb. Ne e dim\u00eb si sillet n\u00eb mesatare kjo k\u00ebrkes\u00eb. 101 milisekonda ishin t\u00eb nevojshme p\u00ebr \u00e7do k\u00ebrkes\u00eb. Kjo \u00ebsht\u00eb nj\u00eb metrik\u00eb tradicionale q\u00eb na nevojitet p\u00ebr t\u00eb kuptuar.<\/p>\n<p><\/p>\n<p>Sa rreshta \u00e7do k\u00ebrkes\u00eb ktheu n\u00eb mesatare. Ne shohim se grupi kthen 8. Sa n\u00eb mesatare mor\u00ebm dhe lexuam nga cache. Ne shohim se gjith\u00e7ka \u00ebsht\u00eb e mbajtur shk\u00eblqyesh\u00ebm n\u00eb cache. T\u00eb gjitha hit-et p\u00ebr grupin e par\u00eb. <\/p>\n<p><\/p>\n<p>Dhe n\u00eb rreshtin e kat\u00ebrt n\u00eb \u00e7do rresht \u2013 \u00ebsht\u00eb sa p\u00ebrqind nga numri total. Ne kemi calls. Le t\u00eb supozojm\u00eb, n\u00eb 1,000,000. Dhe ne mund t\u00eb kuptojm\u00eb \u00e7far\u00eb kontribuon ky grup. Ne shohim se n\u00eb k\u00ebt\u00eb rast grupi i par\u00eb kontribuon m\u00eb pak se 0.01 %. Kjo do t\u00eb thot\u00eb se \u00ebsht\u00eb kaq e ngadalt\u00eb sa nuk e shohim n\u00eb pamjen e p\u00ebrgjithshme. Nd\u00ebrsa grupi i dyt\u00eb \u2013 5 % nga calls. Kjo do t\u00eb thot\u00eb 5 % nga t\u00eb gjitha calls \u2013 \u00ebsht\u00eb grupi i dyt\u00eb. <\/p>\n<p><\/p>\n<p>Po ashtu, edhe p\u00ebr total_time \u00ebsht\u00eb interesante. Ne shpenzuam 14 % t\u00eb gjith\u00eb koh\u00ebs s\u00eb pun\u00ebs p\u00ebr grupin e par\u00eb t\u00eb k\u00ebrkesave. Nd\u00ebrsa p\u00ebr t\u00eb dytin \u2013 11 % etj. <\/p>\n<p><\/p>\n<p>Nuk do t\u00eb thellohem n\u00eb detaje, por aty ka nuanca. Ne nxjerrim gabimin nga lart, sepse, kur krahasojm\u00eb, snapshot-et mund t\u00eb deformohen, dometh\u00ebn\u00eb, disa k\u00ebrkesa mund t\u00eb bien dhe n\u00eb t\u00eb dyt\u00ebn nuk mund t\u00eb jen\u00eb m\u00eb, nd\u00ebrsa disa mund t\u00eb shfaqen t\u00eb reja. Dhe ne aty llogarisim gabimin. N\u00ebse shihni 0, at\u00ebher\u00eb \u00ebsht\u00eb mir\u00eb. Nuk ka gabime. N\u00ebse niveli i gabimit \u00ebsht\u00eb deri n\u00eb 20 %, \u00ebsht\u00eb OK. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/327bbf167bc21d37a3e1597b52528e6d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00eb pas kthehemi n\u00eb tem\u00ebn ton\u00eb. Duhet t\u00eb krijojm\u00eb workload. Ne e marrim nga lart\u00eb posht\u00eb, derisa t\u00eb mbledhim 80 % ose 90 %. Zakonisht jan\u00eb 10-20 grupe. Dhe krijojm\u00eb skedar\u00eb p\u00ebr pgbench. Atje p\u00ebrdorim random. Ndonj\u00ebher\u00eb, fatkeq\u00ebsisht, kjo nuk funksionon. Dhe n\u00eb Postgres 12 do t\u00eb ket\u00eb m\u00eb shum\u00eb mund\u00ebsi p\u00ebr ta p\u00ebrdorur k\u00ebt\u00eb qasje. <\/p>\n<p><\/p>\n<p>Dhe k\u00ebshtu ne arrijm\u00eb 80-90% n\u00eb total_time. \u00c7far\u00eb duhet t\u00eb vendosim pas \"@\"? Shikojm\u00eb thirrjet, shikojm\u00eb sa p\u00ebrqindje dhe kuptojm\u00eb se duhet t\u00eb kemi k\u00ebt\u00eb p\u00ebrqindje k\u00ebtu. Nga k\u00ebto p\u00ebrqindje mund t\u00eb kuptojm\u00eb se si t\u00eb balancojm\u00eb secilin nga skedar\u00ebt. Pas k\u00ebsaj, p\u00ebrdorim pgbench dhe vazhdojm\u00eb pun\u00ebn. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/53edf787f49dcabb5e4731510839332d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kemi gjithashtu K001 dhe K002. <\/p>\n<p><\/p>\n<p>K001 \u2013 \u00ebsht\u00eb nj\u00eb varg i madh me kat\u00ebr n\u00ebnvargje. Kjo \u00ebsht\u00eb karakteristika e ngarkes\u00ebs son\u00eb t\u00eb gjith\u00eb. Shikoni kolonen e dyt\u00eb dhe n\u00ebnvargun e dyt\u00eb. Shikojm\u00eb se \u00ebsht\u00eb rreth nj\u00eb e gjysm\u00eb sekond\u00eb p\u00ebr sekond\u00eb, dmth n\u00ebse do t\u00eb kishte dy b\u00ebrthama, do t\u00eb ishte mir\u00eb. Do t\u00eb ishte rreth 75% ngarkes\u00eb. Dhe k\u00ebshtu do t\u00eb punonte. N\u00ebse do t\u00eb kishim 10 b\u00ebrthama, do t\u00eb ishim plot\u00ebsisht t\u00eb qet\u00eb. K\u00ebshtu mund t\u00eb vler\u00ebsojm\u00eb burimet.<\/p>\n<p><\/p>\n<p>K002 \u2013 k\u00ebtu e quaj klasat e k\u00ebrkesave, dmth SELECT, INSERT, UPDATE, DELETE. Dhe ve\u00e7mas SELECT FOR UPDATE, sepse ky bllokon. <\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu mund t\u00eb arrijm\u00eb n\u00eb p\u00ebrfundimin se SELECT t\u00eb zakonsh\u00ebm lexues \u2013 82% e t\u00eb gjith\u00eb thirrjeve, por nga ana tjet\u00ebr \u2013 74% p\u00ebr total_time. DMTH ato thirren shum\u00eb, por konsumojn\u00eb m\u00eb pak burime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0e0234e696ffe1c80f8b5acccc602dbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe kthehemi tek pyetja: \"Si t\u00eb zgjedhim sakt\u00eb shared_buffers?\". V\u00ebrej se shumica e bllokimeve ndodhen mbi iden\u00eb \u2013 le t\u00eb shikojm\u00eb se \u00e7far\u00eb throughput-i do t\u00eb ket\u00eb, dmth \u00e7far\u00eb do t\u00eb jet\u00eb kapaciteti i kalimit. Kjo matet zakonisht n\u00eb TPS ose QPS.<\/p>\n<p><\/p>\n<p>Dhe ne p\u00ebrpiqemi t\u00eb nxjerrim nga makina sa m\u00eb shum\u00eb transaksione p\u00ebr sekond\u00eb p\u00ebrmes parametrave t\u00eb optimizimit. K\u00ebtu \u00ebsht\u00eb 311 p\u00ebr sekond\u00eb p\u00ebr select.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d5e73f2672d94bb92eed921ef3dfe0b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por askush nuk shkon n\u00eb pun\u00eb dhe p\u00ebrs\u00ebri n\u00eb sht\u00ebpi me makin\u00eb me shpejt\u00ebsin\u00eb maksimale. Kjo \u00ebsht\u00eb e budallall\u00ebk. Po ashtu me databazat. Ne nuk duhet t\u00eb ecim me shpejt\u00ebsin\u00eb maksimale, askush nuk e b\u00ebn k\u00ebt\u00eb. Askush nuk jeton n\u00eb prodhim me 100% CPU. Edhe pse ndoshta dikush jeton, por kjo nuk \u00ebsht\u00eb mir\u00eb. <\/p>\n<p><\/p>\n<p>Ideja \u00ebsht\u00eb q\u00eb ne zakonisht shkojm\u00eb rreth 20% t\u00eb mund\u00ebsive, ndoshta jo m\u00eb shum\u00eb se 50%. Dhe p\u00ebrpiqemi t\u00eb optimizojm\u00eb koh\u00ebn e p\u00ebrgjigjes p\u00ebr p\u00ebrdoruesit tan\u00eb p\u00ebrpara gjith\u00e7kaje. DMTH duhet t\u00eb rrotullojm\u00eb duart tona p\u00ebr t\u00eb pasur latenc\u00eb minimale n\u00eb 20% shpejt\u00ebsie, n\u00eb m\u00ebnyr\u00eb t\u00eb thjesht\u00eb. Kjo \u00ebsht\u00eb nj\u00eb ide q\u00eb ne gjithashtu p\u00ebrpiqemi ta p\u00ebrdorim n\u00eb eksperimentet tona.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c32c2cfb76dce0ce6b50e929bbf914ac.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00eb p\u00ebrfundim t\u00eb rekomandimeve: <\/p>\n<p><\/p>\n<ul>\n<li>Sigurohuni q\u00eb t\u00eb krijoni Database Lab.<\/li>\n<li>Sa m\u00eb shum\u00eb q\u00eb \u00ebsht\u00eb e mundur, krijoni n\u00eb k\u00ebrkes\u00eb, q\u00eb t\u00eb zhvillohet p\u00ebr nj\u00eb koh\u00eb t\u00eb caktuar \u2013 t\u00eb luani dhe t\u00eb hidhni. N\u00ebse keni mb\u00ebshtetje p\u00ebr cloud, kjo \u00ebsht\u00eb vet\u00eb e natyrshme, dmth, keni shum\u00eb q\u00ebndrim. <\/li>\n<li>B\u00ebni kurioz\u00eb. Dhe n\u00ebse di\u00e7ka nuk shkon, kontrolloni me eksperimente se si sillet. Nancy mund t\u00eb p\u00ebrdoret p\u00ebr t\u00eb m\u00ebsuar vet\u00eb, p\u00ebr t\u00eb provuar si funksionon baza.<\/li>\n<li>Dhe synoni koh\u00ebn minimale t\u00eb reagimit. <\/li>\n<li>Dhe mos u friksoni nga kodet burimore t\u00eb Postgres. Kur punoni me kodin burimor, duhet t\u00eb dini anglisht. Ka shum\u00eb komente, gjith\u00e7ka \u00ebsht\u00eb e shpjeguar. <\/li>\n<li>Dhe kontrolloni sh\u00ebndetin e baz\u00ebs rregullisht, t\u00eb pakt\u00ebn nj\u00eb her\u00eb n\u00eb tre muaj manualisht ose me Postgres-checkup. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8d2ac05f44bd245b879799862ce9e233.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pyetje<\/p>\n<p><\/p>\n<p><em>Faleminderit shum\u00eb! \u00ebsht\u00eb nj\u00eb gj\u00eb shum\u00eb interesante.<\/em> <\/p>\n<p><\/p>\n<p>Dy gj\u00ebra.<\/p>\n<p><\/p>\n<p><em>Po, dy gj\u00ebra. Vet\u00ebm se nuk e kuptova plot\u00ebsisht. Kur punojm\u00eb me Nancy, a mund t\u00eb rregullojm\u00eb vet\u00ebm nj\u00eb parameter ose nj\u00eb grup t\u00eb t\u00ebr\u00eb?<\/em><\/p>\n<p><\/p>\n<p>Ne kemi parametrin e konfigurimit delta. Mund t\u00eb rregulloni aq shum\u00eb sa t\u00eb doni nj\u00ebkoh\u00ebsisht. Por duhet t\u00eb kuptoni, kur ndryshoni shum\u00eb gj\u00ebra, mund t\u00eb b\u00ebni konkluzione t\u00eb gabuara. <\/p>\n<p><\/p>\n<p><em>Po. Pse e pyes? Sepse \u00ebsht\u00eb e v\u00ebshtir\u00eb t\u00eb b\u00ebsh eksperimente kur ke vet\u00ebm nj\u00eb parameter. E rregullon, sheh si funksionon. E vendos. Pastaj fillon me parametrin tjet\u00ebr.<\/em><\/p>\n<p><\/p>\n<p>Mund t\u00eb rregullohen nj\u00ebkoh\u00ebsisht, por varet nga situata, sigurisht. Por \u00ebsht\u00eb m\u00eb mir\u00eb t\u00eb provosh nj\u00eb ide. Dje na erdhi nj\u00eb ide. Kishim nj\u00eb situat\u00eb shum\u00eb t\u00eb ngjashme. Kishim dy konfigurime. Dhe nuk mund t\u00eb kuptonim pse kishte nj\u00eb diferenc\u00eb t\u00eb madhe. Na erdhi ideja se duhet t\u00eb p\u00ebrdorim dykalimin p\u00ebr t\u00eb kuptuar dhe gjetur ndryshimin. Mund t\u00eb b\u00ebni menj\u00ebher\u00eb gjysm\u00ebn e parametrave t\u00eb nj\u00ebjt\u00eb, pastaj nj\u00eb t\u00eb kat\u00ebrt\u00ebn e k\u00ebshtu me radh\u00eb. Gjith\u00e7ka \u00ebsht\u00eb fleksib\u00ebl.<\/p>\n<p><\/p>\n<p><em>Dhe ka nj\u00eb pyetje tjet\u00ebr. Projekti \u00ebsht\u00eb i ri, po zhvillohet. A \u00ebsht\u00eb dokumentacioni tashm\u00eb i gatsh\u00ebm, a ka nj\u00eb p\u00ebrshkrim t\u00eb detajuar?<\/em><\/p>\n<p><\/p>\n<p>Kisha b\u00ebr\u00eb nj\u00eb lidhje t\u00eb ve\u00e7ant\u00eb p\u00ebr p\u00ebrshkrimin e parametrave. Kjo ekziston. Por ka shum\u00eb gj\u00ebra ende q\u00eb nuk ekzistojn\u00eb. Po k\u00ebrkoj bashk\u00ebpun\u00ebtor\u00eb. Dhe i gjej ata kur flas. \u00cbsht\u00eb shum\u00eb bukur. Disa tashm\u00eb punojn\u00eb me mua, disa ndihmuan dhe b\u00ebn\u00eb di\u00e7ka atje. Dhe n\u00ebse jeni t\u00eb interesuar n\u00eb k\u00ebt\u00eb tem\u00eb, jepni feedback \u2013 \u00e7far\u00eb mungon. <\/p>\n<p><\/p>\n<p><em>Kur t\u00eb vendosim laboratorin, ndoshta do t\u00eb ket\u00eb feedback. Do ta shikojm\u00eb. Faleminderit!<\/em><\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr referatin! Shihja se ka mb\u00ebshtetje p\u00ebr Amazon. A \u00ebsht\u00eb planifikuar mb\u00ebshtetje p\u00ebr GSP?<\/em><\/p>\n<p><\/p>\n<p>Pyetja e mir\u00eb. Kemi filluar t\u00eb punojm\u00eb. Dhe p\u00ebr momentin e kemi ngrir\u00eb, sepse duam t\u00eb kursejm\u00eb. Do t\u00eb thot\u00eb, ka mb\u00ebshtetje p\u00ebrmes run on localhost. Ju mund t\u00eb krijoni vet\u00eb nj\u00eb instance dhe t\u00eb punoni lokalmente. N\u00eb fakt, k\u00ebshtu ne punojm\u00eb. N\u00eb Getlab e b\u00ebj k\u00ebshtu, aty n\u00eb GSP. Por nuk shohim kuptim n\u00eb organizimin e till\u00eb p\u00ebr momentin, sepse Google nuk ka oferta t\u00eb lira. Ka ??? instances atje, por ato kan\u00eb kufizime. S\u00eb pari, ata gjithmon\u00eb kan\u00eb nj\u00eb zbritje prej 70 % dhe nuk mund t\u00eb luani me \u00e7mimin. Ne rrisim \u00e7mimin e ofertave me 5-10 % p\u00ebr t\u00eb ulur probabilitetin q\u00eb ju t\u00eb eliminoheni. Do t\u00eb thot\u00eb, me ofertat kurseni, por ato mund t'ju merren n\u00eb \u00e7do moment. N\u00ebse jeni pak m\u00eb t\u00eb lart\u00eb \u00e7mimi se t\u00eb tjer\u00ebt, do t\u00eb eliminoheni m\u00eb von\u00eb. Google ka nj\u00eb specifik\u00eb krejt\u00ebsisht ndryshe. Dhe ka nj\u00eb kufizim shum\u00eb t\u00eb pafavorsh\u00ebm \u2013 ato jetojn\u00eb vet\u00ebm 24 or\u00eb. Ndonj\u00ebher\u00eb ne duam t\u00eb eksperimentojm\u00eb p\u00ebr 5 dit\u00eb. Por me ofertat \u00ebsht\u00eb e mundur, ndonj\u00ebher\u00eb ato jetojn\u00eb p\u00ebr muaj me radh\u00eb. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr referatin! Ju p\u00ebrmend\u00ebt p\u00ebr checkup. Si i llogaritni gabimet stat_statements?<\/em><\/p>\n<p><\/p>\n<p>Pyetje shum\u00eb e mir\u00eb. Mund t\u00eb tregoj dhe t\u00eb shpjegoj n\u00eb detaje. N\u00eb shkurtime \u2013 ne shikojm\u00eb se si ka ndryshuar grupi i k\u00ebrkesave: sa jan\u00eb eliminuar dhe sa t\u00eb reja jan\u00eb shfaqur. Dhe m\u00eb pas ne shikojm\u00eb dy metrika: total_time dhe calls, prandaj ka dy gabime. Dhe shohim, \u00e7far\u00eb kontributi kan\u00eb grupet q\u00eb jan\u00eb ndryshuar. Ka dy n\u00ebn-grupe: ato q\u00eb jan\u00eb ikur dhe ato q\u00eb jan\u00eb kthyer. Shikojm\u00eb, \u00e7far\u00eb kontributi kan\u00eb n\u00eb pamjen e p\u00ebrgjithshme. <\/p>\n<p><\/p>\n<p><em>A mos keni frik\u00eb se ajo do t\u00eb rrotullohet dy-tre her\u00eb n\u00eb koh\u00ebn midis snapshot-\u00ebve?<\/em><\/p>\n<p><\/p>\n<p>Do t\u00eb thot\u00eb, ata u regjistruan p\u00ebrs\u00ebri ose si?<\/p>\n<p><\/p>\n<p><em>P\u00ebr shembull, kjo k\u00ebrkes\u00eb \u00ebsht\u00eb eliminuar nj\u00eb her\u00eb, pastaj erdhi dhe u eliminuar p\u00ebrs\u00ebri, pastaj erdhi s\u00ebrish dhe u eliminuar p\u00ebrs\u00ebri. Dhe k\u00ebtu llogaritet di\u00e7ka, dhe ku \u00ebsht\u00eb e gjith\u00eb kjo?<\/em><\/p>\n<p><\/p>\n<p>Pyetje e mir\u00eb, duhet t\u00eb shikojm\u00eb. <\/p>\n<p><\/p>\n<p><em>Kam b\u00ebr\u00eb di\u00e7ka t\u00eb ngjashme. Sigurisht m\u00eb e thjesht\u00eb, e kam b\u00ebr\u00eb vet\u00ebm. Por mu desh t\u00eb rikthehem, t\u00eb b\u00ebj reset stat_statements dhe t\u00eb orientohesha n\u00eb momentin e snapshot-it, duke par\u00eb q\u00eb kishte m\u00eb pak se nj\u00eb pjes\u00eb t\u00eb caktuar, q\u00eb ende nuk kishte arritur n\u00eb kulm, sa stat_statements mund t\u00eb grumbullohen. Dhe un\u00eb orientohesha q\u00eb, me sa duket, nuk ishte eliminuar asgj\u00eb.<\/em> <\/p>\n<p><\/p>\n<p>Po-po. <\/p>\n<p><\/p>\n<p><em>Por nuk e kuptoj si mund ta b\u00ebj ndryshe me sakt\u00ebsi.<\/em><\/p>\n<p><\/p>\n<p>M\u00eb vjen keq, nuk e mbaj mend sakt\u00ebsisht \u2013 p\u00ebrdorim tekstin e k\u00ebrkes\u00ebs ose queryid nga pg_stat_statements dhe mbi t\u00eb orientoheshim. N\u00ebse ne orientoheshim n\u00eb queryid, n\u00eb parim, krahasojm\u00eb gj\u00ebra t\u00eb krahasueshme. <\/p>\n<p><\/p>\n<p><em>Jo, ai mund t\u00eb largohet disa her\u00eb midis snapshot-eve dhe t\u00eb vij\u00eb s\u00ebrish.<\/em><\/p>\n<p><\/p>\n<p>Me k\u00ebt\u00eb ID t\u00eb nj\u00ebjt\u00eb?<\/p>\n<p><\/p>\n<p><em>Po.<\/em> <\/p>\n<p><\/p>\n<p>Ne do ta studiojm\u00eb k\u00ebt\u00eb. Pyetje e mir\u00eb. Duhet t\u00eb hulumtojm\u00eb. Por p\u00ebr momentin, ajo q\u00eb shohim \u00ebsht\u00eb ose 0 shkruhet...<\/p>\n<p><\/p>\n<p><em>Sigurisht, kjo \u00ebsht\u00eb nj\u00eb rast i rrall\u00eb, por m\u00eb tronditi kur m\u00ebsova se stat_statements mund t\u00eb largohet atje.<\/em> <\/p>\n<p><\/p>\n<p>N\u00eb Pg_stat_statements mund t\u00eb ket\u00eb shum\u00eb gj\u00ebra. Ne kemi kaluar me at\u00eb q\u00eb n\u00ebse keni track_utility = on, at\u00ebher\u00eb grupet tuaja gjithashtu ndjeken. <\/p>\n<p><\/p>\n<p><em>Po, sigurisht.<\/em><\/p>\n<p><\/p>\n<p>Dhe n\u00ebse keni java hibernate, q\u00eb \u00ebsht\u00eb rast\u00ebsor, at\u00ebher\u00eb aty fillon t\u00eb bllokohet tabela e hash-it. Dhe sapo t\u00eb \u00e7aktivizoni nj\u00eb aplikacion me ngarkes\u00eb t\u00eb madhe, keni 50-100 grupe. Dhe aty gjith\u00e7ka \u00ebsht\u00eb m\u00eb shum\u00eb-m\u00eb pak e stabilizuar. Nj\u00eb nga m\u00ebnyrat p\u00ebr t\u00eb luftuar k\u00ebt\u00eb \u00ebsht\u00eb t\u00eb rritur pg_stat_statements.max. <\/p>\n<p><\/p>\n<p><em>Po, por duhet t\u00eb dini se sa. Dhe ndonj\u00ebher\u00eb duhet ta monitoroni. Un\u00eb e b\u00ebj k\u00ebt\u00eb. Dometh\u00ebn\u00eb, kam pg_stat_statements.max. Dhe shoh se n\u00eb momentin e snapshot-it nuk kam arritur 70% t\u00eb saj. Mir\u00eb, dometh\u00ebn\u00eb, nuk kemi humbur asgj\u00eb. B\u00ebjm\u00eb reset. Dhe grumbullojm\u00eb s\u00ebrish. N\u00ebse n\u00eb snapshot-in tjet\u00ebr \u00ebsht\u00eb m\u00eb pak se 70, at\u00ebher\u00eb me shum\u00eb gjas\u00eb, s\u00ebrish nuk kemi humbur asgj\u00eb.<\/em><\/p>\n<p><\/p>\n<p>Po. P\u00ebr default tani jan\u00eb 5,000. Dhe shum\u00eb njer\u00ebz e kan\u00eb mjaft k\u00ebt\u00eb. <\/p>\n<p><\/p>\n<p><em>Zakonisht \u2013 po.<\/em> <\/p>\n<p><\/p>\n<p>Video:<\/p>\n<p>\n<center><div class=\"youtube-placeholder\" data-id=\"yvO1jjG-tDI\" onclick=\"loadVideo(this)\">\r\n        <img decoding=\"async\" src=\"https:\/\/img.youtube.com\/vi\/yvO1jjG-tDI\/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>P.S. Nga ana ime, do t\u00eb shtoj se n\u00ebse n\u00eb Postgres ka t\u00eb dh\u00ebna konfidenciale dhe ato nuk duhet t\u00eb kalojn\u00eb n\u00eb ambientin e testimit, at\u00ebher\u00eb mund t\u00eb p\u00ebrdorni <noindex><a rel=\"nofollow\" href=\"https:\/\/labs.dalibo.com\/postgresql_anonymizer\">PostgreSQL Anonymizer<\/a><\/noindex>. Schema \u00ebsht\u00eb p\u00ebraf\u00ebrsisht si m\u00eb posht\u00eb:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cf9a2052364e09a20e68d2347dd0c25b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Burimi: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/498060\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u041d\u0438\u043a\u043e\u043b\u0430\u044f \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432\u0430 &quot;\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445&quot; Shared_buffers = 25% \u2013 \u044d\u0442\u043e \u043c\u043d\u043e\u0433\u043e \u0438\u043b\u0438 \u043c\u0430\u043b\u043e? \u0418\u043b\u0438 \u0432 \u0441\u0430\u043c\u044b\u0439 \u0440\u0430\u0437? \u041a\u0430\u043a \u043f\u043e\u043d\u044f\u0442\u044c, \u043f\u043e\u0434\u0445\u043e\u0434\u0438\u0442 \u043b\u0438 \u044d\u0442\u0430 \u2013 \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u0443\u0441\u0442\u0430\u0440\u0435\u0432\u0448\u0430\u044f \u2013 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u044f \u0432 \u0432\u0430\u0448\u0435\u043c \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435? \u041f\u0440\u0438\u0448\u043b\u043e \u0432\u0440\u0435\u043c\u044f \u043f\u043e\u0434\u043e\u0439\u0442\u0438 \u043a \u0432\u043e\u043f\u0440\u043e\u0441\u0443 \u043f\u043e\u0434\u0431\u043e\u0440\u0430 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u043e\u0432 postgresql.conf &quot;\u043f\u043e-\u0432\u0437\u0440\u043e\u0441\u043b\u043e\u043c\u0443&quot;. \u041d\u0435 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0441\u043b\u0435\u043f\u044b\u0445 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":78743,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-78742","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=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u041d\u0438\u043a\u043e\u043b\u0430\u044f \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432\u0430 &quot;\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445&quot; Shared_buffers = 25% \u2013 \u044d\u0442\u043e \u043c\u043d\u043e\u0433\u043e \u0438\u043b\u0438 \u043c\u0430\u043b\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\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov\" \/>\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\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445\u00bb. \u041d\u0438\u043a\u043e\u043b\u0430\u0439 \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u041d\u0438\u043a\u043e\u043b\u0430\u044f \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432\u0430 &quot;\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445&quot; Shared_buffers = 25% \u2013 \u044d\u0442\u043e \u043c\u043d\u043e\u0433\u043e \u0438\u043b\u0438 \u043c\u0430\u043b\u043e?\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov\" \/>\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-04-21T17:42:46+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-04-21T17:42:46+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\udd47Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave. Nikolai Samokhvalov | ProHoster","description":"Sugjeroj t\u00eb njihemi me p\u00ebrkthimin e raportit t\u00eb Nikolai Samokhvalov \"Qasja industriale p\u00ebr optimizimin e PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave\" Shared_buffers = 25% \u2013 a \u00ebsht\u00eb shum\u00eb apo pak?","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","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\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445\u00bb. \u041d\u0438\u043a\u043e\u043b\u0430\u0439 \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432 | ProHoster","og:description":"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u041d\u0438\u043a\u043e\u043b\u0430\u044f \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432\u0430 &quot;\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445&quot; Shared_buffers = 25% \u2013 \u044d\u0442\u043e \u043c\u043d\u043e\u0433\u043e \u0438\u043b\u0438 \u043c\u0430\u043b\u043e?","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","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-04-21T17:42:46+00:00","article:modified_time":"2020-04-21T17:42:46+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"78742","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 16:51:38","updated":"2022-09-28 02:51:26","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\/78742","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=78742"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/78742\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/78743"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=78742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=78742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=78742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}