{"id":35920,"date":"2019-10-31T22:07:35","date_gmt":"2019-10-31T19:07:35","guid":{"rendered":"https:\/\/prohoster.info\/blog\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\/"},"modified":"2019-10-31T22:07:35","modified_gmt":"2019-10-31T19:07:35","slug":"nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","title":{"rendered":"Linuxi tuumaparameetrite seadistamine PostgreSQL optimeerimiseks","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Linuxi tuumaparameetrite seadistamine PostgreSQL optimeerimiseks\" src=\"\/wp-content\/uploads\/2019\/07\/a28458ff50cdc517aad71b47adab6eb3.jpeg\" style=\"display:block;margin: 0 auto;\" \/> PostgreSQL'i optimeerimine s\u00f5ltub \u00f5igesti m\u00e4\u00e4ratud operatsioonis\u00fcsteemi parameetritest. Vale konfiguratsiooniga OS-i tuumaparameetrid v\u00f5ivad v\u00e4hendada andmebaasi serveri j\u00f5udlust. Seet\u00f5ttu on oluline, et need parameetrid oleksid seadistatud vastavalt andmebaasi serverile ja selle koormusele. Selles postituses arutame m\u00f5ningaid olulisi Linuxi tuumaparaametreid, mis v\u00f5ivad m\u00f5jutada andmebaasi serveri j\u00f5udlust ja nende seadistamise viise.<\/p>\n<h2>SHMMAX \/ SHMALL<\/h2>\n<p>\n<b>SHMMAX<\/b> on tuumaparameeter, mida kasutatakse maksimaalse \u00fchte jagatud m\u00e4lu (shared memory) segmenti suuruse m\u00e4\u00e4ramiseks, mille Linuxi protsess saab eraldada. Enne versiooni 9.2 kasutas PostgreSQL System V (SysV), mis n\u00f5udis SHMMAX seadistamist. P\u00e4rast versiooni 9.2 on PostgreSQL \u00fclemineku POSIX jagatud m\u00e4lule. Nii et n\u00fc\u00fcd on vajalik v\u00e4hem System V jagatud m\u00e4lu baite.<\/p>\n<p>Enne versiooni 9.3 oli SHMMAX k\u00f5ige olulisem tuumaparameeter. SHMMAX v\u00e4\u00e4rtus on tiksustatud baitides.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nSarnasel viisil, <b>SHMALL<\/b> on veel \u00fcks tuumaparameeter, mida kasutatakse m\u00e4\u00e4ramiseks <br \/>\n\u00fcldise jagatud m\u00e4lu lehtede (shared memory) maht. Praeguste SHMMAX, SHMALL v\u00f5i SHMMIN v\u00e4\u00e4rtuste vaatamiseks kasutage k\u00e4sku <i>ipcs<\/i>.<\/p>\n<p><b>SHM* \u00dcksikasjad \u2014 Linux<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -lm\n\n------ Jagatud m\u00e4lu piirangud --------\nmax segmentide arv = 4096\nmax segmendi suurus (kbytes) = 1073741824\nmax kogu jagatud m\u00e4lu (kbytes) = 17179869184\nmin segmendi suurus (baitides) = 1<\/code><\/pre>\n<p>\n<b>SHM* \u00dcksikasjad \u2014 MacOS X<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -M\nIPC staatuse aruandlus alates Thu Aug 16 22:20:35 PKT 2018\nshminfo:\n\tshmmax: 16777216\t(max jagatud m\u00e4lu segmenti suurus)\n\tshmmin:       1\t(min jagatud m\u00e4lu segmenti suurus)\n\tshmmni:      32\t(max jagatud m\u00e4lu identifikaatorite arv)\n\tshmseg:       8\t(max jagatud m\u00e4lu segmente protsessi kohta)\n\tshmall:    1024\t(max jagatud m\u00e4lu hulk lehtedes)\n<\/code><\/pre>\n<p>\nPostgreSQL kasutab <b>System V IPC<\/b> jagatud m\u00e4lu eraldamiseks. See parameeter on \u00fcks t\u00e4htsamaid tuumaparameetreid. Iga kord, kui saate j\u00e4rgmised veateated, t\u00e4hendab see, et teil on vanem PostgreSQL versioon ja SHMMAX v\u00e4\u00e4rtus on v\u00e4ga madal. Eeldatakse, et kasutajad kohandavad ja suurendavad v\u00e4\u00e4rtust vastavalt jagatud m\u00e4lule, mida nad kavatsevad kasutada.<\/p>\n<h3>Vale konfiguratsiooni vead<\/h3>\n<p>\nKui SHMMAX on valesti seadistatud, v\u00f5ite saada vea, kui proovite PostgreSQL klastrit initsialiseerida k\u00e4suga <i>initdb<\/i>.<\/p>\n<p><b>initdb eba\u00f5nnestumine<\/b><br \/>\n<code>DETAIL: Eba s\u00fcsteemik\u00f5ne, mida ei suudetud t\u00e4ita, oli shmget(key=1, size=2072576, 03600). <\/p>\n<p>HINT: See viga t\u00e4hendab tavaliselt, et PostgreSQL'i p\u00e4ring jagatud m\u00e4lufragmenti \u00fcletas teie kernelis oleva SHMMAX parameetri.\u00a0 <br \/>\nV\u00f5ite kas v\u00e4hendada p\u00e4ringu suurust v\u00f5i konfigureerida kernelit suurema SHMMAX-iga. P\u00e4ringu suuruse v\u00e4hendamiseks (praegu 2072576 baitset), <br \/>\nv\u00e4hendage PostgreSQL'i jagatud m\u00e4lukasutust, v\u00f5ib-olla v\u00e4hendades shared_buffers v\u00f5i max_connections. <\/p>\n<p>Kui p\u00e4ringu suurus on juba v\u00e4ike, v\u00f5ib see olla v\u00e4iksem kui teie kernelis oleva SHMMIN parameeter,<br \/>\nsel juhul on vajalik p\u00e4ringu suuruse suurendamine v\u00f5i SHMMIN-i konfigureerimine. <\/p>\n<p>PostgreSQLi dokumentatsioonis on rohkem teavet jagatud m\u00e4lukonfiguratsiooni kohta. lapseprotsess l\u00f5petas t\u00f6\u00f6 v\u00e4ljundkoodiga 1<\/code><\/p>\n<p>Sarnaselt v\u00f5ite saada vea, kui k\u00e4ivitate PostgreSQL serveri k\u00e4suga <i>pg_ctl<\/i>.<\/p>\n<p><b>pg_ctl eba\u00f5nnestumine<\/b><br \/>\n<code>DETAIL: Eba s\u00fcsteemik\u00f5ne oli shmget(key=5432001, size=14385152, 03600).<\/p>\n<p>HINT: See viga t\u00e4hendab tavaliselt, et PostgreSQL'i p\u00e4ring jagatud m\u00e4lufragmenti \u00fcletas teie kernelis oleva SHMMAX parameetri.<\/p>\n<p>Saate kas v\u00e4hendada p\u00e4ringu suurust v\u00f5i konfigureerida kernelit suurema SHMMAX-i m\u00e4\u00e4raga. V\u00e4hendamiseks (hetkel 14385152 baitides) v\u00e4hendage PostgreSQL jagatud m\u00e4lu kasutamist, v\u00f5ib-olla v\u00e4hendades shared_buffers v\u00f5i max_connections.<\/p>\n<p>Kui p\u00e4ringu suurus on juba v\u00e4ike, v\u00f5ib see olla v\u00e4iksem kui teie kernelis oleva SHMMIN parameeter, <br \/>\nsel juhul on vajalik p\u00e4ringu suuruse suurendamine v\u00f5i SHMMIN-i konfigureerimine.<\/p>\n<p>PostgreSQL dokumentatsioon sisaldab rohkem teavet jagatud m\u00e4lu konfiguratsiooni kohta.<\/code><\/p>\n<h3>M\u00f5istmine definitsioonide erinevustest<\/h3>\n<p>\nSHMMAX\/SHMALL parameetrite m\u00e4\u00e4ratlemine on Linuxis ja MacOS X-is veidi erinev:<\/p>\n<ul>\n<li>Linux: kernel.shmmax, kernel.shmall<\/li>\n<li>MacOS X: kern.sysv.shmmax, kern.sysv.shmalla<\/li>\n<\/ul>\n<p>\nMeeskond <i>sysctl<\/i> seda saab kasutada v\u00e4\u00e4rtuse ajutiseks muutmiseks. P\u00fcsivalt v\u00e4\u00e4rtuste seadmiseks lisage kirje <i>\/etc\/sysctl.conf<\/i>. \u00dcksikasjad on allpool.<\/p>\n<p><b>Tuuma seadete muutmine MacOS X-is<\/b><\/p>\n<pre><code class=\"plaintext\"># Get the value of SHMMAX\nsudo sysctl kern.sysv.shmmax\nkern.sysv.shmmax: 4096\n\n# Get the value of SHMALL\nsudo sysctl kern.sysv.shmall \nkern.sysv.shmall: 4096\n\n# Set the value of SHMMAX\nsudo sysctl -w kern.sysv.shmmax=16777216\nkern.sysv.shmmax: 4096 -&gt; 16777216\n\n# Set the value of SHMALL \nsudo sysctl -w kern.sysv.shmall=16777216\nkern.sysv.shmall: 4096 -&gt; 16777216<\/code><\/pre>\n<p>\n<b>Tuuma seadete muutmine Linuxis<\/b><\/p>\n<pre><code class=\"plaintext\"># Get the value of SHMMAX\nsudo sysctl kernel.shmmax\nkernel.shmmax: 4096\n\n# Get the value of SHMALL\nsudo sysctl kernel.shmall\nkernel.shmall: 4096\n\n# Set the value of SHMMAX\nsudo sysctl -w kernel.shmmax=16777216\nkernel.shmmax: 4096 -&gt; 16777216\n\n# Set the value of SHMALL \nsudo sysctl -w kernel.shmall=16777216\nkernel.shmall: 4096 -&gt; 16777216<\/code><\/pre>\n<p>\n<b>\u00c4rge unustage<\/b>: et muudatused oleksid p\u00fcsivad, lisage need v\u00e4\u00e4rtused faili \/etc\/sysctl.conf<\/p>\n<h2>Suurendatud lehed (Huge Pages)<\/h2>\n<p>\nLinuxis kasutatakse vaikimisi 4 KB m\u00e4lulehti, BSD-s \u2014 <i>Super Pages<\/i>, ja Windowsis \u2014 <i>Suured lehed<\/i>. Leht on protsessile eraldatud osa t\u00f6\u00f6m\u00e4lu. Protsessil v\u00f5ib olla mitu lehte s\u00f5ltuvalt m\u00e4lu n\u00f5udmistest. Mida rohkem m\u00e4lu protsess vajab, seda rohkem lehti talle eraldatakse. OPS toetab protsesside lehtede eraldamise tabelit. Mida v\u00e4iksem on lehe suurus, seda suurem on tabel, seda rohkem aega kulub lehe leidmiseks selles lehtede tabelis. Seet\u00f5ttu v\u00f5imaldavad suured lehed kasutada suurt m\u00e4lumahtu v\u00e4iksemate halduskuludega; v\u00e4hem lehte vaadatakse, v\u00e4hem lehe vigu, kiiremad lugemis-\/kirjutamisoperatsioonid suurte pubufferitega. Tulemuseks on j\u00f5udluse paranemine.<\/p>\n<p>PostgreSQL toetab suuri lehti ainult Linuxis. Vaikimisi kasutab Linux 4 KB m\u00e4lulehti, seet\u00f5ttu, kui m\u00e4luoperatsioone on liiga palju, on vajalik seadistada suuremad lehed. Suurte lehtede kasutamisel, mille suurus on 2 MB kuni 1 GB, on t\u00e4heldatud j\u00f5udluse kasvu. Suure lehe suurust saab m\u00e4\u00e4rata k\u00e4ivitamise ajal. Saate oma Linuxi arvuti suurte lehtede seadistusi ja kasutust h\u00f5lpsasti kontrollida k\u00e4suga <i>cat \/proc\/meminfo | grep -i huge<\/i>.<\/p>\n<p><b>Suurete lehtede teabe saamine (ainult Linuxis)<\/b><\/p>\n<pre><code class=\"bash\">M\u00e4rkus: see kehtib ainult Linuxile, teiste ops\u00fcsteemide puhul see toiming ignoreeritakse $ cat \/proc\/meminfo | grep -i huge\nAnonHugePages:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 kB\nShmemHugePages:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 kB\nHugePages_Total:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nHugePages_Free:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nHugePages_Rsvd:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nHugePages_Surp:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\nHugepagesize:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2048 kB<\/code><\/pre>\n<p>\nSelles n\u00e4ites, kuigi suure lehe suurus on seadistatud 2048 (2 MB), on suurte lehtede koguarv 0. See t\u00e4hendab, et suured lehed on v\u00e4lja l\u00fclitatud.<\/p>\n<h3>Suurte lehtede arvu m\u00e4\u00e4ramise skript<\/h3>\n<p>\nSee on lihtne skript, mis tagastab vajalikku arvu suuri lehti. K\u00e4ivitage skript oma Linuxi serveris, kuni PostgreSQL t\u00f6\u00f6tab. Veenduge, et keskkonnamuutuja <i>$PGDATA<\/i> on seadistatud PostgreSQLi andmekatalooge.<\/p>\n<p><b>Suure lehe arvu m\u00e4\u00e4ramine<\/b><\/p>\n<pre><code class=\"bash\">#!\/bin\/bash\npid=`head -1 $PGDATA\/postmaster.pid`\necho \"Pid:\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $pid\"\npeak=`grep ^VmPeak \/proc\/$pid\/status | awk '{ print $2 }'`\necho \"VmPeak:\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $peak kB\"\nhps=`grep ^Hugepagesize \/proc\/meminfo | awk '{ print $2 }'`\necho \"Hugepagesize:\u00a0\u00a0 $hps kB\"\nhp=$((peak\/hps))\necho Set Huge Pages:\u00a0\u00a0\u00a0\u00a0 $hp<\/code><\/pre>\n<p>\nSkripti v\u00e4ljund n\u00e4eb v\u00e4lja j\u00e4rgmine:<\/p>\n<p><b>Skripti v\u00e4ljund<\/b><\/p>\n<pre><code class=\"bash\">Pid:            12737\nVmPeak:        180932 kB\nHugepagesize:   2048 kB\nSet Huge Pages: 88<\/code><\/pre>\n<p>\nSoovitatav suurte lehtede v\u00e4\u00e4rtus on 88, seega peaksite seadistama v\u00e4\u00e4rtuseks 88.<\/p>\n<p><b>Suurte lehtede seadmine<\/b><\/p>\n<pre><code class=\"bash\">sysctl -w vm.nr_hugepages=88<\/code><\/pre>\n<p>\nKontrollige n\u00fc\u00fcd suurte lehtede staatust, n\u00e4ete, et suurte lehti ei kasutata (HugePages_Free = HugePages_Total).<\/p>\n<p><b>Taaskord suurte lehtede teave (ainult Linuxis)<\/b><\/p>\n<pre><code class=\"bash\">$ cat \/proc\/meminfo | grep -i huge\nAnonHugePages:         0 kB\nShmemHugePages:        0 kB\nHugePages_Total:      88\nHugePages_Free:       88\nHugePages_Rsvd:        0\nHugePages_Surp:       0\nHugepagesize:       2048 kB<\/code><\/pre>\n<p>\nN\u00fc\u00fcd seadke huge_pages \u00abon\u00bb $PGDATA\/postgresql.conf failis ja taask\u00e4ivitage server.<\/p>\n<p><b>Ja taas suurte lehtede teave (ainult Linuxis)<\/b><\/p>\n<pre><code class=\"bash\">$ cat \/proc\/meminfo | grep -i huge\nAnonHugePages:         0 kB\nShmemHugePages:        0 kB\nHugePages_Total:      88\nHugePages_Free:       81\nHugePages_Rsvd:       64\nHugePages_Surp:       0\nHugepagesize:       2048 kB<\/code><\/pre>\n<p>\nN\u00fc\u00fcd n\u00e4ete, et v\u00e4ga v\u00e4he suuri lehti on kasutatud. Proovime n\u00fc\u00fcd andmeid andmebaasi lisada.<\/p>\n<p><b>Andmebaasi operatsioonid suurte lehtede \u00e4ra kasutamiseks<\/b><\/p>\n<pre><code class=\"sql\">postgres=# CREATE TABLE foo(a INTEGER);\nCREATE TABLE\npostgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));\nINSERT 0 10000000<\/code><\/pre>\n<p>\nVaadakem, kas me n\u00fc\u00fcd kasutame rohkem suuri lehti kui varem.<\/p>\n<p><b>Veel kord suurte lehtede teave (ainult Linuxis)<\/b><\/p>\n<pre><code class=\"bash\">$ cat \/proc\/meminfo | grep -i huge\nAnonHugePages:         0 kB\nShmemHugePages:        0 kB\nHugePages_Total:      88\nHugePages_Free:       18\nHugePages_Rsvd:       1\nHugePages_Surp:       0\nHugepagesize:       2048 kB<\/code><\/pre>\n<p>\nN\u00fc\u00fcd n\u00e4ete, et enamik suuri lehti on kasutusel.<\/p>\n<p>M\u00e4rkus: siinkohal kasutatud HugePagesi ligikaudne v\u00e4\u00e4rtus on v\u00e4ga madal, mis ei ole tootmisringkonna masina jaoks normaalne. Palun hinnake oma s\u00fcsteemi jaoks vajalik lehtede arv ning seadistage need vastavalt koormusele ja ressurssidele.<\/p>\n<h3>vm.swappiness<\/h3>\n<p>\n<b>vm.swappiness<\/b> \u2014 see on veel \u00fcks tuuma parameeter, mis v\u00f5ib m\u00f5jutada andmebaasi j\u00f5udlust. Seda parameetrit kasutatakse lehe vahetamise (swappiness) k\u00e4itumise juhtimiseks Linuxis. V\u00e4\u00e4rtus ulatub 0 kuni 100. See m\u00e4\u00e4rab, kui palju m\u00e4lu laaditakse v\u00f5i laaditakse tagasi. Null t\u00e4histab vahetamise keelamist, 100 aga agressiivset vahetamist.<\/p>\n<p>Saate head j\u00f5udlust saavutada, seadistades madalamad v\u00e4\u00e4rtused.<\/p>\n<p>Uute tuumade puhul 0 v\u00e4\u00e4rtuse seadmine v\u00f5ib tuua kaasa selle, et OOM Killer (Linuxi m\u00e4lu puhastamise protsess) tapab protsessi. Seega on ohutu seada v\u00e4\u00e4rtus 1, kui soovite vahetamisest teadlikult v\u00e4ltida. Linuxi vaikimisi v\u00e4\u00e4rtus on 60. K\u00f5rgem v\u00e4\u00e4rtus paneb MMU (m\u00e4lu haldamise \u00fchik) kasutama rohkem vahetusruumi kui RAM, samas kui madalam v\u00e4\u00e4rtus hoiab rohkem andmeid\/koodi m\u00e4lus.<\/p>\n<p>Madalam v\u00e4\u00e4rtus on hea valik PostgreSQL j\u00f5udluse parandamiseks.<\/p>\n<h3>vm.overcommit_memory \/ vm.overcommit_ratio<\/h3>\n<p>\nRakendused saavad m\u00e4lu ja vabastavad selle, kui see enam ei ole vajalik. Kuid m\u00f5nel juhul saab rakendus liiga palju m\u00e4lu ja ei vabasta seda. See v\u00f5ib p\u00f5hjustada OOM killer'i. Siin on v\u00f5imalike parameetrite v\u00e4\u00e4rtused <b>vm.overcommit_memory<\/b> iga\u00fche kirjeldusega:<\/p>\n<ol>\n<li> Heuristiline \u00fclekompenseerimine (vaikimisi); p\u00f5hineb tuuma heuristikatel<\/li>\n<li> Lubada \u00fclekompenseerimine igal juhul<\/li>\n<li> \u00c4rge \u00fcle pingutage, \u00e4rge \u00fcletage \u00fclekompensatsiooni suhet.<\/li>\n<\/ol>\n<p>\n<i>Link: <noindex><a rel=\"nofollow\" href=\"https:\/\/www.kernel.org\/doc\/Documentation\/vm\/overcommit-accounting\">https:\/\/www.kernel.org\/doc\/Documentation\/vm\/overcommit-accounting<\/a><\/noindex><\/i><\/p>\n<p><b>vm.overcommit_ratio<\/b> \u2014 protsent RAM-ist, mis on saadaval \u00fclekompenseerimiseks. 50% v\u00e4\u00e4rtus 2 GB RAM-iga s\u00fcsteemis v\u00f5ib eraldada kuni 3 GB RAM-i.<\/p>\n<p>V\u00e4\u00e4rtus 2 vm.overcommit_memory jaoks tagab parema j\u00f5udluse PostgreSQL-ile. See maksimeerib serveri protsessi RAM-i kasutamise ilma OOM killer'iga tapmise m\u00e4rkimisv\u00e4\u00e4rse riskita. Rakendus saab taask\u00e4ivitada, kuid ainult \u00fcletatavates piirides, v\u00e4hendades OOM killer'i protsessi tapmise ohtu. Seega annab v\u00e4\u00e4rtus 2 parema j\u00f5udluse kui vaikimisi 0. Siiski, usaldusv\u00e4\u00e4rsust saab parandada, v\u00e4ltides lubatust k\u00f5rgemal p\u00f5hineva m\u00e4lu \u00fclekoormamist. See v\u00e4listab riski, et protsess tapetakse OOM killer'i poolt.<\/p>\n<p>Ilma vahetusruumita s\u00fcsteemides v\u00f5ib tekkida probleem, kui vm.overcommit_memory on 2.<\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/static\/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT\">https:\/\/www.postgresql.org\/docs\/current\/static\/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT<\/a><\/noindex><\/p>\n<h3>vm.dirty_background_ratio \/ vm.dirty_background_bytes<\/h3>\n<p>\n<b>vm.dirty_background_ratio<\/b> \u2014 see on protsent m\u00e4lu, mis on t\u00e4idetud \u201e\u00fclalolevate\u201d lehtedega, mis tuleb kirjutada kettale. Kettale kirjutamine toimub taustal. Selle parameetri v\u00e4\u00e4rtus on vahemikus 0 kuni 100; siiski v\u00f5ib v\u00e4\u00e4rtus alla 5 olla ebaefektiivne ja m\u00f5ned tuumad ei pruugi seda toetada. Enamikes Linuxi s\u00fcsteemides on vaikimisi v\u00e4\u00e4rtus 10. Saate kirjutamisintensiivsete toimingute jaoks t\u00f6\u00f6v\u00f5imet suurendada madalama suhtarvuga, mis t\u00e4hendab, et Linux kirjutab \u201e\u00fclalolevad\u201d lehed taustal.<\/p>\n<p>Peate seadistama v\u00e4\u00e4rtuse <b>vm.dirty_background_bytes<\/b> vastavalt teie ketta kiirus.<\/p>\n<p>Neil kahel parameetril pole \u201eh\u00e4id\u201d v\u00e4\u00e4rtusi, kuna m\u00f5lemad s\u00f5ltuvad riistvarast. Kuid vm.dirty_background_ratio seadistamine v\u00e4\u00e4rtusele 5 ja vm.dirty_background_bytes seadistamine 25% ketta kiirusest t\u00f5stab j\u00f5udlust enamikes juhtudel ~ 25%.<\/p>\n<h3>vm.dirty_ratio \/ dirty_bytes<\/h3>\n<p>\nSee on sama, mis <b>vm.dirty_background_ratio \/ dirty_background_bytes<\/b>, v\u00e4lja arvatud see, et kirjutamine toimub aktiivses sessioonis, blokeerides rakendust. Seet\u00f5ttu peab vm.dirty_ratio olema suurem kui <b>vm.dirty_background_ratio<\/b>. See tagab, et taustprotsessid k\u00e4ivitatakse varem, et v\u00e4ltida rakenduse maksimaalselt v\u00f5imalikku blokeerimist. Saate reguleerida nende kahe suhte erinevust vastavalt kettasisendite \u00fclesande koormusele.<\/p>\n<h2>Kokkuv\u00f5te<\/h2>\n<p>\nSaate seadistada muid parameetreid j\u00f5udluse suurendamiseks, kuid parandused on minimaalset laadi ja reaalset kasu pole. Peame meeles pidama, et mitte k\u00f5ik parameetrid ei kehti k\u00f5ikidele rakendustele. M\u00f5ned rakendused toimivad paremini, kui seadistame teatud parameetrite, ja m\u00f5ned mitte. Peate leidma \u00f5ige tasakaalu nende parameetrite seadete vahel oodatava koormuse ja rakendust\u00fc\u00fcbi jaoks, samuti tuleb seadistamisel arvestada operatsioonis\u00fcsteemi k\u00e4itumisega. S\u00fcdamiku parameetrite seadistamine pole nii lihtne kui andmebaasi parameetrite seadmine: siin on keerulisem anda oma soovitusi.<br \/>\n<br \/>Allikas: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/458860\/\">habr.com<\/a><\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u0430\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c PostgreSQL \u0437\u0430\u0432\u0438\u0441\u0438\u0442 \u043e\u0442 \u043f\u0440\u0430\u0432\u0438\u043b\u044c\u043d\u043e \u043e\u043f\u0440\u0435\u0434\u0435\u043b\u0435\u043d\u043d\u044b\u0445 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u043e\u0432 \u043e\u043f\u0435\u0440\u0430\u0446\u0438\u043e\u043d\u043d\u043e\u0439 \u0441\u0438\u0441\u0442\u0435\u043c\u044b. \u041f\u043b\u043e\u0445\u043e \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u043d\u044b\u0435 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b \u044f\u0434\u0440\u0430 \u041e\u0421 \u043c\u043e\u0433\u0443\u0442 \u043f\u0440\u0438\u0432\u0435\u0441\u0442\u0438 \u043a \u0441\u043d\u0438\u0436\u0435\u043d\u0438\u044e \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445. \u041f\u043e\u044d\u0442\u043e\u043c\u0443 \u043e\u0431\u044f\u0437\u0430\u0442\u0435\u043b\u044c\u043d\u043e, \u0447\u0442\u043e\u0431\u044b \u044d\u0442\u0438 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b \u0431\u044b\u043b\u0438 \u043d\u0430\u0441\u0442\u0440\u043e\u0435\u043d\u044b \u0432 \u0441\u043e\u043e\u0442\u0432\u0435\u0442\u0441\u0442\u0432\u0438\u0438 \u0441 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u043c \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0435\u0433\u043e \u0440\u0430\u0431\u043e\u0447\u0435\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u043e\u0439. \u0412 \u044d\u0442\u043e\u043c \u043f\u043e\u0441\u0442\u0435 \u043c\u044b \u043e\u0431\u0441\u0443\u0434\u0438\u043c \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0430\u0436\u043d\u044b\u0435 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u044b \u044f\u0434\u0440\u0430 Linux, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043c\u043e\u0433\u0443\u0442 \u043f\u043e\u0432\u043b\u0438\u044f\u0442\u044c \u043d\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":26870,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-35920","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.1.1 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u0430\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c.\" \/>\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\/et\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.1.1\" \/>\n\t\t<meta property=\"og:locale\" content=\"et_EE\" \/>\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\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u043e\u0432 \u044f\u0434\u0440\u0430 Linux \u0434\u043b\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 PostgreSQL | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u0430\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\" \/>\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=\"2019-10-31T19:07:35+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2019-10-31T19:07:35+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\udd47Linuxi ydinparameetrite seadistamine PostgreSQLi optimeerimiseks | ProHoster","description":"Optimaalne j\u00f5udlus.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\u041d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u0430 \u043f\u0430\u0440\u0430\u043c\u0435\u0442\u0440\u043e\u0432 \u044f\u0434\u0440\u0430 Linux \u0434\u043b\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 PostgreSQL | ProHoster","og:description":"\u041e\u043f\u0442\u0438\u043c\u0430\u043b\u044c\u043d\u0430\u044f \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c.","og:url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","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":"2019-10-31T19:07:35+00:00","article:modified_time":"2019-10-31T19:07:35+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"35920","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":"2026-01-22 01:17:19","breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-03-01 01:53:32","updated":"2026-01-22 01:17:19","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/35920","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=35920"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/35920\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/26870"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=35920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=35920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=35920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}