{"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\/ro\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov","title":{"rendered":"Abordare industrial\u0103 pentru tunarea PostgreSQL: experimente asupra bazelor de date. Nikolay Samohvalov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>V\u0103 propun s\u0103 consulta\u021bi transcrierea raportului lui Nikolai Samohvalov \"Abordarea industrial\u0103 a tuning-ului PostgreSQL: experimente pe baze de date\"<\/strong><\/p>\n<p><\/p>\n<p>Shared_buffers = 25% \u2013 este mult sau pu\u021bin? Sau exact c\u00e2t trebuie? Cum s\u0103 \u00een\u021belegi dac\u0103 aceast\u0103 recomandare \u2013 destul de \u00eenvechit\u0103 \u2013 se potrive\u0219te situa\u021biei tale specifice?<\/p>\n<p><\/p>\n<p>A venit timpul s\u0103 abord\u0103m problema selec\u021biei parametrilor postgresql.conf \"\u00een mod serios\". Nu cu ajutorul \"autotunerelor\" oarbe sau al sfaturilor \u00eenvechite din articole \u0219i bloguri, ci pe baza:<\/p>\n<p><\/p>\n<ol>\n<li>experimentelor strict calibrate pe baze de date, realizate automatizat, \u00een cantit\u0103\u021bi mari \u0219i \u00een condi\u021bii c\u00e2t mai apropiate de cele \"de produc\u021bie\",<\/li>\n<li>\u00een\u021belegerii profunde a caracteristicilor de func\u021bionare ale SGBD-ului \u0219i sistemului de operare.<\/li>\n<\/ol>\n<p><\/p>\n<p>Folosind Nancy CLI (<noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres.ai\/nancy\">https:\/\/gitlab.com\/postgres.ai\/nancy<\/a><\/noindex>), vom analiza un exemplu concret \u2013 celebrele shared_buffers \u2013 \u00een diverse situa\u021bii, \u00een diferite proiecte \u0219i vom \u00eencerca s\u0103 \u00een\u021belegem cum s\u0103 alegem setarea optim\u0103 pentru infrastructura noastr\u0103, baza de date \u0219i sarcina de lucru.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" 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>Vor fi discutate experimentele asupra bazelor de date. Aceasta este o poveste care continu\u0103 de pu\u021bin peste \u0219ase luni. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/e77255c4abaa2a01ae37fe888fe67db7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>C\u00e2te ceva despre mine. Am o experien\u021b\u0103 de peste 14 ani cu Postgres. Am fondat mai multe companii \u00een domeniul re\u021belelor sociale, unde Postgres a fost folosit \u0219i este folosit.<\/p>\n<p><\/p>\n<p>De asemenea, grupul RuPostgres pe Meetup, pe locul 2 \u00een lume. Ne apropiem \u00eencet de 2 000 de membri. RuPostgres.org.<\/p>\n<p><\/p>\n<p>\u0218i la diverse conferin\u021be, inclusiv Highload, m\u0103 ocup de baze de date, \u00een special de Postgres, de la \u00eenceputuri.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/fa009b50f1c0293eee80fc03fd2c05f0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i \u00een ultimii c\u00e2\u021biva ani, mi-am reluat practica de consultan\u021b\u0103 Postgres, av\u00e2nd clien\u021bi \u00een 11 fusuri orare diferite.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/550c3ddcd516adca14d2b3de9c34b7ff.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Atunci c\u00e2nd am f\u0103cut acest lucru cu c\u00e2\u021biva ani \u00een urm\u0103, am avut o perioad\u0103 de pauz\u0103 \u00een lucrul activ cu Postgres, probabil din 2010. M-a surprins c\u00e2t de pu\u021bin s-au schimbat provoc\u0103rile zilnice ale DBA-urilor, c\u00e2t de mult\u0103 munc\u0103 manual\u0103 este \u00eenc\u0103 necesar\u0103. \u0218i m-am g\u00e2ndit c\u0103 trebuie s\u0103 fie ceva \u00een neregul\u0103, trebuie s\u0103 automatiz\u0103m mai multe lucruri.<\/p>\n<p><\/p>\n<p>\u0218i av\u00e2nd \u00een vedere c\u0103 majoritatea clien\u021bilor erau \u00een cloud, multe s-au automatizat evident. Despre asta vom vorbi pu\u021bin mai t\u00e2rziu. Adic\u0103, toat\u0103 aceast\u0103 experien\u021b\u0103 a dus la ideea c\u0103 ar trebui s\u0103 existe o serie de instrumente, adic\u0103 o anumit\u0103 platform\u0103 care s\u0103 automatizeze practic toate ac\u021biunile DBA-ului, pentru a putea gestiona un num\u0103r mare de baze de date.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b02097f0bb23fa9d7c30e38ab9f3de7b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen aceast\u0103 prezentare nu vor fi:<\/p>\n<p><\/p>\n<ul>\n<li>Nu exist\u0103 \u201egloan\u021be de argint\u201d \u0219i p\u0103rerile de tipul \u2013 seta\u021bi 8 GB sau 25 % shared_buffers \u0219i ve\u021bi fi bine. Despre shared_buffers nu va fi at\u00e2t de mult. <\/li>\n<li>Interioare hardcore. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ccea359ec78a967c150ddcedc3e50a88.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i ce se va \u00eent\u00e2mpla?<\/p>\n<p><\/p>\n<ul>\n<li>Vor exista principii de optimizare pe care le aplic\u0103m \u0219i le dezvolt\u0103m. Vor exista diverse idei care ne vin pe parcurs \u0219i diverse instrumente pe care le cre\u0103m, \u00een mare parte, \u00een Open Source, adic\u0103 baza o facem \u00een Open Source. Mai mult, avem tichete, toat\u0103 comunicarea se desf\u0103\u0219oar\u0103 practic \u00een Open Source. Pute\u021bi urm\u0103ri ce facem acum, ce va fi \u00een urm\u0103toarea versiune etc. <\/li>\n<li>De asemenea, va exista o anumit\u0103 experien\u021b\u0103 \u00een utilizarea acestor principii \u0219i instrumente \u00een diverse companii: de la startup-uri mici la mari corpora\u021bii. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86e6f379726dec814aca616a30d36a2f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cum se dezvolt\u0103 totul?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c1422d9f2945e717c6ebb31f5fef6329.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen primul r\u00e2nd, principalul obiectiv al DBA-ului pe l\u00e2ng\u0103 asigurarea cre\u0103rii instan\u021belor, desf\u0103\u0219urarea backup-urilor etc. este identificarea punctelor critice \u0219i optimizarea performan\u021bei.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8e682ca8666e94d6688348ebe4540327.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Acum, lucrurile sunt organizate astfel. Ne uit\u0103m la monitorizare, vedem ceva, ne lipsesc anumite detalii. \u00cencepem s\u0103 cercet\u0103m mai atent, de obicei manual \u0219i \u00een\u021belegem ce s\u0103 facem cu asta, \u00eentr-un fel sau altul.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/04599422ea6b6d57c79a4e48ba2db8b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i exist\u0103 dou\u0103 abord\u0103ri. Pg_stat_statements \u2013 solu\u021bia standard implicit\u0103 pentru identificarea interog\u0103rilor lente. \u0218i analiza jurnalelor Postgres cu ajutorul pgBadger.<\/p>\n<p><\/p>\n<p>Fiecare dintre abord\u0103ri are serios dezavantaje. La prima abordare, toate parametrii sunt omise. \u0218i dac\u0103 vedem grupuri SELECT * FROM table where coloana este egal\u0103 cu semnul \u201e?\u201d sau \u201e$\u201d \u00eencep\u00e2nd cu versiunea Postgres 10. Nu \u0219tim \u2013 este scanare a indexului sau scanare secven\u021bial\u0103. Depinde foarte mult de parametru. Dac\u0103 pui o valoare rar \u00eent\u00e2lnit\u0103, va fi scanare a indexului. Dac\u0103 pui o valoare care reprezint\u0103 90 % din tabel, evident va fi scanare secven\u021bial\u0103, pentru c\u0103 Postgres cunoa\u0219te statisticile. \u0218i acesta este un dezavantaj mare al pg_stat_statements, de\u0219i se fac anumite lucr\u0103ri.<\/p>\n<p><\/p>\n<p>Cel mai mare dezavantaj al analizei jurnalelor este c\u0103, \u00een general, nu \u00ee\u021bi po\u021bi permite \u201elog_min_duration_statement = 0\u201d. Despre asta vom vorbi \u0219i noi. \u00cen consecin\u021b\u0103, nu vezi \u00eentreaga imagine. \u0218i o anumit\u0103 interogare care este foarte rapid\u0103 poate consuma o cantitate uria\u0219\u0103 de resurse, dar nu o vei observa, pentru c\u0103 este sub pragul t\u0103u.<\/p>\n<p><\/p>\n<p><strong>Cum rezolv\u0103 DBA-ii problemele identificate?<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/5e57fefdec6256f82045aaac037b516c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>De exemplu, am g\u0103sit o problem\u0103. Ce se face de obicei? Dac\u0103 e\u0219ti dezvoltator, vei face ceva pe un anumit instan\u021b\u0103, care nu este de o dimensiune prea mare. Dac\u0103 e\u0219ti DBA, ai un staging. \u0218i poate fi doar unul. \u0218i acesta a \u00eent\u00e2rziat cu jum\u0103tate de an. \u0218i te g\u00e2nde\u0219ti c\u0103 vei merge \u00een produc\u021bie. Chiar \u0219i DBA-ii experimenta\u021bi verific\u0103 apoi \u00een produc\u021bie, pe replic\u0103. Se \u00eent\u00e2mpl\u0103 chiar s\u0103 creeze un index temporar, se asigur\u0103 c\u0103 acesta ajut\u0103, \u00eel \u0219terg \u0219i-l \u00eenapoie devlopatorilor pentru a-l include \u00een fi\u0219ierele de migrare. Cam a\u0219a se \u00eent\u00e2mpl\u0103 acum. \u0218i asta este o problem\u0103.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/402e80c7e5fbca6a054c0c9ec61e5fe3.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Configurarea ajust\u0103rilor.<\/li>\n<li>Optimizarea setului de indec\u0219i. <\/li>\n<li>Modificarea interog\u0103rii SQL (aceasta este cea mai complex\u0103 abordare).<\/li>\n<li>Ad\u0103ugarea de putere (cea mai simpl\u0103 metod\u0103 \u00een majoritatea cazurilor).<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8a6b3cd56e0df5c7908714926ba1eb05.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cu aceste lucruri sunt foarte multe de spus. Exist\u0103 multe detalii \u00een Postgres. Trebuie s\u0103 \u0219tii foarte multe. Exist\u0103 multe indec\u0219i \u00een Postgres, datorit\u0103 organizatorilor acestei conferin\u021be. Trebuie s\u0103 \u0219tii toate acestea \u0219i tocmai acest lucru le d\u0103 DBA-ilor senza\u021bia c\u0103 se ocup\u0103 de magie neagr\u0103. Asta \u00eenseamn\u0103 c\u0103 trebuie s\u0103 te ocupi timp de 10 ani pentru a \u00eencepe s\u0103 \u00een\u021belegi toate acestea corect.<\/p>\n<p><\/p>\n<p>\u0218i eu sunt un lupt\u0103tor \u00eempotriva acestei magii negre. Vreau s\u0103 fac totul astfel \u00eenc\u00e2t s\u0103 fie tehnologie, nu intui\u021bie \u00een toate acestea.<\/p>\n<p><\/p>\n<p><strong>Exemple din via\u021b\u0103<\/strong><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/70b11c3df9c41c66570635d5f309ed24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Am observat asta \u00een cel pu\u021bin dou\u0103 proiecte, inclusiv al meu. O nou\u0103 postare pe blog ne spune c\u0103 o valoare de 1.000 pentru default_statistic_target este bun\u0103. Bun, haide\u021bi s\u0103 \u00eencerc\u0103m \u00een produc\u021bie.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0b4c2bd6f73df06967c94d5d78b07279.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i aici, folosindu-ne de instrumentul nostru doi ani mai t\u00e2rziu, prin experimentele efectuate asupra bazelor de date de care vorbim ast\u0103zi, putem compara ce a fost \u0219i ce a devenit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/3564eade6532003d2279c2736ab6d733.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i pentru asta trebuie s\u0103 cre\u0103m un experiment. Acesta const\u0103 din patru p\u0103r\u021bi. <\/p>\n<p><\/p>\n<ul>\n<li>Prima parte este mediu. Avem nevoie de hardware. \u0218i c\u00e2nd ajung \u00eentr-o companie \u0219i semnez un contract, spun c\u0103 vreau un hardware asem\u0103n\u0103tor cu cel din produc\u021bie. Pentru fiecare dintre me\u0219terii t\u0103i am nevoie de cel pu\u021bin un hardware de acela\u0219i tip. Fie c\u0103 este o ma\u0219in\u0103 virtual\u0103 instance \u00een Amazon sau Google, fie c\u0103 am nevoie de exact acela\u0219i hardware. Adic\u0103 vreau s\u0103 recreez mediu. \u00cen conceptul de mediu includem versiunea major\u0103 a Postgres. <\/li>\n<li>A doua parte este obiectul cercet\u0103rilor noastre. Aceasta este baza de date. Poate fi creat\u0103 \u00een mai multe moduri. \u00ce\u021bi voi ar\u0103ta cum. <\/li>\n<li>A treia parte este sarcina. Acesta este cel mai complex moment. <\/li>\n<li>\u0218i a patra parte este ceea ce verific\u0103m, adic\u0103 cu ce vom compara. S\u0103 zicem c\u0103 putem schimba unul sau mai multe parametrii \u00een configura\u021bie, sau putem crea un index \u0219i a\u0219a mai departe. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8ccd09ac4cdd336618413375617c10a6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Lans\u0103m un experiment. Iat\u0103 pg_stat_statements. \u00cen st\u00e2nga \u2013 ceea ce era. \u00cen dreapta \u2013 ce a devenit.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/6f82d7e25a77b700f2ddb01f42a861a2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen st\u00e2nga default_statistics_target = 100, \u00een dreapta = 1 000. Vedem c\u0103 ne-a ajutat. \u00cen general, a fost cu 8% mai bine.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/52dda8fb5fbcb9beecaa1eda8443a374.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dar dac\u0103 derul\u0103m \u00een jos, acolo vor fi grupuri de cereri din pgBadger sau din pg_stat_statements. Exist\u0103 dou\u0103 variante. Vom observa c\u0103 o cerere a sc\u0103zut cu 88%. \u0218i aici intervine abordarea ingineriasc\u0103. Putem s\u0103 ne ad\u00e2ncim, pentru c\u0103 e interesant de ce a sc\u0103zut. Trebuie s\u0103 \u00een\u021belegem ce s-a \u00eent\u00e2mplat cu statistica. De ce mai multe bucket-uri \u00een statistic\u0103 duc la un rezultat mai prost.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4e6dfc04af4b3fc2b626dd46c08bdf66.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Sau putem s\u0103 nu ne ad\u00e2ncim, ci s\u0103 facem \u201eALTER TABLE \u2026 ALTER COLUMN\u201d \u0219i s\u0103-i return\u0103m \u00eenapoi 100 de bucket-uri \u00een statistica acestei coloane. \u0218i printr-un alt experiment putem s\u0103 ne asigur\u0103m c\u0103 aceast\u0103 solu\u021bie a ajutat. Gata. Aceasta este abordarea ingineriasc\u0103, care ne ajut\u0103 s\u0103 vedem tabloul \u0219i s\u0103 lu\u0103m decizii pe baza datelor, nu pe baza intui\u021biei.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c87df172b994a4523478beebaf4a0275.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/9e766aa4348bd1a77b697e3179d84bbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>C\u00e2teva exemple din alte domenii. \u00cen teste, exist\u0103 teste CI de mul\u021bi ani. Niciun proiect care este \u00een toate min\u021bile nu va putea tr\u0103i f\u0103r\u0103 teste automate.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b6c21981dc8b2b285973ae42199e2b4c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen alte industrii: \u00een avia\u021bie, \u00een industria auto, atunci c\u00e2nd test\u0103m aerodinamica, avem \u0219i noi oportunitatea de a face experimente. Nu vom lansa imediat ceva \u00een spa\u021biu direct dintr-un desen, sau nu vom scoate o ma\u0219in\u0103 imediat pe drum. De exemplu, exist\u0103 un tub aerodinamic. <\/p>\n<p><\/p>\n<p>Din observa\u021biile asupra altor domenii putem trasa concluzii. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cef02ea9280e5795d1e593c600f0541b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00cen primul r\u00e2nd, avem un mediu special. Este aproape de produc\u021bie, dar nu chiar. Principala sa caracteristic\u0103 este c\u0103 ar trebui s\u0103 fie ieftin, repetabil \u0219i maxim automatizat. \u0218i ar trebui s\u0103 existe instrumente speciale pentru a efectua analize detaliate.<\/p>\n<p><\/p>\n<p>Probabil, c\u00e2nd am lansat avionul \u0219i zbur\u0103m, avem mai pu\u021bine oportunit\u0103\u021bi de a studia fiecare milimetru al suprafe\u021bei aripii dec\u00e2t avem \u00een tunelul aerodynamic. Avem mai multe resurse pentru diagnosticare. Ne putem permite s\u0103 ata\u0219\u0103m mai multe lucruri grele, ceea ce nu ne putem permite s\u0103 facem pe avion \u00een aer. La fel este \u0219i cu Postgres. \u00cen anumite cazuri, putem activa logarea complet\u0103 a interog\u0103rilor \u00een timpul experimentelor. \u0218i nu vrem s\u0103 facem asta \u00een produc\u021bie. Poate c\u0103 vom activa asta cu ajutorul auto_explain.<\/p>\n<p><\/p>\n<p>\u0218i a\u0219a cum am spus deja, un nivel ridicat de automatizare \u00eenseamn\u0103 c\u0103 am ap\u0103sat un buton \u0219i am repetat. A\u0219a ar trebui s\u0103 fie, pentru a avea multe experimente, astfel \u00eenc\u00e2t s\u0103 devin\u0103 un flux.<\/p>\n<p><\/p>\n<p>Nancy CLI \u2013 funda\u021bia \u201elaboratorului de baze de date\u201d<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/f8303df21f309fb209929acec260b757.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i iat\u0103, am realizat o astfel de chestie. Adic\u0103, vorbeam despre aceste idei \u00een iunie, aproape acum un an. \u0218i avem deja \u00een Open Source a\u0219a-numita Nancy CLI. Acesta este fundamentul pentru a construi un laborator de baze de date.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" 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 Aceasta este \u00een Open Source, pe Gitlab. Pute\u021bi s\u0103 spune\u021bi, pute\u021bi \u00eencerca. Am pus un link \u00een slide-uri. Pute\u021bi face clic pe el \u0219i acolo va fi <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/nancy\/-\/blob\/master\/help\/nancy_run.md\">help<\/a><\/noindex> pe toate parametrii.<\/p>\n<p><\/p>\n<p>Desigur, acolo mai sunt multe \u00een stadiu de dezvoltare. Exist\u0103 multe idei. Dar deja este ceva ce aplic\u0103m practic zilnic. \u0218i c\u00e2nd ne trece o idee \u2013 ce se \u00eent\u00e2mpl\u0103 c\u00e2nd \u0219tergem 40 000 000 de r\u00e2nduri \u0219i totul se blocheaz\u0103 din cauza IO-ului, putem face un experiment \u0219i s\u0103 ne uit\u0103m mai \u00een detaliu pentru a \u00een\u021belege ce se \u00eent\u00e2mpl\u0103 \u0219i apoi s\u0103 \u00eencerc\u0103m s\u0103 corect\u0103m pe parcurs. Adic\u0103, facem un experiment. De exemplu, ajust\u0103m ceva \u0219i vedem ce rezultat ob\u021binem. \u0218i facem asta nu \u00een produc\u021bie. Aceasta este esen\u021ba ideii.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c22d332de4b45d604dfb9c5a95404fd6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Unde poate func\u021biona asta? Poate func\u021biona local, adic\u0103 se poate face oriunde, chiar \u0219i pe un MacBook. Ai nevoie de Docker, e\u0219ti gata. \u0218i totul. Poate fi rulat pe un instance pe hardware, sau \u00eentr-o ma\u0219in\u0103 virtual\u0103, oriunde.<\/p>\n<p><\/p>\n<p>Exist\u0103, de asemenea, posibilitatea de a lansa de la distan\u021b\u0103 pe Amazon, \u00een EC2 Instance, \u00een spoturi. Este o oportunitate foarte interesant\u0103. De exemplu, ieri am efectuat peste 500 de experimente pe i3 instance, \u00eencep\u00e2nd cu cel mai mic \u0219i termin\u00e2nd cu i3-16-xlarge. Aceste 500 de experimente ne-au costat 64 de dolari. Fiecare a durat 15 minute. A\u0219adar, datorit\u0103 utiliz\u0103rii spoturilor, costurile sunt foarte reduse \u2013 o reducere de 70%, cu tarifare pe secund\u0103 de la Amazon. Pute\u021bi face foarte multe. Pute\u021bi desf\u0103\u0219ura o cercetare real\u0103.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/64c61cc9dd7181296b2ef5b815a17493.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i sunt acceptate trei versiuni majore Postgres. Nu este at\u00e2t de complicat s\u0103 aducem unele vechi \u0219i noua versiune 12.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/ee147c91014ab698cbd3c60e193bef23.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Obiectul \u00eel putem defini \u00een trei moduri. Acestea sunt:<\/p>\n<p><\/p>\n<ul>\n<li>Dump\/sql-file. <\/li>\n<li>Principalul mod este clonarea directorului PGDATA. De obicei, acesta este preluat de pe serverul de backup. Dac\u0103 ave\u021bi backup-uri binare corecte, pute\u021bi face clone de acolo. Dac\u0103 ave\u021bi servicii de cloud, aceasta va fi realizat\u0103 de compania de cloud, cum ar fi Amazon sau Google. Acesta este principalul mod de a realiza clone \u00een produc\u021bie. Noi desf\u0103\u0219ur\u0103m astfel de opera\u021biuni. <\/li>\n<li>Iar ultima metod\u0103 este potrivit\u0103 pentru cercet\u0103ri, c\u00e2nd dori\u021bi s\u0103 \u00een\u021belege\u021bi cum func\u021bioneaz\u0103 o anumit\u0103 parte a Postgres. Aceasta este pgbench. Pute\u021bi genera cu ajutorul pgbench. Este pur \u0219i simplu o op\u021biune \u00abdb-pgbench\u00bb. \u00cei spune\u021bi ce scal\u0103 dori\u021bi. \u0218i totul va fi generat \u00een cloud, a\u0219a cum este specificat.<\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/2bc3e30cb31a19f66c6b55480447126f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i \u00eenc\u0103rcarea:<\/p>\n<p><\/p>\n<ul>\n<li>\u00cenc\u0103rcarea o putem executa \u00eentr-un singur thread SQL. Acesta este cel mai primitiv mod. <\/li>\n<li>Dar putem simula o \u00eenc\u0103rcare. Iar simularea o putem face \u00een primul r\u00e2nd \u00een felul urm\u0103tor. Trebuie s\u0103 colect\u0103m toate logurile. \u0218i aceasta este o provocare. Voi ar\u0103ta de ce. \u0218i cu ajutorul pgreplay, care este \u00eencorporat \u00een Nancy, vom reda. <\/li>\n<li>Sau exist\u0103 o alternativ\u0103. A\u0219a-numita \u00eenc\u0103rcare manual\u0103, la care lucr\u0103m cu un anumit efort. Analiz\u00e2nd \u00eenc\u0103rcarea noastr\u0103 actual\u0103 pe sistemul de produc\u021bie, extragem grupurile de interog\u0103ri cele mai performante. \u0218i cu ajutorul pgbench putem simula aceast\u0103 \u00eenc\u0103rcare \u00een laborator. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/4adba12881faf9ad9d4f9e9f42c0f5c1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Ori trebuie s\u0103 execut\u0103m o interogare SQL, adic\u0103 verific\u0103m o migrare, cre\u0103m un index, execut\u0103m ANALYZE. \u0218i observ\u0103m ce s-a \u00eent\u00e2mplat \u00eenainte \u0219i dup\u0103 vacuum. Practic, orice SQL.<\/li>\n<li>Ori configur\u0103m unul sau mai multe parametrii. Putem solicita s\u0103 verifice, de exemplu, 100 de valori \u00een Amazon pentru baza noastr\u0103 de un terabyte. \u0218i dup\u0103 c\u00e2teva ore ve\u021bi avea un rezultat. De obicei, baza de un terabyte se desf\u0103\u0219oar\u0103 c\u00e2teva ore. Dar \u00een dezvoltare exist\u0103 un patch, putem avea o serie, adic\u0103 pute\u021bi folosi consecutiv aceea\u0219i pgdata pe acela\u0219i server \u0219i verifica. Postgres se va reporni, cache-urile se vor reseta. \u0218i pute\u021bi aplica sarcin\u0103. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/02985b47bf0ab62e8f1c4cc3ea003f26.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Sose\u0219te un director, \u00een care sunt o mul\u021bime de fi\u0219iere, \u00eencep\u00e2nd cu instantaneele pg.<em>stat<\/em>***. \u0218i cel mai interesant acolo este pg_stat_statements, pg_stat_kcache. Acestea sunt dou\u0103 extensii care analizeaz\u0103 cererile. Iar pg_stat_bgwriter con\u021bine nu doar statistica pgwriter, ci \u0219i date despre checkpoint-uri \u0219i despre modul \u00een care backend-urile \u00eenlocuiesc buffer-urile murdare. \u0218i este interesant de observat. De exemplu, c\u00e2nd configur\u0103m shared_buffers, este foarte interesant s\u0103 vedem c\u00e2te au fost \u00eenlocuite.<\/li>\n<li>De asemenea, sosesc jurnalele Postgres. Dou\u0103 jurnale \u2013 jurnalul de preg\u0103tire \u0219i jurnalul de rulare a sarcinii. <\/li>\n<li>O caracteristic\u0103 relativ nou\u0103 \u2013 FlameGraphs.<\/li>\n<li>De asemenea, dac\u0103 a\u021bi folosit pgreplay sau pgbench pentru simularea \u00eenc\u0103rc\u0103rii, ve\u021bi avea ie\u0219irea lor nativ\u0103. \u0218i ve\u021bi vedea laten\u021ba \u0219i TPS. Se va putea \u00een\u021belege cum au perceput asta. <\/li>\n<li>Informa\u021bii despre sistem. <\/li>\n<li>Verific\u0103ri de baz\u0103 ale CPU \u0219i IO. Acest lucru este mai mult pentru instan\u021bele EC2 din Amazon, c\u00e2nd dori\u021bi s\u0103 rula\u021bi \u00een flux 100 de instan\u021be identice \u0219i s\u0103 executa\u021bi 100 de r\u00e2nduri diferite, ve\u021bi avea 10.000 de experimente. \u0218i trebuie s\u0103 v\u0103 asigura\u021bi c\u0103 nu a\u021bi prins o instan\u021b\u0103 deficitar\u0103, care este deja restric\u021bionat\u0103 de cineva. Pe acest hard, altele activeaz\u0103 \u0219i resursele disponibile sunt reduse. Aceste rezultate ar trebui s\u0103 fie eliminate. \u0218i cu ajutorul sysbench de la Alexei Kopytov facem c\u00e2teva verific\u0103ri scurte, care vor veni \u0219i pot fi comparate cu altele, adic\u0103 ve\u021bi \u00een\u021belege cum se comport\u0103 CPU-ul \u0219i cum se comport\u0103 IO. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c4ddf5283c3c79e3d4c2c061e7dd9e7d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ce dificult\u0103\u021bi tehnice exist\u0103 pe baza diferitelor companii?<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/959690b464a01a4a3cb67ec497becff1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>S\u0103 presupunem c\u0103 dorim s\u0103 replic\u0103m o \u00eenc\u0103rcare real\u0103 folosind jurnalele. Este o idee excelent\u0103, dac\u0103 este construit pe Open Source pgreplay. \u00cel folosim. Dar, pentru a func\u021biona bine, trebuie s\u0103 activa\u021bi logarea complet\u0103 a cererilor cu parametrii \u0219i timing.<\/p>\n<p><\/p>\n<p>Exist\u0103 unele dificult\u0103\u021bi legate de duration \u0219i timestamp. Vom trece cu vederea aceast\u0103 problem\u0103. \u00centrebarea principal\u0103 este: v\u0103 pute\u021bi permite sau nu? <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" 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>Problema este c\u0103 acesta poate fi indisponibil. Trebuie, mai \u00eent\u00e2i, s\u0103 \u00een\u021belege\u021bi ce flux va fi scris \u00een jurnal. Dac\u0103 ave\u021bi pg_stat_statements, pute\u021bi folosi aceast\u0103 interogare (linkul va fi disponibil \u00een slide-uri) pentru a \u00een\u021belege c\u00e2\u021bi bytes vor fi scri\u0219i pe secund\u0103.<\/p>\n<p><\/p>\n<p>Ne uit\u0103m la lungimea interog\u0103rii. Ignor\u0103m faptul c\u0103 nu sunt parametriere, dar \u0219tim lungimea interog\u0103rii \u0219i c\u00e2te ori pe secund\u0103 este executat\u0103. Astfel, putem estima aproximativ c\u00e2\u021bi bytes pe secund\u0103. Ne putem \u00een\u0219ela de dou\u0103 ori, dar ordinea va fi clar\u0103 astfel.<\/p>\n<p><\/p>\n<p>Putem observa c\u0103 aceast\u0103 interogare este executat\u0103 de 802 de ori pe secund\u0103. \u0218i vedem c\u0103 bytes_per sec \u2013 300 kB\/s vor fi scri\u0219i plus minus. \u0218i, \u00een general, ne putem permite acest flux. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/218f905073a8519115adf5c65825b74d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dar! Problema este c\u0103 exist\u0103 diferite sisteme de logare. Iar \u00een mod implicit, oamenii folosesc de obicei \u201esyslog\u201d.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/66054682083ba2ada174afb5e7ee3973.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i dac\u0103 ave\u021bi syslog, atunci poate ap\u0103rea un tablou de genul acesta. Vom lua pgbench, vom activa logarea interog\u0103rilor \u0219i vom vedea ce iese.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a05723dcf10a7733f054ba108bb0f107.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>F\u0103r\u0103 logare \u2013 aceasta este coloana din st\u00e2nga. Aveam 161 000 TPS. Cu syslog \u2013 \u00een Ubuntu 16.04 pe Amazon avem 37 000 TPS. \u0218i dac\u0103 schimb\u0103m c\u0103tre dou\u0103 alte metode de logare, situa\u021bia se \u00eembun\u0103t\u0103\u021be\u0219te semnificativ. Adic\u0103, ne a\u0219teptam s\u0103 scad\u0103, dar nu at\u00e2t de mult.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a7d90af9d29761d3b80eb09bf050760f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Iar \u00een CentOS 7, unde journald este implicat, transform\u00e2nd jurnalele \u00eentr-un format binar pentru c\u0103utare u\u0219oar\u0103 etc., situa\u021bia este \u00eengrozitoare, cu o sc\u0103dere de 44 de ori a TPS.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/86d2af18a0e6364f297a62378b6ede56.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i acesta este ceea ce tr\u0103iesc oamenii. \u0218i adesea, \u00een companii, mai ales \u00een cele mari, este foarte complicat s\u0103 se schimbe. Dac\u0103 pute\u021bi renun\u021ba la syslog, v\u0103 rog, face\u021bi-o.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/a167a6fac7f28c7541515f7eff30bdb4.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Evalua\u021bi IOPS \u0219i fluxul de scriere. <\/li>\n<li>Verifica\u021bi sistemul vostru de logare. <\/li>\n<li>Dac\u0103 \u00eenc\u0103rcarea prev\u0103zut\u0103 este excesiv de mare, lua\u021bi \u00een considerare op\u021biunea de e\u0219antionare. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/127fa1296578ba0ba6eac2b44c5c5398.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Avem pg_stat_statements. Dup\u0103 cum am spus, trebuie s\u0103 fie prezent. \u0218i putem lua \u0219i s\u0103 descriem fiecare grup de interog\u0103ri \u00eentr-un fi\u0219ier special. Apoi putem folosi o func\u021bie foarte convenabil\u0103 \u00een pgbench \u2013 aceea de a introduce mai multe fi\u0219iere folosind op\u021biunea \u201e-f\u201d.<\/p>\n<p><\/p>\n<p>El \u00een\u021belege mult despre \u201e-f\u201d. \u0218i putem spune cu ajutorul \u201e@\u201d la final, ce propor\u021bie ar trebui s\u0103 aib\u0103 fiecare fi\u0219ier. Adic\u0103, putem spune c\u0103 acesta s\u0103 ruleze \u00een 10% din cazuri, iar acesta \u00een 20%. \u0218i asta ne va apropia de ceea ce vedem \u00een produc\u021bie.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/b91d18a9861abdc36bfda54cd72a142f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i cum ne d\u0103m seama ce avem \u00een produc\u021bie? Ce propor\u021bie \u0219i ce anume? Aici ne abatem pu\u021bin. Mai avem un produs. <noindex><a rel=\"nofollow\" href=\"https:\/\/gitlab.com\/postgres-ai\/postgres-checkup\">postgres-checkup<\/a><\/noindex>. Este, de asemenea, o baz\u0103 Open Source. \u0218i acum \u00eel dezvolt\u0103m activ.<\/p>\n<p><\/p>\n<p>A fost creat din alte motive. Din cauza c\u0103 monitorizarea nu este suficient\u0103. Adic\u0103, vii, te ui\u021bi la baz\u0103, te ui\u021bi la problemele existente. \u0218i, de obicei, faci un health_check. Dac\u0103 e\u0219ti un DBA experimentat, atunci faci un health_check. Te ui\u021bi la utilizarea indicilor etc. Dac\u0103 ai OKmeter, atunci e excelent. Este o monitorizare grozav\u0103 pentru Postgres. OKmeter.io \u2013 te rog, instaleaz\u0103-l, este foarte bine realizat. Este contra cost.<\/p>\n<p><\/p>\n<p>Dac\u0103 nu-l ai, de obicei nu ai mare lucru. \u00cen monitorizare, de obicei, exist\u0103 doar CPU, IO \u0219i asta cu rezerve, \u0219i cam at\u00e2t. Dar noi avem nevoie de mai mult. Trebuie s\u0103 vedem cum func\u021bioneaz\u0103 avvacuumul, cum func\u021bioneaz\u0103 checkpoint-ul, \u00een io trebuie s\u0103 separ\u0103m checkpoint-ul de bgwriter \u0219i de backend-uri etc.<\/p>\n<p><\/p>\n<p>Problema este c\u0103, atunci c\u00e2nd aju\u021bi o companie mare, nu pot implementa ceva rapid. Nu pot cump\u0103ra rapid OKmeter. Poate c\u0103 vor cump\u0103ra dup\u0103 \u0219ase luni. Nu pot instala rapid anumite pachete. <\/p>\n<p><\/p>\n<p>\u0218i ne-a venit ideea c\u0103 avem nevoie de un instrument special, care s\u0103 nu necesite nimic \u00een instalare, adic\u0103 nu trebuie s\u0103 instalezi nimic pe produc\u021bie. \u00cel instalezi pe laptopul t\u0103u sau pe un server de observare, de unde vei lansa. \u0218i acesta va analiza multe aspecte: at\u00e2t sistemul de operare, c\u00e2t \u0219i sistemul de fi\u0219iere, \u0219i PostgreSQL, f\u0103c\u00e2nd c\u00e2teva cereri simple care pot fi efectuate direct pe produc\u021bie f\u0103r\u0103 a ceda.<\/p>\n<p><\/p>\n<p>L-am numit Postgres-checkup. Dac\u0103 ar fi pe \u00een\u021belesul medical, este o verificare regulat\u0103 a s\u0103n\u0103t\u0103\u021bii. Dac\u0103 ne raport\u0103m la domeniul auto, este ca un service periodic. Facem service la ma\u0219in\u0103 la fiecare \u0219ase luni sau un an, \u00een func\u021bie de marc\u0103. Dar faci service pentru baza ta? Adic\u0103, faci o investiga\u021bie profund\u0103 regulat? Este necesar. Dac\u0103 faci backup-uri, atunci f\u0103 \u0219i checkup, este la fel de important.<\/p>\n<p><\/p>\n<p>\u0218i avem un astfel de instrument. A \u00eenceput s\u0103 se dezvolte activ doar acum trei luni. Este \u00eenc\u0103 t\u00e2n\u0103r, dar are deja multe func\u021bionalit\u0103\u021bi.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/514d00b5cb710a7997af0a5dde55b5a8.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Colect\u0103m cele mai \u201einfluen\u021babile\u201d grupuri de interog\u0103ri \u2013 raport K003 \u00een Postgres-checkup<\/p>\n<p><\/p>\n<p>\u0218i acolo exist\u0103 un grup de rapoarte K. Trei rapoarte deocamdat\u0103. \u0218i exist\u0103 un astfel de raport K003. Acolo este v\u00e2rful din pg_stat_statements, sortat dup\u0103 total_time.<\/p>\n<p><\/p>\n<p>Atunci c\u00e2nd sort\u0103m grupurile de interog\u0103ri dup\u0103 total_time, vedem un astfel de grup care \u00eengreuneaz\u0103 cel mai mult sistemul nostru, adic\u0103 consum\u0103 cele mai multe resurse. De ce numesc grupuri de interog\u0103ri? Pentru c\u0103 am eliminat parametrii. Acestea nu mai sunt interog\u0103ri, ci grupuri de interog\u0103ri, adic\u0103 sunt abstractizate.<\/p>\n<p><\/p>\n<p>\u0218i dac\u0103 ne vom optimiza de sus \u00een jos, ne vom u\u0219ura resursele \u0219i vom am\u00e2na momentul \u00een care va trebui s\u0103 facem un upgrade. Aceasta este o modalitate foarte bun\u0103 de a economisi bani.<\/p>\n<p><\/p>\n<p>Poate c\u0103 aceasta nu este o metod\u0103 foarte bun\u0103 \u00een ceea ce prive\u0219te grija pentru utilizatori, pentru c\u0103, poate, nu vedem cazuri rare, dar foarte nepl\u0103cute, c\u00e2nd o persoan\u0103 a a\u0219teptat 15 secunde. \u00cen total, sunt at\u00e2t de rare c\u0103 nu le vedem, dar ne ocup\u0103m de resurse.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d16abc423573e4d923b0c03683c2efee.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ce s-a \u00eent\u00e2mplat \u00een aceast\u0103 tabel\u0103? Am f\u0103cut dou\u0103 snapshoturi. Postgres_checkup \u00ee\u021bi va face delta pentru fiecare metric\u0103: total-time, calls, rows, shared_blks_read etc. Totul, delta a fost calculat\u0103. O mare problem\u0103 a pg_stat_statements este c\u0103 nu \u00ee\u0219i aminte\u0219te c\u00e2nd a avut loc resetarea. Dac\u0103 pg_stat_database \u00ee\u0219i aminte\u0219te, pg_stat_statements nu \u00ee\u0219i aminte\u0219te. Vezi c\u0103 acolo este num\u0103rul 1.000.000, dar de unde am calculat, nu \u0219tim.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c370bf1237675e03c0a3d362c025ac24.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Aici \u0219tim, avem dou\u0103 snapshoturi. \u0218tim c\u0103 delta \u00een acest caz a fost de 56 de secunde. Un interval foarte mic. Am sortat dup\u0103 total_time. \u0218i apoi putem diferen\u021bia, adic\u0103 toate metricile le \u00eemp\u0103r\u021bim la duration. Dac\u0103 \u00eemp\u0103r\u021bim fiecare metric\u0103 la duration, vom avea num\u0103rul de apeluri pe secund\u0103.<\/p>\n<p><\/p>\n<p>Mai departe, total_time pe secund\u0103 \u2013 aceasta este metrica mea preferat\u0103. Se m\u0103soar\u0103 \u00een secunde, pe secund\u0103, adic\u0103 c\u00e2te secunde a necesitat sistemul nostru pentru executarea acestui grup de interog\u0103ri pe secund\u0103. Dac\u0103 vezi acolo mai mult de o secund\u0103 pe secund\u0103, asta \u00eenseamn\u0103 c\u0103 ai nevoie de mai mult de un nucleu. Aceasta este o metric\u0103 foarte bun\u0103. Po\u021bi \u00een\u021belege c\u0103 acel tip, de exemplu, are nevoie de minimum trei nuclee.<\/p>\n<p><\/p>\n<p>Aceasta este inova\u021bia noastr\u0103, nu am v\u0103zut a\u0219a ceva nic\u0103ieri. Observa\u021bi \u2013 este o lucru foarte simplu \u2013 secund\u0103 pe secund\u0103. Uneori, c\u00e2nd ai CPU 100%, \u00eenseamn\u0103 c\u0103 ai lucrat timp de jum\u0103tate de or\u0103 pe secund\u0103, adic\u0103 ai fost ocupat doar cu aceste interog\u0103ri. <\/p>\n<p><\/p>\n<p>Apoi vedem r\u00e2nduri pe secund\u0103. \u0218tim c\u00e2te r\u00e2nduri pe secund\u0103 au fost returnate.<\/p>\n<p><\/p>\n<p>\u0218i mai departe este o alt\u0103 chestiune interesant\u0103. C\u00e2te shared_buffers am citit pe secund\u0103 din shared_buffers. Hiturile erau deja acolo, iar r\u00e2ndurile le-am preluat din cache-ul sistemului de operare sau din disk. Prima variant\u0103 este rapid\u0103, iar a doua poate fi rapid\u0103, dar poate nu, depinde de situa\u021bie. <\/p>\n<p><\/p>\n<p>Iar a doua metod\u0103 de diferen\u021biere \u2013 \u00eemp\u0103r\u021bim num\u0103rul de cereri din acest grup. \u00cen a doua coloan\u0103 vei avea \u00eentotdeauna o cerere \u00eemp\u0103r\u021bit\u0103 la cerere. \u0218i apoi devine interesant \u2013 c\u00e2te milisecunde au fost \u00een aceast\u0103 cerere. \u0218tim cum se comport\u0103 \u00een medie aceast\u0103 cerere. 101 milisecunde erau necesare pentru fiecare cerere. Aceasta este o metric\u0103 tradi\u021bional\u0103 de care avem nevoie pentru \u00een\u021belegere.<\/p>\n<p><\/p>\n<p>C\u00e2te r\u00e2nduri a returnat fiecare cerere \u00een medie. Vedem c\u0103 acest grup returneaz\u0103 8. C\u00e2te dintre acestea au fost preluate \u0219i citite din cache \u00een medie. Observ\u0103m c\u0103 totul este foarte bine cache-uit. Doar hituri pentru primul grup. <\/p>\n<p><\/p>\n<p>\u0218i a patra sublinie din fiecare linie \u2013 este c\u00e2te procente din total. Avem calls. S\u0103 presupunem, \u00een 1 000 000. \u0218i putem \u00een\u021belege ce contribu\u021bie aduce acest grup. Vedem c\u0103 \u00een acest caz, prima grupare are o contribu\u021bie mai mic\u0103 de 0,01%. Adic\u0103, este at\u00e2t de lent\u0103 \u00eenc\u00e2t nu o vedem \u00een imaginea de ansamblu. Iar a doua grupare \u2013 5% din apeluri. Adic\u0103, 5% din toate apelurile sunt din a doua grupare. <\/p>\n<p><\/p>\n<p>De asemenea, este interesant \u0219i pentru total_time. Pentru prima grup\u0103 de cereri am cheltuit 14% din tot timpul de func\u021bionare. Iar pentru a doua \u2013 11% \u0219i a\u0219a mai departe. <\/p>\n<p><\/p>\n<p>Nu m\u0103 voi ad\u00e2nci \u00een detalii, dar exist\u0103 nuan\u021be. Afi\u0219\u0103m o eroare \u00een partea de sus, pentru c\u0103 atunci c\u00e2nd compar\u0103m, instantaneele pot fi distorsionate, adic\u0103 unele cereri pot lipsi, iar \u00een a doua faz\u0103 deja nu mai pot fi prezente, iar altele pot ap\u0103rea noi. \u0218i acolo calcul\u0103m eroarea. Dac\u0103 vezi 0, atunci este bine. Nu sunt erori. Dac\u0103 rata de eroare este de p\u00e2n\u0103 la 20%, este \u00een regul\u0103. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/327bbf167bc21d37a3e1597b52528e6d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Apoi ne \u00eentoarcem la tema noastr\u0103. Trebuie s\u0103 cre\u0103m workload-ul. Lu\u0103m de sus \u00een jos, p\u00e2n\u0103 c\u00e2nd str\u00e2ngem 80% sau 90%. De obicei, sunt 10-20 de grupuri. \u0218i facem fi\u0219iere pentru pgbench. Acolo folosim random. Uneori, din p\u0103cate, nu se reu\u0219e\u0219te. Iar \u00een Postgres 12 vor fi mai multe oportunit\u0103\u021bi de a folosi acest tip de abordare. <\/p>\n<p><\/p>\n<p>\u0218i apoi, \u00een acest mod, atingem 80-90 % din total_time. Ce ar trebui s\u0103 introducem dup\u0103 \u00ab@\u00bb? Ne uit\u0103m la apeluri, vedem ce procente sunt \u0219i \u00een\u021belegem c\u0103 aici ar trebui s\u0103 avem un anumit procent. Din aceste procente putem \u00een\u021belege cum s\u0103 echilibr\u0103m fiecare dintre fi\u0219iere. Dup\u0103 aceea, folosim pgbench \u0219i ne apuc\u0103m de lucru. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/53edf787f49dcabb5e4731510839332d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Mai avem K001 \u0219i K002. <\/p>\n<p><\/p>\n<p>K001 \u2013 este un singur string mare cu patru sub-stringuri. Aceasta caracterizeaz\u0103 \u00eentreaga noastr\u0103 \u00eenc\u0103rcare. Uita\u021bi-v\u0103 la a doua coloan\u0103 \u0219i la al doilea sub-string. Vedem c\u0103 aproximativ o jum\u0103tate de secund\u0103 pe secund\u0103, adic\u0103 dac\u0103 avem dou\u0103 nuclee, va fi bine. Va fi aproximativ 75 % din \u00eenc\u0103rcare. A\u0219a va func\u021biona. Dac\u0103 avem 10 nuclee, atunci vom fi foarte lini\u0219ti\u021bi. Astfel putem evalua resursele.<\/p>\n<p><\/p>\n<p>K002 \u2013 acestea sunt clasele de solicit\u0103ri, adic\u0103 SELECT, INSERT, UPDATE, DELETE. \u0218i separat SELECT FOR UPDATE, pentru c\u0103 acesta blocheaz\u0103. <\/p>\n<p><\/p>\n<p>\u0218i aici putem concluziona c\u0103 SELECT-urile normale, de citire \u2013 reprezint\u0103 82 % din toate apelurile, dar \u00een acela\u0219i timp \u2013 74 % din total_time. Adic\u0103 sunt apelate frecvent, dar consum\u0103 mai pu\u021bine resurse. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/0e0234e696ffe1c80f8b5acccc602dbd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i revenind la \u00eentrebarea: \u00abCum ne putem stabili corect shared_buffers?\u00bb. Observ c\u0103 majoritatea benchmarkurilor se bazeaz\u0103 pe ideea \u2013 haide\u021bi s\u0103 vedem care va fi throughput-ul, adic\u0103 care va fi capacitatea de procesare. Aceasta este m\u0103surat\u0103 de obicei \u00een TPS sau QPS.<\/p>\n<p><\/p>\n<p>\u0218i \u00eencerc\u0103m s\u0103 extragem din ma\u0219in\u0103 c\u00e2t mai multe tranzac\u021bii pe secund\u0103 prin intermediul parametrilor de tuning. Aici avem 311 pe secund\u0103 pentru select.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/d5e73f2672d94bb92eed921ef3dfe0b7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dar nimeni nu merge la munc\u0103 \u0219i \u00eenapoi acas\u0103 cu ma\u0219ina pe vitez\u0103 maxim\u0103. Este prostesc. La fel este \u0219i cu bazele de date. Nu ar trebui s\u0103 circul\u0103m pe vitez\u0103 maxim\u0103, iar nimeni nu o face. Nimeni nu tr\u0103ie\u0219te \u00een produc\u021bie cu 100% CPU. De\u0219i poate cineva o face, dar nu este bine. <\/p>\n<p><\/p>\n<p>Ideea este c\u0103 circul\u0103m de obicei la aproximativ 20% din capacitate, de preferin\u021b\u0103 nu mai mult de 50%. \u0218i ne str\u0103duim s\u0103 optimiz\u0103m timpul de r\u0103spuns pentru utilizatorii no\u0219tri \u00een primul r\u00e2nd. Adic\u0103 trebuie s\u0103 ne ajust\u0103m sistemele astfel \u00eenc\u00e2t s\u0103 avem o laten\u021b\u0103 minim\u0103 la o vitez\u0103 de 20%, \u00een mod condi\u021bionat. Aceasta este ideea pe care \u00eencerc\u0103m s\u0103 o folosim \u00een experimentele noastre.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/c32c2cfb76dce0ce6b50e929bbf914ac.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u0218i \u00een concluzie, recomand\u0103rile: <\/p>\n<p><\/p>\n<ul>\n<li>Asigura\u021bi-v\u0103 c\u0103 face\u021bi Database Lab.<\/li>\n<li>Dac\u0103 este posibil, face\u021bi-l on demand, pentru a se desf\u0103\u0219ura pentru o anumit\u0103 perioad\u0103 \u2013 juca\u021bi-v\u0103 \u0219i apoi elimina\u021bi-l. Dac\u0103 ave\u021bi clouduri, aceasta este evident, adic\u0103 ave\u021bi multe instan\u021be disponibile. <\/li>\n<li>Fi\u021bi curio\u0219i. \u0218i dac\u0103 ceva nu este \u00een regul\u0103, verifica\u021bi prin experimente cum se comport\u0103. Nancy poate fi folosit\u0103 pentru a v\u0103 instrui pe voi \u00een\u0219iv\u0103, pentru a verifica cum func\u021bioneaz\u0103 baza de date.<\/li>\n<li>\u0218i viza\u021bi un timp minim de r\u0103spuns. <\/li>\n<li>\u0218i nu v\u0103 teme\u021bi de sursele Postgres. C\u00e2nd lucra\u021bi cu sursele, trebuie s\u0103 \u0219ti\u021bi englez\u0103. Sunt multe comentarii acolo, totul este explicat. <\/li>\n<li>\u0218i verifica\u021bi s\u0103n\u0103tatea bazei de date regulat, cel pu\u021bin o dat\u0103 la trei luni, fie manual, fie cu Postgres-checkup. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/8d2ac05f44bd245b879799862ce9e233.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00centreb\u0103ri<\/p>\n<p><\/p>\n<p><em>Mul\u021bumesc mult! Este o idee foarte interesant\u0103.<\/em> <\/p>\n<p><\/p>\n<p>Dou\u0103 lucruri.<\/p>\n<p><\/p>\n<p><em>Da, dou\u0103 lucruri. Numai c\u0103 nu am \u00een\u021beles complet. C\u00e2nd lucr\u0103m cu Nancy, putem ajusta doar un parametru sau un grup \u00eentreg?<\/em><\/p>\n<p><\/p>\n<p>Noi avem parametru delta-config. Pute\u021bi ajusta c\u00e2te dori\u021bi simultan. Dar trebuie s\u0103 \u00een\u021belege\u021bi c\u0103, atunci c\u00e2nd schimba\u021bi multe lucruri, s-ar putea s\u0103 trage\u021bi concluzii gre\u0219ite. <\/p>\n<p><\/p>\n<p><em>Da. De ce am \u00eentrebat? Pentru c\u0103 este greu s\u0103 conduceti experimente c\u00e2nd ave\u021bi doar un singur parametru. \u00cel ajusta\u021bi, verifica\u021bi cum func\u021bioneaz\u0103. L-a\u021bi setat. Apoi \u00eencepe\u021bi cu urm\u0103torul.<\/em><\/p>\n<p><\/p>\n<p>Pute\u021bi ajusta simultan, dar depinde de situa\u021bie, desigur. Dar este mai bine s\u0103 verifica\u021bi o singur\u0103 idee. Ieri ne-a venit o idee. Am avut o situa\u021bie foarte asem\u0103n\u0103toare. Au fost dou\u0103 configura\u021bii. \u0218i nu am putut \u00een\u021belege de ce exist\u0103 o diferen\u021b\u0103 mare. \u0218i ne-a venit ideea c\u0103 trebuie s\u0103 folosim dicotomia pentru a \u00een\u021belege treptat \u0219i a g\u0103si \u00een ce const\u0103 diferen\u021ba. Pute\u021bi face imediat jum\u0103tate din parametrii identici, apoi o p\u0103trime, \u0219i a\u0219a mai departe. Totul este flexibil.<\/p>\n<p><\/p>\n<p><em>\u0218i mai am o \u00eentrebare. Proiectul este t\u00e2n\u0103r, se dezvolt\u0103. Documenta\u021bia este deja gata, exist\u0103 o descriere detaliat\u0103?<\/em><\/p>\n<p><\/p>\n<p>Am f\u0103cut acolo o leg\u0103tur\u0103 special\u0103 c\u0103tre descrierea parametrilor. Aceasta exist\u0103. Dar multe lucruri nu sunt \u00eenc\u0103 gata. Caut oameni cu acelea\u0219i idei. \u0218i \u00eei g\u0103sesc c\u00e2nd \u021bin prezent\u0103ri. Este foarte grozav. Cineva colaboreaz\u0103 deja cu mine, cineva m-a ajutat \u0219i a f\u0103cut ceva acolo. \u0218i dac\u0103 sunte\u021bi interesa\u021bi de acest subiect, v\u0103 rug\u0103m s\u0103 ne oferi\u021bi feedback - ce v\u0103 lipse\u0219te. <\/p>\n<p><\/p>\n<p><em>C\u00e2nd vom avea laboratorul, poate va exista feedback. Vom vedea. Mul\u021bumesc!<\/em><\/p>\n<p><\/p>\n<p><em>Bun\u0103 ziua! Mul\u021bumesc pentru prezentare! Am observat c\u0103 exist\u0103 suport pentru Amazon. Este planificat\u0103 \u0219i suportul pentru GSP?<\/em><\/p>\n<p><\/p>\n<p>O \u00eentrebare bun\u0103. Am \u00eenceput s\u0103 lucr\u0103m la asta. \u0218i deocamdat\u0103 am suspendat, deoarece dorim s\u0103 economisim. Adic\u0103, exist\u0103 suport prin executarea pe localhost. Pute\u021bi crea singur un instance \u0219i lucra local. Apropo, a\u0219a facem. La Getlab fac asta, acolo pe GSP. Dar acum nu vedem sensul de a face o astfel de orchestrare, deoarece Google nu are spoturi ieftine. Exist\u0103 ??? instances, dar au restric\u021bii. \u00cen primul r\u00e2nd, ace\u0219tia ofer\u0103 \u00eentotdeauna doar o reducere de 70% \u0219i nu se poate negocia pre\u021bul. La spoturi, cre\u0219tem pre\u021bul cu 5-10% pentru a reduce probabilitatea ca s\u0103 fi\u021bi elimina\u021bi. Adic\u0103, economisi\u021bi cu spoturile, dar acestea v\u0103 pot fi luate oric\u00e2nd. Dac\u0103 face\u021bi un pre\u021b pu\u021bin mai mare dec\u00e2t ceilal\u021bi, ve\u021bi fi elimina\u021bi mai repede. Google are o specifica\u021bie complet diferit\u0103. \u0218i mai exist\u0103 o restric\u021bie foarte nepl\u0103cut\u0103 - acestea tr\u0103iesc doar 24 de ore. Dar uneori dorim s\u0103 desf\u0103\u0219ur\u0103m un experiment timp de 5 zile. \u00cens\u0103 asta se poate face la spoturi, uneori acestea r\u0103m\u00e2n active luni \u00eentregi. <\/p>\n<p><\/p>\n<p><em>Bun\u0103 ziua! V\u0103 mul\u021bumesc pentru prezentare! A\u021bi men\u021bionat despre controlul st\u0103rii. Cum calcula\u021bi erorile stat_statements?<\/em><\/p>\n<p><\/p>\n<p>O \u00eentrebare foarte bun\u0103. Pot s\u0103 explic \u0219i s\u0103 ar\u0103t foarte detaliat. Pe scurt - ne uit\u0103m la cum a evoluat grupul de cereri: c\u00e2te au e\u0219uat \u0219i c\u00e2te noi au ap\u0103rut. Apoi ne uit\u0103m la dou\u0103 metrice: total_time \u0219i calls, a\u0219a c\u0103 sunt dou\u0103 erori. \u0218i analiz\u0103m contribu\u021bia grupurilor afectate. Exist\u0103 dou\u0103 subgrupuri: cele care s-au retras \u0219i cele care au ap\u0103rut. Observ\u0103m contribu\u021bia lor \u00een imaginea general\u0103. <\/p>\n<p><\/p>\n<p><em>Nu v\u0103 teme\u021bi c\u0103 s-ar putea relua de dou\u0103-trei ori \u00eentre instantaneele de captur\u0103?<\/em><\/p>\n<p><\/p>\n<p>Adic\u0103, s-au \u00eenregistrat din nou sau cum?<\/p>\n<p><\/p>\n<p><em>De exemplu, aceast\u0103 cerere a fost deja eliminat\u0103 o dat\u0103, apoi a venit din nou \u0219i a fost eliminat\u0103, apoi a venit din nou \u0219i a fost eliminat\u0103. \u0218i voi a\u021bi calculat ceva, dar unde este totul?<\/em><\/p>\n<p><\/p>\n<p>O \u00eentrebare bun\u0103, trebuie s\u0103 analiz\u0103m. <\/p>\n<p><\/p>\n<p><em>Am f\u0103cut un lucru similar. Desigur, pe o scar\u0103 mai mic\u0103, l-am realizat singur. Dar a trebuit s\u0103 resetez, s\u0103 fac reset la stat_statements \u0219i s\u0103 m\u0103 orientez \u00een momentul instantaneei de captur\u0103, c\u0103 trebuie s\u0103 existe o anumit\u0103 propor\u021bie mai mic\u0103, c\u0103 totu\u0219i nu a ajuns la maximul pe care stat_statements \u00eel poate acumula. \u0218i m\u0103 orientez c\u0103, cel mai probabil, nu s-a eliminat nimic.<\/em> <\/p>\n<p><\/p>\n<p>Da-da. <\/p>\n<p><\/p>\n<p><em>Dar nu \u00een\u021beleg cum s\u0103 fac altfel \u00een mod fiabil.<\/em><\/p>\n<p><\/p>\n<p>Din p\u0103cate, nu \u00eemi amintesc exact - folosim textul cererii sau queryid de la pg_stat_statements \u0219i ne orient\u0103m dup\u0103 el. Dac\u0103 ne orient\u0103m dup\u0103 queryid, atunci, teoretic, compar\u0103m lucruri comparabile. <\/p>\n<p><\/p>\n<p><em>Nu, el poate fi \u00eenlocuit de mai multe ori \u00eentre snapshot-uri \u0219i poate reveni din nou.<\/em><\/p>\n<p><\/p>\n<p>Cu acela\u0219i ID?<\/p>\n<p><\/p>\n<p><em>Da.<\/em> <\/p>\n<p><\/p>\n<p>O s\u0103 ne uit\u0103m la asta. O \u00eentrebare bun\u0103. Trebuie s\u0103 studiem. Dar p\u00e2n\u0103 acum, ceea ce vedem este c\u0103 avem fie 0 scris...<\/p>\n<p><\/p>\n<p><em>Asta, desigur, este un caz rar, dar am fost zguduit c\u00e2nd am aflat c\u0103 stat_statements poate fi \u00eenlocuit acolo.<\/em> <\/p>\n<p><\/p>\n<p>\u00cen Pg_stat_statements poate fi multe lucruri. Ne-am confruntat cu situa\u021bia \u00een care, dac\u0103 ave\u021bi track_utility = on, atunci seturile sunt de asemenea urm\u0103rite. <\/p>\n<p><\/p>\n<p><em>Da, desigur.<\/em><\/p>\n<p><\/p>\n<p>\u0218i dac\u0103 ave\u021bi java hibernate, care este aleatorie, atunci \u00eencepe s\u0103 se blocheze tabela hash. \u0218i imediat ce dezactiva\u021bi o aplica\u021bie foarte solicitat\u0103, ajunge\u021bi la 50-100 de grupuri. \u0218i acolo totul devine mai mult sau mai pu\u021bin stabil. Una dintre metodele de combatere a acestui lucru este de a cre\u0219te pg_stat_statements.max. <\/p>\n<p><\/p>\n<p><em>Da, dar trebuie s\u0103 \u0219tii c\u00e2t de mult. \u0218i trebuie s\u0103-l monitoriz\u0103m. A\u0219a fac eu. Adic\u0103, am pg_stat_statements.max. \u0218i m\u0103 uit, la momentul snapshot-ului nu am ajuns la 70%. Bun, \u00eenseamn\u0103 c\u0103 nu am pierdut nimic. Facem reset. \u0218i acumul\u0103m din nou. Dac\u0103 \u00een urm\u0103torul snapshot este mai pu\u021bin de 70, atunci \u00eenseamn\u0103 c\u0103, cel mai probabil, din nou nu am pierdut nimic.<\/em><\/p>\n<p><\/p>\n<p>Da. \u00cen mod implicit acum 5000. \u0218i acest lucru este suficient pentru foarte mul\u021bi. <\/p>\n<p><\/p>\n<p><em>De obicei \u2013 da.<\/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=\"Reda\u021bi 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. Din partea mea, adaug c\u0103 dac\u0103 \u00een Postgres se afl\u0103 date confiden\u021biale \u0219i acestea nu trebuie s\u0103 ajung\u0103 \u00een medii de testare, atunci se poate folosi <noindex><a rel=\"nofollow\" href=\"https:\/\/labs.dalibo.com\/postgresql_anonymizer\">PostgreSQL Anonymizer<\/a><\/noindex>. Schema este aproximativ urm\u0103toarea:<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date&quot;. Nikolai Samohvalov\" src=\"\/wp-content\/uploads\/2020\/04\/cf9a2052364e09a20e68d2347dd0c25b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p>Sursa: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/498060\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u041d\u0438\u043a\u043e\u043b\u0430\u044f \u0421\u0430\u043c\u043e\u0445\u0432\u0430\u043b\u043e\u0432\u0430 &quot;\u041f\u0440\u043e\u043c\u044b\u0448\u043b\u0435\u043d\u043d\u044b\u0439 \u043f\u043e\u0434\u0445\u043e\u0434 \u043a \u0442\u044e\u043d\u0438\u043d\u0433\u0443 PostgreSQL: \u044d\u043a\u0441\u043f\u0435\u0440\u0438\u043c\u0435\u043d\u0442\u044b \u043d\u0430\u0434 \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445&quot; Shared_buffers = 25% \u2013 \u044d\u0442\u043e \u043c\u043d\u043e\u0433\u043e \u0438\u043b\u0438 \u043c\u0430\u043b\u043e? \u0418\u043b\u0438 \u0432 \u0441\u0430\u043c\u044b\u0439 \u0440\u0430\u0437? \u041a\u0430\u043a \u043f\u043e\u043d\u044f\u0442\u044c, \u043f\u043e\u0434\u0445\u043e\u0434\u0438\u0442 \u043b\u0438 \u044d\u0442\u0430 \u2013 \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u0443\u0441\u0442\u0430\u0440\u0435\u0432\u0448\u0430\u044f \u2013 \u0440\u0435\u043a\u043e\u043c\u0435\u043d\u0434\u0430\u0446\u0438\u044f \u0432 \u0432\u0430\u0448\u0435\u043c \u043a\u043e\u043d\u043a\u0440\u0435\u0442\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435? \u041f\u0440\u0438\u0448\u043b\u043e \u0432\u0440\u0435\u043c\u044f \u043f\u043e\u0434\u043e\u0439\u0442\u0438 \u043a \u0432\u043e\u043f\u0440\u043e\u0441\u0443 \u043f\u043e\u0434\u0431\u043e\u0440\u0430 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u043e\u0432 postgresql.conf &quot;\u043f\u043e-\u0432\u0437\u0440\u043e\u0441\u043b\u043e\u043c\u0443&quot;. \u041d\u0435 \u0441 \u043f\u043e\u043c\u043e\u0449\u044c\u044e \u0441\u043b\u0435\u043f\u044b\u0445 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":78743,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-78742","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2.1 - 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\/ro\/blog\/administrirovanie\/promyshlennyj-podhod-k-tyuningu-postgresql-eksperimenty-nad-bazami-dannyh-nikolaj-samohvalov\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"ro_RO\" \/>\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\/ro\/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\udd47Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date\u00bb. Nikolai Samohvalov | ProHoster","description":"V\u0103 propun s\u0103 lua\u021bi \u00een considerare transcrierea prezent\u0103rii lui Nikolai Samohvalov \"Abordare industrial\u0103 pentru optimizarea PostgreSQL: experimente asupra bazelor de date\" Shared_buffers = 25% \u2013 este mult sau pu\u021bin?","canonical_url":"https:\/\/prohoster.info\/ro\/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":"ro_RO","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\/ro\/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\/ro\/wp-json\/wp\/v2\/posts\/78742","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/comments?post=78742"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/posts\/78742\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media\/78743"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/media?parent=78742"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/categories?post=78742"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/ro\/wp-json\/wp\/v2\/tags?post=78742"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}