{"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\/sk\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","title":{"rendered":"Priemyseln\u00fd pr\u00edstup k ladeniu PostgreSQL: experimenty s datab\u00e1zami\u00ae. Nikolaj Samokhvalov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Odpor\u00fa\u010dam v\u00e1m obozn\u00e1mi\u0165 sa s preh\u013eadom predn\u00e1\u0161ky Nikolaja Samochvalova \"Priemyseln\u00fd pr\u00edstup k ladenie PostgreSQL: experimenty s datab\u00e1zami\"<\/strong><\/p>\n<p><\/p>\n<p>Shared_buffers = 25 % \u2013 je to ve\u013ea alebo m\u00e1lo? Alebo akur\u00e1t? Ako zisti\u0165, \u010di je toto \u2013 pomerne zastaran\u00e9 \u2013 odpor\u00fa\u010danie vhodn\u00e9 vo va\u0161om konkr\u00e9tnom pr\u00edpade?<\/p>\n<p><\/p>\n<p>Pri\u0161iel \u010das prist\u00fapi\u0165 k ot\u00e1zke vo\u013eby parametrov postgresql.conf \"dospel\u00fdm\" sp\u00f4sobom. Nie pomocou slep\u00fdch \"autotunerov\" alebo zastaran\u00fdch rad, ale na z\u00e1klade:<\/p>\n<p><\/p>\n<ol>\n<li>pr\u00edsne kontrolovan\u00fdch experimentov na DB, vykon\u00e1van\u00fdch automatizovane, vo ve\u013ekom mno\u017estve a v podmienkach \u010do najbli\u017e\u0161\u00edch \"bojov\u00fdm\",<\/li>\n<li>hlbok\u00e9ho porozumenia zvl\u00e1\u0161tnostiam fungovania DBMS a OS.<\/li>\n<\/ol>\n<p><\/p>\n<p>Pou\u017e\u00edvaj\u00fac Nancy CLI (<noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres.ai\/nancy\">https:\/\/gitlab.com\/postgres.ai\/nancy<\/a><\/noindex>), presk\u00famame konkr\u00e9tny pr\u00edklad \u2013 notoricky zn\u00e1me shared_buffers \u2013 v r\u00f4znych situ\u00e1ci\u00e1ch, v r\u00f4znych projektoch a pok\u00fasime sa zisti\u0165, ako zvoli\u0165 optim\u00e1lne nastavenie pre na\u0161u infra\u0161trukt\u00faru, DB a z\u00e1\u0165a\u017e.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj 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>Hovor\u00edme o experimentoch nad datab\u00e1zami. Je to pr\u00edbeh, ktor\u00fd trv\u00e1 u\u017e viac ako pol roka. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/e77255c4abaa2a01ae37fe888fe67db7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nie\u010do o mne. Sk\u00fasenosti s Postgres u\u017e viac ako 14 rokov. R\u00f4zne spolo\u010dnosti som zalo\u017eil v oblasti soci\u00e1lnych siet\u00ed. V\u0161ade bol Postgres a st\u00e1le sa pou\u017e\u00edva.<\/p>\n<p><\/p>\n<p>A tie\u017e skupina RuPostgres na Meetup, 2. miesto na svete. Pomaly sa bl\u00ed\u017eime k 2 000 \u013eu\u010fom. RuPostgres.org.<\/p>\n<p><\/p>\n<p>A na r\u00f4znych konferenci\u00e1ch vr\u00e1tane Highload, zodpoved\u00e1m za datab\u00e1zy, najm\u00e4 Postgres od ich vzniku.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/fa009b50f1c0293eee80fc03fd2c05f0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A v posledn\u00fdch nieko\u013ek\u00fdch rokoch som obnovil svoju prax v Postgres konzult\u00e1ci\u00ed v 11 \u010dasov\u00fdch z\u00f3nach odtia\u013eto.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/550c3ddcd516adca14d2b3de9c34b7ff.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A ke\u010f som to urobil pred nieko\u013ek\u00fdmi rokmi, mal som ur\u010dit\u00e9 preru\u0161enie akt\u00edvneho ru\u010dn\u00e9ho pr\u00e1ce s Postgresom, pravdepodobne od roku 2010. Prekvapilo ma, ako m\u00e1lo sa zmenil pracovn\u00fd de\u0148 DBA, ako ve\u013ea ru\u010dnej pr\u00e1ce je st\u00e1le potrebn\u00e9. A hne\u010f sme si pomyslel, \u017ee tu nie\u010do nie je v poriadku, je potrebn\u00e9 automatizova\u0165 ove\u013ea viac.<\/p>\n<p><\/p>\n<p>A ke\u010f\u017ee to v\u0161etko bolo v od\u013eahl\u00fdch oblastiach, v\u00e4\u010d\u0161ina klientov bola v cloude. A u\u017e sa ve\u013ea automatizovalo, to je jasn\u00e9. O tom nesk\u00f4r. T.j. v\u0161etko to vy\u00fastilo do my\u0161lienky, \u017ee mus\u00ed existova\u0165 mno\u017estvo n\u00e1strojov, t.j. ak\u00e1si platforma, ktor\u00e1 automatizuje prakticky v\u0161etky \u010dinnosti DBA, aby sa dalo spravova\u0165 ve\u013ek\u00e9 mno\u017estvo datab\u00e1z.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b02097f0bb23fa9d7c30e38ab9f3de7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V tejto spr\u00e1ve nebude:<\/p>\n<p><\/p>\n<ul>\n<li>\"Strieborn\u00e9 gu\u013eky\" a vyhl\u00e1senia typu \u2013 nastavte 8 GB alebo 25 % shared_buffers a budete v poriadku. O shared_buffers nebude to\u013eko. <\/li>\n<li>Hardcore \u201evn\u00fatornosti\u201c. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ccea359ec78a967c150ddcedc3e50a88.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A \u010do bude?<\/p>\n<p><\/p>\n<ul>\n<li>Bude existova\u0165 mno\u017estvo princ\u00edpov optimaliz\u00e1cie, ktor\u00e9 uplat\u0148ujeme a rozv\u00edjame. Bud\u00fa r\u00f4zne n\u00e1pady, ktor\u00e9 n\u00e1m na ceste prich\u00e1dzaj\u00fa na um, a r\u00f4zne n\u00e1stroje, ktor\u00e9 vo v\u00e4\u010d\u0161ine vytv\u00e1rame v Open Source, t.j. z\u00e1klad rob\u00edme v Open Source. Navy\u0161e, m\u00e1me tikety, takmer v\u0161etka komunik\u00e1cia prebieha v Open Source. M\u00f4\u017eete sledova\u0165, \u010do teraz rob\u00edme, \u010do bude v nasleduj\u00facej verzii a pod. <\/li>\n<li>A tie\u017e bud\u00fa ur\u010dit\u00e9 sk\u00fasenosti s pou\u017eit\u00edm t\u00fdchto princ\u00edpov a n\u00e1strojov v rade spolo\u010dnost\u00ed: od mal\u00fdch startupov po ve\u013ek\u00e9 firmy. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86e6f379726dec814aca616a30d36a2f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ako sa to v\u0161etko vyv\u00edja?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c1422d9f2945e717c6ebb31f5fef6329.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Predov\u0161etk\u00fdm je hlavn\u00fdm \u00falohou DBA okrem zabezpe\u010denia vytv\u00e1rania instances, nasadzovania z\u00e1loh a pod. h\u013eadanie \u00fazkych miest a optimaliz\u00e1cia v\u00fdkonu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8e682ca8666e94d6688348ebe4540327.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Moment\u00e1lne to funguje takto. Sledujeme monitoring, nie\u010do vid\u00edme, niektor\u00e9 podrobnosti n\u00e1m ch\u00fdbaj\u00fa. Za\u010d\u00edname sa d\u00f4kladnej\u0161ie sk\u00fama\u0165, zvy\u010dajne ru\u010dne, a ch\u00e1peme, \u010do s t\u00fdm robi\u0165 tak alebo onak.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/04599422ea6b6d57c79a4e48ba2db8b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Existuj\u00fa dva pr\u00edstupy. Pg_stat_statements \u2013 \u0161tandardn\u00e9 predvolen\u00e9 rie\u0161enie na identifik\u00e1ciu pomal\u00fdch dopytov. A anal\u00fdza logov Postgres pomocou pgBadger.<\/p>\n<p><\/p>\n<p>Oba pr\u00edstupy maj\u00fa v\u00e1\u017ene nedostatky. Pri prvom pr\u00edstupe s\u00fa v\u0161etky parametre vynechan\u00e9. A ak vid\u00edme skupiny SELECT * FROM table where st\u013apec je rovn\u00fd znaku \u201e?\u201c alebo \u201e$\u201c od verzie Postgres 10. Nevieme, \u010di ide o indexov\u00fd preh\u013ead alebo sekven\u010dn\u00fd preh\u013ead. Ve\u013emi z\u00e1vis\u00ed od parametra. Pou\u017eijete tam zriedkavo sa vyskytuj\u00facu hodnotu, bude to indexov\u00fd preh\u013ead. Pou\u017eijete hodnotu, ktor\u00e1 zaber\u00e1 90 % tabu\u013eky, bude to jednozna\u010dne sekven\u010dn\u00fd preh\u013ead, preto\u017ee Postgres pozn\u00e1 \u0161tatistiku. A to je ve\u013ek\u00fd nedostatok pg_stat_statements, hoci sa na tom pracuje.<\/p>\n<p><\/p>\n<p>Pri anal\u00fdze logov je najv\u00e4\u010d\u0161\u00ed probl\u00e9m ten, \u017ee si nem\u00f4\u017eete dovoli\u0165 \u201elog_min_duration_statement = 0\u201c, zvy\u010dajne. A o tom budeme tie\u017e hovori\u0165. Preto nevid\u00edte celkom obr\u00e1zku. A nejak\u00fd dopyt, ktor\u00fd je ve\u013emi r\u00fdchly, m\u00f4\u017ee spotrebov\u00e1va\u0165 obrovsk\u00e9 mno\u017estvo zdrojov, ale neuvid\u00edte ho, preto\u017ee je pod va\u0161\u00edm prahom.<\/p>\n<p><\/p>\n<p><strong>Ako DBA rie\u0161ia zisten\u00e9 probl\u00e9my?<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/5e57fefdec6256f82045aaac037b516c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Napr\u00edklad, na\u0161li sme nejak\u00fd probl\u00e9m. \u010co sa zvy\u010dajne rob\u00ed? Ak ste v\u00fdvoj\u00e1r, budete nie\u010do robi\u0165 na nejakom instanci, ktor\u00fd nie je tak\u00e9ho rozmeru. Ak ste DBA, m\u00e1te staging. A ten m\u00f4\u017ee by\u0165 len jeden. A zastavil sa pred polrokom. A mysl\u00edte si, \u017ee prejdete na production. A aj sk\u00fasen\u00ed DBA to potom kontroluj\u00fa na production, na replike. A niekedy vytv\u00e1raj\u00fa do\u010dasn\u00fd index, presved\u010dia sa, \u017ee pom\u00e1ha, zru\u0161ia ho a odovzdaj\u00fa v\u00fdvoj\u00e1rom, aby ho vlo\u017eili do migra\u010dn\u00fdch s\u00faborov. Tak\u00fdto nezmysel sa teraz deje. A to je trag\u00e9dia.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/402e80c7e5fbca6a054c0c9ec61e5fe3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Lad\u00ed\u0165 konfigur\u00e1cie.<\/li>\n<li>Optimalizova\u0165 s\u00fabor indexov. <\/li>\n<li>Zmeni\u0165 samotn\u00fd SQL dotaz (to je najkomplikovanej\u0161\u00ed sp\u00f4sob).<\/li>\n<li>Prida\u0165 v\u00fdkon (najjednoduch\u0161\u00ed sp\u00f4sob vo v\u00e4\u010d\u0161ine pr\u00edpadov).<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8a6b3cd56e0df5c7908714926ba1eb05.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S t\u00fdmito vecami je toho ve\u013emi ve\u013ea. V PostgreSQL je ve\u013ea ovl\u00e1dac\u00edch prvkov. Je potrebn\u00e9 vedie\u0165 ve\u013ea. Mnoho indexov v PostgreSQL, v\u010faka aj organiz\u00e1torom tejto konferencie. A v\u0161etko toto je potrebn\u00e9 vedie\u0165, a pr\u00e1ve to u nie DBA vyvol\u00e1va pocit, \u017ee sa DBA zaoberaj\u00fa \u010diernou m\u00e1giou. T. j. je potrebn\u00e9 sa tomu venova\u0165 pribli\u017ene 10 rokov, aby ste to za\u010dali norm\u00e1lne ch\u00e1pa\u0165.<\/p>\n<p><\/p>\n<p>A ja som bojovn\u00edk proti tejto \u010diernej m\u00e1gii. Chcem, aby bola technol\u00f3gia, a nie intu\u00edcia vo v\u0161etkom tomto.<\/p>\n<p><\/p>\n<p><strong>Pr\u00edklady z praxe<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/70b11c3df9c41c66570635d5f309ed24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Toto som pozoroval aspo\u0148 v dvoch projektoch, vr\u00e1tane svojho. \u010eal\u0161\u00ed pr\u00edspevok na blogu n\u00e1m oznamuje, \u017ee hodnota 1 000 pre default_statistic_target \u2013 to je dobr\u00e9. Dobre, sk\u00fasme to v production.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0b4c2bd6f73df06967c94d5d78b07279.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tu sme, s na\u0161\u00edm n\u00e1strojom dva roky nesk\u00f4r, pomocou experimentov s datab\u00e1zami, o ktor\u00fdch dnes hovor\u00edme, m\u00f4\u017eeme porovna\u0165, \u010do bolo a \u010do sa stalo. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/3564eade6532003d2279c2736ab6d733.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A na to potrebujeme vytvori\u0165 experiment. Ten sa sklad\u00e1 zo \u0161tyroch \u010dast\u00ed. <\/p>\n<p><\/p>\n<ul>\n<li>Prv\u00e1 \u2013 to je prostredie. Potrebujeme hardv\u00e9r. A ke\u010f pr\u00eddem do nejakej spolo\u010dnosti a uzavriem zmluvu, hovor\u00edm, aby mi poskytli rovnak\u00fd hardv\u00e9r ako na production. Pre ka\u017ed\u00e9ho z va\u0161ich majstrov potrebujem aspo\u0148 jeden tak\u00fdto hardv\u00e9r. Bu\u010f je to virtu\u00e1lny stroj instance v Amazone alebo v Googli, alebo potrebujem presne tak\u00fd ist\u00fd hardv\u00e9r. T. j. chcem reprodukova\u0165 prostredie. A do pojmu prostredie zah\u0155\u0148ame hlavn\u00fa verziu PostgreSQL. <\/li>\n<li>Druh\u00e1 \u010das\u0165 \u2013 to je objekt na\u0161ich sk\u00faman\u00ed. To je datab\u00e1za. T\u00fa je mo\u017en\u00e9 vytvori\u0165 nieko\u013ek\u00fdmi sp\u00f4sobmi. Uk\u00e1\u017eem ako. <\/li>\n<li>Tretia \u010das\u0165 \u2013 to je z\u00e1\u0165a\u017e. To je najn\u00e1ro\u010dnej\u0161\u00ed moment. <\/li>\n<li>A \u0161tvrt\u00e1 \u010das\u0165 je to, \u010do kontrolujeme, t. j. s \u010d\u00edm budeme porovn\u00e1va\u0165. Predpokladajme, \u017ee m\u00f4\u017eeme v konfigura\u010dnom s\u00fabore zmeni\u0165 jeden alebo nieko\u013eko parametrov, alebo m\u00f4\u017eeme vytvori\u0165 index a podobne. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8ccd09ac4cdd336618413375617c10a6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Sp\u00fa\u0161\u0165ame experiment. Tu je pg_stat_statements. Na\u013eavo \u2013 to, \u010do bolo. Napravo \u2013 \u010do sa stalo.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/6f82d7e25a77b700f2ddb01f42a861a2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na\u013eavo default_statistics_target = 100, napravo = 1 000. Vid\u00edme, \u017ee n\u00e1m to pomohlo. O 8 % je to celkovo lep\u0161ie.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/52dda8fb5fbcb9beecaa1eda8443a374.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale ak posunieme nadol, uvid\u00edme skupiny dopytov z pgBadger alebo pg_stat_statements. Tu s\u00fa dve mo\u017enosti. Uvid\u00edme, \u017ee niektor\u00fd dopyt poklesol o 88 %. A tu u\u017e je in\u017einiersky pr\u00edstup. M\u00f4\u017eeme sa h\u013abi\u0165 \u010falej, preto\u017ee je zauj\u00edmav\u00e9, pre\u010do poklesol. Mus\u00edme ch\u00e1pa\u0165, \u010do sa tam deje so \u0161tatistikami. Pre\u010do viac bucketov v \u0161tatistik\u00e1ch vedie k hor\u0161iemu v\u00fdsledku.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4e6dfc04af4b3fc2b626dd46c08bdf66.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale m\u00f4\u017eeme sa aj nepreh\u013abi\u0165, a robi\u0165 \u201eALTER TABLE \u2026 ALTER COLUMN\u201c a vr\u00e1ti\u0165 mu sp\u00e4\u0165 100 bucketov do \u0161tatistiky tohto st\u013apca. A n\u00e1sledne sa experimentom m\u00f4\u017eeme presved\u010di\u0165, \u017ee t\u00e1to oprava pomohla. A to je in\u017einiersky pr\u00edstup, ktor\u00fd n\u00e1m pom\u00e1ha vidie\u0165 celkov\u00fd obraz a prij\u00edma\u0165 rozhodnutia na z\u00e1klade \u00fadajov, nie na z\u00e1klade intu\u00edcie.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj 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=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/9e766aa4348bd1a77b697e3179d84bbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nieko\u013eko pr\u00edkladov z in\u00fdch oblast\u00ed. V testoch s\u00fa u\u017e mnoho rokov CI-testy. A \u017eiaden projekt v zdravom rozume u\u017e nebude existova\u0165 bez automatick\u00fdch testov.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b6c21981dc8b2b285973ae42199e2b4c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V in\u00fdch oblastiach: v letectve, v automobilovom priemysle, ke\u010f testujeme aerodynamiku, m\u00e1me tie\u017e mo\u017enos\u0165 robi\u0165 experimenty. Okam\u017eite nie\u010do z n\u00e1vrhu nevypust\u00edme do vesm\u00edru ani nevyvedieme \u017eiadne vozidlo na cestu. Napr\u00edklad m\u00e1me aerodynamick\u00fd tunel. <\/p>\n<p><\/p>\n<p>Z pozorovan\u00ed in\u00fdch oblast\u00ed m\u00f4\u017eeme vyvodi\u0165 z\u00e1very. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cef02ea9280e5795d1e593c600f0541b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Po prv\u00e9, m\u00e1me \u0161peci\u00e1lne prostredie. Je bl\u00edzko produkcii, ale nie ve\u013emi. Jeho najv\u00e4\u010d\u0161ou v\u00fdhodou je, \u017ee by malo by\u0165 lacn\u00e9, opakovate\u013en\u00e9 a maxim\u00e1lne automatizovan\u00e9. A mali by by\u0165 tie\u017e \u0161peci\u00e1lne n\u00e1stroje na podrobn\u00fa anal\u00fdzu.<\/p>\n<p><\/p>\n<p>Pravdepodobne, ke\u010f sme spustili lietadlo a let\u00edme, m\u00e1me menej mo\u017enost\u00ed presk\u00fama\u0165 ka\u017ed\u00fd milimeter povrchu kr\u00eddla, ne\u017e je to v aerodynamickom tuneli. M\u00e1me viac prostriedkov na diagnostiku. M\u00f4\u017eeme si dovoli\u0165 prida\u0165 viac \u0165a\u017ek\u00fdch vec\u00ed, ktor\u00e9 si nem\u00f4\u017eeme dovoli\u0165 prida\u0165 na lietadlo vo vzduchu. Rovnako je to aj s Postgresom. V niektor\u00fdch pr\u00edpadoch m\u00f4\u017eeme zapn\u00fa\u0165 \u00fapln\u00e9 logovanie dotazov pri experimentoch. A to na produkcii nechceme robi\u0165. Mo\u017eno to aj zapneme pomocou auto_explain.<\/p>\n<p><\/p>\n<p>A ako som u\u017e povedal, vysok\u00e1 \u00farove\u0148 automatiz\u00e1cie znamen\u00e1, \u017ee sme stla\u010dili tla\u010didlo a zopakovali to. Takto to m\u00e1 by\u0165, aby bolo ve\u013ea experimentov, aby to bola na pr\u00fade.<\/p>\n<p><\/p>\n<p>Nancy CLI \u2013 z\u00e1klad \"laborat\u00f3ria DB\"<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/f8303df21f309fb209929acec260b757.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tak sme vytvorili tak\u00fa vec. T. j. o t\u00fdchto my\u0161lienkach som hovoril v j\u00fani, takmer pred rokom. A u\u017e m\u00e1me v Open Source takzvan\u00fa Nancy CLI. To je z\u00e1klad na to, aby sme budovali laborat\u00f3rium datab\u00e1z.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj 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 Je to v Open Source, na Gitlabe. M\u00f4\u017eete poveda\u0165, m\u00f4\u017eete vysk\u00fa\u0161a\u0165. Dal som tam link v sn\u00edmkach. Na \u0148u m\u00f4\u017eete klikn\u00fa\u0165 a tam bude <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/nancy\/-\/blob\/master\/help\/nancy_run.md\">help<\/a><\/noindex> po v\u0161etk\u00fdch parametroch.<\/p>\n<p><\/p>\n<p>Samozrejme, tam je e\u0161te ve\u013ea vec\u00ed vo v\u00fdvoji. Je tam mno\u017estvo n\u00e1padov. Ale toto je u\u017e to, \u010do aplikujeme takmer ka\u017ed\u00fd de\u0148. A ke\u010f sa n\u00e1m objav\u00ed my\u0161lienka \u2013 a \u010do sa stane pri delete 40 000 000 riadkov, ak naraz\u00edme na IO, m\u00f4\u017eeme uskuto\u010dni\u0165 experiment a pozrie\u0165 sa, \u010do sa deje a potom sa pok\u00fasi\u0165 to opravi\u0165 za pochodu. T. j. rob\u00edme experiment. Napr\u00edklad nie\u010do uprav\u00edme a pozrieme sa, \u010do nakoniec vyjde. A rob\u00edme to nie na produkcii. To je podstata my\u0161lienky.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c22d332de4b45d604dfb9c5a95404fd6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kde to m\u00f4\u017ee fungova\u0165? M\u00f4\u017ee to fungova\u0165 lok\u00e1lne, t. j. m\u00f4\u017eete to robi\u0165 kdeko\u013evek, m\u00f4\u017eete to dokonca spusti\u0165 na MacBooku. Potrebujete Docker, ideme. A v\u0161etko. M\u00f4\u017eete to spusti\u0165 na nejakom instance na hardv\u00e9ri alebo vo virtu\u00e1lke, kdeko\u013evek.<\/p>\n<p><\/p>\n<p>Existuje tie\u017e mo\u017enos\u0165 sp\u00fa\u0161\u0165a\u0165 vzdialene v Amazone v EC2 Instance, v spotov\u00fdch in\u0161tanci\u00e1ch. A to je naozaj skvel\u00e1 mo\u017enos\u0165. Napr\u00edklad v\u010dera sme realizovali viac ako 500 experimentov na i3 instance, od najmen\u0161ej a\u017e po i3-16-xlarge. A t\u00fdch 500 experimentov n\u00e1s st\u00e1lo 64 dol\u00e1rov. Ka\u017ed\u00fd trval 15 min\u00fat. T. j. v\u010faka pou\u017eit\u00fdm spotom je to naozaj lacn\u00e9 \u2013 z\u013eava 70 %, platenie za sekundu v Amazone. M\u00f4\u017eete robi\u0165 naozaj ve\u013ea. M\u00f4\u017eete robi\u0165 re\u00e1lny v\u00fdskum.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/64c61cc9dd7181296b2ef5b815a17493.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tri hlavn\u00e9 verzie Postgres s\u00fa podporovan\u00e9. Nie je tak \u0165a\u017ek\u00e9 doladi\u0165 nejak\u00e9 star\u00e9 a aj nov\u00fa 12. verziu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ee147c91014ab698cbd3c60e193bef23.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Objekt m\u00f4\u017eeme definova\u0165 tromi sp\u00f4sobmi. A to s\u00fa:<\/p>\n<p><\/p>\n<ul>\n<li>Dump\/sql s\u00fabor. <\/li>\n<li>Hlavn\u00fd sp\u00f4sob je klon PGDATA adres\u00e1ra. Zvy\u010dajne sa berie z backup servera. Ak m\u00e1te norm\u00e1lne bin\u00e1rne backupy, odtia\u013e m\u00f4\u017eete robi\u0165 klony. Ak m\u00e1te cloud, potom sa o to postar\u00e1 za v\u00e1s poskytovate\u013e cloudov\u00fdch slu\u017eieb ako Amazon a Google. Toto je najd\u00f4le\u017eitej\u0161\u00ed sp\u00f4sob pre klony re\u00e1lneho produk\u010dn\u00e9ho prostredia. T\u00fdmto sp\u00f4sobom sa pr\u00e1ve rozv\u00edjame. <\/li>\n<li>A posledn\u00fd sp\u00f4sob je vhodn\u00fd pre v\u00fdskum, ke\u010f m\u00e1te z\u00e1ujem zisti\u0165, ako v Postgres funguje nejak\u00e1 vec. To je pgbench. M\u00f4\u017eete generova\u0165 pomocou pgbench. Je to jednoducho jedna vo\u013eba \u201edb-pgbench\u201c. Povedzte mu, ak\u00fa \u0161k\u00e1lu. A v\u0161etko bude v cloude vygenerovan\u00e9, ako sa uv\u00e1dza.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/2bc3e30cb31a19f66c6b55480447126f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A z\u00e1\u0165a\u017e:<\/p>\n<p><\/p>\n<ul>\n<li>Z\u00e1\u0165a\u017e m\u00f4\u017eeme vykon\u00e1va\u0165 v jednom vl\u00e1kne SQL. To je najprimit\u00edvnej\u0161\u00ed sp\u00f4sob. <\/li>\n<li>Ale m\u00f4\u017eeme emulova\u0165 z\u00e1\u0165a\u017e. A emulova\u0165 ju m\u00f4\u017eeme predov\u0161etk\u00fdm nasleduj\u00facim sp\u00f4sobom. Mus\u00edme zbiera\u0165 v\u0161etky logy. A to je bolestiv\u00e9. Uk\u00e1\u017eem v\u00e1m, pre\u010do. A pomocou pgreplay, ktor\u00fd je zabudovan\u00fd v Nancy, to prehr\u00e1vame. <\/li>\n<li>Alebo in\u00e1 varianta. Takzvan\u00e1 remeseln\u00e1 z\u00e1\u0165a\u017e, ktor\u00fa rob\u00edme s ur\u010dit\u00fdm mno\u017estvom \u00fasilia. Analyzuj\u00fac na\u0161u aktu\u00e1lnu z\u00e1\u0165a\u017e na bojovom syst\u00e9me, vyber\u00e1me top skupiny dopytov. A pomocou pgbench m\u00f4\u017eeme emulova\u0165 t\u00fato z\u00e1\u0165a\u017e v laborat\u00f3riu. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4adba12881faf9ad9d4f9e9f42c0f5c1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Alebo mus\u00edme vykona\u0165 nejak\u00fd SQL, t. j. overujeme nejak\u00fa migr\u00e1ciu, vytv\u00e1rame index, vykon\u00e1vame ANALYZE. A sledujeme, \u010do bolo pred a po vypr\u00e1zdnen\u00ed. V podstate ak\u00fdko\u013evek SQL.<\/li>\n<li>Bu\u010f zm\u011bn\u00edme jeden nebo v\u00edce parametr\u016f v konfigura\u010dn\u00edm souboru. M\u016f\u017eeme po\u017e\u00e1dat, aby zkontrolovali nap\u0159\u00edklad 100 hodnot na Amazonu pro na\u0161i terabajtovou datab\u00e1zi. A za n\u011bkolik hodin m\u00e1te v\u00fdsledek. Obvykle se terabajtov\u00e1 datab\u00e1ze rozj\u00ed\u017ed\u00ed n\u011bkolik hodin. Ale v n\u00e1vrhu je z\u00e1plata, m\u00e1me mo\u017enost s\u00e9rie, tj. m\u016f\u017eete na stejn\u00e9m serveru pou\u017e\u00edt stejn\u00fd pgdata a testovat. Postgres se restartuje, ke\u0161e se vyma\u017eou. A m\u016f\u017eete zkou\u0161et z\u00e1t\u011b\u017e. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/02985b47bf0ab62e8f1c4cc3ea003f26.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>P\u0159ijede adres\u00e1\u0159, ve kter\u00e9m je spousta r\u016fzn\u00fdch soubor\u016f, po\u010d\u00ednaje snapshoty pg.<em>stat<\/em>***. A tam je to nejzaj\u00edmav\u011bj\u0161\u00ed \u2013 pg_stat_statements, pg_stat_kcacke. To jsou dv\u011b roz\u0161\u00ed\u0159en\u00ed, kter\u00e1 analyzuj\u00ed dotazy. A pg_stat_bgwriter obsahuje nejen statistiky pgwriter, ale tak\u00e9 informace o checkpointu a o tom, jak se backendy zbavuj\u00ed \u0161pinav\u00fdch buffer\u016f. A to v\u0161echno je zaj\u00edmav\u00e9 si prohl\u00e9dnout. Nap\u0159\u00edklad, kdy\u017e nastavujeme shared_buffers, je velmi zaj\u00edmav\u00e9 vid\u011bt, kolik kdo vyhnal.<\/li>\n<li>Tak\u00e9 p\u0159ich\u00e1zej\u00ed logy Postgresu. Dva logy \u2013 log p\u0159\u00edpravy a log p\u0159ehr\u00e1v\u00e1n\u00ed z\u00e1t\u011b\u017ee. <\/li>\n<li>Relativn\u011b nov\u00e1 funkce \u2013 FlameGraphs.<\/li>\n<li>Tak\u00e9, pokud jste pou\u017e\u00edvali pgreplay nebo pgbench varianty p\u0159ehr\u00e1v\u00e1n\u00ed z\u00e1t\u011b\u017ee, zobraz\u00ed se jejich nativn\u00ed v\u00fdstup. A uvid\u00edte latenci a TPS. Budete moci pochopit, jak to vid\u011bli. <\/li>\n<li>Informace o syst\u00e9mu. <\/li>\n<li>Z\u00e1kladn\u00ed kontroly CPU a IO. To je v\u00edce pro EC2 instance na Amazonu, kdy\u017e chcete ve streamu spustit 100 stejn\u00fdch instanc\u00ed a tam prov\u00e9st 100 r\u016fzn\u00fdch b\u011bh\u016f, budete m\u00edt 10 000 experiment\u016f. A mus\u00edte se ujistit, \u017ee se v\u00e1m nedostala po\u0161kozen\u00e1 instance, kterou u\u017e n\u011bkdo obt\u011b\u017euje. Na tomto hardwaru jin\u00ed aktivn\u011b z\u016fst\u00e1vaj\u00ed a vy m\u00e1te m\u00e1lo zdroj\u016f. Takov\u00e9 v\u00fdsledky je lep\u0161\u00ed vylou\u010dit. A pr\u00e1v\u011b s pomoc\u00ed sysbench od Alexeje Kopytova d\u011bl\u00e1me n\u011bkolik kr\u00e1tk\u00fdch kontrol, kter\u00e9 p\u0159ijdou a m\u016f\u017eete je porovnat s ostatn\u00edmi, tj. pochop\u00edte, jak se CPU chov\u00e1 a jak se IO chov\u00e1. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c4ddf5283c3c79e3d4c2c061e7dd9e7d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak\u00e9 jsou technick\u00e9 slo\u017eitosti na p\u0159\u00edkladu r\u016fzn\u00fdch spole\u010dnost\u00ed?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/959690b464a01a4a3cb67ec497becff1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u0159edpokl\u00e1dejme, \u017ee chceme re\u00e1ln\u011b opakovat z\u00e1t\u011b\u017e pomoc\u00ed log\u016f. Skv\u011bl\u00fd n\u00e1pad, pokud je to na Open Source pgreplay naps\u00e1no. Pou\u017e\u00edv\u00e1me ho. Ale aby to fungovalo dob\u0159e, mus\u00edte zapnout \u00fapln\u00e9 logov\u00e1n\u00ed dotaz\u016f s parametry a \u010dasov\u00e1n\u00edm.<\/p>\n<p><\/p>\n<p>Existuj\u00fa ur\u010dit\u00e9 komplik\u00e1cie, pokia\u013e ide o duration a timestamp. T\u00fato str\u00e1nku \u00faplne presko\u010d\u00edme. Hlavn\u00e1 ot\u00e1zka znie \u2013 m\u00f4\u017eete si to dovoli\u0165 alebo nie? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj 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>Probl\u00e9m je v tom, \u017ee to m\u00f4\u017ee by\u0165 nedostupn\u00e9. Mus\u00edte si najprv uvedomi\u0165, ak\u00fd tok sa bude zapisova\u0165 do logu. Ak m\u00e1te pg_stat_statements, m\u00f4\u017eete pomocou tohto dotazu (odkaz bude k dispoz\u00edcii v prezent\u00e1cii) zisti\u0165, ko\u013eko bajtov sa pribli\u017ene zap\u00ed\u0161e za sekundu.<\/p>\n<p><\/p>\n<p>Sledujeme d\u013a\u017eku dotazu. Ignorujeme fakt, \u017ee tam nie s\u00fa parametre, ale vieme, ak\u00e1 je d\u013a\u017eka dotazu a ko\u013ekokr\u00e1t za sekundu bol vykonan\u00fd. Takto m\u00f4\u017eeme odhadn\u00fa\u0165, ko\u013eko bajtov za sekundu. M\u00f4\u017eeme sa pom\u00fdli\u0165 pr\u00edpadu max. dvakr\u00e1t, ale poradie ur\u010d\u00edme presne t\u00fdmto sp\u00f4sobom.<\/p>\n<p><\/p>\n<p>Vid\u00edme, \u017ee tento dotaz sa vykon\u00e1va 802-kr\u00e1t za sekundu. A vid\u00edme, \u017ee bytes_per_sec \u2013 300 kB\/s bude zapisovan\u00e9 plus m\u00ednus. A zvy\u010dajne si tak\u00fd tok m\u00f4\u017eeme dovoli\u0165. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/218f905073a8519115adf5c65825b74d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale! Probl\u00e9m je v tom, \u017ee existuj\u00fa r\u00f4zne syst\u00e9my logovania. A predvolene maj\u00fa \u013eudia zvy\u010dajne \u00absyslog\u00bb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/66054682083ba2ada174afb5e7ee3973.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A ak m\u00e1te syslog, m\u00f4\u017ee to vyzera\u0165 takto. Vezmeme pgbench, zapneme logovanie dotazov a pozrieme sa, \u010do z\u00edskame.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a05723dcf10a7733f054ba108bb0f107.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Bez logovania \u2013 to je st\u013apec v\u013eavo. Dosiahli sme 161 000 TPS. So syslogom \u2013 na Ubuntu 16.04 na Amazone m\u00e1me 37 000 TPS. A ak zmen\u00edme na dva in\u00e9 sp\u00f4soby logovania, situ\u00e1cia sa v\u00fdrazne zlep\u0161\u00ed. T. j. o\u010dak\u00e1vali sme pokles, ale nie a\u017e tak\u00fd.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a7d90af9d29761d3b80eb09bf050760f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na CentOS 7, kde sa z\u00fa\u010dast\u0148uje aj journald, prev\u00e1dzaj\u00faci logy do bin\u00e1rneho form\u00e1tu pre pohodln\u00e9 vyh\u013ead\u00e1vanie at\u010f., sme klesli na 44-n\u00e1sobne ni\u017e\u0161iu TPS.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86d2af18a0e6364f297a62378b6ede56.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A to je nie\u010do, s \u010d\u00edm \u013eudia \u017eij\u00fa. A \u010dasto je to v spolo\u010dnostiach, najm\u00e4 vo ve\u013ek\u00fdch, ve\u013emi \u0165a\u017ek\u00e9 zmeni\u0165. Ak sa m\u00f4\u017eete od syslogu odkloni\u0165, urobte to pros\u00edm.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a167a6fac7f28c7541515f7eff30bdb4.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Odhadnite IOPS a z\u00e1pisov\u00fd tok. <\/li>\n<li>Skontrolujte svoj syst\u00e9m logovania. <\/li>\n<li>Ak je predpokladan\u00e1 z\u00e1\u0165a\u017e pr\u00edli\u0161 vysok\u00e1, zv\u00e1\u017ete mo\u017enosti s vzorkovan\u00edm. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/127fa1296578ba0ba6eac2b44c5c5398.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00e1me pg_stat_statements. Ako som povedal, mus\u00ed tam by\u0165. A m\u00f4\u017eeme zobrazi\u0165 ka\u017ed\u00fa skupinu dotazov \u0161peci\u00e1lnym sp\u00f4sobom v s\u00fabore. A potom m\u00f4\u017eeme vyu\u017ei\u0165 ve\u013emi u\u017eito\u010dn\u00fa funkciu v pgbench \u2013 mo\u017enos\u0165 doda\u0165 nieko\u013eko s\u00faborov pomocou mo\u017enosti \u00ab-f\u00bb.<\/p>\n<p><\/p>\n<p>Ch\u00e1pe ve\u013ea \"-f\". A pomocou \"@\" na konci m\u00f4\u017eeme poveda\u0165, ak\u00e1 \u010das\u0165 by mala by\u0165 pre ka\u017ed\u00fd s\u00fabor. T. j. m\u00f4\u017eeme poveda\u0165, \u017ee toto sa m\u00e1 spusti\u0165 v 10 % pr\u00edpadov a toto v 20 %. A to n\u00e1s pribl\u00ed\u017ei k tomu, \u010do vid\u00edme v produkcii.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b91d18a9861abdc36bfda54cd72a142f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A ako zist\u00edme, \u010do m\u00e1me v produkcii? Ak\u00fd podiel a \u010do ako? Tu trochu odbo\u010d\u00edme. M\u00e1me e\u0161te jeden produkt. <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/postgres-checkup\">postgres-checkup<\/a><\/noindex>. Je to tie\u017e datab\u00e1za s otvoren\u00fdm zdrojov\u00fdm k\u00f3dom. A v s\u00fa\u010dasnosti ju akt\u00edvne rozv\u00edjame.<\/p>\n<p><\/p>\n<p>Narodil sa z trochu in\u00fdch d\u00f4vodov. Z d\u00f4vodov, \u017ee monitorovanie nesta\u010d\u00ed. T. j. prijdete, pozriete sa na datab\u00e1zu, pozriete sa na probl\u00e9my, ktor\u00e9 existuj\u00fa. A zvy\u010dajne vykon\u00e1vate health_check. Ak ste sk\u00fasen\u00fd DBA, rob\u00edte health_check. Pozreli ste sa na vyu\u017eitie indexov a pod. Ak m\u00e1te OKmeter, je to v\u00fdborn\u00e9. Je to skvel\u00e9 monitorovanie pre Postgres. OKmeter.io \u2013 pros\u00edm, nain\u0161talujte ho, je to ve\u013emi dobre uroben\u00e9. Je to platen\u00e9.<\/p>\n<p><\/p>\n<p>Ak ho nem\u00e1te, zvy\u010dajne nem\u00e1te ve\u013ea mo\u017enost\u00ed. V monitorovan\u00ed zvy\u010dajne m\u00e1te CPU, IO a to s obmedzeniami, a to je v\u0161etko. A potrebujeme viac. Mus\u00edme vidie\u0165, ako funguje autovacuum, ako funguje checkpoint, v IO mus\u00edme oddeli\u0165 checkpoint od bgwriter a od backendov a pod.<\/p>\n<p><\/p>\n<p>Probl\u00e9m je v tom, ke\u010f pom\u00e1hate nejakej ve\u013ekej spolo\u010dnosti, nem\u00f4\u017eu r\u00fdchlo nie\u010do zavies\u0165. Nem\u00f4\u017eu r\u00fdchlo k\u00fapi\u0165 OKmeter. Mo\u017eno ho k\u00fapia o pol roka. Nem\u00f4\u017eu r\u00fdchlo nain\u0161talova\u0165 nejak\u00e9 bal\u00ed\u010dky. <\/p>\n<p><\/p>\n<p>A napadlo n\u00e1m, \u017ee potrebujeme tak\u00fd \u0161peci\u00e1lny n\u00e1stroj, ktor\u00fd nevy\u017eaduje ni\u010d na in\u0161tal\u00e1ciu, t. j. nemus\u00edte na produkciu in\u0161talova\u0165 v\u00f4bec ni\u010d. Nain\u0161talujete si ho na svoj notebook, alebo na server na pozorovanie, odkia\u013e ho budete sp\u00fa\u0161\u0165a\u0165. A bude analyzova\u0165 ve\u013ea vec\u00ed: opera\u010dn\u00fd syst\u00e9m, s\u00faborov\u00fd syst\u00e9m a samotn\u00fd Postgres, vykon\u00e1vaj\u00fac nejak\u00e9 \u013eahk\u00e9 po\u017eiadavky, ktor\u00e9 m\u00f4\u017eete spusti\u0165 priamo na produkcii a ni\u010d nezlyh\u00e1.<\/p>\n<p><\/p>\n<p>Nazvali sme ho Postgres-checkup. Ak by sme hovorili medic\u00ednsky, je to pravideln\u00e1 kontrola zdravia. Ak by sme hovorili automobilovou terminol\u00f3giou, je to ako technick\u00e1 kontrola. Rob\u00edte technick\u00fa kontrolu va\u0161emu autu ka\u017ed\u00fdch pol roka alebo roka, v z\u00e1vislosti od zna\u010dky. A rob\u00edte aj technick\u00fa kontrolu pre svoju datab\u00e1zu? T. j. vykon\u00e1vate h\u013abkov\u00fd v\u00fdskum pravidelne? Je to potrebn\u00e9 robi\u0165. Ak rob\u00edte z\u00e1lohy, robte aj checkup, to je rovnako d\u00f4le\u017eit\u00e9.<\/p>\n<p><\/p>\n<p>A m\u00e1me tak\u00fd n\u00e1stroj. Za\u010dal sa akt\u00edvne rozv\u00edja\u0165 len pred tromi mesiacmi. Je e\u0161te mlad\u00fd, ale u\u017e tam toho ve\u013ea je.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/514d00b5cb710a7997af0a5dde55b5a8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zbierame najvplyvnej\u0161ie skupiny dopytov \u2013 spr\u00e1va K003 v Postgres-checkup<\/p>\n<p><\/p>\n<p>A tam je skupina spr\u00e1v K. Doteraz s\u00fa tri spr\u00e1vy. A existuje tak\u00e1 spr\u00e1va K003. Tam je vrchol z pg_stat_statements, zoraden\u00fd pod\u013ea total_time.<\/p>\n<p><\/p>\n<p>Ke\u010f zorad\u00edme skupiny dopytov pod\u013ea total_time, na vrchu vid\u00edme skupinu, ktor\u00e1 najviac za\u0165a\u017euje na\u0161u s\u00fastavu, t. j. spotrebuje najviac zdrojov. Pre\u010do ich naz\u00fdvam skupinami dopytov? Preto\u017ee sme vyhodili parametre. U\u017e to nie s\u00fa dopyty, ale skupiny dopytov, t. j. s\u00fa abstrahovan\u00e9.<\/p>\n<p><\/p>\n<p>A ak budeme optimalizova\u0165 zhora nadol, u\u013eah\u010d\u00edme na\u0161e zdroje a odlo\u017e\u00edme okamih, kedy budeme musie\u0165 urobi\u0165 upgrade. To je ve\u013emi dobr\u00fd sp\u00f4sob, ako u\u0161etri\u0165 peniaze.<\/p>\n<p><\/p>\n<p>Mo\u017eno to nie je najlep\u0161\u00ed sp\u00f4sob, pokia\u013e ide o starostlivos\u0165 o pou\u017e\u00edvate\u013eov, preto\u017ee mo\u017eno nevid\u00edme zriedkav\u00e9, ale ve\u013emi nepr\u00edjemn\u00e9 pr\u00edpady, ke\u010f \u010dlovek \u010dakal 15 sek\u00fand. V s\u00fahrne s\u00fa tak zriedkav\u00e9, \u017ee ich nevid\u00edme, ale zaober\u00e1me sa zdrojmi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d16abc423573e4d923b0c03683c2efee.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u010co sa stalo v tejto tabu\u013eke? Urobili sme dve sn\u00edmky. Postgres_checkup v\u00e1m vypo\u010d\u00edta delta pre ka\u017ed\u00fa met\u00f3du: pod\u013ea total-time, calls, rows, shared_blks_read a pod. V\u0161etko, delta vypo\u010d\u00edtal. U pg_stat_statements je ve\u013ek\u00fd probl\u00e9m v tom, \u017ee si nepam\u00e4t\u00e1, kedy bol reset. Ak pg_stat_database si pam\u00e4t\u00e1, tak pg_stat_statements si nepam\u00e4t\u00e1. Vid\u00edte, \u017ee tam je \u010d\u00edslo 1 000 000, ale odkia\u013e sme to po\u010d\u00edtali, nevieme.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c370bf1237675e03c0a3d362c025ac24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tu vieme, m\u00e1me dve sn\u00edmky. Vieme, \u017ee delta bola v tomto pr\u00edpade 56 sek\u00fand. Ve\u013emi mal\u00fd interval. Pod\u013ea total_time zoraden\u00e9. A potom m\u00f4\u017eeme diferencova\u0165, t. j. v\u0161etky metriky del\u00edme na duration. Ak ka\u017ed\u00fa metriku rozdel\u00edme na duration, dostaneme po\u010det volan\u00ed za sekundu.<\/p>\n<p><\/p>\n<p>\u010ealej total_time per second \u2013 to je moja ob\u013e\u00faben\u00e1 metrika. Meria sa v sekund\u00e1ch, za sekundu, t. j. ko\u013eko sek\u00fand potrebovala na\u0161a s\u00fastava na vykonanie tejto skupiny dopytov za sekundu. Ak tam vid\u00edte viac ako sekundu za sekundu, znamen\u00e1 to, \u017ee ste potrebovali viac ako jedno jadro. To je ve\u013emi dobr\u00e1 metrika. M\u00f4\u017eete pochopi\u0165, \u017ee tomuto chlap\u00edkovi, napr\u00edklad, potrebujete minim\u00e1lne tri jadr\u00e1.<\/p>\n<p><\/p>\n<p>Tu je na\u0161e know-how, tak\u00e9to som nikde nevidel. Upozor\u0148ujem \u2013 je to ve\u013emi jednoduch\u00e1 vec \u2013 sekunda za sekundu. Niekedy, ke\u010f je CPU na 100 %, tak pol hodiny za sekundu, t. j. ste 30 min\u00fat zaberali len t\u00fdmto dopytom. <\/p>\n<p><\/p>\n<p>\u010ealej vid\u00edme riadky za sekundu. Vieme, ko\u013eko riadkov za sekundu bolo vr\u00e1ten\u00fdch.<\/p>\n<p><\/p>\n<p>A \u010falej je tie\u017e zauj\u00edmav\u00e1 vec. Ko\u013eko sme shared_buffers pre\u010d\u00edtali za sekundu z samotn\u00e9ho shared_buffers. Hity u\u017e boli tam, a riadky sme vzali z cache opera\u010dn\u00e9ho syst\u00e9mu, alebo z disku. Prv\u00e1 mo\u017enos\u0165 je r\u00fdchla, a druh\u00e1 m\u00f4\u017ee by\u0165 r\u00fdchla, ale m\u00f4\u017ee by\u0165 aj pomal\u00e1, to z\u00e1vis\u00ed od situ\u00e1cie. <\/p>\n<p><\/p>\n<p>A druh\u00fd sp\u00f4sob diferenci\u00e1cie \u2013 del\u00edme po\u010det po\u017eiadaviek v tejto skupine. V druhej kol\u00f3nke v\u017edy bude jedna po\u017eiadavka delen\u00e1 na po\u017eiadavku. A potom je zauj\u00edmav\u00e9 \u2013 ko\u013eko milisek\u00fand bolo v tejto po\u017eiadavke. Vieme, ako sa t\u00e1to po\u017eiadavka spr\u00e1vne spr\u00e1va. Na ka\u017ed\u00fa po\u017eiadavku bolo potrebn\u00fdch 101 milisek\u00fand. Toto je tradi\u010dn\u00e1 metrika, ktor\u00fa potrebujeme na porozumenie.<\/p>\n<p><\/p>\n<p>Ko\u013eko riadkov ka\u017ed\u00e1 po\u017eiadavka v priemere vr\u00e1tila. Vid\u00edme, \u017ee t\u00e1to skupina vracia 8. Ko\u013eko v priemere sa vzalo a pre\u010d\u00edtalo z cache. Vid\u00edme, \u017ee v\u0161etko je pekne vyrovnan\u00e9 v cache. Sam\u00e9 hity pre prv\u00fa skupinu. <\/p>\n<p><\/p>\n<p>A \u0161tvrt\u00fd podriadok v ka\u017edom riadku \u2013 to je ko\u013eko percent z celkov\u00e9ho po\u010dtu. M\u00e1me volania. Predpokladajme, \u017ee je to 1 000 000. A m\u00f4\u017eeme pochopi\u0165, ak\u00fd pr\u00edspevok t\u00e1to skupina vklad\u00e1. Vid\u00edme, \u017ee v tomto pr\u00edpade prv\u00e1 skupina prispieva menej ako 0,01 %. T. j. je tak\u00e1 pomal\u00e1, \u017ee ju nevid\u00edme v celkovom obraze. A druh\u00e1 skupina \u2013 5 % z volan\u00ed. T. j. 5 % zo v\u0161etk\u00fdch volan\u00ed je druh\u00e1 skupina. <\/p>\n<p><\/p>\n<p>Pod\u013ea total_time je to tie\u017e zauj\u00edmav\u00e9. Na prv\u00fa skupinu po\u017eiadaviek sme minuli 14 % celkov\u00e9ho \u010dasu pr\u00e1ce. A na druh\u00fa \u2013 11 % at\u010f. <\/p>\n<p><\/p>\n<p>Do detailov sa nebudem hlboko pon\u00e1ra\u0165, ale s\u00fa tam nuansy. Hore vypisujeme chybu, preto\u017ee ke\u010f porovn\u00e1vame, sn\u00edmky m\u00f4\u017eu by\u0165 posunut\u00e9, t. j. niektor\u00e9 po\u017eiadavky m\u00f4\u017eu vypadn\u00fa\u0165 a v druhom u\u017e nemusia by\u0165 pr\u00edtomn\u00e9, a niektor\u00e9 nov\u00e9 m\u00f4\u017eu vznikn\u00fa\u0165. A my tam po\u010d\u00edtame chybu. Ak vid\u00edte 0, tak je to dobr\u00e9. To znamen\u00e1, \u017ee \u017eiadne chyby nie s\u00fa. Ak je ukazovate\u013e chyby a\u017e 20 %, je to v poriadku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/327bbf167bc21d37a3e1597b52528e6d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Potom sa op\u00e4\u0165 vraciame k na\u0161ej t\u00e9me. Mus\u00edme vytvori\u0165 workload. Ideme zhora nadol, k\u00fdm nenazbierame 80 % alebo 90 %. Zvy\u010dajne je to 10-20 skup\u00edn. A pripravujeme s\u00fabory pre pgbench. Tam pou\u017e\u00edvame random. Niekedy to, bohu\u017eia\u013e, nefunguje. A v Postgres 12 bude viac mo\u017enost\u00ed, ako tento pr\u00edstup vyu\u017ei\u0165. <\/p>\n<p><\/p>\n<p>A takto z\u00edskavame 80-90 % z celkov\u00e9ho \u010dasu. \u010co \u010falej vlo\u017ei\u0165 za \u201e@\u201c? Pozer\u00e1me sa na volania, sledujeme, ko\u013eko percent a ch\u00e1peme, \u017ee tu by sme mali ma\u0165 to\u013eko percent. Z t\u00fdchto percent m\u00f4\u017eeme pochopi\u0165, ako vyv\u00e1\u017ei\u0165 ka\u017ed\u00fd z s\u00faborov. Potom pou\u017e\u00edvame pgbench a m\u00f4\u017eeme pokra\u010dova\u0165 v pr\u00e1ci. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/53edf787f49dcabb5e4731510839332d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00e1me aj K001 a K002. <\/p>\n<p><\/p>\n<p>K001 \u2013 to je jedna ve\u013ek\u00e1 l\u00ednia so \u0161tyrmi podriadkami. Je to charakteristika celej na\u0161ej z\u00e1\u0165a\u017ee. Sledujte druh\u00fd st\u013apec a druh\u00fa podriadku. Vid\u00edme, \u017ee asi 1,5 sekundy za sekundu, t. j. ak bud\u00fa dve jadr\u00e1, bude to dobr\u00e9. Bude to asi s 75 % za\u0165a\u017een\u00edm. A takto to bude fungova\u0165. Ak budeme ma\u0165 10 jadier, budeme \u00faplne v pokoji. Takto m\u00f4\u017eeme hodnoti\u0165 zdroje.<\/p>\n<p><\/p>\n<p>K002 \u2013 naz\u00fdvam to triedy po\u017eiadaviek, t. j. SELECT, INSERT, UPDATE, DELETE. A osobitne SELECT FOR UPDATE, preto\u017ee zablokuje. <\/p>\n<p><\/p>\n<p>A tu m\u00f4\u017eeme vyvodi\u0165 z\u00e1ver, \u017ee be\u017en\u00e9 \u010d\u00edtacie SELECTy \u2013 82 % zo v\u0161etk\u00fdch volan\u00ed, ale pritom \u2013 74 % z celkov\u00e9ho \u010dasu. T. j. sa \u010dasto volaj\u00fa, ale spotrebov\u00e1vaj\u00fa menej zdrojov. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0e0234e696ffe1c80f8b5acccc602dbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A vraciame sa k ot\u00e1zke: \u201eAko spr\u00e1vne nastavi\u0165 shared_buffers?\u201c. Pozorujem, \u017ee v\u00e4\u010d\u0161ina benchmarkov je zalo\u017een\u00e1 na my\u0161lienke \u2013 pozrime sa, ak\u00fd throughput dosiahneme, t. j. ak\u00e1 bude priepustnos\u0165. Meria sa obvykle v TPS alebo QPS.<\/p>\n<p><\/p>\n<p>A sna\u017e\u00edme sa vy\u017em\u00fdka\u0165 z ma\u0161iny \u010do najviac transakci\u00ed za sekundu pomocou parametrov ladenia. Tu je presne 311 za sekundu pre select.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d5e73f2672d94bb92eed921ef3dfe0b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale nikto nechod\u00ed do pr\u00e1ce a sp\u00e4\u0165 domov autom na pln\u00fd v\u00fdkon. To je hl\u00fape. Rovnako je to aj s datab\u00e1zami. Nemali by sme jazdi\u0165 na pln\u00fd v\u00fdkon, nikto to ani nerob\u00ed. Nikto ne\u017eije v produkcii s 100% CPU. Hoci mo\u017eno niekto \u00e1no, nie je to dobr\u00e9. <\/p>\n<p><\/p>\n<p>Idea je tak\u00e1, \u017ee obvykle jazd\u00edme pri 20 % na\u0161ich mo\u017enost\u00ed, ide\u00e1lne nie viac ako 50 %. A sna\u017e\u00edme sa optimalizova\u0165 odozvu pre na\u0161ich pou\u017e\u00edvate\u013eov predov\u0161etk\u00fdm. T. j. na\u0161e ovl\u00e1da\u010de by mali by\u0165 nastaven\u00e9 tak, aby minimalizovali latenciu pri 20 % r\u00fdchlosti, podmiene\u010dne. Toto je my\u0161lienka, ktor\u00fa sa sna\u017e\u00edme vyu\u017ei\u0165 aj v na\u0161ich experimentoch.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c32c2cfb76dce0ce6b50e929bbf914ac.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A na z\u00e1ver odpor\u00fa\u010dania: <\/p>\n<p><\/p>\n<ul>\n<li>Ur\u010dite vytvorte Database Lab.<\/li>\n<li>Ak je to mo\u017en\u00e9, robte na po\u017eiadanie, aby sa rozv\u00edjalo na nejak\u00fd \u010das \u2013 hrali sa a potom zahodili. Ak m\u00e1te cloud, je to samozrejm\u00e9, t. j. majte ve\u013ea st\u00e1lych. <\/li>\n<li>Bu\u010fte zvedav\u00ed. A pokud n\u011bco nen\u00ed v po\u0159\u00e1dku, prov\u011b\u0159te to experimenty, jak se to chov\u00e1. Nancy lze pou\u017e\u00edt k vlastn\u00edmu u\u010den\u00ed, abyste prov\u011b\u0159ili, jak datab\u00e1ze funguje.<\/li>\n<li>A m\u00ed\u0159te na minim\u00e1ln\u00ed dobu odezvy. <\/li>\n<li>A nebojte se zdrojov\u00fdch k\u00f3d\u016f Postgres. Kdy\u017e pracujete se zdrojov\u00fdmi k\u00f3dy, mus\u00edte zn\u00e1t angli\u010dtinu. Je tam spousta koment\u00e1\u0159\u016f, v\u0161echno je vysv\u011btleno. <\/li>\n<li>A pravideln\u011b kontrolujte zdrav\u00ed datab\u00e1ze, alespo\u0148 jednou za t\u0159i m\u011bs\u00edce ru\u010dn\u011b nebo podle Postgres-checkup. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8d2ac05f44bd245b879799862ce9e233.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ot\u00e1zky<\/p>\n<p><\/p>\n<p><em>D\u011bkuji moc! To je velmi zaj\u00edmav\u00e1 v\u011bc.<\/em> <\/p>\n<p><\/p>\n<p>Dva kusy.<\/p>\n<p><\/p>\n<p><em>Ano, dva kusy. Jen\u017ee jsem \u00fapln\u011b nepochopil. Kdy\u017e pracujeme s Nancy, m\u016f\u017eeme ladit pouze jeden parametr nebo celou skupinu?<\/em><\/p>\n<p><\/p>\n<p>M\u00e1me delta-config parametr. M\u016f\u017eete tam najednou upravit kolik chcete. Ale mus\u00edte ch\u00e1pat, \u017ee kdy\u017e m\u011bn\u00edte hodn\u011b v\u011bc\u00ed, m\u016f\u017eete doj\u00edt k chybn\u00fdm z\u00e1v\u011br\u016fm. <\/p>\n<p><\/p>\n<p><em>Ano. Pro\u010d se pt\u00e1m? Proto\u017ee je obt\u00ed\u017en\u00e9 prov\u00e1d\u011bt experimenty, kdy\u017e m\u00e1te pouze jeden parametr. Ten uprav\u00edte, pod\u00edv\u00e1te se, jak to funguje. Nastav\u00edte ho. Pak za\u010dnete s dal\u0161\u00edm.<\/em><\/p>\n<p><\/p>\n<p>M\u016f\u017eete ladit v\u00edce parametr\u016f najednou, ale samoz\u0159ejm\u011b to z\u00e1vis\u00ed na situaci. Ale je lep\u0161\u00ed testovat jednu my\u0161lenku. V\u010dera jsme m\u011bli n\u00e1pad. M\u011bli jsme velmi podobnou situaci. Byly dva konfigurace. A nemohli jsme pochopit, pro\u010d je tak velk\u00fd rozd\u00edl. A objevila se my\u0161lenka, \u017ee pot\u0159ebujeme pou\u017e\u00edt dichotomii, abychom postupn\u011b porozum\u011bli a na\u0161li, v \u010dem je rozd\u00edl. M\u016f\u017eeme ihned ud\u011blat polovinu parametr\u016f stejn\u00fdch, pak \u010dtvrtinu atd. V\u0161echno je flexibiln\u00ed.<\/p>\n<p><\/p>\n<p><em>A je\u0161t\u011b je tu ot\u00e1zka. Projekt je mlad\u00fd, vyv\u00edj\u00ed se. Je u\u017e dokumentace p\u0159ipravena, je podrobn\u00e9 popis?<\/em><\/p>\n<p><\/p>\n<p>Odkazoval jsem se na popis parametr\u016f. To je. Ale zat\u00edm moc v\u011bc\u00ed je\u0161t\u011b nen\u00ed. Hled\u00e1m podobn\u011b sm\u00fd\u0161lej\u00edc\u00ed lidi. A nach\u00e1z\u00edm je, kdy\u017e vystupuji. To je velmi super. N\u011bkdo u\u017e se mnou pracuje, n\u011bkdo pomohl a n\u011bco ud\u011blal. A pokud v\u00e1s toto t\u00e9ma zaj\u00edm\u00e1, dejte pros\u00edm zp\u011btnou vazbu \u2013 co chyb\u00ed. <\/p>\n<p><\/p>\n<p><em>A\u017e ud\u011bl\u00e1me laborato\u0159, mo\u017en\u00e1 budu m\u00edt zp\u011btnou vazbu. Uvid\u00edme. D\u011bkuji!<\/em><\/p>\n<p><\/p>\n<p><em>Dobr\u00fd den! D\u011bkuji za p\u0159edn\u00e1\u0161ku! Vid\u011bl jsem, \u017ee je podpora pro Amazon. Pl\u00e1nuje se podpora GSP?<\/em><\/p>\n<p><\/p>\n<p>Dobr\u00fd dotaz. Za\u010dali jsme s t\u00edm. A zat\u00edm jsme to pozastavili, proto\u017ee chceme \u0161et\u0159it. T. j. je zde podpora prost\u0159ednictv\u00edm b\u011bhu na localhostu. M\u016f\u017eete si vytvo\u0159it instanci a pracovat lok\u00e1ln\u011b. Mimochodem, tak to d\u011bl\u00e1me. V Getlab to d\u011bl\u00e1m takto, tam na GSP. Ale zat\u00edm nevid\u00edme smysl v t\u00e9to orkestraci, proto\u017ee Google nem\u00e1 levn\u00e9 spoty. Existuj\u00ed ??? instance, ale maj\u00ed omezen\u00ed. Za prv\u00e9, v\u017edy maj\u00ed pouze slevu 70 % a nelze si s cenou hr\u00e1t. U spot\u016f zvy\u0161ujeme cenu o 5-10 %, abychom sn\u00ed\u017eili pravd\u011bpodobnost, \u017ee v\u00e1s vy\u0159ad\u00ed. T. j. u spot\u016f \u0161et\u0159\u00edte, ale kdykoliv v\u00e1s mohou vz\u00edt. Pokud nastav\u00edte cenu o n\u011bco vy\u0161\u0161\u00ed ne\u017e ostatn\u00ed, pozd\u011bji v\u00e1s vy\u0159ad\u00ed. U Google je \u00fapln\u011b jin\u00e1 specifikace. A je zde je\u0161t\u011b velmi nep\u0159\u00edjemn\u00e9 omezen\u00ed \u2013 \u017eij\u00ed pouze 24 hodin. A n\u011bkdy chceme experiment provozovat 5 dn\u00ed. Ale na spotech to ud\u011blat lze, spoty n\u011bkdy \u017eij\u00ed m\u011bs\u00edce. <\/p>\n<p><\/p>\n<p><em>Dobr\u00fd den! D\u011bkuji za prezentaci! Zm\u00ednili jste se o checkupu. Jak vypo\u010d\u00edt\u00e1v\u00e1te chyby stat_statements?<\/em><\/p>\n<p><\/p>\n<p>Velmi dobr\u00fd dotaz. Mohu to podrobn\u011b uk\u00e1zat a vysv\u011btlit. Stru\u010dn\u011b \u2013 sledujeme, jak se pohybovala skupina dotaz\u016f: kolik jich selhalo a kolik nov\u00fdch se objevilo. A pak sledujeme dv\u011b metriky: total_time a calls, proto existuj\u00ed dv\u011b chyby. A d\u00edv\u00e1me se, jak\u00fd je p\u0159\u00edsp\u011bvek selhan\u00fdch skupin. Existuj\u00ed dv\u011b podskupiny: odchoz\u00ed a p\u0159\u00edchoz\u00ed. Zkoum\u00e1me, jak\u00fd maj\u00ed p\u0159\u00edsp\u011bvek do celkov\u00e9ho obrazu. <\/p>\n<p><\/p>\n<p><em>Neboj\u00edte se, \u017ee se tam b\u011bhem \u010dasu mezi snapshoty znovu rozto\u010d\u00ed dvakr\u00e1t nebo t\u0159ikr\u00e1t?<\/em><\/p>\n<p><\/p>\n<p>T. j. znovu se zaregistrovali nebo jak?<\/p>\n<p><\/p>\n<p><em>Nap\u0159\u00edklad, tento dotaz byl jednou ji\u017e vy\u0159azen, pak se vr\u00e1til a znovu byl vy\u0159azen, a pak se vr\u00e1til znovu a znovu byl vy\u0159azen. A co jste tady spo\u010d\u00edtali, a kde je to v\u0161echno?<\/em><\/p>\n<p><\/p>\n<p>Dobr\u00fd dotaz, je t\u0159eba se pod\u00edvat. <\/p>\n<p><\/p>\n<p><em>J\u00e1 jsem n\u011bco podobn\u00e9ho d\u011blal. Samoz\u0159ejm\u011b jednodu\u0161eji, d\u011blal jsem to s\u00e1m. Ale musel jsem to resetovat, ud\u011blat reset stat_statements a orientovat se v okam\u017eiku snapshotu, \u017ee tam je m\u00e9n\u011b ur\u010dit\u00e9ho pod\u00edlu, \u017ee se stejn\u011b nedostalo k maximu, kolik stat_statements m\u016f\u017ee nashrom\u00e1\u017edit. A orientuji se, \u017ee pravd\u011bpodobn\u011b nic nebylo vy\u0159azeno.<\/em> <\/p>\n<p><\/p>\n<p>Ano-ano. <\/p>\n<p><\/p>\n<p><em>Ale jak jinak ud\u011blat spolehliv\u011b, to v\u016fbec nech\u00e1pu.<\/em><\/p>\n<p><\/p>\n<p>Bohu\u017eel si p\u0159esn\u011b nepamatuji \u2013 zda tam pou\u017e\u00edv\u00e1me text dotazu nebo queryid s pg_stat_statements a na to se orientujeme. Pokud se orientujeme na queryid, pak srovn\u00e1v\u00e1me srovnateln\u00e9 v\u011bci. <\/p>\n<p><\/p>\n<p><em>Nie, m\u00f4\u017ee sa prekr\u00fdva\u0165 nieko\u013ekokr\u00e1t medzi snapshotmi a vr\u00e1ti\u0165 sa sp\u00e4\u0165.<\/em><\/p>\n<p><\/p>\n<p>S t\u00fdmto id?<\/p>\n<p><\/p>\n<p><em>\u00c1no.<\/em> <\/p>\n<p><\/p>\n<p>Toto presk\u00famame. Dobr\u00e1 ot\u00e1zka. Mus\u00edme to presk\u00fama\u0165. Ale zatia\u013e to, \u010do vid\u00edme, je bu\u010f 0 nap\u00edsan\u00e9...<\/p>\n<p><\/p>\n<p><em>Toto je s\u00edce zriedkav\u00fd pr\u00edpad, ale bolo to pre m\u0148a \u0161okuj\u00face, ke\u010f som sa dozvedel, \u017ee stat_statements tam m\u00f4\u017ee prekr\u00fdva\u0165.<\/em> <\/p>\n<p><\/p>\n<p>V Pg_stat_statements m\u00f4\u017ee by\u0165 ve\u013ea vec\u00ed. Stretli sme sa s t\u00fdm, \u017ee ak m\u00e1te track_utility = on, tak sa aj va\u0161e sety sleduj\u00fa. <\/p>\n<p><\/p>\n<p><em>\u00c1no, samozrejme.<\/em><\/p>\n<p><\/p>\n<p>A ak m\u00e1te Java Hibernate, ktor\u00fd je n\u00e1hodn\u00fd, tak sa tam za\u010d\u00edna zamyka\u0165 hash-tablica. A ke\u010f len vypnete ve\u013emi za\u0165a\u017een\u00fa aplik\u00e1ciu, m\u00e1te 50-100 skup\u00edn. A je to celkom stabiln\u00e9. Jeden zo sp\u00f4sobov, ako s t\u00fdm bojova\u0165, je zv\u00fd\u0161i\u0165 pg_stat_statements.max. <\/p>\n<p><\/p>\n<p><em>\u00c1no, ale mus\u00edte vedie\u0165, o ko\u013eko. A nejak\u00fdm sp\u00f4sobom to mus\u00edte sledova\u0165. Ja to tak rob\u00edm. T. j. m\u00e1m pg_stat_statements.max. A sledujem, \u017ee v momente snapshotu som nedosiahol 70 %. Dobre, znamen\u00e1 to, \u017ee sme ni\u010d nestratili. Rob\u00edme reset. A op\u00e4\u0165 zbierame. Ak je v nasleduj\u00facom snapshotu menej ako 70, znamen\u00e1 to, \u017ee pravdepodobne sme znovu ni\u010d nestratili.<\/em><\/p>\n<p><\/p>\n<p>\u00c1no. Predvolene to teraz je 5 000. A mnoh\u00fdm z toho sta\u010d\u00ed. <\/p>\n<p><\/p>\n<p><em>Zvy\u010dajne - \u00e1no.<\/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=\"Prehrajte video\" 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. Od seba dod\u00e1m, \u017ee ak s\u00fa v Postgres citliv\u00e9 d\u00e1ta a nemaj\u00fa prenikn\u00fa\u0165 do testovacieho prostredia, je mo\u017en\u00e9 vyu\u017ei\u0165 <noindex><a rel=\"nofollow\" href=\"https:\/\/labs.dalibo.com\/postgresql_anonymizer\">PostgreSQL Anonymizer<\/a><\/noindex>. Sch\u00e9ma je pribli\u017ene tak\u00e1to:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami&quot;. Nikolaj Samokhvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cf9a2052364e09a20e68d2347dd0c25b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Zdroj: <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.3 - 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\/sk\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"sk_SK\" \/>\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\/sk\/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\udd47Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami\u00bb. Nikolaj Samokhvalov | ProHoster","description":"Navrhujem obozn\u00e1mi\u0165 sa s prepisom predn\u00e1\u0161ky Nikolaja Samokhvalova \"Priemyseln\u00fd pr\u00edstup k laden\u00ed PostgreSQL: experimenty s datab\u00e1zami\" Shared_buffers = 25% \u2013 je to ve\u013ea alebo m\u00e1lo?","canonical_url":"https:\/\/prohoster.info\/sk\/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":"sk_SK","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\/sk\/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\/sk\/wp-json\/wp\/v2\/posts\/78742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/comments?post=78742"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/posts\/78742\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media\/78743"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/media?parent=78742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/categories?post=78742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sk\/wp-json\/wp\/v2\/tags?post=78742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}