{"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\/lv\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","title":{"rendered":"Linux kodola parametru iestat\u012b\u0161ana PostgreSQL optimiz\u0113\u0161anai","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"Linux kodola parametru iestat\u012b\u0161ana PostgreSQL optimiz\u0113\u0161anai\" src=\"\/wp-content\/uploads\/2019\/07\/a28458ff50cdc517aad71b47adab6eb3.jpeg\" style=\"display:block;margin: 0 auto;\" \/> Optimiz\u0113t\u0101 PostgreSQL veiktsp\u0113ja ir atkar\u012bga no pareizi noteiktiem oper\u0113t\u0101jsist\u0113mas parametriem. Slikti konfigur\u0113ti OS kodola parametri var samazin\u0101t datu b\u0101zes servera veiktsp\u0113ju. T\u0101p\u0113c ir b\u016btiski, lai \u0161ie parametri b\u016btu piel\u0101goti datu b\u0101zes serverim un t\u0101 slodzei. \u0160aj\u0101 ierakst\u0101 m\u0113s apspried\u012bsim da\u017eus svar\u012bgus Linux kodola parametrus, kas var ietekm\u0113t datu b\u0101zes servera veiktsp\u0113ju, un to iestat\u012b\u0161anas veidus.<\/p>\n<h2>SHMMAX \/ SHMALL<\/h2>\n<p>\n<b>SHMMAX<\/b> ir kodola parametrs, kas tiek izmantots, lai noteiktu maksim\u0101lo vienas segmenta koplieto\u0161anas atmi\u0146as (shared memory) izm\u0113ru, ko var pie\u0161\u0137irt Linux process. L\u012bdz versijai 9.2 PostgreSQL izmantoja System V (SysV), kurai nepiecie\u0161ama SHMMAX iestat\u012b\u0161ana. P\u0113c 9.2 PostgreSQL p\u0101rg\u0101ja uz POSIX koplieto\u0161anas atmi\u0146u. T\u0101tad tagad nepiecie\u0161ams maz\u0101k System V koplieto\u0161anas atmi\u0146as baitiem.<\/p>\n<p>L\u012bdz versijai 9.3 SHMMAX bija vissvar\u012bg\u0101kais kodola parametrs. SHMMAX v\u0113rt\u012bba tiek noteikta baitos.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nL\u012bdz\u012bgi, <b>SHMALL<\/b> ir v\u0113l viens kodola parametrs, kas tiek izmantots, lai noteiktu <br \/>\nkop\u0113jo lapu apjomu koplieto\u0161anas atmi\u0146\u0101 (shared memory). Lai apskat\u012btu pa\u0161reiz\u0113j\u0101s SHMMAX, SHMALL vai SHMMIN v\u0113rt\u012bbas, izmantojiet komandu <i>ipcs<\/i>.<\/p>\n<p><b>SHM* deta\u013cas \u2014 Linux<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -lm\n\n------ Koplieto\u0161anas Atmi\u0146as Ierobe\u017eojumi --------\nmaksim\u0101lais segmentu skaits = 4096\nmaksim\u0101lais segmenta lielums (kbytes) = 1073741824\nmaksim\u0101l\u0101 kop\u0113j\u0101 koplieto\u0161anas atmi\u0146a (kbytes) = 17179869184\nminim\u0101lais segmenta lielums (baitos) = 1<\/code><\/pre>\n<p>\n<b>SHM* deta\u013cas \u2014 MacOS X<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -M\nIPC statuss no 2018. gada 16. augusta 22:20:35 PKT\nshminfo:\n\tshmmax: 16777216\t(maksim\u0101lais koplieto\u0161anas atmi\u0146as segmenta lielums)\n\tshmmin:       1\t(minim\u0101lais koplieto\u0161anas atmi\u0146as segmenta lielums)\n\tshmmni:      32\t(maksim\u0101lais koplieto\u0161anas atmi\u0146as identifikatoru skaits)\n\tshmseg:       8\t(maksim\u0101lais koplieto\u0161anas atmi\u0146as segmentu skaits uz procesu)\n\tshmall:    1024\t(maksim\u0101lais koplieto\u0161anas atmi\u0146as apjoms lap\u0101s)\n<\/code><\/pre>\n<p>\nPostgreSQL izmanto <b>System V IPC<\/b> lai pie\u0161\u0137irtu koplieto\u0161anas atmi\u0146u. \u0160is parametrs ir viens no vissvar\u012bg\u0101kajiem kodola parametriem. Katru reizi, kad sa\u0146emat sekojo\u0161\u0101s k\u013c\u016bdas zi\u0146as, tas noz\u012bm\u0113, ka jums ir vec\u0101ka PostgreSQL versija un \u013coti zema SHMMAX v\u0113rt\u012bba. Tiek sagaid\u012bts, ka lietot\u0101ji piel\u0101gos un palielin\u0101s v\u0113rt\u012bbu saska\u0146\u0101 ar koplieto\u0161anas atmi\u0146u, ko vi\u0146i pl\u0101no izmantot.<\/p>\n<h3>Iesp\u0113jam\u0101s nepareizas konfigur\u0101cijas k\u013c\u016bdas<\/h3>\n<p>\nJa SHMMAX ir nepareizi iestat\u012bts, j\u016bs varat sa\u0146emt k\u013c\u016bdu, m\u0113\u0123inot inicializ\u0113t PostgreSQL klasteri, izmantojot komandu <i>initdb<\/i>.<\/p>\n<p><b>initdb neveiksme<\/b><br \/>\n<code>DETA\u013bAS: Neizdev\u0101s izsaukt sist\u0113mas funkciju shmget(key=1, size=2072576, 03600). <\/p>\n<p>PADOMS: \u0160\u012b k\u013c\u016bda parasti noz\u012bm\u0113, ka PostgreSQL piepras\u012bjums p\u0113c koplietojam\u0101s atmi\u0146as segmenta p\u0101rsniedza j\u016bsu kodola SHMMAX parametrus.\u00a0 <br \/>\nJ\u016bs varat samazin\u0101t piepras\u012bjuma izm\u0113ru vai p\u0101rlabot kodolu ar liel\u0101ku SHMMAX. Lai samazin\u0101tu piepras\u012bjuma izm\u0113ru (patlaban 2072576 baitus), <br \/>\nsamaziniet PostgreSQL koplietojam\u0101s atmi\u0146as pat\u0113ri\u0146u, iesp\u0113jams, samazinot shared_buffers vai max_connections. <\/p>\n<p>Ja piepras\u012bjuma izm\u0113rs jau ir neliels, iesp\u0113jams, ka tas ir maz\u0101ks par j\u016bsu kodola SHMMIN parametru,<br \/>\nt\u0101d\u0101 gad\u012bjum\u0101 j\u0101palielina piepras\u012bjuma izm\u0113rs vai j\u0101veic SHMMIN konfigur\u0101cijas izmai\u0146as. <\/p>\n<p>PostgreSQL dokument\u0101cija satur vair\u0101k inform\u0101cijas par koplietojam\u0101s atmi\u0146as konfigur\u0101ciju. b\u0113rnu process izn\u0101ca ar izn\u0101kuma kodu 1.<\/code><\/p>\n<p>L\u012bdz\u012bgi, j\u016bs varat sa\u0146emt k\u013c\u016bdu, palai\u017eot PostgreSQL serveri, izmantojot komandu <i>pg_ctl<\/i>.<\/p>\n<p><b>pg_ctl neveiksme<\/b><br \/>\n<code>DZI\u013bAS: Neizdev\u0101s sist\u0113mas izsaukums shmget(key=5432001, size=14385152, 03600).<\/p>\n<p>PADOMS: \u0160\u012b k\u013c\u016bda parasti noz\u012bm\u0113, ka PostgreSQL piepras\u012bjums p\u0113c koplietojam\u0101s atmi\u0146as segmenta p\u0101rsniedza j\u016bsu kodola SHMMAX parametrus.<\/p>\n<p>J\u016bs varat samazin\u0101t piepras\u012bjuma izm\u0113ru vai p\u0101rkonfigur\u0113t kodolu ar liel\u0101ku SHMMAX.; Lai samazin\u0101tu piepras\u012bjuma izm\u0113ru (pa\u0161laik 14385152 baiti), samaziniet PostgreSQL koplieto\u0161anas atmi\u0146as pat\u0113ri\u0146u, iesp\u0113jams, samazinot shared_buffers vai max_connections.<\/p>\n<p>Ja piepras\u012bjuma izm\u0113rs jau ir neliels, iesp\u0113jams, ka tas ir maz\u0101ks par j\u016bsu kodola SHMMIN parametru, <br \/>\nt\u0101d\u0101 gad\u012bjum\u0101 j\u0101palielina piepras\u012bjuma izm\u0113rs vai j\u0101veic SHMMIN konfigur\u0101cijas izmai\u0146as.<\/p>\n<p>PostgreSQL dokument\u0101cij\u0101 ir vair\u0101k inform\u0101cijas par koplieto\u0161anas atmi\u0146as konfigur\u0101ciju.<\/code><\/p>\n<h3>Izpratne at\u0161\u0137ir\u012bbas defin\u012bcij\u0101s<\/h3>\n<p>\nSHMMAX\/SHMALL parametru defin\u012bcija Linux un MacOS X nedaudz at\u0161\u0137iras:<\/p>\n<ul>\n<li>Linux: kernel.shmmax, kernel.shmall<\/li>\n<li>MacOS X: kern.sysv.shmmax, kern.sysv.shmall<\/li>\n<\/ul>\n<p>\nKomanda <i>sysctl<\/i> var tikt izmantots, lai pagaidu main\u012btu v\u0113rt\u012bbu. Lai iestat\u012btu past\u0101v\u012bgas v\u0113rt\u012bbas, pievienojiet ierakstu <i>\/etc\/sysctl.conf<\/i>. S\u012bk\u0101ka inform\u0101cija sniegta zem\u0101k.<\/p>\n<p><b>Kern parametru mai\u0146a MacOS X<\/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>Kern parametru mai\u0146a Linux<\/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>Neaizmirstiet<\/b>: lai izdar\u012btu izmai\u0146as past\u0101v\u012bgas, pievienojiet \u0161\u012bs v\u0113rt\u012bbas fail\u0101 \/etc\/sysctl.conf<\/p>\n<h2>Lielas lapas (Huge Pages)<\/h2>\n<p>\nLinux p\u0113c noklus\u0113juma izmanto 4 kB atmi\u0146as lapas, kam\u0113r BSD \u2014 <i>Super Pages<\/i>, bet Windows \u2014 <i>Large Pages<\/i>. Lapa ir da\u013ca no RAM, ko pie\u0161\u0137ir procesam. Procesam var b\u016bt vair\u0101kas lapas atkar\u012bb\u0101 no atmi\u0146as pras\u012bb\u0101m. Jo vair\u0101k atmi\u0146as procesam nepiecie\u0161ams, jo vair\u0101k lapu tam tiks pie\u0161\u0137irts. Oper\u0113t\u0101jsist\u0113ma atbalsta lapu pie\u0161\u0137ir\u0161anas tabulu procesiem. Jo maz\u0101ks lapas izm\u0113rs, jo liel\u0101ka tiks izmantota tabula, un ilg\u0101k aiz\u0146ems lapas mekl\u0113\u0161ana \u0161aj\u0101 lapu tabul\u0101. T\u0101d\u0113j\u0101di lielas lapas \u013cauj izmantot lielu atmi\u0146as apjomu ar samazin\u0101tiem izdevumiem; maz\u0101k lapu skat\u012bjumu, maz\u0101k lapu k\u013c\u016bdu, \u0101tr\u0101kas las\u012b\u0161anas\/ierakst\u012b\u0161anas oper\u0101cijas caur lieliem buferiem. Rezult\u0101t\u0101 \u2014 veiktsp\u0113jas uzlabo\u0161ana.<\/p>\n<p>PostgreSQL atbalsta liel\u0101s lapas tikai Linux. P\u0113c noklus\u0113juma Linux izmanto 4 kB atmi\u0146as lapas, t\u0101d\u0113\u013c gad\u012bjumos, kad ir p\u0101r\u0101k daudz atmi\u0146as oper\u0101ciju, ir nepiecie\u0161ams iestat\u012bt liel\u0101ka izm\u0113ra lapas. Lielu lapu izmanto\u0161anai, kuru izm\u0113rs ir 2 MB un l\u012bdz 1 GB, nov\u0113rojama veiktsp\u0113jas pieaugums. Liel\u0101s lapas izm\u0113ru var iestat\u012bt palaid\u0113. J\u016bs varat viegli p\u0101rbaud\u012bt lielo lapu parametrus un to izmanto\u0161anu sav\u0101 Linux dator\u0101, izmantojot komandu <i>cat \/proc\/meminfo | grep -i huge<\/i>.<\/p>\n<p><b>Inform\u0101cijas ieg\u016b\u0161ana par lielaj\u0101m lap\u0101m (tikai Linux)<\/b><\/p>\n<pre><code class=\"bash\">Piez\u012bme: Tas attiecas tikai uz Linux, cit\u0101m oper\u0113t\u0101jsist\u0113m\u0101m \u0161\u012b oper\u0101cija tiek ignor\u0113ta$ cat \/proc\/meminfo | grep -i huge\nAnonHugePages:         0 kB\nShmemHugePages:        0 kB\nHugePages_Total:       0\nHugePages_Free:        0\nHugePages_Rsvd:       0\nHugePages_Surp:       0\nHugepagesize:       2048 kB<\/code><\/pre>\n<p>\n\u0160aj\u0101 piem\u0113r\u0101, lai gan liel\u0101s lapas izm\u0113rs ir iestat\u012bts uz 2048 (2 MB), kop\u0113jais lielo lapu skaits ir 0. Tas noz\u012bm\u0113, ka liel\u0101s lapas ir izsl\u0113gtas.<\/p>\n<h3>Lielas lapas skaita noteik\u0161anas skripts<\/h3>\n<p>\n\u0160is vienk\u0101r\u0161ais skripts atgrie\u017e nepiecie\u0161amo lielo lapu skaitu. Palaidiet skriptu sav\u0101 Linux server\u012b, kam\u0113r darbojas PostgreSQL. P\u0101rliecinieties, ka videi <i>$PGDATA<\/i> ir pie\u0161\u0137irta PostgreSQL datu katalogs.<\/p>\n<p><b>Lielo lapu skaita ieg\u016b\u0161ana<\/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>\nSkripta izvade izskat\u0101s \u0161\u0101di:<\/p>\n<p><b>Skripta izvade<\/b><\/p>\n<pre><code class=\"bash\">Pid:            12737\nVmPeak:        180932 kB\nHugepagesize:   2048 kB\nSet Huge Pages: 88<\/code><\/pre>\n<p>\nIeteicam\u0101 lielo lapu v\u0113rt\u012bba ir 88, t\u0101p\u0113c jums j\u0101sal\u012bdzina 88.<\/p>\n<p><b>Lielo lapu iestat\u012b\u0161ana<\/b><\/p>\n<pre><code class=\"bash\">sysctl -w vm.nr_hugepages=88<\/code><\/pre>\n<p>\nP\u0101rbaudiet liel\u0101s lapas tagad, j\u016bs redz\u0113siet, ka liel\u0101s lapas netiek izmantotas (HugePages_Free = HugePages_Total).<\/p>\n<p><b>V\u0113lreiz inform\u0101cija par lielaj\u0101m lap\u0101m (tikai Linux)<\/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>\nTagad iestatiet huge_pages parametru \u00abon\u00bb $PGDATA\/postgresql.conf un restart\u0113jiet serveri.<\/p>\n<p><b>Un v\u0113lreiz inform\u0101cija par lielaj\u0101m lap\u0101m (tikai Linux)<\/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>\nTagad j\u016bs varat redz\u0113t, ka \u013coti maz lielo lapu tiek izmantots. Tagad pam\u0113\u0123in\u0101sim pievienot da\u017eus datus datu b\u0101zei.<\/p>\n<p><b>Da\u017eas datu b\u0101zes oper\u0101cijas, lai p\u0101rstr\u0101d\u0101tu liel\u0101s lapas<\/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>\nVai apskat\u012bsim, vai m\u0113s tagad izmantojam vair\u0101k lielo lapu nek\u0101 iepriek\u0161.<\/p>\n<p><b>V\u0113lreiz inform\u0101cija par lielaj\u0101m lap\u0101m (tikai Linux)<\/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>\nTagad j\u016bs varat redz\u0113t, ka liel\u0101k\u0101 da\u013ca lielo lapu tiek izmantota.<\/p>\n<p>Piez\u012bme: \u0161eit izmantot\u0101 HugePages aptuven\u0101 v\u0113rt\u012bba ir \u013coti zema, kas nav norm\u0101la v\u0113rt\u012bba ra\u017eo\u0161anas vid\u0113. L\u016bdzu, nov\u0113rt\u0113jiet nepiecie\u0161amo lapu skaitu j\u016bsu sist\u0113mai un attiec\u012bgi iestatiet to, \u0146emot v\u0113r\u0101 slodzi un resursus.<\/p>\n<h3>vm.swappiness<\/h3>\n<p>\n<b>vm.swappiness<\/b> \u2014 tas ir v\u0113l viens kodola parametrs, kas var ietekm\u0113t datu b\u0101zes veiktsp\u0113ju. \u0160is parametrs tiek izmantots, lai kontrol\u0113tu apmai\u0146as uzved\u012bbu (swappiness) (lappu\u0161u apmai\u0146as procesus Linux). V\u0113rt\u012bba sv\u0101rst\u0101s no 0 l\u012bdz 100. T\u0101 nosaka, cik daudz atmi\u0146as tiks izsv\u012btrota vai iek\u013cauta. Nulle noz\u012bm\u0113 apmai\u0146as atsl\u0113g\u0161anu, bet 100 noz\u012bm\u0113 agres\u012bvu apmai\u0146u.<\/p>\n<p>J\u016bs varat pan\u0101kt labu veiktsp\u0113ju, iestatot zem\u0101kas v\u0113rt\u012bbas.<\/p>\n<p>Iestatot 0 v\u0113rt\u012bbu jaun\u0101kajos kodolos, OOM Killer (atmi\u0146as t\u012br\u012b\u0161anas process Linux) var izn\u012bcin\u0101t procesu. T\u0101d\u0113j\u0101di dro\u0161i varat iestat\u012bt v\u0113rt\u012bbu 1, ja v\u0113laties minimiz\u0113t apmai\u0146u. Noklus\u0113juma v\u0113rt\u012bba Linux ir 60. Augst\u0101ka v\u0113rt\u012bba liek MMU (apzi\u0146as kontroles bloks) izmantot vair\u0101k apmai\u0146as vietas nek\u0101 RAM, savuk\u0101rt zem\u0101ka v\u0113rt\u012bba saglab\u0101 vair\u0101k datu\/koda atmi\u0146\u0101.<\/p>\n<p>Zem\u0101ka v\u0113rt\u012bba ir laba likme, lai uzlabotu veiktsp\u0113ju PostgreSQL.<\/p>\n<h3>vm.overcommit_memory \/ vm.overcommit_ratio<\/h3>\n<p>\nLietojumprogrammas sa\u0146em atmi\u0146u un atbr\u012bvo to, kad t\u0101 vairs nav nepiecie\u0161ama. Bet da\u017eos gad\u012bjumos lietojumprogramma sa\u0146em p\u0101r\u0101k daudz atmi\u0146as un neatbr\u012bvo to. Tas var izrais\u012bt OOM killer. \u0160eit ir iesp\u0113jam\u0101s parametr\u0101 v\u0113rt\u012bbas <b>vm.overcommit_memory<\/b> ar aprakstu katrai:<\/p>\n<ol>\n<li> Heuristiskais overcommit (noklus\u0113juma); kodola balst\u012bta heuristika<\/li>\n<li> At\u013caut overcommit jebkur\u0101 gad\u012bjum\u0101<\/li>\n<li> Nep\u0101rslogojiet, nep\u0101rsniedziet overcommit koeficientu.<\/li>\n<\/ol>\n<p>\n<i>Saite: <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 procentu\u0101lais daudzums operat\u012bv\u0101s atmi\u0146as, kas pieejama p\u0101rm\u0113r\u012bgai iekrau\u0161anai. 50% v\u0113rt\u012bba sist\u0113m\u0101 ar 2 GB RAM var pie\u0161\u0137irt l\u012bdz 3 GB RAM.<\/p>\n<p>V\u0113rt\u012bba 2 vm.overcommit_memory nodro\u0161ina lab\u0101ku veiktsp\u0113ju PostgreSQL. \u0160\u012b v\u0113rt\u012bba maksimiz\u0113 operat\u012bv\u0101s atmi\u0146as izmanto\u0161anu servera proces\u0101, nepalielinot risku, ka process tiks nogalin\u0101ts ar OOM killer. Lietojumprogramma var\u0113s restart\u0113ties, bet tikai p\u0101rsnieg\u0161anas robe\u017e\u0101s, kas samazina risku, ka OOM killer nogalin\u0101s procesu. T\u0101d\u0113j\u0101di v\u0113rt\u012bba 2 sniedz lab\u0101ku veiktsp\u0113ju nek\u0101 noklus\u0113juma v\u0113rt\u012bba 0. Tom\u0113r uzticam\u012bbu var uzlabot, nepie\u013caujot, ka atmi\u0146a p\u0101rsniedz pie\u013caujamo diapazonu. Tas izsl\u0113dz risku, ka process tiks nogalin\u0101ts ar OOM killer.<\/p>\n<p>Sist\u0113m\u0101s bez apmai\u0146as var rasties probl\u0113mas ar vm.overcommit_memory, kas ir vien\u0101ds ar 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 tas ir atmi\u0146as procents, ko aiz\u0146em net\u012bri lapas, kuras ir j\u0101ieraksta disk\u0101. Ieraksts disk\u0101 notiek fona re\u017e\u012bm\u0101. \u0160\u012b parametra v\u0113rt\u012bba sv\u0101rst\u0101s no 0 l\u012bdz 100; tom\u0113r v\u0113rt\u012bba zem 5 var b\u016bt neefekt\u012bva, un da\u017ei kodoli to neatbalsta. 10 ir noklus\u0113juma v\u0113rt\u012bba liel\u0101kaj\u0101 da\u013c\u0101 Linux sist\u0113mu. J\u016bs varat uzlabot veiktsp\u0113ju rakst\u012b\u0161anas oper\u0101cij\u0101s ar zem\u0101ku koeficientu, kas noz\u012bm\u0113s, ka Linux fona re\u017e\u012bm\u0101 ierakst\u012bs net\u012bras lapas.<\/p>\n<p>Jums j\u0101konfigur\u0113 v\u0113rt\u012bba <b>vm.dirty_background_bytes<\/b> atkar\u012bb\u0101 no j\u016bsu diska \u0101truma.<\/p>\n<p>\u0160iem diviem parametriem nav \u201elabu\u201d v\u0113rt\u012bbu, jo abi ir atkar\u012bgi no aparat\u016bras. Tom\u0113r iest\u0101dot vm.dirty_background_ratio uz v\u0113rt\u012bbu 5 un vm.dirty_background_bytes uz 25% no diska \u0101truma, veiktsp\u0113ju var palielin\u0101t l\u012bdz ~ 25% liel\u0101kaj\u0101 da\u013c\u0101 gad\u012bjumu.<\/p>\n<h3>vm.dirty_ratio \/ dirty_bytes<\/h3>\n<p>\nTas ir tas pats, kas <b>vm.dirty_background_ratio \/ dirty_background_bytes<\/b>, iz\u0146emot to, ka ieraksts notiek darba sesij\u0101, blo\u0137\u0113jot lietojumprogrammu. T\u0101p\u0113c vm.dirty_ratio j\u0101b\u016bt augst\u0101kam nek\u0101 <b>vm.dirty_background_ratio<\/b>. Tas nodro\u0161ina, ka fona procesi tiek aktiviz\u0113ti agr\u0101k, lai nov\u0113rstu maksim\u0101lo iesp\u0113jamo lietojumprogrammas blo\u0137\u0113\u0161anu. J\u016bs varat piel\u0101got at\u0161\u0137ir\u012bbu starp \u0161iem diviem ratios atkar\u012bb\u0101 no diska I\/O slodzes.<\/p>\n<h2>Rezult\u0101ts<\/h2>\n<p>\nJ\u016bs varat piel\u0101got citus parametrus, lai uzlabotu veiktsp\u0113ju, bet uzlabojumi b\u016bs minim\u0101li un ieguvumi \u012bpa\u0161i neieg\u016bsiet. Mums j\u0101patur pr\u0101t\u0101, ka ne visi parametri attiecas uz visiem lietojumprogrammu veidiem. Da\u017eas lietojumprogrammas darbojas lab\u0101k, kad m\u0113s konfigur\u0113jam noteiktus parametrus, bet da\u017eas - n\u0113. Jums j\u0101atrod pareizais l\u012bdzsvars starp \u0161o parametru konfigur\u0101cij\u0101m atbilsto\u0161i gaid\u0101majai slodzei un lietojumprogrammas veidam, k\u0101 ar\u012b j\u0101patur pr\u0101t\u0101 OS uzved\u012bba, konfigur\u0113jot. Kodola parametru piel\u0101go\u0161ana nav tik vienk\u0101r\u0161a k\u0101 datu b\u0101zes parametru piel\u0101go\u0161ana: \u0161eit ir gr\u016bt\u0101k sniegt savus ieteikumus.<br \/>\n<br \/>Avots: <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.4 - 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\/lv\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.4\" \/>\n\t\t<meta property=\"og:locale\" content=\"lv_LV\" \/>\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\/lv\/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\udd47Linux kodola parametru iestat\u012b\u0161ana PostgreSQL optimiz\u0113\u0161anai | ProHoster","description":"Optimiz\u0113ta veiktsp\u0113ja.","canonical_url":"https:\/\/prohoster.info\/lv\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"lv_LV","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\/lv\/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\/lv\/wp-json\/wp\/v2\/posts\/35920","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/comments?post=35920"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/posts\/35920\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/media\/26870"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/media?parent=35920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/categories?post=35920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/lv\/wp-json\/wp\/v2\/tags?post=35920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}