{"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\/hu\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","title":{"rendered":"A Linux kernel param\u00e9tereinek be\u00e1ll\u00edt\u00e1sa PostgreSQL optimaliz\u00e1l\u00e1s\u00e1hoz","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><img decoding=\"async\" alt=\"A Linux kernel param\u00e9tereinek be\u00e1ll\u00edt\u00e1sa PostgreSQL optimaliz\u00e1l\u00e1s\u00e1hoz\" src=\"\/wp-content\/uploads\/2019\/07\/a28458ff50cdc517aad71b47adab6eb3.jpeg\" style=\"display:block;margin: 0 auto;\" \/> A PostgreSQL optimal performance depends on correctly defined operating system parameters. Poorly configured OS kernel parameters can lead to a decrease in database server performance. Therefore, it is essential that these parameters are configured in accordance with the database server and its workload. In this post, we will discuss some important Linux kernel parameters that can affect database server performance and how to configure them.<\/p>\n<h2>SHMMAX \/ SHMALL<\/h2>\n<p>\n<b>SHMMAX<\/b> \u2014 this is a kernel parameter used to determine the maximum size of a single segment of shared memory that a Linux process can allocate. Before version 9.2, PostgreSQL used System V (SysV), which required the configuration of SHMMAX. After version 9.2, PostgreSQL switched to POSIX shared memory. Therefore, now fewer bytes of System V shared memory are required.<\/p>\n<p>Before version 9.3, SHMMAX was the most critical kernel parameter. The value of SHMMAX is set in bytes.<br \/>\n<noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><br \/>\nSimilarly, <b>SHMALL<\/b> \u2014 this is another kernel parameter used to determine <br \/>\nthe total volume of shared memory pages. To view the current values of SHMMAX, SHMALL, or SHMMIN, use the command <i>ipcs<\/i>.<\/p>\n<p><b>SHM* Details \u2014 Linux<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -lm\n\n------ Shared Memory Limits --------\nmax number of segments = 4096\nmax seg size (kbytes) = 1073741824\nmax total shared memory (kbytes) = 17179869184\nmin seg size (bytes) = 1<\/code><\/pre>\n<p>\n<b>SHM* Details \u2014 MacOS X<\/b><\/p>\n<pre><code class=\"bash\">$ ipcs -M\nIPC status from  as of Thu Aug 16 22:20:35 PKT 2018\nshminfo:\n\tshmmax: 16777216\t(max shared memory segment size)\n\tshmmin:       1\t(min shared memory segment size)\n\tshmmni:      32\t(max number of shared memory identifiers)\n\tshmseg:       8\t(max shared memory segments per process)\n\tshmall:    1024\t(max amount of shared memory in pages)\n<\/code><\/pre>\n<p>\nPostgreSQL uses <b>System V IPC<\/b> for allocating shared memory. This parameter is one of the most important kernel parameters. Whenever you receive the following error messages, it means that you have an older version of PostgreSQL and a very low SHMMAX value. Users are expected to adjust and increase the value in accordance with the shared memory they intend to use.<\/p>\n<h3>Possible misconfiguration errors<\/h3>\n<p>\nIf SHMMAX is configured incorrectly, you may receive an error when trying to initialize the PostgreSQL cluster using the command <i>initdb<\/i>.<\/p>\n<p><b>initdb Failure<\/b><br \/>\n<code>DETAIL: Failed system call was shmget(key=1, size=2072576, 03600). <\/p>\n<p>HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.\u00a0 <br \/>\nK\u00e9t lehet\u0151s\u00e9g van: cs\u00f6kkentheti a k\u00e9r\u00e9s m\u00e9ret\u00e9t, vagy \u00fajrakonfigur\u00e1lhatja a kernelt nagyobb SHMMAX \u00e9rt\u00e9kkel. A k\u00e9r\u00e9s m\u00e9ret\u00e9nek cs\u00f6kkent\u00e9s\u00e9hez (jelenleg 2072576 b\u00e1jt), <br \/>\ncs\u00f6kkentse a PostgreSQL megosztott mem\u00f3riahaszn\u00e1lat\u00e1t, p\u00e9ld\u00e1ul a shared_buffers vagy a max_connections cs\u00f6kkent\u00e9s\u00e9vel. <\/p>\n<p>Ha a k\u00e9r\u00e9s m\u00e9rete m\u00e1r kicsi, lehets\u00e9ges, hogy az kisebb, mint a kernel SHMMIN param\u00e9tere,<br \/>\nebben az esetben a k\u00e9r\u00e9s m\u00e9ret\u00e9nek n\u00f6vel\u00e9se vagy a SHMMIN \u00fajrakonfigur\u00e1l\u00e1sa sz\u00fcks\u00e9ges. <\/p>\n<p>A PostgreSQL dokument\u00e1ci\u00f3 r\u00e9szletesebb inform\u00e1ci\u00f3kat tartalmaz a megosztott mem\u00f3ria konfigur\u00e1ci\u00f3j\u00e1r\u00f3l. A gyermekfolyamat kil\u00e9pett a 1-es k\u00f3dal.<\/code><\/p>\n<p>Hasonl\u00f3k\u00e9ppen, hib\u00e1t kaphat a PostgreSQL szerver ind\u00edt\u00e1sakor a k\u00f6vetkez\u0151 parancs haszn\u00e1lat\u00e1val: <i>pg_ctl<\/i>.<\/p>\n<p><b>pg_ctl hiba<\/b><br \/>\n<code>R\u00c9SZLET: A sikertelen rendszerh\u00edv\u00e1s: shmget(key=5432001, size=14385152, 03600).<\/p>\n<p>HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.<\/p>\n<p>K\u00e9t lehet\u0151s\u00e9g van: cs\u00f6kkentheti a k\u00e9r\u00e9s m\u00e9ret\u00e9t, vagy \u00fajrakonfigur\u00e1lhatja a kernelt nagyobb SHMMAX \u00e9rt\u00e9kkel. A k\u00e9r\u00e9s m\u00e9ret\u00e9nek cs\u00f6kkent\u00e9s\u00e9hez (jelenleg 14385152 b\u00e1jt) cs\u00f6kkentse a PostgreSQL megosztott mem\u00f3ria haszn\u00e1lat\u00e1t, p\u00e9ld\u00e1ul a shared_buffers vagy a max_connections cs\u00f6kkent\u00e9s\u00e9vel.<\/p>\n<p>Ha a k\u00e9r\u00e9s m\u00e9rete m\u00e1r kicsi, lehets\u00e9ges, hogy az kisebb, mint a kernel SHMMIN param\u00e9tere, <br \/>\nebben az esetben a k\u00e9r\u00e9s m\u00e9ret\u00e9nek n\u00f6vel\u00e9se vagy a SHMMIN \u00fajrakonfigur\u00e1l\u00e1sa sz\u00fcks\u00e9ges.<\/p>\n<p>A PostgreSQL dokument\u00e1ci\u00f3 r\u00e9szletesebb inform\u00e1ci\u00f3kat tartalmaz a megosztott mem\u00f3ria konfigur\u00e1ci\u00f3j\u00e1r\u00f3l.<\/code><\/p>\n<h3>A meghat\u00e1roz\u00e1sok k\u00f6z\u00f6tti k\u00fcl\u00f6nbs\u00e9gek meg\u00e9rt\u00e9se<\/h3>\n<p>\nA SHMMAX\/SHMALL param\u00e9terek meghat\u00e1roz\u00e1sa kiss\u00e9 elt\u00e9r a Linux \u00e9s a MacOS X rendszerekben:<\/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>\nA parancs <i>sysctl<\/i> haszn\u00e1lhat\u00f3 az \u00e9rt\u00e9k ideiglenes megv\u00e1ltoztat\u00e1s\u00e1ra. A tart\u00f3s \u00e9rt\u00e9kek be\u00e1ll\u00edt\u00e1s\u00e1hoz add hozz\u00e1 a bejegyz\u00e9st a <i>\/etc\/sysctl.conf<\/i>. Tov\u00e1bbi r\u00e9szletek lent tal\u00e1lhat\u00f3k.<\/p>\n<p><b>Kernel param\u00e9terek m\u00f3dos\u00edt\u00e1sa MacOS X-en<\/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>Kernel param\u00e9terek m\u00f3dos\u00edt\u00e1sa Linux-on<\/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>Ne felejtsd el<\/b>: a v\u00e1ltoztat\u00e1sok tart\u00f3s megval\u00f3s\u00edt\u00e1s\u00e1hoz add hozz\u00e1 ezeket az \u00e9rt\u00e9keket a \/etc\/sysctl.conf f\u00e1jlhoz<\/p>\n<h2>Nagy oldalak (Huge Pages)<\/h2>\n<p>\nLinux alatt alap\u00e9rtelmez\u00e9s szerint 4 KB mem\u00f3ri\u00e1v\u00e1nyok haszn\u00e1latosak, a BSD rendszerekben \u2014 <i>Super Pages<\/i>, Windows alatt pedig \u2014 <i>Large Pages<\/i>. Az oldal egy r\u00e9sze a RAM-b\u00f3l, amit a folyamat kap. A folyamat rendelkezhet t\u00f6bb oldallal a mem\u00f3riaig\u00e9nyek f\u00fcggv\u00e9ny\u00e9ben. Min\u00e9l t\u00f6bb mem\u00f3ria sz\u00fcks\u00e9ges a folyamat sz\u00e1m\u00e1ra, ann\u00e1l t\u00f6bb oldalt fog kapni. Az oper\u00e1ci\u00f3s rendszer egy lapfoglal\u00e1si t\u00e1bl\u00e1t tart fenn a folyamatok sz\u00e1m\u00e1ra. Min\u00e9l kisebb az oldal m\u00e9rete, ann\u00e1l nagyobb a t\u00e1bl\u00e1zat, ann\u00e1l t\u00f6bb id\u0151t ig\u00e9nyel az oldal keres\u00e9se a lapok t\u00e1bl\u00e1j\u00e1ban. Ez\u00e9rt a nagy oldalak lehet\u0151v\u00e9 teszik nagy mennyis\u00e9g\u0171 mem\u00f3ria haszn\u00e1lat\u00e1t cs\u00f6kkentett k\u00f6lts\u00e9gekkel; kevesebb oldalmegtekint\u00e9s, kevesebb oldalbeli hiba, gyorsabb \u00edr\u00e1s\/olvas\u00e1s m\u0171veletek nagyobb puffereken kereszt\u00fcl. Ennek eredm\u00e9nyek\u00e9nt javul a teljes\u00edtm\u00e9ny.<\/p>\n<p>A PostgreSQL csak Linux alatt t\u00e1mogatja a nagy oldalakat. Alap\u00e9rtelmez\u00e9s szerint a Linux 4 KB mem\u00f3ri\u00e1kat haszn\u00e1l, ez\u00e9rt ha t\u00fal sok mem\u00f3ria-kezel\u00e9si m\u0171velet van, sz\u00fcks\u00e9ges nagyobb m\u00e9ret\u0171 oldalakat be\u00e1ll\u00edtani. Teljes\u00edtm\u00e9nyn\u00f6veked\u00e9s \u00e9szlelhet\u0151 2 MB-os \u00e9s ak\u00e1r 1 GB-os nagy oldalak haszn\u00e1latakor. A nagy oldal m\u00e9rete a rendszerind\u00edt\u00e1skor be\u00e1ll\u00edthat\u00f3. A nagy oldalak param\u00e9tereit \u00e9s haszn\u00e1lat\u00e1t egyszer\u0171en ellen\u0151rizheti Linux sz\u00e1m\u00edt\u00f3g\u00e9p\u00e9n a k\u00f6vetkez\u0151 parancs haszn\u00e1lat\u00e1val <i>cat \/proc\/meminfo | grep -i huge<\/i>.<\/p>\n<p><b>Nagy oldalak inform\u00e1ci\u00f3inak megszerz\u00e9se (csak Linuxon)<\/b><\/p>\n<pre><code class=\"bash\">Megjegyz\u00e9s: Ez csak Linuxra vonatkozik, m\u00e1s oper\u00e1ci\u00f3s rendszerek eset\u00e9n ez a m\u0171velet figyelmen k\u00edv\u00fcl hagyhat\u00f3$ 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>\nEbben a p\u00e9ld\u00e1ban, b\u00e1r a nagy oldal m\u00e9rete 2048 (2 MB) be\u00e1ll\u00edt\u00e1sra ker\u00fclt, a nagy oldalak \u00f6sszes\u00edtett sz\u00e1ma 0. Ez azt jelenti, hogy a nagy oldalak ki vannak kapcsolva.<\/p>\n<h3>Nagy oldalak sz\u00e1m\u00e1nak meghat\u00e1roz\u00e1s\u00e1ra szolg\u00e1l\u00f3 script<\/h3>\n<p>\nEz egy egyszer\u0171 script, amely visszaadja a sz\u00fcks\u00e9ges nagy oldalak sz\u00e1m\u00e1t. Futassa a scriptet Linux szerver\u00e9n, mik\u00f6zben a PostgreSQL fut. Gy\u0151z\u0151dj\u00f6n meg r\u00f3la, hogy a k\u00f6rnyezeti v\u00e1ltoz\u00f3 <i>$PGDATA<\/i> a PostgreSQL adatk\u00f6nyvt\u00e1rra van be\u00e1ll\u00edtva.<\/p>\n<p><b>A sz\u00fcks\u00e9ges nagy oldalak sz\u00e1m\u00e1nak meghat\u00e1roz\u00e1sa<\/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>\nA script kimenete a k\u00f6vetkez\u0151k\u00e9ppen n\u00e9z ki:<\/p>\n<p><b>A script kimenete<\/b><\/p>\n<pre><code class=\"bash\">Pid:            12737\nVmPeak:         180932 kB\nHugepagesize:   2048 kB\nSet Huge Pages: 88<\/code><\/pre>\n<p>\nA javasolt nagy oldalak sz\u00e1ma 88, ez\u00e9rt ezt az \u00e9rt\u00e9ket 88-ra kell be\u00e1ll\u00edtania.<\/p>\n<p><b>Nagy oldalak be\u00e1ll\u00edt\u00e1sa<\/b><\/p>\n<pre><code class=\"bash\">sysctl -w vm.nr_hugepages=88<\/code><\/pre>\n<p>\nEllen\u0151rizze a nagy oldalakat most, l\u00e1tni fogja, hogy a nagy oldalak nincsenek haszn\u00e1latban (HugePages_Free = HugePages_Total).<\/p>\n<p><b>Ism\u00e9t inform\u00e1ci\u00f3 a nagy oldalakr\u00f3l (csak Linuxon)<\/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>\nMost \u00e1ll\u00edtsa be a huge_pages param\u00e9tert \"on\" \u00e9rt\u00e9kre a $PGDATA\/postgresql.conf f\u00e1jlban, majd ind\u00edtsa \u00fajra a szervert.<\/p>\n<p><b>\u00c9s ism\u00e9t inform\u00e1ci\u00f3 a nagy oldalakr\u00f3l (csak Linuxon)<\/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>\nMost l\u00e1thatja, hogy nagyon kev\u00e9s nagy oldal van haszn\u00e1latban. Most pr\u00f3b\u00e1ljunk meg n\u00e9h\u00e1ny adatot hozz\u00e1adni az adatb\u00e1zishoz.<\/p>\n<p><b>N\u00e9h\u00e1ny adatb\u00e1zis m\u0171velet a nagy oldalak kihelyez\u00e9s\u00e9hez<\/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>\nN\u00e9zz\u00fck meg, haszn\u00e1lunk-e jelenleg t\u00f6bb nagy oldalt, mint kor\u00e1bban.<\/p>\n<p><b>M\u00e9g egyszer inform\u00e1ci\u00f3 a nagy oldalakr\u00f3l (csak Linuxon)<\/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>\nMost m\u00e1r l\u00e1thatja, hogy a legt\u00f6bb nagy oldal haszn\u00e1latban van.<\/p>\n<p>Megjegyz\u00e9s: A HugePages-hez haszn\u00e1lt becs\u00fclt \u00e9rt\u00e9k itt nagyon alacsony, ami nem norm\u00e1lis \u00e9rt\u00e9k egy termel\u00e9si k\u00f6rnyezetben. K\u00e9rj\u00fck, \u00e9rt\u00e9kelje, hogy h\u00e1ny oldalra van sz\u00fcks\u00e9ge a rendszer\u00e9hez, \u00e9s \u00e1ll\u00edtsa be azokat megfelel\u0151en a terhel\u00e9s \u00e9s az er\u0151forr\u00e1sok f\u00fcggv\u00e9ny\u00e9ben.<\/p>\n<h3>vm.swappiness<\/h3>\n<p>\n<b>vm.swappiness<\/b> \u2014 ez egy m\u00e1sik kernel param\u00e9ter, amely befoly\u00e1solhatja az adatb\u00e1zis teljes\u00edtm\u00e9ny\u00e9t. Ez a param\u00e9ter a lapoz\u00e1s (swappiness) viselked\u00e9s\u00e9nek kezel\u00e9s\u00e9re szolg\u00e1l (lapok bet\u00f6lt\u00e9se \u00e9s elt\u00e1vol\u00edt\u00e1sa a mem\u00f3ri\u00e1b\u00f3l) Linuxon. Az \u00e9rt\u00e9k 0 \u00e9s 100 k\u00f6z\u00f6tt v\u00e1ltozik. Meghat\u00e1rozza, hogy mennyi mem\u00f3ria ker\u00fcl ki\u00fcr\u00edt\u00e9sre vagy felhaszn\u00e1l\u00e1sra. A nulla letiltja a swap-ot, m\u00edg a 100 agressz\u00edv swap-ot jelent.<\/p>\n<p>J\u00f3 teljes\u00edtm\u00e9nyt \u00e9rhet el, ha alacsonyabb \u00e9rt\u00e9keket \u00e1ll\u00edt be.<\/p>\n<p>A 0 \u00e9rt\u00e9k be\u00e1ll\u00edt\u00e1sa az \u00fajabb kernelben OOM Killer-t (mem\u00f3ria tiszt\u00edt\u00f3 folyamatot Linuxon) okozhat, amely meg\u00f6lheti a folyamatot. \u00cdgy biztons\u00e1gosan be\u00e1ll\u00edthatja az 1 \u00e9rt\u00e9ket, ha minimaliz\u00e1lni szeretn\u00e9 a swap-ot. Az alap\u00e9rtelmezett \u00e9rt\u00e9k Linuxon 60. A magasabb \u00e9rt\u00e9k arra k\u00e9nyszer\u00edti az MMU-t (mem\u00f3riakezel\u0151 egys\u00e9get), hogy t\u00f6bb swap ter\u00fcletet haszn\u00e1ljon, mint a RAM, m\u00edg az alacsonyabb \u00e9rt\u00e9k t\u00f6bb adat\/k\u00f3dot tart a mem\u00f3ri\u00e1ban.<\/p>\n<p>Az alacsonyabb \u00e9rt\u00e9k j\u00f3 fogad\u00e1st jelent a PostgreSQL teljes\u00edtm\u00e9ny\u00e9nek jav\u00edt\u00e1s\u00e1ra.<\/p>\n<h3>vm.overcommit_memory \/ vm.overcommit_ratio<\/h3>\n<p>\nA programok mem\u00f3ri\u00e1t kapnak \u00e9s felszabad\u00edtj\u00e1k, amikor m\u00e1r nincs r\u00e1 sz\u00fcks\u00e9g. De bizonyos esetekben a program t\u00fal sok mem\u00f3ri\u00e1t kap \u00e9s nem szabad\u00edtja fel. Ez OOM killert okozhat. \u00cdme n\u00e9h\u00e1ny lehets\u00e9ges nilai a param\u00e9terhez, <b>vm.overcommit_memory<\/b> melyek mindegyik\u00e9nek le\u00edr\u00e1sa:<\/p>\n<ol>\n<li> Heurisztikus overcommit (alap\u00e9rtelmez\u00e9s szerint); kernel alap\u00fa heurisztika<\/li>\n<li> Overcommit enged\u00e9lyez\u00e9se minden esetben<\/li>\n<li> Ne t\u00falozzon, ne haladja meg az overcommit ar\u00e1nyt.<\/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 a RAM percentage available for overcommit. A value of 50% in a system with 2 GB of RAM can allocate up to 3 GB of RAM.<\/p>\n<p>A value of 2 for vm.overcommit_memory provides better performance for PostgreSQL. This value maximizes RAM usage for the server process without significant risk of being killed by the OOM killer. The application can restart, but only within the overcommit limit, reducing the risk that the OOM killer will terminate the process. Therefore, a value of 2 gives better performance than the default value of 0. However, reliability can be improved by not overcommitting memory beyond the permitted range. This eliminates the risk of the process being killed by the OOM killer.<\/p>\n<p>In systems without swap, there may be an issue with vm.overcommit_memory set to 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 is the percentage of memory filled with dirty pages that need to be written to disk. The flushing to disk is done in the background. The value of this parameter ranges from 0 to 100; however, a value below 5 may be ineffective, and some kernels do not support it. 10 is the default value in most Linux systems. You can improve performance for write-intensive operations with a lower ratio, which will mean that Linux will flush dirty pages in the background.<\/p>\n<p>You need to set the value <b>vm.dirty_background_bytes<\/b> depending on your disk speed.<\/p>\n<p>There are no 'good' values for these two parameters since both depend on hardware. However, setting vm.dirty_background_ratio to 5 and vm.dirty_background_bytes to 25% of the disk speed improves performance by ~ 25% in most cases.<\/p>\n<h3>vm.dirty_ratio \/ dirty_bytes<\/h3>\n<p>\nThis is the same as <b>vm.dirty_background_ratio \/ dirty_background_bytes<\/b>, except that the flushing occurs in the active session, blocking the application. Therefore, vm.dirty_ratio should be higher than <b>vm.dirty_background_ratio<\/b>. This ensures that background processes are triggered earlier to avoid maximum application blocking. You can adjust the difference between these two ratios depending on your I\/O load.<\/p>\n<h2>Result<\/h2>\n<p>\nM\u00e1s be\u00e1ll\u00edt\u00e1sokat is megadhat a teljes\u00edtm\u00e9ny jav\u00edt\u00e1sa \u00e9rdek\u00e9ben, de a javul\u00e1s minim\u00e1lis lesz, \u00e9s k\u00fcl\u00f6n\u00f6sebb el\u0151ny\u00f6ket nem fog tapasztalni. Eml\u00e9kezz\u00fcnk, hogy nem minden param\u00e9ter vonatkozik minden alkalmaz\u00e1s t\u00edpusra. N\u00e9h\u00e1ny alkalmaz\u00e1s jobban m\u0171k\u00f6dik, amikor be\u00e1ll\u00edtunk bizonyos param\u00e9tereket, m\u00edg m\u00e1sok nem. Meg kell tal\u00e1lnia a megfelel\u0151 egyens\u00falyt ezeknek a param\u00e9tereknek a konfigur\u00e1ci\u00f3i k\u00f6z\u00f6tt az elv\u00e1rt munkaterhel\u00e9s \u00e9s az alkalmaz\u00e1s t\u00edpusa szerint, \u00e9s a be\u00e1ll\u00edt\u00e1s sor\u00e1n figyelembe kell venni az oper\u00e1ci\u00f3s rendszer viselked\u00e9s\u00e9t. A kernel param\u00e9terek be\u00e1ll\u00edt\u00e1sa nem olyan egyszer\u0171, mint az adatb\u00e1zis-param\u00e9terek\u00e9: itt nehezebb aj\u00e1nl\u00e1sokat adni.<br \/>\n<br \/>Forr\u00e1s: <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.3 - 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\/hu\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"hu_HU\" \/>\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\/hu\/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 kernel param\u00e9terek be\u00e1ll\u00edt\u00e1sa a PostgreSQL optimaliz\u00e1l\u00e1s\u00e1hoz | ProHoster","description":"Optim\u00e1lis teljes\u00edtm\u00e9ny.","canonical_url":"https:\/\/prohoster.info\/hu\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"hu_HU","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\/hu\/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\/hu\/wp-json\/wp\/v2\/posts\/35920","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/comments?post=35920"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/posts\/35920\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/media\/26870"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/media?parent=35920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/categories?post=35920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/hu\/wp-json\/wp\/v2\/tags?post=35920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}