{"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 p\u00ebr tuning PostgreSQL: eksperimentet mbi bazat e t\u00eb dh\u00ebnave&#187;. Nikolai Samokhvalov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Ju lutem, shikoni p\u00ebrkthimin e raportit nga Nikolai Samokhvalov \"Qasja industriale p\u00ebr tuning 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? Apo ndoshta \u00ebsht\u00eb perfekt? Si mund ta kuptoni n\u00ebse kjo \u2013 nj\u00eb rekomandim mjaft i vjet\u00ebr \u2013 i p\u00ebrshtatet rastit tuaj konkret?<\/p>\n<p><\/p>\n<p>\u00cbsht\u00eb koha t\u00eb qasemi n\u00eb \u00e7\u00ebshtjen e p\u00ebrcaktimit t\u00eb parametrave postgresql.conf \"si duhet\". Jo p\u00ebrmes \"autotuner\u00ebve\" t\u00eb verb\u00ebr ose k\u00ebshillave t\u00eb vjetra nga artikuj dhe blogje, por bazuar n\u00eb:<\/p>\n<p><\/p>\n<ol>\n<li>eksperimente t\u00eb sakta mbi DB, t\u00eb kryera n\u00eb m\u00ebnyr\u00eb automatike, n\u00eb sasi t\u00eb m\u00ebdha dhe n\u00eb kushte sa m\u00eb af\u00ebr \"luft\u00ebs\",<\/li>\n<li>njohjes s\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 famshmit 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 konfigurimin optimal p\u00ebr infrastruktur\u00ebn ton\u00eb, DB dhe ngarkes\u00ebn.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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>Do t\u00eb flasim p\u00ebr eksperimente mbi bazat e t\u00eb dh\u00ebnave. Kjo \u00ebsht\u00eb nj\u00eb histori q\u00eb vazhdon p\u00ebr m\u00eb shum\u00eb se gjasht\u00eb muaj. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/e77255c4abaa2a01ae37fe888fe67db7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pak t\u00eb dh\u00ebna p\u00ebr veten time. Kam mbi 14 vjet p\u00ebrvoj\u00eb me Postgres. Kam themeluar disa kompani n\u00eb rrjetet sociale. Kudo \u00ebsht\u00eb p\u00ebrdorur dhe p\u00ebrdoret Postgres.<\/p>\n<p><\/p>\n<p>Gjithashtu, grupi RuPostgres n\u00eb Meetup, vendi i dyt\u00eb n\u00eb bot\u00eb. Po afrohemi ngadal\u00eb drejt 2000 personave. RuPostgres.org.<\/p>\n<p><\/p>\n<p>Dhe n\u00eb shum\u00eb konferenca, duke p\u00ebrfshir\u00eb Highload, un\u00eb jam p\u00ebrgjegj\u00ebs p\u00ebr bazat e t\u00eb dh\u00ebnave, ve\u00e7an\u00ebrisht Postgres q\u00eb nga fillimi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 k\u00ebshillimit p\u00ebr Postgres n\u00eb 11 zona orare nga k\u00ebtu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 vite m\u00eb par\u00eb, pata nj\u00eb ndales\u00eb t\u00eb dukshme n\u00eb pun\u00ebn manuale me Postgres, ndoshta q\u00eb nga viti 2010. U habit\u00ebm se sa pak kishin ndryshuar dit\u00ebt e pun\u00ebs s\u00eb DBA-ve, sa shum\u00eb pun\u00eb manuale ende \u00ebsht\u00eb e nevojshme. Dhe menj\u00ebher\u00eb mendova se di\u00e7ka nuk shkon, duhet t\u00eb automatizohet m\u00eb shum\u00eb.<\/p>\n<p><\/p>\n<p>Dhe p\u00ebr shkak se kjo ndodhte kryesisht n\u00eb distanc\u00eb, shumica e klient\u00ebve ishin n\u00eb cloud. Dhe tashm\u00eb shum\u00eb \u00ebsht\u00eb automatizuar, kjo \u00ebsht\u00eb e qart\u00eb. P\u00ebr k\u00ebt\u00eb do t\u00eb flasim m\u00eb von\u00eb. Kjo do t\u00eb thot\u00eb se gjith\u00e7ka ky\u00e7ej n\u00eb nj\u00eb ide, q\u00eb duhet t\u00eb kemi nj\u00eb mori instrumentesh, pra nj\u00eb platform\u00eb, e cila do t\u00eb automatizonte pothuajse t\u00eb gjitha veprimet e DBA-ve, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mund t\u00eb menaxhonim nj\u00eb num\u00ebr t\u00eb madh bazash.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 diskutim nuk do t\u00eb ket\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>\u201eFlet\u00ebt argjendi\u201d dhe deklarata si \u2013 vendosni 8 GB ose 25% shared_buffers dhe do t'ju shkoj\u00eb mir\u00eb. P\u00ebr shared_buffers nuk do t\u00eb flitet shum\u00eb. <\/li>\n<li>Hardcore \u201ethell\u00ebsi\u201d. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ccea359ec78a967c150ddcedc3e50a88.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Po \u00e7far\u00eb do t\u00eb ket\u00eb?<\/p>\n<p><\/p>\n<ul>\n<li>Do t\u00eb jen\u00eb parime optimizimi q\u00eb ne i aplikojm\u00eb dhe i zhvillojm\u00eb. Do t\u00eb jen\u00eb \u00e7do lloj ideje q\u00eb na lind n\u00eb rrug\u00eb dhe nj\u00eb mori instrumentesh q\u00eb krijojm\u00eb kryesisht n\u00eb Open Source, pra ne krijojm\u00eb nj\u00eb baz\u00eb n\u00eb Open Source. P\u00ebr m\u00eb tep\u00ebr, kemi bileta, t\u00eb gjith\u00eb komunikimi \u00ebsht\u00eb praktikisht n\u00eb Open Source. Mund t\u00eb shihni se \u00e7far\u00eb po b\u00ebjm\u00eb tani, \u00e7far\u00eb do t\u00eb jet\u00eb n\u00eb versionin e ardhsh\u00ebm, etj. <\/li>\n<li>Do t\u00eb ket\u00eb gjithashtu disa p\u00ebrvoja mbi p\u00ebrdorimin e k\u00ebtyre parimeve dhe k\u00ebtyre instrumenteve n\u00eb nj\u00eb s\u00ebr\u00eb kompanish: nga fillestar\u00eb t\u00eb vegj\u00ebl deri tek kompani t\u00eb m\u00ebdha. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86e6f379726dec814aca616a30d36a2f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Si zhvillohet gjith\u00e7ka kjo?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c1422d9f2945e717c6ebb31f5fef6329.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S\u00eb pari, detyra kryesore e DBA-s\u00eb p\u00ebrve\u00e7 siguris\u00eb s\u00eb krijimit t\u00eb instanceve, p\u00ebrhapjes s\u00eb kopjeve t\u00eb rezerv\u00ebs etj., \u00ebsht\u00eb identifikimi i ngushticave dhe optimizimi i performanc\u00ebs.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8e682ca8666e94d6688348ebe4540327.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tani, kjo \u00ebsht\u00eb strukturuar k\u00ebshtu. Ne shikojm\u00eb monitorimin, shohim di\u00e7ka, na mungojn\u00eb disa detaje. Fillojm\u00eb t\u00eb hetojm\u00eb m\u00eb n\u00eb thell\u00ebsi, zakonisht me duar dhe kuptojm\u00eb se \u00e7far\u00eb duhet t\u00eb b\u00ebjm\u00eb k\u00ebshtu ose ashtu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/04599422ea6b6d57c79a4e48ba2db8b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe ka dy qasje. Pg_stat_statements \u2013 zgjidhja standarde p\u00ebr identifikimin e k\u00ebrkesave t\u00eb ngadalta. Dhe analiza e log\u00ebve t\u00eb Postgres me pgBadger.<\/p>\n<p><\/p>\n<p>\u00c7do qasje ka t\u00eb meta t\u00eb r\u00ebnda. N\u00eb qasjen e par\u00eb ne hedhim t\u00eb gjitha parametrat. Dhe n\u00ebse ne shohim grupe SELECT * FROM table where kolona \u00ebsht\u00eb e barabart\u00eb me simbolin \"?\" ose \"$\" q\u00eb nga versione Postgres 10. Ne nuk e dim\u00eb \u2013 b\u00ebhet fjal\u00eb p\u00ebr nj\u00eb skanim indeksi apo nj\u00eb skanim sekondar. V\u00ebrtet varet shum\u00eb nga parametri. N\u00ebse shton nj\u00eb vler\u00eb t\u00eb rrall\u00eb, do t\u00eb jet\u00eb skanim indeksi. N\u00ebse fut nj\u00eb vler\u00eb q\u00eb p\u00ebrfshin 90% t\u00eb tabel\u00ebs, do t\u00eb jet\u00eb skanim sekondar, sepse Postgres di statistik\u00ebn. Dhe kjo \u00ebsht\u00eb nj\u00eb mang\u00ebsi e madhe e pg_stat_statements, megjithat\u00eb disa pun\u00eb po zhvillohen.<\/p>\n<p><\/p>\n<p>Analiza e log\u00ebve ka mang\u00ebsin\u00eb m\u00eb t\u00eb madhe q\u00eb nuk mund t\u00eb lejoni \"log_min_duration_statement = 0\", si rregull. Dhe p\u00ebr k\u00ebt\u00eb do t\u00eb flasim gjithashtu. P\u00ebr k\u00ebt\u00eb arsye, nuk e shihni t\u00ebr\u00eb pamjen. Dhe nj\u00eb k\u00ebrkes\u00eb q\u00eb \u00ebsht\u00eb shum\u00eb e shpejt\u00eb, mund t\u00eb konsumoj\u00eb nj\u00eb sasi t\u00eb madhe burimesh, por ju nuk do ta shihni at\u00eb, sepse \u00ebsht\u00eb n\u00ebn pragun tuaj.<\/p>\n<p><\/p>\n<p><strong>Si i zgjidhin DBA-t\u00eb problemet e gjetura?<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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, gjet\u00ebm nj\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 i madh. N\u00ebse jeni DBA, keni staging. Dhe ai mund t\u00eb jet\u00eb vet\u00ebm nj\u00eb. Dhe \u00ebsht\u00eb pas me gjasht\u00eb muaj. Dhe mendoni se do t\u00eb shkoni n\u00eb production. Edhe DBA t\u00eb p\u00ebrvojsh\u00ebm kontrollojn\u00eb pastaj n\u00eb production, n\u00eb replik\u00eb. Dhe ndonj\u00ebher\u00eb krijojn\u00eb nj\u00eb indeks t\u00eb p\u00ebrkohsh\u00ebm, e kontrollojn\u00eb n\u00ebse ndihmon, e heqin dhe ia dor\u00ebzojn\u00eb zhvilluesve p\u00ebr ta futur n\u00eb skedar\u00ebt e migrimit. Kjo \u00ebsht\u00eb \u00e7far\u00eb po ndodh tani. Dhe kjo \u00ebsht\u00eb e keqe.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 optimizoni konfigurimet.<\/li>\n<li>T\u00eb optimizoni grupin e indekseve. <\/li>\n<li>T\u00eb ndryshoni krijimin e SQL (kjo \u00ebsht\u00eb m\u00ebnyra m\u00eb e v\u00ebshtir\u00eb).<\/li>\n<li>T\u00eb shtoni kapacitet (m\u00ebnyra m\u00eb e leht\u00eb n\u00eb shumic\u00ebn e rasteve).<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 b\u00ebr\u00eb. Ka shum\u00eb mund\u00ebsi n\u00eb Postgres. Duhet t\u00eb dini shum\u00eb. Ka shum\u00eb indekse n\u00eb Postgres, fal\u00eb organizator\u00ebve t\u00eb k\u00ebsaj konference. Dhe t\u00eb gjitha k\u00ebto duhet t\u00eb dihen, dhe pik\u00ebrisht kjo i jep ndjesin\u00eb atyre q\u00eb nuk jan\u00eb DBA se ata merren me magji t\u00eb zez\u00eb. Pra, duhet t\u00eb kaloni rreth 10 vjet p\u00ebr t\u00eb filluar t\u00eb kuptoni t\u00eb gjitha k\u00ebto si\u00e7 duhet.<\/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 m\u00ebnyr\u00eb q\u00eb t\u00eb ket\u00eb teknologji, e jo intuit\u00eb 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 ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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\u00ebzhguar n\u00eb t\u00eb pakt\u00ebn dy projekte, p\u00ebrfshir\u00eb t\u00eb mien. Nj\u00eb postim i ri n\u00eb blog na tregon se vlera 1 000 p\u00ebr default_statistict_target \u2013 \u00ebsht\u00eb e mir\u00eb. Mir\u00eb, le t\u00eb provojm\u00eb n\u00eb production.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 me eksperimente mbi bazat e t\u00eb dh\u00ebnave p\u00ebr t\u00eb cilat po flasim sot, mund t\u00eb krahasojm\u00eb \u00e7far\u00eb ka qen\u00eb dhe \u00e7far\u00eb \u00ebsht\u00eb b\u00ebr\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 mjedisi. Na nevojitet harduer. Dhe kur shkoj n\u00eb ndonj\u00eb kompani dhe lidh nj\u00eb kontrat\u00eb, un\u00eb them q\u00eb t\u00eb m\u00eb japin harduerin e nj\u00ebjt\u00eb si n\u00eb production. P\u00ebr secilin nga Master-at tuaja, m\u00eb nevojitet t\u00eb pakt\u00ebn nj\u00eb harduer i till\u00eb. Qoft\u00eb kjo nj\u00eb makin\u00eb virtuale instance n\u00eb Amazon ose n\u00eb Google, ose m\u00eb nevojitet pik\u00ebrisht nj\u00eb harduer i nj\u00ebjt\u00eb. Pra, dua ta riprodhoj mjedisin. Dhe n\u00eb konceptin e mjedisit 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 \u2013 \u00ebsht\u00eb ajo q\u00eb ne kontrollojm\u00eb, pra \u00e7far\u00eb do t\u00eb krahasojm\u00eb. Le t\u00eb themi, mund t\u00eb ndryshojm\u00eb nj\u00eb ose disa parametra n\u00eb konfigurim, ose mund t\u00eb krijojm\u00eb nj\u00eb indeks dhe k\u00ebshtu me radh\u00eb. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8ccd09ac4cdd336618413375617c10a6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ne fillojm\u00eb eksperimentin. Ja pg_stat_statements. Nga e majta \u2013 ajo q\u00eb ishte. Nga e djathta \u2013 sidomos si \u00ebsht\u00eb b\u00ebr\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/6f82d7e25a77b700f2ddb01f42a861a2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nga e majta default_statistics_target = 100, nga e djathta = 1 000. Shohim se na ndihmoi. N\u00eb 8% gjith\u00e7ka \u00ebsht\u00eb p\u00ebrmir\u00ebsuar.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 rrokullisim posht\u00eb, do t\u00eb ket\u00eb grupe k\u00ebrkesash nga pgBadger ose nga pg_stat_statements. K\u00ebtu ka dy variante. Ne do t\u00eb shohim q\u00eb ndonj\u00eb k\u00ebrkes\u00eb ka r\u00ebn\u00eb me 88%. Dhe k\u00ebtu \u00ebsht\u00eb nj\u00eb qasje inxhinierike. Ne mund t\u00eb thellojm\u00eb m\u00eb tej, sepse na intereson, pse ra. Duhet t\u00eb kuptojm\u00eb se \u00e7far\u00eb ndodhi me statistik\u00ebn. Pse m\u00eb shum\u00eb bakete n\u00eb statistik\u00eb \u00e7ojn\u00eb n\u00eb nj\u00eb rezultat m\u00eb t\u00eb keq.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 s\u00ebrish 100 bakete n\u00eb statistik\u00ebn e k\u00ebsaj kolone. Dhe m\u00eb tej, me nj\u00eb eksperiment, ne mund t\u00eb sigurohemi se kjo zgjidhje ndihmoi. Kjo \u00ebsht\u00eb qasje inxhinierike, q\u00eb na ndihmon t\u00eb shohim imazhin dhe t\u00eb marrim vendime mbi t\u00eb dh\u00ebna, dhe jo mbi intuit\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/9e766aa4348bd1a77b697e3179d84bbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pak shembuj nga fusha t\u00eb tjera. N\u00eb testime ka CI-testime p\u00ebr shum\u00eb vite. Dhe asnj\u00eb projekt tashm\u00eb n\u00eb mendjen e sh\u00ebndosh\u00eb nuk do t\u00eb ekzistoj\u00eb pa teste automatike.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b6c21981dc8b2b285973ae42199e2b4c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb fusha t\u00eb tjera: n\u00eb aviacion, n\u00eb automobiliz\u00ebm, kur testojm\u00eb aerodinamik\u00ebn, ne gjithashtu kemi mund\u00ebsin\u00eb t\u00eb b\u00ebjm\u00eb eksperimente. Nuk do t\u00eb hedhim ndonj\u00eb gj\u00eb t\u00eb hartuar menj\u00ebher\u00eb n\u00eb hap\u00ebsir\u00eb ose nuk do t\u00eb nxjerrim ndonj\u00eb makin\u00eb menj\u00ebher\u00eb n\u00eb rrug\u00eb. P\u00ebr shembull, ka nj\u00eb tunel aerodinamik. <\/p>\n<p><\/p>\n<p>Nga v\u00ebzhgimet n\u00eb fusha t\u00eb tjera, ne mund t\u00eb nxjerrim p\u00ebrfundime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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, kemi nj\u00eb mjedis t\u00eb specializuar. Ai \u00ebsht\u00eb af\u00ebr production, por nuk \u00ebsht\u00eb af\u00ebr. Karakteristika kryesore \u00ebsht\u00eb se duhet t\u00eb jet\u00eb i lir\u00eb, i riprodhuesh\u00ebm dhe maksimalisht i automatizuar. Dhe gjithashtu duhet t\u00eb kemi mjete speciale p\u00ebr nj\u00eb analiz\u00eb t\u00eb detajuar.<\/p>\n<p><\/p>\n<p>Shum\u00eb mund\u00ebsi, kur hedhim avionin dhe flasim, n\u00eb kemi m\u00eb pak mund\u00ebsi p\u00ebr t\u00eb studiuar \u00e7do milimet\u00ebr t\u00eb sip\u00ebrfaqes s\u00eb krahut, sesa kemi n\u00eb tunelin aerodinamik. Ne kemi m\u00eb shum\u00eb mjete p\u00ebr diagnostikim. Mund t\u00eb lejojm\u00eb veten t\u00eb vendosim m\u00eb shum\u00eb pesh\u00eb, q\u00eb nuk mund ta vendosim n\u00eb avion nd\u00ebrsa fluturojm\u00eb. Po ashtu edhe me Postgres. N\u00eb disa raste, mund t\u00eb aktivizojm\u00eb regjistrimin e plot\u00eb t\u00eb k\u00ebrkesave gjat\u00eb eksperimenteve. Dhe ne nuk duam ta b\u00ebjm\u00eb k\u00ebt\u00eb n\u00eb production. Ndoshta madje k\u00ebt\u00eb do ta aktivizojm\u00eb me planet duke p\u00ebrdorur auto_explain.<\/p>\n<p><\/p>\n<p>Dhe si\u00e7 thash\u00eb, niveli i lart\u00eb i automatizimit do t\u00eb thot\u00eb q\u00eb ne e shtyp\u00ebm butonin dhe e kemi p\u00ebrs\u00ebritur. K\u00ebshtu duhet t\u00eb jet\u00eb q\u00eb t\u00eb ket\u00eb shum\u00eb eksperimente, q\u00eb t\u00eb jet\u00eb n\u00eb fluks.<\/p>\n<p><\/p>\n<p>Nancy CLI \u2013 baza e \"laboratorit t\u00eb DB\"<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/f8303df21f309fb209929acec260b757.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe ja \u00e7far\u00eb kemi b\u00ebr\u00eb. Dometh\u00ebn\u00eb, p\u00ebr k\u00ebto ide kam folur n\u00eb qershor, gati nj\u00eb vit m\u00eb par\u00eb. Dhe ne tashm\u00eb kemi n\u00eb Open Source at\u00eb q\u00eb quhet Nancy CLI. Ky \u00ebsht\u00eb themeli p\u00ebr nd\u00ebrtimin e laboratorit t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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> \u2014 Kjo \u00ebsht\u00eb n\u00eb Open Source, n\u00eb Gitlab. Mund ta shikoni, mund ta provoni. Kam l\u00ebn\u00eb nj\u00eb lidhje n\u00eb slidet. Mund t\u00eb klikoni dhe atje do t\u00eb jet\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, aty ka shum\u00eb q\u00eb jan\u00eb ende n\u00eb zhvillim. Ka shum\u00eb ide. Por kjo \u00ebsht\u00eb di\u00e7ka q\u00eb ne e aplikojm\u00eb n\u00eb p\u00ebrditshm\u00ebri. Dhe kur na vjen nj\u00eb ide \u2013 a \u00e7far\u00eb ndodh kur fshihet 40 000 000 rreshta dhe gjith\u00e7ka p\u00ebrfundon n\u00eb IO, ne mund t\u00eb kryejm\u00eb nj\u00eb eksperiment dhe t\u00eb shohim m\u00eb n\u00eb detaje, p\u00ebr t\u00eb kuptuar \u00e7far\u00eb po ndodh dhe pastaj t\u00eb p\u00ebrpiqemi ta rregullojm\u00eb at\u00eb n\u00eb moment. Dometh\u00ebn\u00eb, ne b\u00ebjm\u00eb nj\u00eb eksperiment. P\u00ebr shembull, ndryshojm\u00eb di\u00e7ka dhe shohim \u00e7far\u00eb rezultati merrim. Dhe ne e b\u00ebjm\u00eb k\u00ebt\u00eb jo n\u00eb prodhim. Kjo \u00ebsht\u00eb thelbi i ideve.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 lokalisht, dometh\u00ebn\u00eb mund ta b\u00ebni kudo, mund ta nisni edhe n\u00eb MacBook. Duhet Docker, le t\u00eb nisim. Dhe gjith\u00eb kjo. Mund ta nisim n\u00eb ndonj\u00eb instanc\u00eb n\u00eb harduer, ose n\u00eb nj\u00eb makin\u00eb virtuale, kudo.<\/p>\n<p><\/p>\n<p>Dhe ka gjithashtu mund\u00ebsin\u00eb p\u00ebr ta nisur nga larg n\u00eb Amazon n\u00eb EC2 Instance, n\u00eb spot. Dhe kjo \u00ebsht\u00eb nj\u00eb mund\u00ebsi shum\u00eb e shk\u00eblqyer. P\u00ebr shembull, dje ne kryem mbi 500 eksperimentet n\u00eb instanc\u00ebn i3, duke filluar nga m\u00eb e vogla dhe duke p\u00ebrfunduar me i3-16-xlarge. Dhe na kushtuan 500 eksperimente 64 dollar\u00eb. \u00c7do nj\u00ebra zgjati 15 minuta. Dometh\u00ebn\u00eb, p\u00ebr shkak se aty p\u00ebrdoren spotet, kjo \u00ebsht\u00eb shum\u00eb e lir\u00eb \u2013 zbritje 70%, tarifimi p\u00ebr sekond\u00eb nga Amazon. Mund t\u00eb b\u00ebni shum\u00eb. Mund t\u00eb kryeni nj\u00eb studim t\u00eb v\u00ebrtet\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 aq e v\u00ebshtir\u00eb t\u00eb p\u00ebrshtatni disa t\u00eb vjetra dhe versionin e ri 12 gjithashtu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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. K\u00ebto jan\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>Dump\/sql-file. <\/li>\n<li>M\u00ebnyra kryesore \u2013 \u00ebsht\u00eb kloni i drejtoris\u00eb PGDATA. N\u00eb p\u00ebrgjith\u00ebsi merret nga serveri i backup-it. N\u00ebse keni backup-e binar\u00eb t\u00eb mira, mund t\u00eb b\u00ebni klona atje. N\u00ebse keni cloud, at\u00ebher\u00eb kjo do t\u00eb b\u00ebhet nga kompania cloud si Amazon ose Google. Kjo \u00ebsht\u00eb m\u00ebnyra kryesore p\u00ebr klonat e prodhimit real. N\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb ne po e zbatojm\u00eb. <\/li>\n<li>Dhe m\u00ebnyra e fundit \u00ebsht\u00eb e p\u00ebrshtatshme p\u00ebr hulumtime, kur d\u00ebshirojm\u00eb t\u00eb kuptojm\u00eb 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 zgjedhje \"db-pgbench\". I thoni atij se \u00e7far\u00eb shkalle. Dhe gjith\u00e7ka do t\u00eb gjenerohet n\u00eb cloud, si\u00e7 thuhet.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 emulimi mund t\u00eb b\u00ebhet kryesisht n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb. Duhet t\u00eb mbledhim t\u00eb gjitha log-et. Dhe kjo \u00ebsht\u00eb e dhimbshme. Do t\u00eb tregoj pse. Dhe me ndihm\u00ebn e pgreplay, q\u00eb \u00ebsht\u00eb i integruar n\u00eb Nancy. <\/li>\n<li>Ose nj\u00eb alternativ\u00eb tjet\u00ebr. E ashtuquajtura ngarkes\u00eb artizanal, q\u00eb ne e b\u00ebjm\u00eb me disa p\u00ebrpjekje. Duke analizuar ngarkes\u00ebn ton\u00eb aktuale n\u00eb sistemin aktiv, ne t\u00ebrheqim grupet kryesore t\u00eb k\u00ebrkesave. Dhe me ndihm\u00ebn e pgbench mund t\u00eb emulojm\u00eb k\u00ebt\u00eb ngarkes\u00eb n\u00eb laborator. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 ndoshta duam t\u00eb ekzekutojm\u00eb ndonj\u00eb SQL, dometh\u00ebn\u00eb kontrollojm\u00eb migrimin, krijojm\u00eb nj\u00eb indeks, p\u00ebrfundojm\u00eb nj\u00eb ANALAZE. Dhe shohim \u00e7far\u00eb ndodhi para dhe pas vakuumit. N\u00eb p\u00ebrgjith\u00ebsi, \u00e7do SQL.<\/li>\n<li>Ose ne n\u00eb konfigurim ndryshojm\u00eb nj\u00eb ose disa parametra. Mund t\u00eb themi q\u00eb t\u00eb kontrollojm\u00eb, p\u00ebr shembull, 100 vlera n\u00eb Amazon p\u00ebr baz\u00ebn ton\u00eb nj\u00eb terabajt. Dhe pas disa or\u00ebsh do t\u00eb keni rezultatin. N\u00eb p\u00ebrgjith\u00ebsi, baza e t\u00eb dh\u00ebnave nj\u00eb terabajt do t\u00eb zgjerohet p\u00ebr disa or\u00eb. Por n\u00eb zhvillim ka njoll\u00eb, kemi mund\u00ebsi serike, dometh\u00ebn\u00eb mund t\u00eb p\u00ebrdorni rendin e nj\u00ebjt\u00eb t\u00eb pgdata n\u00eb t\u00eb nj\u00ebjtin server dhe t\u00eb kontrolloni. Postgres do t\u00eb rindez\u00eb, cache-et do t\u00eb fshihen. Dhe mund t\u00eb provoni ngarkes\u00ebn. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 direktor\u00eb, n\u00eb t\u00eb cil\u00ebn ka shum\u00eb skedar\u00eb, duke filluar nga snapshot-et pg<em>stat<\/em>***. Dhe aty m\u00eb e r\u00ebnd\u00ebsishmja \u2013 jan\u00eb 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 informacion p\u00ebr checkpoint dhe si backend-at vet\u00eb shtyjn\u00eb buferat e ndotura. Dhe gjith\u00e7ka \u00ebsht\u00eb interesante p\u00ebr t'u par\u00eb. P\u00ebr shembull, kur e konfiguroni shared_buffers, \u00ebsht\u00eb shum\u00eb interesante t\u00eb shihni sa shum\u00eb jan\u00eb kaluar.<\/li>\n<li>Po ashtu vijn\u00eb log-et e Postgres. Dy log-e \u2013 log-u i p\u00ebrgatitjes dhe log-u i ekzekutimit t\u00eb ngarkes\u00ebs. <\/li>\n<li>Tipar relativisht i ri \u2013 jan\u00eb FlameGraphs.<\/li>\n<li>Po t\u00eb nj\u00ebjt\u00ebn m\u00ebnyr\u00eb, n\u00ebse keni p\u00ebrdorur pgreplay ose pgbench opsionet e ngarkes\u00ebs, do t\u00eb keni daljen e tyre natyrale. Dhe do t\u00eb shihni latenc\u00ebn dhe TPS-in. Do t'ju ndihmoj\u00eb t\u00eb kuptoni si i kan\u00eb par\u00eb ato. <\/li>\n<li>Informacion n\u00eb lidhje me sistemin. <\/li>\n<li>Kontrolli bazik i CPU-s\u00eb dhe IO. Kjo \u00ebsht\u00eb m\u00eb shum\u00eb p\u00ebr instancat EC2 n\u00eb Amazon, kur d\u00ebshironi t\u00eb ekzekutoni 100 instanca t\u00eb nj\u00ebjta dhe t\u00eb ekzekutoni 100 teste t\u00eb ndryshme, do t\u00eb keni 10,000 eksperimente. Dhe duhet t\u00eb siguroheni q\u00eb t\u00eb mos p\u00ebrfshiheni me nj\u00eb instanc\u00eb t\u00eb d\u00ebmtuar q\u00eb dikush tjet\u00ebr e ka shkarkuar. N\u00eb k\u00ebt\u00eb pajisje, t\u00eb tjer\u00eb jan\u00eb aktivizuar dhe juve ju mbetet pak burim. T\u00eb tilla rezultate \u00ebsht\u00eb m\u00eb mir\u00eb t'i hidhni posht\u00eb. Dhe pik\u00ebrisht me ndihm\u00ebn e sysbench nga Aleksei Kopytov, realizojm\u00eb disa kontrole t\u00eb shkurtra, t\u00eb cilat vijn\u00eb dhe mund t\u00eb krahasohen me t\u00eb tjer\u00ebt, pra do t\u00eb kuptoni si sillet CPU dhe si sillet IO. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c4ddf5283c3c79e3d4c2c061e7dd9e7d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb \u00ebsht\u00eb e komplikuar teknikisht p\u00ebrmes shembujve t\u00eb ndrysh\u00ebm t\u00eb kompanive?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/959690b464a01a4a3cb67ec497becff1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Supozoni se d\u00ebshirojm\u00eb t\u00eb rip\u00ebrs\u00ebrisim ngarkes\u00ebn reale me ndihm\u00ebn e skedar\u00ebve t\u00eb log. Nj\u00eb ide e shk\u00eblqyer n\u00ebse \u00ebsht\u00eb shkruar n\u00eb Open Source pgreplay. Ne e p\u00ebrdorim. Por, p\u00ebr t\u00eb punuar mir\u00eb, duhet t\u00eb aktivizoni regjistrimin e plot\u00eb t\u00eb k\u00ebrkesave me parametra dhe koh\u00eb.<\/p>\n<p><\/p>\n<p>Ka disa v\u00ebshtir\u00ebsi sa i p\u00ebrket koh\u00ebzgjatjes dhe timestamp-eve. Ne do ta lem\u00eb k\u00ebt\u00eb p\u00ebr momentin. Pyetja kryesore \u00ebsht\u00eb \u2013 a mund ta lejoni k\u00ebt\u00eb apo jo? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 disponueshme. Duhet t\u00eb kuptoni s\u00eb pari se \u00e7far\u00eb lloj fluksi do t\u00eb shkruhet n\u00eb log. N\u00ebse keni pg_stat_statements, mund t\u00eb p\u00ebrdorni k\u00ebt\u00eb pyetje (linku do t\u00eb jet\u00eb i disponuesh\u00ebm n\u00eb slidet) p\u00ebr t\u00eb kuptuar se sa shum\u00eb byte do t\u00eb shkruhen p\u00ebr sekond\u00eb.<\/p>\n<p><\/p>\n<p>Shikojm\u00eb n\u00eb gjat\u00ebsi t\u00eb pyetjes. N\u00eb k\u00ebt\u00eb rast injorojm\u00eb faktin se nuk ka parametra, por e dim\u00eb gjat\u00ebsi e pyetjes dhe e dim\u00eb sa her\u00eb p\u00ebr sekond\u00eb ajo \u00ebsht\u00eb ekzekutuar. K\u00ebshtu, mund t\u00eb b\u00ebjm\u00eb nj\u00eb vler\u00ebsim se sa byte do t\u00eb shkruhet p\u00ebr sekond\u00eb. Mund t\u00eb gabojm\u00eb dy her\u00eb, por rendi do ta kuptojm\u00eb me siguri n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb.<\/p>\n<p><\/p>\n<p>Mund t\u00eb shohim q\u00eb kjo pyetje ekzekutohet 802 her\u00eb n\u00eb sekond\u00eb. Dhe shohim se bytes_per sec \u2013 300 kB\/s do t\u00eb shkruhen plus minus. Dhe, zakonisht, ne mund ta lejojm\u00eb nj\u00eb fluks t\u00eb till\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/218f905073a8519115adf5c65825b74d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por! Problemi \u00ebsht\u00eb se ka sisteme t\u00eb ndryshme regjistrimi. Dhe, n\u00eb p\u00ebrgjith\u00ebsi, njer\u00ebzit zakonisht kan\u00eb \u00absyslog\u00bb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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, mund t\u00eb ndodheni me nj\u00eb pamje t\u00eb till\u00eb. Do t\u00eb marrim pgbench, do t\u00eb aktivizojm\u00eb regjistrimin e k\u00ebrkesave dhe do t\u00eb shikojm\u00eb se \u00e7far\u00eb ndodh.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a05723dcf10a7733f054ba108bb0f107.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pa regjistrim \u2013 ky \u00ebsht\u00eb kolona e majt\u00eb. Ne kishim 161,000 TPS. Me syslog \u2013 kjo n\u00eb Ubuntu 16.04 n\u00eb Amazon rezulton 37,000 TPS. Dhe n\u00ebse ne ndryshojm\u00eb n\u00eb dy metoda t\u00eb tjera regjistrimi, situata \u00ebsht\u00eb shum\u00eb m\u00eb e mir\u00eb. Pra, prisnim q\u00eb do t\u00eb bjer\u00eb, por jo kaq shum\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a7d90af9d29761d3b80eb09bf050760f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nd\u00ebrsa n\u00eb CentOS 7, ku merr pjes\u00eb edhe journald, regjistrimi i logeve n\u00eb formatin binar p\u00ebr k\u00ebrkime m\u00eb t\u00eb lehta, atje ndodh nj\u00eb situat\u00eb katastrofike, me 44 her\u00eb r\u00ebnie n\u00eb TPS.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 di\u00e7ka me t\u00eb cil\u00ebn p\u00ebrballen njer\u00ebzit. Dhe shpesh n\u00eb kompanit\u00eb, ve\u00e7an\u00ebrisht n\u00eb ato t\u00eb m\u00ebdha, \u00ebsht\u00eb shum\u00eb e v\u00ebshtir\u00eb t\u00eb ndryshohet di\u00e7ka. n\u00ebse mund t\u00eb largoheni nga syslog, at\u00ebher\u00eb shkoni.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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-in dhe fluksin e shkrimit. <\/li>\n<li>Kontrolloni sistemin tuaj t\u00eb regjistrimit. <\/li>\n<li>N\u00ebse ngarkesa e parashikuar \u00ebsht\u00eb tep\u00ebr e lart\u00eb, konsideroni mund\u00ebsin\u00eb e kampionimit. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/127fa1296578ba0ba6eac2b44c5c5398.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ne kemi pg_stat_statements. Si\u00e7 thash\u00eb, ai duhet t\u00eb jet\u00eb patjet\u00ebr. Dhe mund t\u00eb marrim dhe t\u00eb p\u00ebrshkruajm\u00eb \u00e7do grup k\u00ebrkesash n\u00eb nj\u00eb skedar t\u00eb ve\u00e7ant\u00eb. Pastaj mund t\u00eb p\u00ebrdorim nj\u00eb ve\u00e7ori shum\u00eb t\u00eb rehatshme n\u00eb pgbench \u2013 mund\u00ebsin\u00eb p\u00ebr t\u00eb futur disa skedare p\u00ebrmes opsionit \u00ab-f\u00bb.<\/p>\n<p><\/p>\n<p>Ai kupton shum\u00eb \u00ab-f\u00bb. Dhe mund t\u00eb themi me ndihm\u00ebn e \u00ab@\u00bb n\u00eb fund, se cila \u00ebsht\u00eb p\u00ebrqindja p\u00ebr secilin skedar. Pra, mund t\u00eb themi se ky do t\u00eb ekzekutohet n\u00eb 10% t\u00eb rasteve, nd\u00ebrsa ky n\u00eb 20%. Dhe kjo do t\u00eb na afroj\u00eb m\u00eb af\u00ebr asaj q\u00eb shohim n\u00eb prodhim.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b91d18a9861abdc36bfda54cd72a142f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Si do t\u00eb kuptojm\u00eb se \u00e7far\u00eb ka n\u00eb prodhim? Cila \u00ebsht\u00eb p\u00ebrqindja dhe \u00e7far\u00eb? K\u00ebtu pak dalim nga tema. Kemi edhe nj\u00eb produkt tjet\u00ebr <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/postgres-checkup\">postgres-checkup<\/a><\/noindex>. Gjithashtu nj\u00eb baz\u00eb n\u00eb Open Source. Dhe ne tani po e zhvillojm\u00eb me p\u00ebrkushtim.<\/p>\n<p><\/p>\n<p>Ai lindi p\u00ebr disa arsye t\u00eb tjera. P\u00ebr shkak t\u00eb faktit q\u00eb monitorimi \u00ebsht\u00eb i pamjaftuesh\u00ebm. Pra, ju erdhni, shikoni baz\u00ebn, shikoni problemet q\u00eb ka. Dhe, n\u00eb p\u00ebrgjith\u00ebsi, ju b\u00ebni nj\u00eb kontrolle sh\u00ebndeti. N\u00ebse jeni nj\u00eb DBA me p\u00ebrvoj\u00eb, ju b\u00ebni nj\u00eb kontroll sh\u00ebndeti. Shikoni p\u00ebrdorimin e indekseve, etj. N\u00ebse keni OKmeter, at\u00ebher\u00eb shk\u00eblqyer. Ky \u00ebsht\u00eb nj\u00eb monitorim fantastik p\u00ebr Postgres. OKmeter.io \u2013 ju lutemi, installoni at\u00eb, 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, zakonisht nuk keni shum\u00eb. N\u00eb monitorim zakonisht kemi CPU, IO dhe k\u00ebt\u00eb me kusht, dhe asgj\u00eb m\u00eb shum\u00eb. Por na nevojitet m\u00eb shum\u00eb. Na nevojitet t\u00eb shohim si funksionon avtokorrigjimi, si funksionon pik\u00ebnisja, n\u00eb io duhet t\u00eb ndahen pik\u00ebnisja nga bgwriter dhe nga backend-et etj.<\/p>\n<p><\/p>\n<p>Problemi \u00ebsht\u00eb se kur ndihmon nj\u00eb kompani t\u00eb madhe, ata nuk mund t\u00eb implementojn\u00eb di\u00e7ka shpejt. Nuk mund t\u00eb blejn\u00eb shpejt OKmeter-in. Mbase do ta blejn\u00eb pas gjasht\u00eb muajsh. Nuk mund t\u00eb instalohet ndonj\u00eb paket\u00eb shpejt. <\/p>\n<p><\/p>\n<p>Na kemi erdhi ideja se na nevojitet nj\u00eb mjet i ve\u00e7ant\u00eb, i cili nuk k\u00ebrkon ndonj\u00eb instalim, pra nuk keni nevoj\u00eb t\u00eb instaloni asgj\u00eb n\u00eb prodhim. E vendosni n\u00eb laptopin tuaj, ose n\u00eb serverin e v\u00ebzhgimit, nga ku do ta lanconi. Dhe ai do t\u00eb analizoj\u00eb shum\u00eb gj\u00ebra: sistemin operativ, sistemin e skedar\u00ebve, dhe vet\u00eb Postgres, duke b\u00ebr\u00eb disa k\u00ebrkesa t\u00eb lehta, t\u00eb cilat mund t'i d\u00ebrgoni direkt n\u00eb prodhim dhe asgj\u00eb nuk do t\u00eb d\u00ebshtoj\u00eb.<\/p>\n<p><\/p>\n<p>Ne e quajtam at\u00eb Postgres-checkup. N\u00ebse flasim n\u00eb terma mjek\u00ebsor\u00eb, kjo \u00ebsht\u00eb nj\u00eb kontroll i rregullt i sh\u00ebndetit. N\u00ebse e shohim nga perspektiva automobilistike, kjo \u00ebsht\u00eb si nj\u00eb kontroll teknik. Ju b\u00ebni kontrollin teknik p\u00ebr makin\u00ebn tuaj \u00e7do gjasht\u00eb muaj apo nj\u00eb her\u00eb n\u00eb vit, n\u00eb var\u00ebsi t\u00eb mark\u00ebs. Po, a b\u00ebni kontroll teknik p\u00ebr baz\u00ebn tuaj? Kjo \u00ebsht\u00eb, a b\u00ebni k\u00ebrkime t\u00eb thella rregullisht? Kjo \u00ebsht\u00eb e nevojshme. N\u00ebse b\u00ebni backup, at\u00ebher\u00eb b\u00ebni edhe nj\u00eb kontroll, kjo \u00ebsht\u00eb po aq e r\u00ebnd\u00ebsishme.<\/p>\n<p><\/p>\n<p>Dhe ne kemi nj\u00eb mjet t\u00eb till\u00eb. Ai ka filluar t\u00eb zhvillohet aktivisht vet\u00ebm p\u00ebr tre muaj. Ai \u00ebsht\u00eb ende i ri, por ka shum\u00eb funksionalitete.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/514d00b5cb710a7997af0a5dde55b5a8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Mblidhni grupet m\u00eb \"t\u00eb influencueshme\" t\u00eb k\u00ebrkesave - raporti K003 n\u00eb Postgres-checkup.<\/p>\n<p><\/p>\n<p>Dhe aty ka nj\u00eb grup raportesh K. Nj\u00eb grup raportesh t\u00eb till\u00eb. Tani ka tre raporte. Dhe ka nj\u00eb raport t\u00eb till\u00eb K003. Aty \u00ebsht\u00eb maja nga pg_stat_statements, e renditur sipas total_time.<\/p>\n<p><\/p>\n<p>Kur ne rendisim sipas total_time grupet e k\u00ebrkesave, ne shohim nj\u00eb grup n\u00eb krye, i cili ngarkon sistemin ton\u00eb m\u00eb s\u00eb shumti, pra konsumon m\u00eb shum\u00eb burime. Pse e quaj grupet e k\u00ebrkesave? Sepse ne e kemi hequr parametrin. K\u00ebto nuk jan\u00eb m\u00eb k\u00ebrkesa, por grupe k\u00ebrkesash, pra ato jan\u00eb t\u00eb abastraktuara.<\/p>\n<p><\/p>\n<p>Dhe n\u00ebse ne do t\u00eb optimizojm\u00eb nga lart posht\u00eb, do t'i leht\u00ebsojm\u00eb burimet tona dhe do ta shtyjm\u00eb momentin kur na duhen p\u00ebrmir\u00ebsime. Ky \u00ebsht\u00eb nj\u00eb m\u00ebnyr\u00eb e shk\u00eblqyer p\u00ebr t\u00eb kursyer para.<\/p>\n<p><\/p>\n<p>Mund t\u00eb mos jet\u00eb m\u00ebnyra m\u00eb e mir\u00eb p\u00ebr t\u00eb kujdesur p\u00ebr p\u00ebrdoruesit, sepse ndoshta ne nuk i shohim rastet e 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 ne nuk i shohim, por ne jemi duke u marr\u00eb me burimet.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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? B\u00ebm\u00eb dy snapshot-e. Postgres_checkup do t'ju b\u00ebj\u00eb diferenc\u00ebn p\u00ebr \u00e7do metrik\u00eb: p\u00ebr total-time, calls, rows, shared_blks_read, etj. E gjitha, diferenca \u00ebsht\u00eb llogaritur. Nj\u00eb problem i madh me pg_stat_statements \u00ebsht\u00eb se ai nuk mban mend kur u resetua. N\u00ebse pg_stat_database mban mend, pg_stat_statements nuk mban mend. Ju shihni atje numrin 1,000,000, por nuk e dim\u00eb se nga e kemi llogaritur.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c370bf1237675e03c0a3d362c025ac24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por k\u00ebtu ne e dim\u00eb, k\u00ebtu kemi dy snapshot-e. Ne e dim\u00eb q\u00eb diferenca ka qen\u00eb n\u00eb k\u00ebt\u00eb rast 56 sekonda. Nj\u00eb interval shum\u00eb i vog\u00ebl. E rendit\u00ebm sipas total_time. Dhe m\u00eb pas mund t\u00eb diferencojm\u00eb, pra ne i ndajm\u00eb t\u00eb gjitha metrikat sipas koh\u00ebs s\u00eb gjat\u00eb. N\u00ebse ne e ndajm\u00eb secil\u00ebn metrik\u00eb me koh\u00ebn e gjat\u00eb, 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, pra sa sekonda iu desh sistemit ton\u00eb p\u00ebr t\u00eb ekzekutuar k\u00ebt\u00eb grup k\u00ebrkesash n\u00eb sekond\u00eb. N\u00ebse ju shihni atje m\u00eb shum\u00eb se nj\u00eb sekond\u00eb n\u00eb sekond\u00eb, kjo do t\u00eb thot\u00eb se ju kishit nevoj\u00eb p\u00ebr m\u00eb shum\u00eb se nj\u00eb b\u00ebrtham\u00eb. Kjo \u00ebsht\u00eb nj\u00eb metrik\u00eb shum\u00eb e mir\u00eb. Ju mund t\u00eb kuptoni se ky person, p\u00ebr shembull, ka nevoj\u00eb p\u00ebr t\u00eb pakt\u00ebn tre b\u00ebrthama.<\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb noviteti yn\u00eb, nuk kam par\u00eb asnj\u00ebher\u00eb di\u00e7ka t\u00eb till\u00eb. V\u00ebreni \u2013 kjo \u00ebsht\u00eb di\u00e7ka shum\u00eb e thjesht\u00eb \u2013 sekonda n\u00eb sekond\u00eb. N sometimes, kur CPU \u00ebsht\u00eb 100 %, ju keni gjysm\u00eb ore n\u00eb sekond\u00eb, pra ju keni kaluar gjysm\u00eb ore vet\u00ebm me k\u00ebto k\u00ebrkesa. <\/p>\n<p><\/p>\n<p>M\u00eb pas shohim rreshta n\u00eb sekond\u00eb. Ne e dim\u00eb se sa rreshta jan\u00eb kthyer n\u00eb sekond\u00eb.<\/p>\n<p><\/p>\n<p>Dhe m\u00eb pas \u00ebsht\u00eb gjithashtu di\u00e7ka interesante. Sa shared_buffers kemi lexuar n\u00eb sekond\u00eb nga shared_buffers. Goditjet tashm\u00eb ishin atje, nd\u00ebrsa rreshtat i mor\u00ebm nga memorieja e sistemit operativ, ose nga disku. Opcioni i par\u00eb \u00ebsht\u00eb i shpejt\u00eb, nd\u00ebrsa i dyti, ndoshta \u00ebsht\u00eb i shpejt\u00eb, ndoshta jo, varet nga situata. <\/p>\n<p><\/p>\n<p>Dhe m\u00ebnyra e dyt\u00eb e diferencimit \u2013 ne ndajm\u00eb numrin e k\u00ebrkesave n\u00eb k\u00ebt\u00eb grup. N\u00eb kolon\u00ebn e dyt\u00eb gjithmon\u00eb do t\u00eb keni nj\u00eb k\u00ebrkes\u00eb p\u00ebr t\u00eb ndar\u00eb me k\u00ebrkes\u00ebn. Dhe m\u00eb pas \u00ebsht\u00eb interesante \u2013 sa milisekonda ishte n\u00eb k\u00ebt\u00eb k\u00ebrkes\u00eb. Ne e dim\u00eb se si sillet mesatarisht kjo k\u00ebrkes\u00eb. 101 milisekonda nevojiteshin p\u00ebr \u00e7do k\u00ebrkes\u00eb. Kjo \u00ebsht\u00eb nj\u00eb metrik\u00eb tradicionale q\u00eb na nevojitet p\u00ebr kuptimin.<\/p>\n<p><\/p>\n<p>Sa rreshta ktheu \u00e7do k\u00ebrkes\u00eb n\u00eb mesatare. Ne shohim se grupi kthen 8. Sa mesatarisht mori dhe lexoi nga cache. Ne shohim se gjith\u00e7ka \u00ebsht\u00eb e ruajtur n\u00eb memory. Goditje t\u00eb p\u00ebrgjithshme p\u00ebr grupin e par\u00eb. <\/p>\n<p><\/p>\n<p>Dhe substrati i kat\u00ebrt n\u00eb \u00e7do rresht \u2013 \u00ebsht\u00eb p\u00ebrqindja e p\u00ebrgjithshme. Ne kemi thirrje. Le ta themi, n\u00eb 1,000,000. Dhe ne mund t\u00eb kuptojm\u00eb se \u00e7far\u00eb kontributi sjell ky grup. Ne shohim se n\u00eb k\u00ebt\u00eb rast grupi i par\u00eb kontribuon m\u00eb pak se 0,01%. Kjo \u00ebsht\u00eb, ajo \u00ebsht\u00eb kaq e ngadalshme sa q\u00eb ne nuk e shohim n\u00eb pamjen e p\u00ebrgjithshme. Nd\u00ebrsa grupi i dyt\u00eb \u2013 5% nga thirrjet. Kjo \u00ebsht\u00eb, 5% e t\u00eb gjitha thirrjeve jan\u00eb nga grupi i dyt\u00eb. <\/p>\n<p><\/p>\n<p>\u00cbsht\u00eb gjithashtu interesante p\u00ebr total_time. P\u00ebr grupin e par\u00eb t\u00eb k\u00ebrkesave, harxhuam 14% t\u00eb gjith\u00eb koh\u00ebs s\u00eb pun\u00ebs. Nd\u00ebrsa p\u00ebr t\u00eb dytin \u2013 11% etj. <\/p>\n<p><\/p>\n<p>Nuk do t\u00eb hyj n\u00eb detaje, por aty ka nuanca. Ne nxjerrim nj\u00eb gabim n\u00eb sip\u00ebrfaqe, sepse kur krahasojm\u00eb, snapshotet mund t\u00eb l\u00ebkunden, dmth disa k\u00ebrkesa mund t\u00eb humbasin dhe n\u00eb t\u00eb dytin nuk mund t\u00eb jen\u00eb m\u00eb t\u00eb pranishme, nd\u00ebrsa disa mund t\u00eb shfaqen t\u00eb reja. Dhe aty ne llogarisim gabimin. N\u00ebse shihni 0, at\u00ebher\u00eb \u00ebsht\u00eb mir\u00eb. Nuk ka gabime. N\u00ebse treguesi i gabimeve \u00ebsht\u00eb deri n\u00eb 20%, \u00ebsht\u00eb OK. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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. Ne duhet t\u00eb konceptojm\u00eb workload. Shkojm\u00eb nga lart posht\u00eb derisa t\u00eb mbledhim 80% ose 90%. Zakonisht kjo \u00ebsht\u00eb 10-20 grupe. Dhe b\u00ebm\u00eb skedar\u00ebt p\u00ebr pgbench. Atje p\u00ebrdorim random. Ndonj\u00ebher\u00eb, p\u00ebr fat t\u00eb keq, kjo nuk funksionon. Dhe n\u00eb Postgres 12 do t\u00eb ket\u00eb m\u00eb shum\u00eb mund\u00ebsi p\u00ebr t\u00eb p\u00ebrdorur k\u00ebt\u00eb qasje. <\/p>\n<p><\/p>\n<p>M\u00eb pas k\u00ebshtu ne mbledhim 80-90% n\u00eb total_time. \u00c7far\u00eb t\u00eb vendosim m\u00eb pas pas \u00ab@\u00bb? Ne shohim thirrjet, shohim sa p\u00ebrqindje dhe kuptojm\u00eb se k\u00ebtu duhet t\u00eb kemi kaq p\u00ebrqindje. Nga k\u00ebto p\u00ebrqindje ne mund t\u00eb kuptojm\u00eb se si t\u00eb balancojm\u00eb secilin nga skedar\u00ebt. Pas k\u00ebsaj ne p\u00ebrdorim pgbench dhe fillojm\u00eb t\u00eb punojm\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/53edf787f49dcabb5e4731510839332d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ka 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 gjith\u00eb ngarkes\u00ebs son\u00eb. Shihni kolon\u00ebn e dyt\u00eb dhe n\u00ebnvargun e dyt\u00eb. Ne shohim se rreth nj\u00eb sekond n\u00eb sekond\u00eb, dmth n\u00ebse do t\u00eb ket\u00eb dy b\u00ebrthama, do t\u00eb jet\u00eb mir\u00eb. Do t\u00eb ket\u00eb rreth 75% ngarkes\u00eb. Dhe k\u00ebshtu do t\u00eb funksionoj\u00eb. N\u00ebse kemi 10 b\u00ebrthama, do t\u00eb jemi plot\u00ebsisht t\u00eb qet\u00eb. K\u00ebshtu mund t\u00eb vler\u00ebsojm\u00eb burimet.<\/p>\n<p><\/p>\n<p>K002 \u2013 k\u00ebto i quaj klasat e k\u00ebrkesave, dmth SELECT, INSERT, UPDATE, DELETE. Dhe ve\u00e7mas SELECT FOR UPDATE, sepse ai bllokon. <\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu mund t\u00eb p\u00ebrfundojm\u00eb se SELECT t\u00eb zakonshmet lexuese \u2013 82% e t\u00eb gjitha thirrjeve, por n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb \u2013 74% n\u00eb total_time. Pra, ato thirren shpesh, por konsumojn\u00eb m\u00eb pak burime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. Nikolai Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0e0234e696ffe1c80f8b5acccc602dbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe kthehemi n\u00eb pyetjen: \u00abSi t\u00eb p\u00ebrshtatim sakt\u00ebsisht shared_buffers?\u00bb. V\u00ebrej q\u00eb shumica e benchmarking-ve jan\u00eb nd\u00ebrtuar mbi iden\u00eb \u2013 le t\u00eb shohim se cili do t\u00eb jet\u00eb throughput, dmth cili do t\u00eb jet\u00eb kapaciteti. Ajo zakonisht matet n\u00eb TPS ose QPS.<\/p>\n<p><\/p>\n<p>Dhe ne mundohemi t\u00eb nxjerrim sa m\u00eb shum\u00eb transaksione n\u00eb sekond\u00eb nga makina me parametrat e tuningut. K\u00ebtu sakt\u00ebsisht 311 n\u00eb sekond\u00eb p\u00ebr select.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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\u00ebr t\u00eb rikthyer sht\u00ebpin\u00eb me makin\u00eb me shpejt\u00ebsi maksimale. Kjo \u00ebsht\u00eb e budallall\u00ebk. Ashtu \u00ebsht\u00eb dhe me bazat e t\u00eb dh\u00ebnave. Ne nuk duhet t\u00eb ecim me shpejt\u00ebsi maksimale, askush nuk e b\u00ebn k\u00ebt\u00eb. Askush nuk jeton n\u00eb production q\u00eb ka 100% CPU. Megjithat\u00eb, ndoshta dikush jeton, por kjo nuk \u00ebsht\u00eb e mir\u00eb. <\/p>\n<p><\/p>\n<p>Ideja \u00ebsht\u00eb q\u00eb zakonisht ne ecim n\u00eb 20% t\u00eb mund\u00ebsive, preferohet t\u00eb mos kalojm\u00eb 50%. Dhe ne mundohemi t\u00eb optimizojm\u00eb koh\u00ebn e p\u00ebrgjigjes p\u00ebr p\u00ebrdoruesit tan\u00eb, s\u00eb pari. Pra, duhet t\u00eb manovrojm\u00eb dor\u00ebt tona p\u00ebr t\u00eb pasur latency minimale n\u00eb 20% shpejt\u00ebsi, n\u00eb parim. Kjo \u00ebsht\u00eb nj\u00eb ide q\u00eb ne gjithashtu mundohemi ta p\u00ebrdorim n\u00eb eksperimentet tona.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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, rekomandimet: <\/p>\n<p><\/p>\n<ul>\n<li>Sigurisht, b\u00ebni Database Lab.<\/li>\n<li>Sa m\u00eb shum\u00eb q\u00eb t\u00eb jet\u00eb e mundur, b\u00ebni on demand, q\u00eb t\u00eb krijohet p\u00ebr nj\u00eb periudh\u00eb \u2013 luani dhe pastaj hidhni. N\u00ebse keni n\u00eb oblake, kjo \u00ebsht\u00eb e natyrshme, dmth, keni shum\u00eb standing. <\/li>\n<li>Jini kureshtar\u00eb. Dhe n\u00ebse di\u00e7ka nuk shkon, kontrolloni me eksperimente se si sillet. Nancy mund t\u00eb jet\u00eb p\u00ebrdorur p\u00ebr t\u00eb m\u00ebsuar veten, p\u00ebr t\u00eb verifikuar si funksionon baza.<\/li>\n<li>Dhe q\u00ebndroni t\u00eb fokusuar n\u00eb koh\u00ebn minimale t\u00eb p\u00ebrgjigjes. <\/li>\n<li>Dhe mos kini frik\u00eb nga burimet e Postgres. Kur punoni me burimet, duhet t\u00eb dini anglisht. Atje ka shum\u00eb komente, gjith\u00e7ka \u00ebsht\u00eb shpjeguar. <\/li>\n<li>Dhe kontrolloni sh\u00ebndetin e baz\u00ebs rregullisht, t\u00eb pakt\u00ebn nj\u00eb her\u00eb n\u00eb tre muaj me duar, ose Postgres-checkup. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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! 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. Por un\u00eb nuk e kuptova plot\u00ebsisht. Kur punojm\u00eb me Nancy, a mund t\u00eb rregullojm\u00eb vet\u00ebm nj\u00eb parameter apo nj\u00eb grup t\u00eb t\u00ebr\u00eb?<\/em><\/p>\n<p><\/p>\n<p>Ne kemi parameterin e d\u00eblta-konfigurimit. Mund t\u00eb sjell\u00ebsh sa m\u00eb shum\u00eb t\u00eb duash nj\u00ebher\u00ebsh. Por duhet t\u00eb kuptosh, kur ndryshon shum\u00eb gj\u00ebra, mund t\u00eb jesh n\u00eb nj\u00eb p\u00ebrfundim t\u00eb gabuar. <\/p>\n<p><\/p>\n<p><em>Po. Pse e pyeta? Sepse \u00ebsht\u00eb e v\u00ebshtir\u00eb t\u00eb kryesh eksperimente kur ke vet\u00ebm nj\u00eb parameter. E rregullon at\u00eb, e ke par\u00eb si punon. E vendos at\u00eb. Pastaj fillon me tjetrin.<\/em><\/p>\n<p><\/p>\n<p>Mund t\u00eb rregullohen nj\u00ebher\u00ebsh, por varet nga situata, natyrisht. Por m\u00eb mir\u00eb \u00ebsht\u00eb t\u00eb kontrollosh nj\u00eb ide. Dje na erdhi nj\u00eb ide. Kemi patur 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. Dhe erdhi ideja q\u00eb t\u00eb p\u00ebrdorim dykah\u00ebsin\u00eb, p\u00ebr t\u00eb kuptuar n\u00eb radh\u00eb dhe p\u00ebr t\u00eb gjetur se \u00e7far\u00eb \u00ebsht\u00eb ndryshe. Mund t\u00eb b\u00ebjm\u00eb menj\u00ebher\u00eb gjysm\u00ebn e parametrave t\u00eb nj\u00ebjt\u00eb, pastaj nj\u00eb t\u00eb kat\u00ebrtin, etj. T\u00eb gjitha fleksibel.<\/p>\n<p><\/p>\n<p><em>Dhe ka nj\u00eb pyetje tjet\u00ebr. Projekti \u00ebsht\u00eb i ri, n\u00eb zhvillim. Dokumentacioni \u00ebsht\u00eb tashm\u00eb i gatsh\u00ebm, ka nj\u00eb p\u00ebrshkrim t\u00eb detajuar?<\/em><\/p>\n<p><\/p>\n<p>E kam b\u00ebr\u00eb lidhjen n\u00eb p\u00ebrshkrimin e parametrave. Kjo \u00ebsht\u00eb aty. Por ka shum\u00eb gj\u00ebra q\u00eb akoma mungojn\u00eb. Po k\u00ebrkoj njer\u00ebz t\u00eb ngjash\u00ebm. I gjej kur kam paraqitje. Kjo \u00ebsht\u00eb shum\u00eb e mrekullueshme. Disa tashm\u00eb po punojn\u00eb me mua, disa ndihmuan dhe b\u00ebn\u00eb di\u00e7ka. Dhe n\u00ebse jeni t\u00eb interesuar p\u00ebr k\u00ebt\u00eb tem\u00eb, lutem jepni komentet tuaja \u2013 \u00e7far\u00eb mungon. <\/p>\n<p><\/p>\n<p><em>Kur t\u00eb realizojm\u00eb laboratorin, ndoshta do t\u00eb kemi p\u00ebrgjigje. Do ta shohim. Faleminderit!<\/em><\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr prezantimin! Vura re q\u00eb ka mb\u00ebshtetje p\u00ebr Amazon. A \u00ebsht\u00eb planifikuar mb\u00ebshtetje p\u00ebr GSP?<\/em><\/p>\n<p><\/p>\n<p>Nj\u00eb pyetje e mir\u00eb. Kemi filluar pun\u00ebn. Dhe p\u00ebr momentin e kemi pezulluar, sepse duam t\u00eb kursejm\u00eb. Pra, ka mb\u00ebshtetje p\u00ebrmes run on localhost. Ju mund t\u00eb krijoni vet\u00eb nj\u00eb instance dhe t\u00eb punoni lokal. P\u00ebr m\u00eb tep\u00ebr, ne ashtu e b\u00ebjm\u00eb. N\u00eb Getlab e b\u00ebj k\u00ebshtu, atje n\u00eb GSP. Por p\u00ebr t\u00eb realizuar nj\u00eb orkestrim t\u00eb till\u00eb nuk shohim ende sens, sepse Google nuk ka oferta t\u00eb lira. Aty ka ??? instances, por ato kan\u00eb kufizime. S\u00eb pari, ata gjithmon\u00eb kan\u00eb vet\u00ebm 70% zbritje dhe nuk mund t\u00eb luani me \u00e7mimin. Ne rrisim \u00e7mimin e spoteve me 5-10% p\u00ebr t\u00eb ulur probabilitetin q\u00eb t'ju nxjerrin jasht\u00eb. Dometh\u00ebn\u00eb, me spote ju kurseni, por mund t'ju marrin n\u00eb \u00e7do moment. N\u00ebse vendosni nj\u00eb \u00e7mim pak m\u00eb t\u00eb lart\u00eb se t\u00eb tjer\u00ebt, mund t'ju vrasin m\u00eb von\u00eb. Google ka nj\u00eb specifik tjet\u00ebr krejt\u00ebsisht. Dhe ka nj\u00eb kufizim q\u00eb nuk \u00ebsht\u00eb i mir\u00eb \u2013 ata jetojn\u00eb vet\u00ebm 24 or\u00eb. E ndonj\u00ebher\u00eb ne duam t\u00eb b\u00ebjm\u00eb eksperimente p\u00ebr 5 dit\u00eb. Por me spote kjo mund t\u00eb b\u00ebhet, spote ndonj\u00ebher\u00eb jetojn\u00eb p\u00ebr muaj. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr prezantimin! P\u00ebrmend\u00ebt p\u00ebr checkup. Si llogaritni gabimet stat_statements?<\/em><\/p>\n<p><\/p>\n<p>Pyetje shum\u00eb e mir\u00eb. Mund t\u00eb tregoj dhe shpjegoj shum\u00eb detajisht. N\u00eb shkurt \u2013 ne shikojm\u00eb si evoluon grupi i k\u00ebrkesave: sa u shk\u00ebput dhe sa u shfaqen t\u00eb reja. Pastaj shikojm\u00eb dy metri: total_time dhe calls, prandaj ka dy gabime. Shikojm\u00eb gjithashtu, cila \u00ebsht\u00eb kontributi i grupeve t\u00eb shk\u00ebputura. Ka dy n\u00ebngrupe: t\u00eb larguara dhe t\u00eb ardhura. Shikojm\u00eb, cila \u00ebsht\u00eb kontributi i tyre n\u00eb p\u00ebrgjith\u00ebsin\u00eb. <\/p>\n<p><\/p>\n<p><em>A nuk keni frik\u00eb se ajo mund t\u00eb rrotullohet dy-tre her\u00eb gjat\u00eb koh\u00ebs midis snapshot-ve?<\/em><\/p>\n<p><\/p>\n<p>Dometh\u00ebn\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 shtypur nj\u00eb her\u00eb, pastaj erdhi p\u00ebrs\u00ebri dhe u shtyp, pastaj p\u00ebrs\u00ebri erdhi dhe u shtyp. Dhe ju keni b\u00ebr\u00eb disa llogaritje, dhe ku jan\u00eb t\u00eb gjith\u00eb ata?<\/em><\/p>\n<p><\/p>\n<p>Pyetje e mir\u00eb, duhet t\u00eb shohim. <\/p>\n<p><\/p>\n<p><em>Un\u00eb kam b\u00ebr\u00eb nj\u00eb gj\u00eb t\u00eb ngjashme. Sigurisht m\u00eb e thjesht\u00eb, e kam b\u00ebr\u00eb vet\u00ebm. Por m\u00eb duhej t\u00eb shlyej, t\u00eb b\u00ebj nj\u00eb reset stat_statements dhe t\u00eb orientohem n\u00eb momentin e snapshot, q\u00eb atje t\u00eb ket\u00eb nj\u00eb pjes\u00eb p\u00ebrkat\u00ebse, q\u00eb p\u00ebrs\u00ebri nuk \u00ebsht\u00eb arritur n\u00eb kufirin e maksimalit sa stat_statements mund t\u00eb grumbullohen. Dhe un\u00eb orientohem, q\u00eb me gjas\u00eb nuk \u00ebsht\u00eb zhdukur asgj\u00eb.<\/em> <\/p>\n<p><\/p>\n<p>Po-po. <\/p>\n<p><\/p>\n<p><em>Por si mund ta b\u00ebsh ndryshe n\u00eb m\u00ebnyr\u00eb t\u00eb besueshme, nuk e kuptoj.<\/em><\/p>\n<p><\/p>\n<p>M\u00eb vjen keq, nuk e mbaj mend sakt\u00ebsisht \u2013 a p\u00ebrdorim tekstin e k\u00ebrkes\u00ebs apo queryid me pg_stat_statements dhe n\u00eb at\u00eb orientohemi. N\u00ebse orientohemi n\u00eb queryid, at\u00ebher\u00eb teorikisht po krahasonim gj\u00ebra t\u00eb krahasueshme. <\/p>\n<p><\/p>\n<p><em>Jo, ai mund t\u00eb shtypet disa her\u00eb midis snapshot-ve dhe t\u00eb vij\u00eb p\u00ebrs\u00ebri.<\/em><\/p>\n<p><\/p>\n<p>Me k\u00ebt\u00eb id?<\/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 ta studiojm\u00eb. Por p\u00ebr momentin, ajo q\u00eb shohim \u00ebsht\u00eb ose shkruhet 0...<\/p>\n<p><\/p>\n<p><em>Sigurisht, ky \u00ebsht\u00eb nj\u00eb rast i rrall\u00eb, por jam tronditur kur m\u00ebsova se stat_statements mund t\u00eb shtypet.<\/em> <\/p>\n<p><\/p>\n<p>N\u00eb Pg_stat_statements mund t\u00eb ket\u00eb shum\u00eb gj\u00ebra. Ndeshim se n\u00ebse keni track_utility = e aktivizuar, at\u00ebher\u00eb kurset gjithashtu regjistrohen. <\/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 fillon t\u00eb bllokohet tabela e hasheve. Dhe sapo t\u00eb fikni nj\u00eb aplikacion shum\u00eb t\u00eb ngarkuar, ju mbeteni me 50-100 grupe. Dhe aty gjith\u00e7ka b\u00ebhet 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 rrisni pg_stat_statements.max. <\/p>\n<p><\/p>\n<p><em>Po, por duhet t\u00eb dini, sa. Dhe duhet t\u00eb mbani ndjekje. Un\u00eb e b\u00ebj k\u00ebshtu. Dometh\u00ebn\u00eb, un\u00eb kam pg_stat_statements.max. Dhe shikoj q\u00eb n\u00eb momentin e snapshot nuk kam arritur m\u00eb shum\u00eb se 70%. Mir\u00eb, dometh\u00ebn\u00eb nuk kemi humbur asgj\u00eb. B\u00ebjm\u00eb reset. Dhe grumbullojm\u00eb p\u00ebrs\u00ebri. N\u00ebse n\u00eb snapshot-in tjet\u00ebr \u00ebsht\u00eb n\u00ebn 70, at\u00ebher\u00eb me gjas\u00eb p\u00ebrs\u00ebri nuk kemi humbur asgj\u00eb.<\/em><\/p>\n<p><\/p>\n<p>Po. N\u00eb m\u00ebnyr\u00eb t\u00eb paracaktuar tani jan\u00eb 5,000. Dhe shum\u00eb njer\u00ebz mjaftojn\u00eb me 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 vetja do t\u00eb shtoja se n\u00ebse n\u00eb Postgres ka t\u00eb dh\u00ebna konfidenciale dhe nuk duhet t\u00eb hyjn\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>. Schemi \u00ebsht\u00eb p\u00ebraf\u00ebrsisht si vijon:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave&quot;. 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 4.9.10 - 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? \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\" \/>\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) 4.9.10\" \/>\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? \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\" \/>\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 Industrial p\u00ebr Rregullimin e PostgreSQL: Eksperimentet mbi Bazat e Dh\u00ebnave\u00bb. Nikolai Samokhvalov | ProHoster","description":"Propozoj t\u00eb shikoni transkriptin e prezantimit t\u00eb Nikolai Samokhvalov \"Qasja industriale ndaj optimizimit t\u00eb PostgreSQL: eksperimente mbi bazat e t\u00eb dh\u00ebnave\" Shared_buffers = 25% \u2013 a \u00ebsht\u00eb shum\u00eb apo pak? Apo \u00ebsht\u00eb p\u00ebrmbyllja ideale? Si ta kuptoni n\u00ebse kjo rekomandim \u2013 mjaft e vjet\u00ebr \u2013 i p\u00ebrshtatet rastit tuaj specifik? Koha p\u00ebr t'u afruar me \u00e7\u00ebshtjen e p\u00ebrshtatjes s\u00eb parametrave postgresql.conf \"si nj\u00eb profesional\". Jo me m\u00ebnyr\u00ebn e verb\u00ebr.","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? \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","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"},"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}]}}