{"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\/cs\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","title":{"rendered":"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi \u00bb. Nikolaj Samohvalov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Doporu\u010duji se sezn\u00e1mit s anal\u00fdzou zpr\u00e1vy Nikolaje Samochvalova \"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi\"<\/strong><\/p>\n<p><\/p>\n<p>Shared_buffers = 25% \u2013 je to hodn\u011b nebo m\u00e1lo? Nebo akor\u00e1t spr\u00e1vn\u011b? Jak pochopit, zda je tato \u2013 dost zastaral\u00e1 \u2013 doporu\u010den\u00ed vhodn\u00e1 pro v\u00e1\u0161 konkr\u00e9tn\u00ed p\u0159\u00edpad?<\/p>\n<p><\/p>\n<p>Nastal \u010das p\u0159istoupit k ot\u00e1zce v\u00fdb\u011bru parametr\u016f postgresql.conf \"seri\u00f3zn\u011b\". Ne pomoc\u00ed slep\u00fdch \"autoladitel\u016f\" nebo zastaral\u00fdch rad z \u010dl\u00e1nk\u016f a blog\u016f, ale na z\u00e1klad\u011b:<\/p>\n<p><\/p>\n<ol>\n<li>p\u0159esn\u011b prov\u00e1d\u011bn\u00fdch experiment\u016f na DB, realizovan\u00fdch automatizovan\u011b, ve velk\u00e9m mno\u017estv\u00ed a za podm\u00ednek maxim\u00e1ln\u011b p\u0159ibl\u00ed\u017een\u00fdch \"boji\u0161t\u011b\".<\/li>\n<li>hlubok\u00e9ho porozum\u011bn\u00ed specifik\u016fm fungov\u00e1n\u00ed DBMS a OS.<\/li>\n<\/ol>\n<p><\/p>\n<p>Pou\u017eit\u00edm Nancy CLI (<noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres.ai\/nancy\">https:\/\/gitlab.com\/postgres.ai\/nancy<\/a><\/noindex>), se pod\u00edv\u00e1me na konkr\u00e9tn\u00ed p\u0159\u00edklad \u2013 notoricky zn\u00e1m\u00e9 shared_buffers \u2013 v r\u016fzn\u00fdch situac\u00edch, v r\u016fzn\u00fdch projektech a pokus\u00edme se p\u0159ij\u00edt na to, jak optim\u00e1ln\u011b nastavit na\u0161i infrastrukturu, datab\u00e1zi a z\u00e1t\u011b\u017e.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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>Bude \u0159e\u010d o experimentech s datab\u00e1zemi. Toto je p\u0159\u00edb\u011bh, kter\u00fd trv\u00e1 n\u011bco m\u00e1lo p\u0159es p\u016fl roku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/e77255c4abaa2a01ae37fe888fe67db7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Trochu o m\u011b. Zku\u0161enosti s Postgres m\u00e1m u\u017e v\u00edce ne\u017e 14 let. Zalo\u017eil jsem n\u011bkolik spole\u010dnost\u00ed v oblasti soci\u00e1ln\u00edch s\u00edt\u00ed. V\u0161ude byl Postgres pou\u017e\u00edv\u00e1n a je pou\u017e\u00edv\u00e1n.<\/p>\n<p><\/p>\n<p>Tak\u00e9 skupina RuPostgres na Meetupu, 2. m\u00edsto na sv\u011bt\u011b. Pomalu se bl\u00ed\u017e\u00edme k 2 000 lidem. RuPostgres.org.<\/p>\n<p><\/p>\n<p>A na r\u016fzn\u00fdch konferenc\u00edch, v\u010detn\u011b Highload, odpov\u00edd\u00e1m za datab\u00e1ze, zejm\u00e9na Postgres od jeho zalo\u017een\u00ed.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/fa009b50f1c0293eee80fc03fd2c05f0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A v posledn\u00edch n\u011bkolika letech jsem restartoval svou praxi v oblasti poradenstv\u00ed k Postgres v 11 \u010dasov\u00fdch p\u00e1smech odtud.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/550c3ddcd516adca14d2b3de9c34b7ff.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A kdy\u017e jsem to ud\u011blal p\u0159ed n\u011bkolika lety, m\u011bl jsem ur\u010dit\u00fd p\u0159est\u00e1vku od aktivn\u00ed manu\u00e1ln\u00ed pr\u00e1ce s Postgres, pravd\u011bpodobn\u011b od roku 2010. Byl jsem p\u0159ekvapen, jak m\u00e1lo se zm\u011bnily pracovn\u00ed dny DBA, jak moc je st\u00e1le pot\u0159eba pou\u017e\u00edvat manu\u00e1ln\u00ed pr\u00e1ci. A hned m\u011b napadlo, \u017ee tu n\u011bco nen\u00ed v po\u0159\u00e1dku, je t\u0159eba v\u00edce automatizovat.<\/p>\n<p><\/p>\n<p>A proto\u017ee to v\u0161echno bylo v cloudu, v\u011bt\u0161ina klient\u016f byla v oblac\u00edch. A u\u017e bylo mnoho v\u011bc\u00ed automatizov\u00e1no, to je o\u010dividn\u00e9. O tom v\u00edce pozd\u011bji. Tedy z toho v\u0161eho vze\u0161la my\u0161lenka, \u017ee by m\u011bl b\u00fdt soubor n\u00e1stroj\u016f, t.j. jak\u00e1si platforma, kter\u00e1 by automatizovala prakticky v\u0161echny \u00fakony DBA, abychom mohli spravovat velk\u00e9 mno\u017estv\u00ed datab\u00e1z\u00ed.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b02097f0bb23fa9d7c30e38ab9f3de7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V t\u00e9to zpr\u00e1v\u011b nebudou:<\/p>\n<p><\/p>\n<ul>\n<li>\"St\u0159\u00edbrn\u00e9 kulky\" a tvrzen\u00ed typu \u2013 dejte 8 GB nebo 25 % shared_buffers a budete spokojeni. O shared_buffers nebude tolik. <\/li>\n<li>Hardwarov\u00e9 \"vnit\u0159nosti\". <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ccea359ec78a967c150ddcedc3e50a88.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Co se stane?<\/p>\n<p><\/p>\n<ul>\n<li>Budou zde principy optimalizace, kter\u00e9 pou\u017e\u00edv\u00e1me a rozv\u00edj\u00edme. Budou r\u016fzn\u00e9 n\u00e1pady, kter\u00e9 n\u00e1m p\u0159ich\u00e1zej\u00ed na mysl a r\u016fzn\u00e1 n\u00e1stroje, kter\u00e9 vytv\u00e1\u0159\u00edme v\u011bt\u0161inou v Open Source, tj. z\u00e1klady d\u011bl\u00e1me v Open Source. Nav\u00edc m\u00e1me tikety, ve\u0161ker\u00e1 komunikace prob\u00edh\u00e1 prakticky v Open Source. M\u016f\u017eete sledovat, co pr\u00e1v\u011b d\u011bl\u00e1me, co bude v n\u00e1sleduj\u00edc\u00ed verzi atd. <\/li>\n<li>A tak\u00e9 n\u011bjak\u00e9 zku\u0161enosti s pou\u017e\u00edv\u00e1n\u00edm t\u011bchto princip\u016f a n\u00e1stroj\u016f v \u0159ad\u011b firem: od mal\u00fdch startup\u016f po velk\u00e9 spole\u010dnosti. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86e6f379726dec814aca616a30d36a2f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak se to v\u0161echno vyv\u00edj\u00ed?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c1422d9f2945e717c6ebb31f5fef6329.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Za prv\u00e9, hlavn\u00edm \u00fakolem DBA krom\u011b zaji\u0161t\u011bn\u00ed vytv\u00e1\u0159en\u00ed instances, nasazov\u00e1n\u00ed z\u00e1loh atd. je hled\u00e1n\u00ed \u00fazk\u00fdch m\u00edst a optimalizace v\u00fdkonu.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8e682ca8666e94d6688348ebe4540327.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nyn\u00ed je to uspo\u0159\u00e1d\u00e1no t\u00edmto zp\u016fsobem. Sledujeme monitoring, n\u011bco vid\u00edme, chyb\u00ed n\u00e1m n\u011bjak\u00e9 podrobnosti. Za\u010d\u00edn\u00e1me se podrobn\u011bji prohrabovat, obvykle ru\u010dn\u011b, a ch\u00e1peme, co s t\u00edm d\u011blat tak \u010di onak.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/04599422ea6b6d57c79a4e48ba2db8b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Existuj\u00ed dva p\u0159\u00edstupy. Pg_stat_statements \u2013 standardn\u00ed v\u00fdchoz\u00ed \u0159e\u0161en\u00ed pro identifikaci pomal\u00fdch dotaz\u016f. A anal\u00fdza log\u016f Postgres pomoc\u00ed pgBadger.<\/p>\n<p><\/p>\n<p>Ka\u017ed\u00fd z p\u0159\u00edstup\u016f m\u00e1 v\u00e1\u017en\u00e9 nedostatky. U prvn\u00edho p\u0159\u00edstupu m\u00e1me vyhozeny v\u0161echny parametry. A pokud vid\u00edme skupiny SELECT * FROM table where sloupec odpov\u00edd\u00e1 znaku \u201e?\u201c nebo \u201e$\u201c od verze Postgres 10. Nev\u00edme, zda jde o index scan nebo seq scan. Velmi siln\u011b to z\u00e1vis\u00ed na parametru. Pokud tam dosad\u00edte hodnotu, kter\u00e1 se vyskytuje z\u0159\u00eddka, bude to index scan. Pokud tam dosad\u00edte hodnotu, kter\u00e1 tvo\u0159\u00ed 90 % tabulky, bude to o\u010dividn\u011b seq scan, proto\u017ee Postgres zn\u00e1 statistiku. A to je velk\u00fd nedostatek pg_stat_statements, i kdy\u017e se n\u011bjak\u00e9 pr\u00e1ce prov\u00e1d\u011bj\u00ed.<\/p>\n<p><\/p>\n<p>U anal\u00fdz log\u016f je nejv\u011bt\u0161\u00ed nedostatek v tom, \u017ee si v\u011bt\u0161inou nem\u016f\u017eete dovolit \u201elog_min_duration_statement = 0\u201c. O tom tak\u00e9 budeme mluvit. Podle toho nevid\u00edte cel\u00fd obraz. A n\u011bjak\u00fd dotaz, kter\u00fd je velmi rychl\u00fd, m\u016f\u017ee spot\u0159ebov\u00e1vat obrovsk\u00e9 mno\u017estv\u00ed zdroj\u016f, ale neuvid\u00edte ho, proto\u017ee je pod va\u0161\u00edm prahem.<\/p>\n<p><\/p>\n<p><strong>Jak DBA \u0159e\u0161\u00ed nalezen\u00e9 probl\u00e9my?<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/5e57fefdec6256f82045aaac037b516c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Nap\u0159\u00edklad jsme objevili n\u011bjak\u00fd probl\u00e9m. Co se obvykle d\u011bl\u00e1? Pokud jste v\u00fdvoj\u00e1\u0159, budete n\u011bco d\u011blat na n\u011bjak\u00e9m instance, kter\u00fd nen\u00ed tak velk\u00fd. Pokud jste DBA, m\u00e1te staging. A ten m\u016f\u017ee b\u00fdt jen jeden. A je pozadu o p\u016fl roku. A p\u0159em\u00fd\u0161l\u00edte, \u017ee p\u016fjdete na produkci. A dokonce i zku\u0161en\u00ed DBA to kontroluj\u00ed na produkci, na replikaci. A n\u011bkdy vytvo\u0159\u00ed do\u010dasn\u00fd index, ujist\u00ed se, \u017ee to pom\u00e1h\u00e1, zlikviduj\u00ed ho a vr\u00e1t\u00ed v\u00fdvoj\u00e1\u0159\u016fm, aby ho za\u0159adili do migra\u010dn\u00edch soubor\u016f. Takov\u00e9 nesmysly se te\u010f d\u011bj\u00ed. A to je trag\u00e9die.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/402e80c7e5fbca6a054c0c9ec61e5fe3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Ladit konfigurace.<\/li>\n<li>Optimalizovat sadu index\u016f. <\/li>\n<li>M\u011bnit samotn\u00fd SQL dotaz (to je nejkomplexn\u011bj\u0161\u00ed zp\u016fsob).<\/li>\n<li>P\u0159id\u00e1vat v\u00fdkon (to je v\u011bt\u0161inou nejjednodu\u0161\u0161\u00ed zp\u016fsob).<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8a6b3cd56e0df5c7908714926ba1eb05.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S t\u011bmito v\u011bcmi je toho hodn\u011b. V Postgresu je spousta mo\u017enost\u00ed. Je pot\u0159eba hodn\u011b v\u011bd\u011bt. V Postgresu je mnoho index\u016f, tak\u00e9 d\u00edky organiz\u00e1tor\u016fm t\u00e9to konference. A to v\u0161echno je pot\u0159eba zn\u00e1t, a pr\u00e1v\u011b to u ne-DBA vyvol\u00e1v\u00e1 pocit, \u017ee se DBA zab\u00fdvaj\u00ed \u010dernou magi\u00ed. Tedy je pot\u0159eba se v\u011bnovat tomu 10 let, abyste tomu za\u010dali norm\u00e1ln\u011b rozum\u011bt.<\/p>\n<p><\/p>\n<p>A j\u00e1 jsem bojovn\u00edk proti t\u00e9to \u010dern\u00e9 magii. Chci v\u0161echno d\u011blat tak, aby technologie p\u0159evl\u00e1dala a ne intuice.<\/p>\n<p><\/p>\n<p><strong>P\u0159\u00edklady z praxe<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/70b11c3df9c41c66570635d5f309ed24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>To jsem pozoroval minim\u00e1ln\u011b ve dvou projektech, v\u010detn\u011b sv\u00e9ho. Dal\u0161\u00ed p\u0159\u00edsp\u011bvek na blogu n\u00e1m \u0159\u00edk\u00e1, \u017ee hodnota 1 000 pro default_statistict_target je dobr\u00e1. Dob\u0159e, zkusme to na produkci.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0b4c2bd6f73df06967c94d5d78b07279.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A te\u010f, s pomoc\u00ed na\u0161eho n\u00e1stroje po dvou letech experimentov\u00e1n\u00ed s datab\u00e1zemi, o kter\u00fdch dnes mluv\u00edme, m\u016f\u017eeme porovnat, co bylo a co je nyn\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/3564eade6532003d2279c2736ab6d733.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A k tomu pot\u0159ebujeme vytvo\u0159it experiment. Ten se skl\u00e1d\u00e1 ze \u010dty\u0159 \u010d\u00e1st\u00ed. <\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed \u010d\u00e1st \u2013 to je prost\u0159ed\u00ed. Pot\u0159ebujeme hardware. A kdy\u017e p\u0159ich\u00e1z\u00edm do n\u011bjak\u00e9 firmy a uzav\u00edr\u00e1m smlouvu, \u0159\u00edk\u00e1m, aby mi dali stejn\u00fd hardware jako na produkci. Pro ka\u017ed\u00e9ho z va\u0161ich Majstr\u016f pot\u0159ebuji alespo\u0148 jeden stejn\u00fd hardware. Bu\u010f je to virtu\u00e1ln\u00ed stroj instance na Amazonu nebo Google, nebo pot\u0159ebuji p\u0159esn\u011b takov\u00fd hardware. Tedy chci reprodukovat prost\u0159ed\u00ed. A do pojmu prost\u0159ed\u00ed zahrnujeme hlavn\u00ed verzi Postgres. <\/li>\n<li>Druh\u00e1 \u010d\u00e1st \u2013 to je objekt na\u0161ich v\u00fdzkum\u016f. To je datab\u00e1ze. M\u016f\u017ee b\u00fdt vytvo\u0159ena n\u011bkolika zp\u016fsoby. Uk\u00e1\u017eu jak. <\/li>\n<li>T\u0159et\u00ed \u010d\u00e1st \u2013 to je z\u00e1t\u011b\u017e. To je nejkomplexn\u011bj\u0161\u00ed moment. <\/li>\n<li>A \u010dtvrt\u00e1 \u010d\u00e1st je to, co kontrolujeme, t.j. co s \u010d\u00edm porovn\u00e1me. P\u0159edpokl\u00e1dejme, \u017ee m\u016f\u017eeme v konfiguraci zm\u011bnit jeden nebo v\u00edce parametr\u016f, nebo m\u016f\u017eeme vytvo\u0159it index atd. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8ccd09ac4cdd336618413375617c10a6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zahajujeme experiment. Tady je pg_stat_statements. Vlevo \u2013 co bylo. Vpravo \u2013 co se stalo.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/6f82d7e25a77b700f2ddb01f42a861a2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Vlevo default_statistics_target = 100, vpravo = 1 000. Vid\u00edme, \u017ee n\u00e1m to pomohlo. Celkov\u011b se to zlep\u0161ilo o 8 %.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/52dda8fb5fbcb9beecaa1eda8443a374.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale pokud se posuneme dol\u016f, uvid\u00edme skupiny dotaz\u016f z pgBadger nebo z pg_stat_statements. Jsou zde dv\u011b mo\u017enosti. Uvid\u00edme, \u017ee n\u011bjak\u00fd dotaz klesl o 88 %. A zde u\u017e je in\u017een\u00fdrsk\u00fd p\u0159\u00edstup. M\u016f\u017eeme kopat hloub\u011bji, proto\u017ee je zaj\u00edmav\u00e9, pro\u010d klesl. Je t\u0159eba pochopit, co se d\u011blo se statistikou. Pro\u010d v\u011bt\u0161\u00ed po\u010det bucket\u016f ve statistice vede k hor\u0161\u00edmu v\u00fdsledku.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4e6dfc04af4b3fc2b626dd46c08bdf66.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u016f\u017eeme ale tak\u00e9 nekopat a prov\u00e9st \u201eALTER TABLE \u2026 ALTER COLUMN\u201c a vr\u00e1t\u00edme mu zp\u011bt 100 bucket\u016f ve statistice tohoto sloupce. A d\u00e1le m\u016f\u017eeme experiment\u00e1ln\u011b ov\u011b\u0159it, \u017ee tato oprava pomohla. To je in\u017een\u00fdrsk\u00fd p\u0159\u00edstup, kter\u00fd n\u00e1m pom\u00e1h\u00e1 vid\u011bt celkov\u00fd obraz a rozhodovat se na z\u00e1klad\u011b dat, nikoli na z\u00e1klad\u011b intuice.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c87df172b994a4523478beebaf4a0275.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/9e766aa4348bd1a77b697e3179d84bbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u00e1r p\u0159\u00edklad\u016f z jin\u00fdch oblast\u00ed. V testech jsou CI testy u\u017e mnoho let. A \u017e\u00e1dn\u00fd rozumn\u00fd projekt u\u017e nebude \u017e\u00edt bez automatick\u00fdch test\u016f.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b6c21981dc8b2b285973ae42199e2b4c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V jin\u00fdch oborech: v letectv\u00ed, automobilov\u00e9m pr\u016fmyslu, kdy\u017e testujeme aerodynamiku, m\u00e1me tak\u00e9 mo\u017enost prov\u00e1d\u011bt experimenty. Nezah\u00e1j\u00edme n\u011bco rovnou z v\u00fdkresu do vesm\u00edru, ani nevyjedeme \u017e\u00e1dn\u00e9 auto rovnou na silnici. Nap\u0159\u00edklad existuje aerodynamick\u00fd tunel. <\/p>\n<p><\/p>\n<p>Z pozorov\u00e1n\u00ed v jin\u00fdch oblastech m\u016f\u017eeme vyvodit z\u00e1v\u011bry. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cef02ea9280e5795d1e593c600f0541b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Za prv\u00e9, m\u00e1me speci\u00e1ln\u00ed prost\u0159ed\u00ed. To je bl\u00edzko produkce, ale nen\u00ed bl\u00edzko. Jeho hlavn\u00edm rysem je, \u017ee by m\u011blo b\u00fdt levn\u00e9, opakovateln\u00e9 a maxim\u00e1ln\u011b automatizovan\u00e9. A tak\u00e9 by m\u011bly b\u00fdt speci\u00e1ln\u00ed n\u00e1stroje pro prov\u00e1d\u011bn\u00ed podrobn\u00e9 anal\u00fdzy.<\/p>\n<p><\/p>\n<p>Pravd\u011bpodobn\u011b, kdy\u017e jsme spustili letadlo a let\u00edme, nem\u00e1me tolik p\u0159\u00edle\u017eitost\u00ed prozkoumat ka\u017ed\u00fd milimetr povrchu k\u0159\u00eddla jako v aerodynamick\u00e9m tunelu. M\u00e1me v\u00edce prost\u0159edk\u016f pro diagnostiku. M\u016f\u017eeme si dovolit p\u0159\u00edv\u011bsit v\u00edce t\u011b\u017ek\u00fdch v\u011bc\u00ed, ne\u017e si m\u016f\u017eeme dovolit m\u00edt na letadle ve vzduchu. Stejn\u011b je to s Postgres. V n\u011bkter\u00fdch p\u0159\u00edpadech m\u016f\u017eeme p\u0159i experimentech zapnout \u00fapln\u00e9 logov\u00e1n\u00ed dotaz\u016f. A na produkci to d\u011blat nechceme. Mo\u017en\u00e1 to dokonce zapneme pomoc\u00ed auto_explain.<\/p>\n<p><\/p>\n<p>A jak jsem ji\u017e \u0159ekl, vysok\u00e1 \u00farove\u0148 automatizace znamen\u00e1, \u017ee jsme stiskli tla\u010d\u00edtko a zopakovali to. Tak by to m\u011blo b\u00fdt, aby bylo hodn\u011b experiment\u016f, aby to bylo v procesu.<\/p>\n<p><\/p>\n<p>Nancy CLI \u2013 z\u00e1klad \"laborato\u0159e DB\"<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/f8303df21f309fb209929acec260b757.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tak jsme vytvo\u0159ili tuto v\u011bc. Tedy, o t\u011bchto n\u00e1padech jsem mluvil v \u010dervnu, t\u00e9m\u011b\u0159 p\u0159ed rokem. A u\u017e m\u00e1me v Open Source takzvanou Nancy CLI. To je z\u00e1klad pro vybudov\u00e1n\u00ed laborato\u0159e datab\u00e1ze.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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 Je to v Open Source, na Gitlab. M\u016f\u017eete \u0159\u00edct, m\u016f\u017eete zkusit. Dal jsem odkaz na slajdech. M\u016f\u017eete kliknout a tam bude <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/nancy\/-\/blob\/master\/help\/nancy_run.md\">help<\/a><\/noindex> ve v\u0161ech parametrech.<\/p>\n<p><\/p>\n<p>Samoz\u0159ejm\u011b, \u017ee tam je je\u0161t\u011b mnoho ve v\u00fdvoji. Je tam spousta n\u00e1pad\u016f. Ale to u\u017e je n\u011bco, co prakticky pou\u017e\u00edv\u00e1me ka\u017ed\u00fd den. A kdy\u017e m\u00e1me n\u00e1pad \u2013 co se stane, kdy\u017e p\u0159i maz\u00e1n\u00ed 40 000 000 \u0159\u00e1dk\u016f se v\u0161e zasekne v IO, pak m\u016f\u017eeme prov\u00e9st experiment a pod\u00edvat se podrobn\u011bji, abychom pochopili, co se d\u011bje a pokusili se to opravit za b\u011bhu. Tedy prov\u00e1d\u00edme experiment. Nap\u0159\u00edklad n\u011bco uprav\u00edme a pod\u00edv\u00e1me se, co z toho nakonec dostaneme. A d\u011bl\u00e1me to ne na produkci. To je podstata my\u0161lenky.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c22d332de4b45d604dfb9c5a95404fd6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kde to m\u016f\u017ee fungovat? M\u016f\u017ee to fungovat lok\u00e1ln\u011b, tedy to m\u016f\u017eete d\u011blat kdekoliv, dokonce i na MacBooku. Pot\u0159ebujete Docker, jedeme. A je to. M\u016f\u017eete to spustit na n\u011bjak\u00e9m instance na \u017eeleze, nebo ve virtu\u00e1lce, kdekoliv.<\/p>\n<p><\/p>\n<p>A je zde tak\u00e9 mo\u017enost spu\u0161t\u011bn\u00ed vzd\u00e1len\u011b v Amazonu na EC2 Instance, ve spotech. A to je skute\u010dn\u011b skv\u011bl\u00e1 mo\u017enost. Nap\u0159\u00edklad v\u010dera jsme provedli v\u00edce ne\u017e 500 experiment\u016f na i3 instance, za\u010d\u00ednaje t\u00edm nejmen\u0161\u00edm a kon\u010de i3-16-xlarge. A t\u011bch 500 experiment\u016f n\u00e1s st\u00e1lo 64 dolar\u016f. Ka\u017ed\u00fd trval 15 minut. To znamen\u00e1, \u017ee d\u00edky pou\u017e\u00edv\u00e1n\u00ed spot\u016f je to velmi levn\u00e9 \u2013 sleva 70 %, cenov\u00e1 nab\u00eddka Amazonu se \u00fa\u010dtuje po vte\u0159in\u00e1ch. M\u016f\u017eete d\u011blat velmi mnoho. M\u016f\u017eete prov\u00e1d\u011bt skute\u010dn\u00fd v\u00fdzkum.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/64c61cc9dd7181296b2ef5b815a17493.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A podporov\u00e1ny jsou t\u0159i major verze Postgres. Nen\u00ed tak slo\u017eit\u00e9 doladit n\u011bjak\u00e9 star\u00e9 a tak\u00e9 novou 12. verzi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ee147c91014ab698cbd3c60e193bef23.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Objekt m\u016f\u017eeme zad\u00e1vat t\u0159emi zp\u016fsoby. To je:<\/p>\n<p><\/p>\n<ul>\n<li>Dump\/sql soubor. <\/li>\n<li>Hlavn\u00ed zp\u016fsob je klon PGDATA adres\u00e1\u0159e. Obvykle se bere z backup serveru. Pokud m\u00e1te norm\u00e1ln\u00ed bin\u00e1rn\u00ed z\u00e1lohy, odtud m\u016f\u017eete d\u011blat klony. Pokud m\u00e1te cloud, tak za v\u00e1s to ud\u011bl\u00e1 cloudov\u00e1 firma jako Amazon nebo Google. To je hlavn\u00ed zp\u016fsob klon\u016f skute\u010dn\u00e9ho produk\u010dn\u00edho prost\u0159ed\u00ed. Takhle to rozv\u00edj\u00edme. <\/li>\n<li>A posledn\u00ed zp\u016fsob je vhodn\u00fd pro v\u00fdzkum, kdy\u017e je touha pochopit, jak v Postgres funguje n\u011bjak\u00e1 v\u011bc. To je pgbench. M\u016f\u017eete to vygenerovat pomoc\u00ed pgbench. To je jednodu\u0161e jedna volba \u201edb-pgbench\u201c. \u0158eknete mu, jak\u00fd scale. A v\u0161echno bude v cloudu vygenerov\u00e1no, jak je \u0159e\u010deno.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/2bc3e30cb31a19f66c6b55480447126f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A z\u00e1t\u011b\u017e:<\/p>\n<p><\/p>\n<ul>\n<li>Z\u00e1t\u011b\u017e m\u016f\u017eeme vykon\u00e1vat v jednom vl\u00e1knu SQL. To je nejsaPikodn\u011bj\u0161\u00ed zp\u016fsob. <\/li>\n<li>Nebo m\u016f\u017eeme emulovat z\u00e1t\u011b\u017e. A emulovat ji m\u016f\u017eeme p\u0159edev\u0161\u00edm n\u00e1sleduj\u00edc\u00edm zp\u016fsobem. Mus\u00edme sb\u00edrat v\u0161echny logy. A to je bolestiv\u00e9. Uk\u00e1\u017eu, pro\u010d. A pomoc\u00ed pgreplay p\u0159ehr\u00e1v\u00e1me, kter\u00fd je vestav\u011bn v Nancy. <\/li>\n<li>Nebo jin\u00e1 varianta. Takzvan\u00e1 \u0159emesln\u00e1 z\u00e1t\u011b\u017e, kterou d\u011bl\u00e1me s ur\u010dit\u00fdm mno\u017estv\u00edm \u00fasil\u00ed. Analyzov\u00e1n\u00edm na\u0161\u00ed aktu\u00e1ln\u00ed z\u00e1t\u011b\u017ee na bojov\u00e9m syst\u00e9mu vybereme nejlep\u0161\u00ed skupiny dotaz\u016f. A pomoc\u00ed pgbench m\u016f\u017eeme emulovat tuto z\u00e1t\u011b\u017e v laborato\u0159i. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4adba12881faf9ad9d4f9e9f42c0f5c1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Nebo mus\u00edme vykonat n\u011bjak\u00fd SQL, to znamen\u00e1, kontrolujeme n\u011bjakou migraci, vytv\u00e1\u0159\u00edme index, prov\u00e1d\u00edme ANALYZE. A sledujeme, co bylo p\u0159ed vakuem a po vaku. Obecn\u011b jak\u00fdkoli SQL.<\/li>\n<li>Bu\u010f zm\u011bn\u00edme jeden nebo v\u00edce parametr\u016f v konfiguraci. M\u016f\u017eeme \u0159\u00edct, aby n\u00e1m zkontrolovali nap\u0159\u00edklad 100 hodnot na Amazonu pro na\u0161i terabajtovou datab\u00e1zi. A b\u011bhem n\u011bkolika hodin budete m\u00edt v\u00fdsledek. Obvykle se terabajtov\u00e1 datab\u00e1ze nasazuje n\u011bkolik hodin. Ale ve v\u00fdvoji m\u00e1me patch, co\u017e znamen\u00e1, \u017ee m\u016f\u017eete postupn\u011b na jednom a tom sam\u00e9m serveru pou\u017e\u00edt stejnou pgdata a kontrolovat. Postgres se restartuje, cache se vypr\u00e1zdn\u00ed. A m\u016f\u017eete spou\u0161t\u011bt z\u00e1t\u011b\u017e. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/02985b47bf0ab62e8f1c4cc3ea003f26.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>P\u0159ijde 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_kcache. To jsou dv\u011b roz\u0161\u00ed\u0159en\u00ed, kter\u00e1 analyzuj\u00ed dotazy. A pg_stat_bgwriter obsahuje nejen statistiku pgwriter, ale tak\u00e9 informace o checkpointu a o tom, jak se backendy zbavuj\u00ed \u0161pinav\u00fdch buffer\u016f. A to je v\u0161echno zaj\u00edmav\u00e9 si prohl\u00e9dnout. Nap\u0159\u00edklad kdy\u017e nastavujeme shared_buffers, je velmi zaj\u00edmav\u00e9 pod\u00edvat se, kolik kdo vy\u0159adil.<\/li>\n<li>Tak\u00e9 p\u0159ich\u00e1zej\u00ed logy Postgres. 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, tak bude i jejich v\u00fdstup. A uvid\u00edte latency a TPS. M\u016f\u017eete 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 spustit 100 stejn\u00fdch instanc\u00ed v proudu a prov\u00e9st 100 r\u016fzn\u00fdch b\u011bh\u016f, dostanete 10 000 experiment\u016f. A mus\u00edte se ujistit, \u017ee jste nenarazili na po\u0161kozenou instanci, kterou u\u017e n\u011bkdo zat\u011b\u017euje. Na tomto hardwaru aktivn\u011b pracuj\u00ed dal\u0161\u00ed a v\u00e1m z\u016fst\u00e1v\u00e1 m\u00e1lo zdroj\u016f. Takov\u00e9 v\u00fdsledky je lep\u0161\u00ed vynechat. A pr\u00e1v\u011b d\u00edky sysbench od Alexeje Kopytova prov\u00e1d\u00edme n\u011bkolik kr\u00e1tk\u00fdch kontrol, kter\u00e9 p\u0159ijdou a budete moci 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=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/959690b464a01a4a3cb67ec497becff1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u0159edstavme si, \u017ee chceme opakovat skute\u010dnou z\u00e1t\u011b\u017e pomoc\u00ed log\u016f. Skv\u011bl\u00fd n\u00e1pad, pokud je to naps\u00e1no na Open Source pgreplay. Pou\u017e\u00edv\u00e1me ho. Ale aby dob\u0159e fungoval, mus\u00edte povolit \u00fapln\u00e9 logov\u00e1n\u00ed dotaz\u016f s parametry a \u010dasov\u00e1n\u00edm.<\/p>\n<p><\/p>\n<p>Existuj\u00ed ur\u010dit\u00e9 probl\u00e9my ohledn\u011b duration a timestamp. O tuto \u010d\u00e1st se nebudeme starat. Hlavn\u00ed ot\u00e1zka je, zda si to m\u016f\u017eete dovolit nebo ne. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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 nemus\u00ed b\u00fdt dostupn\u00e9. Nejprve mus\u00edte porozum\u011bt tomu, jak\u00fd tok se bude zapisovat do logu. Pokud m\u00e1te pg_stat_statements, m\u016f\u017eete t\u00edmto dotazem (odkaz bude dostupn\u00fd na slidach) zjistit, kolik bajt\u016f se p\u0159ibli\u017en\u011b zapisuje za sekundu.<\/p>\n<p><\/p>\n<p>Sledujeme d\u00e9lku dotazu. Ignorujeme fakt, \u017ee tam nejsou parametry, ale v\u00edme d\u00e9lku dotazu a v\u00edme, kolikr\u00e1t za sekundu byl vykon\u00e1n. Takto m\u016f\u017eeme odhadnout, kolik p\u0159ibli\u017en\u011b bajt\u016f za sekundu. M\u016f\u017eeme ud\u011blat chybu a\u017e dvakr\u00e1t, ale po\u0159ad\u00ed p\u0159esn\u011b pochop\u00edme t\u00edmto zp\u016fsobem.<\/p>\n<p><\/p>\n<p>M\u016f\u017eeme vid\u011bt, \u017ee tento dotaz se vykon\u00e1v\u00e1 802kr\u00e1t za sekundu. A vid\u00edme, \u017ee bytes_per sec \u2013 300 kB\/s se zapisuje plus m\u00ednus. A obvykle si takov\u00fd tok m\u016f\u017eeme dovolit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/218f905073a8519115adf5c65825b74d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale! Faktem je, \u017ee existuj\u00ed r\u016fzn\u00e9 syst\u00e9my logov\u00e1n\u00ed. A lid\u00e9 obvykle pou\u017e\u00edvaj\u00ed defaultn\u011b \u201esyslog\u201c.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/66054682083ba2ada174afb5e7ee3973.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A pokud m\u00e1te syslog, m\u016f\u017ee to vypadat takto. Vezmeme pgbench, zapneme logov\u00e1n\u00ed dotaz\u016f a pod\u00edv\u00e1me se, co se stane.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a05723dcf10a7733f054ba108bb0f107.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Bez logov\u00e1n\u00ed \u2013 to je sloupec nalevo. M\u011bli jsme 161 000 TPS. Se syslogem \u2013 na Ubuntu 16.04 v Amazonu m\u00e1me 37 000 TPS. A pokud zm\u011bn\u00edme na dva jin\u00e9 zp\u016fsoby logov\u00e1n\u00ed, situace se v\u00fdrazn\u011b zlep\u0161\u00ed. Tedy o\u010dek\u00e1vali jsme pokles, ale ne takov\u00fd.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a7d90af9d29761d3b80eb09bf050760f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A na CentOS 7, kde se pod\u00edl\u00ed tak\u00e9 journald, kter\u00fd p\u0159ev\u00e1d\u00ed logy do bin\u00e1rn\u00edho form\u00e1tu pro snadn\u00e9 vyhled\u00e1v\u00e1n\u00ed atd., je to \u00fapln\u00e1 katastrofa, pokles o 44kr\u00e1t ve TPS.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86d2af18a0e6364f297a62378b6ede56.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A s t\u00edmto lid\u00e9 \u017eij\u00ed. A \u010dasto je to ve firm\u00e1ch, zvl\u00e1\u0161t\u011b ve velk\u00fdch, velmi obt\u00ed\u017en\u00e9 zm\u011bnit. Pokud se m\u016f\u017eete vzd\u00e1t syslogu, pros\u00edm, ud\u011blejte to.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a167a6fac7f28c7541515f7eff30bdb4.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Vyhodno\u0165te IOPS a tok z\u00e1pisu. <\/li>\n<li>Ov\u011b\u0159te sv\u016fj syst\u00e9m logov\u00e1n\u00ed. <\/li>\n<li>Pokud je p\u0159edpokl\u00e1dan\u00e1 z\u00e1t\u011b\u017e p\u0159\u00edli\u0161 vysok\u00e1, zva\u017ete mo\u017enost vzorkov\u00e1n\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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. Jak jsem ji\u017e \u0159ekl, ten mus\u00ed b\u00fdt ur\u010dit\u011b aktivov\u00e1n. M\u016f\u017eeme vz\u00edt a ka\u017edou skupinu dotaz\u016f speci\u00e1ln\u00edm zp\u016fsobem popsat v souboru. A pot\u00e9 m\u016f\u017eeme pou\u017e\u00edt velmi u\u017eite\u010dnou funkci v pgbench \u2013 mo\u017enost dodat n\u011bkolik soubor\u016f pomoc\u00ed volby \u201e-f\u201c.<\/p>\n<p><\/p>\n<p>Ch\u00e1pe mnoho \u201e-f\u201c. A m\u016f\u017eeme \u0159\u00edct pomoc\u00ed \u201e@\u201c na konci, jak\u00fd pod\u00edl by m\u011bl m\u00edt ka\u017ed\u00fd soubor. T. j. m\u016f\u017eeme \u0159\u00edct, \u017ee toto by se m\u011blo vykon\u00e1vat ve 10 % p\u0159\u00edpad\u016f a toto ve 20 %. A to n\u00e1s p\u0159ibl\u00ed\u017e\u00ed k tomu, co vid\u00edme na produkci.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b91d18a9861abdc36bfda54cd72a142f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A jak pochop\u00edme, co m\u00e1me na produkci? Jak\u00fd pod\u00edl a \u010deho? Tady se trochu odbo\u010duje. M\u00e1me je\u0161t\u011b jeden produkt. <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/postgres-checkup\">postgres-checkup<\/a><\/noindex>. Tak\u00e9 zalo\u017een\u00fd na Open Source. A nyn\u00ed ho aktivn\u011b vyv\u00edj\u00edme.<\/p>\n<p><\/p>\n<p>Vznikl trochu z jin\u00fdch d\u016fvod\u016f. Z d\u016fvod\u016f, \u017ee monitorov\u00e1n\u00ed nesta\u010dilo. T. j. p\u0159ich\u00e1z\u00edte, pod\u00edv\u00e1te se na datab\u00e1zi, zkontrolujete probl\u00e9my, kter\u00e9 existuj\u00ed. A v\u011bt\u0161inou d\u011bl\u00e1te health_check. Pokud jste zku\u0161en\u00fd DBA, tak d\u011bl\u00e1te health_check. Pod\u00edvali jste se na vyu\u017eit\u00ed index\u016f atd. Pokud m\u00e1te OKmeter, pak je to skv\u011bl\u00e9. Je to skv\u011bl\u00e9 monitorov\u00e1n\u00ed pro Postgres. OKmeter.io \u2013 pros\u00edm, nainstalujte ho, je tam v\u0161echno skv\u011ble ud\u011blan\u00e9. Je to placen\u00e9.<\/p>\n<p><\/p>\n<p>Pokud ho nem\u00e1te, pak obvykle m\u00e1te m\u00e1lo v\u011bc\u00ed. V monitoringu obvykle jsou CPU, IO a to s v\u00fdhradami, a v\u0161echno. A pot\u0159ebujeme v\u00edce. Mus\u00edme vid\u011bt, jak funguje autovakuum, jak funguje checkpoint, v IO je t\u0159eba odd\u011blit checkpoint od bgwriter a backend\u016f atd.<\/p>\n<p><\/p>\n<p>Probl\u00e9m je, \u017ee kdy\u017e pom\u00e1h\u00e1te n\u011bjak\u00e9 velk\u00e9 spole\u010dnosti, nemohou rychle n\u011bco implementovat. Nemohou rychle zakoupit OKmeter. Mo\u017en\u00e1 ho koup\u00ed a\u017e za p\u016fl roku. Nemohou rychle nainstalovat n\u011bjak\u00e9 bal\u00ed\u010dky. <\/p>\n<p><\/p>\n<p>A vznikla n\u00e1m my\u0161lenka, \u017ee pot\u0159ebujeme takov\u00fd speci\u00e1ln\u00ed n\u00e1stroj, kter\u00fd nevy\u017eaduje \u017e\u00e1dnou instalaci, t. j. nemus\u00edte na produkci instalovat v\u016fbec nic. Nainstalujete si ho na sv\u016fj notebook nebo na server pro sledov\u00e1n\u00ed, odkud ho budete spou\u0161t\u011bt. A ten bude analyzovat mnoho v\u011bc\u00ed: jak opera\u010dn\u00ed syst\u00e9m, tak souborov\u00fd syst\u00e9m, a samotn\u00fd Postgres, prov\u00e1d\u011bj\u00edc\u00ed n\u011bjak\u00e9 lehk\u00e9 dotazy, kter\u00e9 m\u016f\u017eete provozovat p\u0159\u00edmo na produkci a nic se nezhrout\u00ed.<\/p>\n<p><\/p>\n<p>Nazvali jsme ho Postgres-checkup. Pokud to vezmeme l\u00e9ka\u0159sky, je to pravideln\u00e1 kontrola zdrav\u00ed. Pokud v automobilov\u00e9m kontextu, tak \u2013 to je jako technick\u00e1 prohl\u00eddka. Prov\u00e1d\u00edte technickou prohl\u00eddku u auta ka\u017ed\u00fdch \u0161est m\u011bs\u00edc\u016f nebo rok, v z\u00e1vislosti na zna\u010dce. A d\u011bl\u00e1te technickou prohl\u00eddku pro svou datab\u00e1zi? T. j. d\u011bl\u00e1te pravideln\u011b hlubok\u00e9 vy\u0161et\u0159en\u00ed? Je to pot\u0159eba. Pokud d\u011bl\u00e1te z\u00e1lohy, d\u011blejte i checkup, to je stejn\u011b d\u016fle\u017eit\u00e9.<\/p>\n<p><\/p>\n<p>A m\u00e1me takov\u00fd n\u00e1stroj. Za\u010dal se aktivn\u011b rozv\u00edjet teprve p\u0159ed t\u0159emi m\u011bs\u00edci. Je je\u0161t\u011b mlad\u00fd, ale je tam hodn\u011b zaj\u00edmav\u00e9ho.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/514d00b5cb710a7997af0a5dde55b5a8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Sestavujeme ty nejvlivn\u011bj\u0161\u00ed skupiny dotaz\u016f \u2013 zpr\u00e1va K003 v Postgres-checkup<\/p>\n<p><\/p>\n<p>A tam je skupina report\u016f K. Zat\u00edm t\u0159i reporty. A je tu zpr\u00e1va K003. Tam je vrchol z pg_stat_statements, se\u0159azen\u00fd podle total_time.<\/p>\n<p><\/p>\n<p>Kdy\u017e se\u0159ad\u00edme skupiny dotaz\u016f podle total_time, vid\u00edme na vrcholu takovou skupinu, kter\u00e1 nejv\u00edce zat\u011b\u017euje n\u00e1\u0161 syst\u00e9m, tedy spot\u0159ebov\u00e1v\u00e1 v\u011bt\u0161\u00ed mno\u017estv\u00ed zdroj\u016f. Pro\u010d je naz\u00fdv\u00e1m skupinami dotaz\u016f? Proto\u017ee jsme vy\u0159adili parametry. To u\u017e nejsou dotazy, ale skupiny dotaz\u016f, tedy jsou abstrahovan\u00e9.<\/p>\n<p><\/p>\n<p>A pokud budeme optimalizovat shora dol\u016f, usnadn\u00edme na\u0161e zdroje a odd\u00e1l\u00edme okam\u017eik, kdy budeme muset prov\u00e9st upgrade. To je velmi dobr\u00fd zp\u016fsob, jak u\u0161et\u0159it pen\u00edze.<\/p>\n<p><\/p>\n<p>Mo\u017en\u00e1 to nen\u00ed nejlep\u0161\u00ed zp\u016fsob, jak se starat o u\u017eivatele, proto\u017ee mo\u017en\u00e1 nezaznamen\u00e1me vz\u00e1cn\u00e9, ale velmi nep\u0159\u00edjemn\u00e9 p\u0159\u00edpady, kdy \u010dlov\u011bk \u010dekal 15 sekund. Celkov\u011b jsou takov\u00e9 p\u0159\u00edpady tak vz\u00e1cn\u00e9, \u017ee je nevid\u00edme, ale zab\u00fdv\u00e1me se zdroji.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d16abc423573e4d923b0c03683c2efee.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Co se stalo v t\u00e9to tabulce? U\u010dinili jsme dva snapshoty. Postgres_checkup v\u00e1m ud\u011bl\u00e1 delta pro ka\u017edou metriky: podle total-time, calls, rows, shared_blks_read atd. V\u0161echno, delta byla spo\u010d\u00edtan\u00e1. U pg_stat_statements je velk\u00fd probl\u00e9m, \u017ee si nepamatuje, kdy do\u0161lo k resetu. Pokud pg_stat_database pamatuje, pg_stat_statements si nepamatuje. Vid\u00edte, \u017ee tam je \u010d\u00edslo 1 000 000, ale odkud jsme ho spo\u010d\u00edtali, nev\u00edme.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c370bf1237675e03c0a3d362c025ac24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A tady to v\u00edme, m\u00e1me dva snapshoty. V\u00edme, \u017ee delta v tomto p\u0159\u00edpad\u011b byla 56 sekund. Velmi mal\u00fd interval. Se\u0159adili jsme podle total_time. A d\u00e1le m\u016f\u017eeme diferencovat, tedy v\u0161echny metriky d\u011bl\u00edme na duration. Pokud ka\u017edou metriku vyd\u011bl\u00edme duration, dostaneme po\u010det vol\u00e1n\u00ed za sekundu.<\/p>\n<p><\/p>\n<p>D\u00e1le total_time za sekundu \u2013 to je moje obl\u00edben\u00e1 metrika. M\u011b\u0159\u00ed se v sekund\u00e1ch, za sekundu, tedy kolik sekund na\u0161eho syst\u00e9mu bylo pot\u0159eba k proveden\u00ed t\u00e9to skupiny dotaz\u016f za sekundu. Pokud tam vid\u00edte v\u00edce ne\u017e sekundu za sekundu, znamen\u00e1 to, \u017ee v\u00e1m bylo pot\u0159eba d\u00e1t v\u00edce ne\u017e jedno j\u00e1dro. To je velmi dobr\u00e1 metrika. M\u016f\u017eete pochopit, \u017ee tomuto chlap\u00edkovi nap\u0159\u00edklad pot\u0159ebujete alespo\u0148 t\u0159i j\u00e1dra.<\/p>\n<p><\/p>\n<p>Tohle je na\u0161e know-how, n\u011bco takov\u00e9ho jsem nikde nevid\u011bl. V\u0161imn\u011bte si \u2013 je to velmi jednoduch\u00e1 v\u011bc \u2013 sekunda za sekundu. N\u011bkdy, kdy\u017e m\u00e1te CPU na 100 %, je to p\u016fl hodiny za sekundu, tedy jste p\u016fl hodiny zab\u00edjeli pouze t\u00edmto dotazem. <\/p>\n<p><\/p>\n<p>D\u00e1le vid\u00edme po\u010det \u0159\u00e1dk\u016f za sekundu. V\u00edme, kolik \u0159\u00e1dk\u016f za sekundu se vr\u00e1tilo.<\/p>\n<p><\/p>\n<p>A d\u00e1le tu m\u00e1me zaj\u00edmavou v\u011bc. Kolik jsme p\u0159e\u010detli ze shared_buffers za sekundu. Hity u\u017e byly tam, a \u0159\u00e1dky jsme vzali z cache opera\u010dn\u00edho syst\u00e9mu nebo z disku. Prvn\u00ed varianta je rychl\u00e1, a druh\u00e1 m\u016f\u017ee b\u00fdt rychl\u00e1, ale tak\u00e9 m\u016f\u017ee b\u00fdt pomal\u00e1, z\u00e1le\u017e\u00ed na situaci. <\/p>\n<p><\/p>\n<p>Druh\u00fd zp\u016fsob diferenciace spo\u010d\u00edv\u00e1 v tom, \u017ee d\u011bl\u00edme po\u010det dotaz\u016f v t\u00e9to skupin\u011b. Ve druh\u00e9m sloupci bude v\u017edy jeden dotaz d\u011blen\u00fd na dotaz. A d\u00e1le je zaj\u00edmav\u00e9, kolik milisekund bylo v tomto dotazu. V\u00edme, jak se ten dotaz v pr\u016fm\u011bru chov\u00e1. 101 milisekund bylo pot\u0159eba na ka\u017ed\u00fd dotaz. To je tradi\u010dn\u00ed metrika, kterou pot\u0159ebujeme pro pochopen\u00ed.<\/p>\n<p><\/p>\n<p>Kolik \u0159\u00e1dk\u016f ka\u017ed\u00fd dotaz vr\u00e1til v pr\u016fm\u011bru. Vid\u00edme, \u017ee tato skupina vrac\u00ed 8. Kolik v pr\u016fm\u011bru bylo vzato a p\u0159e\u010dteno z cache. Vid\u00edme, \u017ee v\u0161echno je skv\u011ble zakov\u00e1no. Sam\u00e9 hity pro prvn\u00ed skupinu. <\/p>\n<p><\/p>\n<p>A \u010dtvrt\u00fd pod\u0159\u00e1dek v ka\u017ed\u00e9m \u0159\u00e1dku je procento ze celkov\u00e9ho po\u010dtu. M\u00e1me nap\u0159\u00edklad call\u016f. P\u0159edpokl\u00e1dejme, \u017ee 1 000 000. A m\u016f\u017eeme pochopit, jak\u00fd p\u0159\u00edsp\u011bvek tato skupina p\u0159in\u00e1\u0161\u00ed. Vid\u00edme, \u017ee v tomto p\u0159\u00edpad\u011b prvn\u00ed skupina p\u0159in\u00e1\u0161\u00ed p\u0159\u00edsp\u011bvek men\u0161\u00ed ne\u017e 0,01 %. Tedy je tak pomal\u00e1, \u017ee ji v celkov\u00e9m obrazci nevid\u00edme. A druh\u00e1 skupina \u2013 5 % ze v\u0161ech vol\u00e1n\u00ed. Tedy 5 % v\u0161ech vol\u00e1n\u00ed pat\u0159\u00ed do druh\u00e9 skupiny. <\/p>\n<p><\/p>\n<p>Ohledn\u011b total_time to tak\u00e9 je zaj\u00edmav\u00e9. Na prvn\u00ed skupinu dotaz\u016f jsme vynalo\u017eili 14 % celkov\u00e9ho \u010dasu. A na druhou \u2013 11 % a tak d\u00e1le. <\/p>\n<p><\/p>\n<p>Nechci zab\u00edhat do detail\u016f, ale tam jsou jemnosti. Na za\u010d\u00e1tku vykazujeme chybu, proto\u017ee kdy\u017e porovn\u00e1v\u00e1me, snapshoty mohou selhat, tedy n\u011bkter\u00e9 dotazy mohou chyb\u011bt a ve druh\u00e9m u\u017e nemus\u00ed b\u00fdt p\u0159\u00edtomny, a n\u011bkter\u00e9 mohou nov\u011b vzniknout. A tam po\u010d\u00edt\u00e1me chybu. Pokud vid\u00edte 0, je to dobr\u00e9. To znamen\u00e1, \u017ee nen\u00ed \u017e\u00e1dn\u00e1 chyba. Pokud je chybovost do 20 %, je to v po\u0159\u00e1dku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/327bbf167bc21d37a3e1597b52528e6d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>D\u00e1le se vrac\u00edme k na\u0161emu t\u00e9matu. Mus\u00edme sestavit workload. Jdeme shora dol\u016f, dokud nenashrom\u00e1\u017ed\u00edme 80 % nebo 90 %. Obvykle je to 10-20 skupin. A vytv\u00e1\u0159\u00edme soubory pro pgbench. Tam pou\u017e\u00edv\u00e1me random. N\u011bkdy to bohu\u017eel nevyjde. A v Postgres 12 bude v\u00edce mo\u017enost\u00ed, jak takov\u00fd p\u0159\u00edstup pou\u017e\u00edt. <\/p>\n<p><\/p>\n<p>A d\u00e1le takto z\u00edsk\u00e1v\u00e1me 80-90 % podle total_time. Co d\u00e1le p\u0159id\u00e1vat za \u201e@\u201c? Sledujeme vol\u00e1n\u00ed, kolik procent a ch\u00e1peme, \u017ee zde bychom m\u011bli m\u00edt tolik procent. Z t\u011bchto procent m\u016f\u017eeme pochopit, jak balancovat ka\u017ed\u00fd z soubor\u016f. Pot\u00e9 pou\u017eijeme pgbench a jdeme pracovat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/53edf787f49dcabb5e4731510839332d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>M\u00e1me tak\u00e9 K001 a K002. <\/p>\n<p><\/p>\n<p>K001 \u2013 to je jeden velk\u00fd \u0159et\u011bzec se \u010dty\u0159mi pod\u0159et\u011bzci. To je charakteristika na\u0161eho zat\u00ed\u017een\u00ed. Pod\u00edvejte se na druh\u00fd sloupec a druh\u00fd pod\u0159et\u011bzec. Vid\u00edme, \u017ee p\u0159ibli\u017en\u011b 1,5 sekundy za sekundu, t.j. pokud budou dv\u011b j\u00e1dra, bude to dobr\u00e9. Budeme m\u00edt p\u0159ibli\u017en\u011b 75 % zat\u00ed\u017een\u00ed. A tak to bude fungovat. Pokud budeme m\u00edt 10 jader, budeme naprosto klidn\u00ed. Takto m\u016f\u017eeme hodnotit zdroje.<\/p>\n<p><\/p>\n<p>K002 \u2013 to naz\u00fdv\u00e1m t\u0159\u00eddami po\u017eadavk\u016f, t.j. SELECT, INSERT, UPDATE, DELETE. A zvl\u00e1\u0161\u0165 SELECT FOR UPDATE, proto\u017ee ten zamyk\u00e1. <\/p>\n<p><\/p>\n<p>A zde m\u016f\u017eeme doj\u00edt k z\u00e1v\u011bru, \u017ee b\u011b\u017en\u00e9 \u010dten\u00ed SELECT p\u0159edstavuje 82 % v\u0161ech vol\u00e1n\u00ed, ale p\u0159itom 74 % podle total_time. T.j. jsou \u010dasto vol\u00e1ny, ale m\u00e9n\u011b spot\u0159ebov\u00e1vaj\u00ed zdroje. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0e0234e696ffe1c80f8b5acccc602dbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A vrac\u00edme se k ot\u00e1zce: \u201eJak spr\u00e1vn\u011b nastavit shared_buffers?\u201c. Pozoruji, \u017ee v\u011bt\u0161ina benchmark\u016f je zalo\u017eena na my\u0161lence \u2013 poj\u010fme se pod\u00edvat, jak\u00fd throughput bude, t.j. jak\u00e1 bude propustnost. Obvykle se m\u011b\u0159\u00ed v TPS nebo QPS.<\/p>\n<p><\/p>\n<p>A sna\u017e\u00edme se z vozu vy\u017ed\u00edmat co nejv\u00edce transakc\u00ed za sekundu pomoc\u00ed ladic\u00edch parametr\u016f. Tady m\u00e1me pr\u00e1v\u011b 311 za sekundu pro select.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d5e73f2672d94bb92eed921ef3dfe0b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale nikdo nejezd\u00ed do pr\u00e1ce a zp\u011bt dom\u016f autem na plnou rychlost. To je hloup\u00e9. Tak i s datab\u00e1zemi. Nem\u011bli bychom jezdit na plnou rychlost, nikdo to ani ned\u011bl\u00e1. Nikdo ne\u017eije v prost\u0159ed\u00ed produkce, kde je 100% CPU. A\u010dkoliv, mo\u017en\u00e1 n\u011bkte\u0159\u00ed \u017eij\u00ed, ale to nen\u00ed dobr\u00e9. <\/p>\n<p><\/p>\n<p>My\u0161lenka je takov\u00e1, \u017ee obvykle jezd\u00edme na 20 % na\u0161ich mo\u017enost\u00ed, ide\u00e1ln\u011b ne v\u00edce ne\u017e 50 %. A sna\u017e\u00edme se optimalizovat dobu odezvy pro na\u0161e u\u017eivatele p\u0159edev\u0161\u00edm. T.j. mus\u00edme na\u0161e nastaven\u00ed konfigurovat tak, abychom zajistili minim\u00e1ln\u00ed latenci p\u0159i 20% rychlosti, podm\u00edn\u011bn\u011b. To je koncept, kter\u00fd tak\u00e9 sna\u017e\u00edme aplikovat v na\u0161ich experimentech.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c32c2cfb76dce0ce6b50e929bbf914ac.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A na z\u00e1v\u011br doporu\u010den\u00ed: <\/p>\n<p><\/p>\n<ul>\n<li>Ur\u010dit\u011b vytvo\u0159te Database Lab.<\/li>\n<li>Pokud to je mo\u017en\u00e9, ud\u011blejte to na vy\u017e\u00e1d\u00e1n\u00ed, aby se to spustilo na ur\u010ditou dobu \u2013 vyzkou\u0161eli a zahodili. Pokud m\u00e1te cloud, tak to je samoz\u0159ejmost, t.j. m\u011bjte hodn\u011b standing. <\/li>\n<li>Bu\u010fte zv\u00eddav\u00ed. A pokud n\u011bco nen\u00ed v po\u0159\u00e1dku, ov\u011b\u0159te si to experimenty, jak to funguje. Nancy lze pou\u017e\u00edt k tomu, abyste se u\u010dili, 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 pomoc\u00ed Postgres-checkup. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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 mnohokr\u00e1t! Velmi zaj\u00edmav\u00e1 v\u011bc.<\/em> <\/p>\n<p><\/p>\n<p>Dv\u011b v\u011bci.<\/p>\n<p><\/p>\n<p><em>Ano, dv\u011b v\u011bci. Jenom jsem to \u00fapln\u011b nepochopil. Kdy\u017e pracujeme s Nancy, m\u016f\u017eeme m\u011bnit jen jeden parametr, nebo celou skupinu?<\/em><\/p>\n<p><\/p>\n<p>M\u00e1me delta-config parametr. M\u016f\u017eete do n\u011bj najednou zm\u011bnit kolik chcete. Ale je t\u0159eba m\u00edt na pam\u011bti, \u017ee kdy\u017e zm\u011bn\u00edte moc v\u011bc\u00ed, m\u016f\u017eete ud\u011blat nespr\u00e1vn\u00e9 z\u00e1v\u011bry. <\/p>\n<p><\/p>\n<p><em>Ano. Pro\u010d jsem se ptal? Proto\u017ee je slo\u017eit\u00e9 prov\u00e1d\u011bt experimenty, kdy\u017e m\u00e1te jen jeden parametr. Zm\u011bn\u00edte ho, pod\u00edv\u00e1te se, jak to funguje. Nastavili jste ho. Pak za\u010dnete s dal\u0161\u00edm.<\/em><\/p>\n<p><\/p>\n<p>Lze m\u011bnit v\u00edce parametr\u016f sou\u010dasn\u011b, ale samoz\u0159ejm\u011b z\u00e1le\u017e\u00ed na situaci. Ale je lep\u0161\u00ed testovat jednu my\u0161lenku. V\u010dera n\u00e1s napadla my\u0161lenka. M\u011bli jsme velmi podobnou situaci. M\u011bli jsme dv\u011b konfigurace. A nemohli jsme pochopit, pro\u010d je tak velk\u00fd rozd\u00edl. A napadlo n\u00e1s, \u017ee mus\u00edme pou\u017e\u00edt dichotomii k tomu, abychom postupn\u011b pochopili a na\u0161li, v \u010dem je rozd\u00edl. M\u016f\u017eete okam\u017eit\u011b nastavit polovinu parametr\u016f stejn\u011b, pak \u010dtvrtinu a tak d\u00e1le. V\u0161echno je flexibiln\u00ed.<\/p>\n<p><\/p>\n<p><em>A je\u0161t\u011b m\u00e1m ot\u00e1zku. Projekt je mlad\u00fd, rozv\u00edj\u00ed se. Je ji\u017e k dispozici dokumentace a podrobn\u00fd popis?<\/em><\/p>\n<p><\/p>\n<p>Ud\u011blala jsem tam speci\u00e1ln\u00ed odkaz na popis parametr\u016f. To je k dispozici. Ale je\u0161t\u011b nen\u00ed hodn\u011b v\u011bc\u00ed. Hled\u00e1m sp\u0159\u00edzn\u011bn\u00e9 du\u0161e. A j\u00e1 je nach\u00e1z\u00edm, kdy\u017e vystupuji. To je velmi skv\u011bl\u00e9. 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 mi zp\u011btnou vazbu \u2013 co chyb\u00ed. <\/p>\n<p><\/p>\n<p><em>A\u017e ud\u011bl\u00e1me laborato\u0159, mo\u017en\u00e1 bude zp\u011btn\u00e1 vazba. 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 Amazonu. Pl\u00e1nuje se podpora GSP?<\/em><\/p>\n<p><\/p>\n<p>Dobr\u00fd dotaz. Za\u010dali jsme pracovat. A zat\u00edm jsme to pozastavili, proto\u017ee chceme \u0161et\u0159it. Tedy je zde podpora pomoc\u00ed spu\u0161t\u011bn\u00ed na localhostu. M\u016f\u017eete si vytvo\u0159it instance a pracovat lok\u00e1ln\u011b. Mimochodem, tak to d\u011bl\u00e1me. V Getlabu to d\u011bl\u00e1m takto, tam na GSP. Ale d\u011blat p\u0159esn\u011b takovou orchestraci zat\u00edm nevid\u00edme smysl, proto\u017ee Google nem\u00e1 levn\u00e9 spoty. Existuj\u00ed ??? instance, ale maj\u00ed omezen\u00ed. Za prv\u00e9, tam je v\u017edy pouze sleva 70 % a nen\u00ed mo\u017en\u00e9 si s cenou hr\u00e1t. Spoty zvy\u0161ujeme cenu o 5-10 %, abychom sn\u00ed\u017eili pravd\u011bpodobnost, \u017ee v\u00e1s vy\u0159ad\u00ed. Tedy u spot\u016f \u0161et\u0159\u00edte, ale mohou v\u00e1m je kdykoliv vz\u00edt. Pokud m\u00e1te cenu o n\u011bco vy\u0161\u0161\u00ed ne\u017e ostatn\u00ed, budete eliminov\u00e1ni pozd\u011bji. U Googlu je to \u00fapln\u011b jin\u00e1 specifika. A tak\u00e9 existuje velmi \u0161patn\u00e9 omezen\u00ed \u2013 \u017eij\u00ed pouze 24 hodin. A n\u011bkdy chceme experiment prov\u00e1d\u011bt 5 dn\u00ed. Ale na spotech se to d\u00e1 d\u011blat, spoty ob\u010das \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 po\u010d\u00edt\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 vyv\u00edj\u00ed soubor skupin dotaz\u016f: kolik jich selhalo a kolik nov\u00fdch se objevilo. A pak se d\u00edv\u00e1me na dv\u011b metriky: total_time a calls, proto tam jsou dv\u011b chyby. A sledujeme, jak\u00fd p\u0159\u00ednos maj\u00ed selh\u00e1vaj\u00edc\u00ed skupiny. Tam jsou dv\u011b podskupiny: odch\u00e1zej\u00edc\u00ed a p\u0159ich\u00e1zej\u00edc\u00ed. D\u00edv\u00e1me se, jak\u00fd maj\u00ed p\u0159\u00ednos v celkov\u00e9m obraze. <\/p>\n<p><\/p>\n<p><em>Neboj\u00edte se, \u017ee se tam b\u011bhem doby mezi snapshoty oto\u010d\u00ed dvakr\u00e1t \u010di t\u0159ikr\u00e1t?<\/em><\/p>\n<p><\/p>\n<p>Tedy se znovu zaregistrovali nebo jak?<\/p>\n<p><\/p>\n<p><em>Nap\u0159\u00edklad tento dotaz u\u017e jednou byl vy\u0159azen, pak se vr\u00e1til a znovu byl vy\u0159azen, pak znovu p\u0159i\u0161el a znovu byl vy\u0159azen. A vy jste n\u011bco 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>Podobnou v\u011bc jsem d\u011blal. Samoz\u0159ejm\u011b jednodu\u0161\u0161\u00ed, d\u011blal jsem ji 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 to stejn\u011b nedostalo na maximum, kolik stat_statements m\u016f\u017ee nahromadit. 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 nev\u00edm, jak jinak to ud\u011blat spolehliv\u011b.<\/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, tak bychom v ide\u00e1ln\u00edm p\u0159\u00edpad\u011b porovn\u00e1vali porovnateln\u00e9 v\u011bci. <\/p>\n<p><\/p>\n<p><em>Ne, m\u016f\u017ee se n\u011bkolikr\u00e1t uvolnit mezi snapshoty a znovu p\u0159ijet.<\/em><\/p>\n<p><\/p>\n<p>S t\u00edmto ID?<\/p>\n<p><\/p>\n<p><em>Ano.<\/em> <\/p>\n<p><\/p>\n<p>Zkoum\u00e1me to. Dobr\u00fd dotaz. Mus\u00edme to prostudovat. Ale zat\u00edm to, co vid\u00edme, m\u00e1me bu\u010f 0 napsan\u00e9...<\/p>\n<p><\/p>\n<p><em>To je samoz\u0159ejm\u011b vz\u00e1cn\u00fd p\u0159\u00edpad, ale \u0161okovalo m\u011b, kdy\u017e jsem zjistil, \u017ee stat_statements tam m\u016f\u017ee uvolnit.<\/em> <\/p>\n<p><\/p>\n<p>V Pg_stat_statements m\u016f\u017ee b\u00fdt spousta v\u011bc\u00ed. Setkali jsme se s t\u00edm, \u017ee pokud m\u00e1te track_utility = on, tak\u00e9 se v\u00e1m sleduj\u00ed sety. <\/p>\n<p><\/p>\n<p><em>Ano, samoz\u0159ejm\u011b.<\/em><\/p>\n<p><\/p>\n<p>A pokud m\u00e1te java hibernate, kter\u00fd je n\u00e1hodn\u00fd, tak se za\u010dne zamykat hash tabulka. A jakmile vypnete velmi zat\u00ed\u017eenou aplikaci, m\u00e1te 50-100 skupin. A tam je to v\u00edcem\u00e9n\u011b stabiln\u00ed. Jedn\u00edm z zp\u016fsob\u016f, jak s t\u00edm bojovat, je zv\u00fd\u0161it pg_stat_statements.max. <\/p>\n<p><\/p>\n<p><em>Ano, ale mus\u00edte v\u011bd\u011bt, o kolik. A n\u011bjak ho mus\u00edte sledovat. D\u011bl\u00e1m to takto. T. j. m\u00e1m pg_stat_statements.max. A sleduji, \u017ee na okam\u017eik snapshotu jsem nedos\u00e1hl 70 %. Dob\u0159e, znamen\u00e1 to, \u017ee jsme nic neztratili. D\u011bl\u00e1me reset. A znovu sb\u00edr\u00e1me. Pokud v dal\u0161\u00edm snapshotu bude m\u00e9n\u011b ne\u017e 70, pak pravd\u011bpodobn\u011b op\u011bt nic neztrat\u00edme.<\/em><\/p>\n<p><\/p>\n<p>Ano. Ve v\u00fdchoz\u00edm nastaven\u00ed je to nyn\u00ed 5 000. A mnoha lidem to sta\u010d\u00ed. <\/p>\n<p><\/p>\n<p><em>Obvykle \u2013 ano.<\/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=\"P\u0159ehr\u00e1t 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. P\u0159id\u00e1m, \u017ee pokud se v Postgresu nach\u00e1zej\u00ed citliv\u00e1 data, kter\u00e1 nesm\u00ed skon\u010dit v testovac\u00edm prost\u0159ed\u00ed, je mo\u017en\u00e9 vyu\u017e\u00edt <noindex><a rel=\"nofollow\" href=\"https:\/\/labs.dalibo.com\/postgresql_anonymizer\">PostgreSQL Anonymizer<\/a><\/noindex>. Sch\u00e9ma je zhruba n\u00e1sleduj\u00edc\u00ed:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi.&quot; Nikolaj Samochvalov\" 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\/cs\/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=\"cs_CZ\" \/>\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\/cs\/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\udd47Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi\u00bb. Nikolaj Samochvalov | ProHoster","description":"Doporu\u010duji se sezn\u00e1mit s v\u00fdkladem zpr\u00e1vy Nikolaje Samochvalova \"Pr\u016fmyslov\u00fd p\u0159\u00edstup k lad\u011bn\u00ed PostgreSQL: experimenty s datab\u00e1zemi\". Shared_buffers = 25 % \u2013 je to hodn\u011b nebo m\u00e1lo?","canonical_url":"https:\/\/prohoster.info\/cs\/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":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/78742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=78742"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/78742\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/78743"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=78742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=78742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=78742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}