🥇 Fa'alogoina fa'amaufa'ailoga fatu Linux e fa'amalieina PostgreSQL

Ole fa'atinoga lelei ole PostgreSQL e fa'alagolago ile fa'auiga sa'o ole fa'atonuga ole faiga. Le lelei le fa'atulagaina o le OS kernel e mafai ona i'u ai i le le lelei o fa'agaioiga a le server database. O le mea lea, e taua tele le faʻatulagaina o nei faʻatulagaga e tusa ai ma le server database ma ana galuega. I totonu o lenei pou, o le a tatou talanoaina nisi o mea taua ole kernel Linux e mafai ona aʻafia ai le faʻaogaina o faʻamaumauga faʻamaumauga ma pe faʻafefea ona faʻapipiʻi.

SHMMAX / SHMALL

SHMMAX o se fa'amaufa'ailoga fatu e fa'aaogaina e fuafua ai le maualuga maualuga o se vaega fa'asoa fa'asoa e tasi e mafai e se faiga Linux ona fa'asoa. Aʻo leʻi oʻo i le version 9.2, PostgreSQL faʻaaoga le System V (SysV), lea e manaʻomia ai le SHMMAX seti. Ina ua maeʻa le 9.2, na sui le PostgreSQL i le POSIX faʻasoa manatua. O lea la ua itiiti bytes o le System V shared memory e manaʻomia.

A'o le'i o'o i le version 9.3, o le SHMMAX o le pito sili ona taua o le fatu. O le tau SHMMAX o loʻo faʻamaonia i bytes.

Faapea foi, FA'ATAU o se isi fa'amaufa'ailoga kernel fa'aaoga e fa'amautu ai
fa'a-lautele le tele o itulau manatua fa'asoa. Ina ia vaʻai i le SHMMAX, SHMALL, poʻo le SHMMIN o loʻo iai nei, faʻaaoga le poloaiga ipcs.

SHM* Fa'amatalaga - Linux

$ ipcs -lm

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 1073741824
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

SHM* Fa'amatalaga - MacOS X

$ ipcs -M
IPC status from  as of Thu Aug 16 22:20:35 PKT 2018
shminfo:
	shmmax: 16777216	(max shared memory segment size)
	shmmin:       1	(min shared memory segment size)
	shmmni:      32	(max number of shared memory identifiers)
	shmseg:       8	(max shared memory segments per process)
	shmall:    1024	(max amount of shared memory in pages)

PostgreSQL fa'aoga Faiga V IPC e fa'asoa manatu fa'asoa. O lenei fa'amaufa'ailoga o se tasi o fa'amaufa'ailoga pito sili ona taua. Soo se taimi lava e te mauaina ai faʻamatalaga sese, o lona uiga o loʻo i ai sau lomiga tuai o le PostgreSQL ma o lau SHMMAX e matua maualalo lava. O tagata faʻaoga e faʻamoemoe e fetuunai ma faʻateleina le tau e tusa ai ma mafaufauga faʻasoa latou te faʻamoemoe e faʻaoga.

Fa'aletonu le fa'atulagaina o mea sese

Afai e le saʻo le faʻatulagaina o le SHMMAX, e mafai ona e mauaina se mea sese pe a taumafai e faʻauluina se vaega PostgreSQL e faʻaaoga ai le poloaiga. initdb.

initdb Fa'aletonu
DETAIL: Failed system call was shmget(key=1, size=2072576, 03600).


HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  

You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 2072576 bytes), 

reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. 
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,

in which case raising the request size or reconfiguring SHMMIN is called for.

The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1

E faʻapea foʻi, e mafai ona e mauaina se mea sese pe a amata le PostgreSQL server e faʻaaoga ai le poloaiga pg_ctl.

pg_ctl Le manuia
DETAIL: Failed system call was shmget(key=5432001, size=14385152, 03600).


HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX.; To reduce the request size (currently 14385152 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, 

in which case raising the request size or reconfiguring SHMMIN is called for.

The PostgreSQL documentation contains more information about shared memory configuration.

Malamalama i eseesega i faauigaga

O le fa'amalamalamaina o fa'amaufa'ailoga SHMMAX/SHMALL e ese teisi ile Linux ma MacOS X:

Linux: kernel.shmmax, kernel.shmall
MacOS X: kern.sysv.shmmax, kern.sysv.shmall

au sysctl e mafai ona fa'aoga e sui ai mo sina taimi le tau. Ina ia setiina tulaga taua, faʻaopoopo se faʻamatalaga i /etc/sysctl.conf. O lo'o i lalo fa'amatalaga.

Suia Faiga Kernel ile MacOS X

# Get the value of SHMMAX
sudo sysctl kern.sysv.shmmax
kern.sysv.shmmax: 4096

# Get the value of SHMALL
sudo sysctl kern.sysv.shmall 
kern.sysv.shmall: 4096

# Set the value of SHMMAX
sudo sysctl -w kern.sysv.shmmax=16777216
kern.sysv.shmmax: 4096 -> 16777216

# Set the value of SHMALL 
sudo sysctl -w kern.sysv.shmall=16777216
kern.sysv.shmall: 4096 -> 16777216

Suia Parameter Kernel i Linux

# Get the value of SHMMAX
sudo sysctl kernel.shmmax
kernel.shmmax: 4096

# Get the value of SHMALL
sudo sysctl kernel.shmall
kernel.shmall: 4096

# Set the value of SHMMAX
sudo sysctl -w kernel.shmmax=16777216
kernel.shmmax: 4096 -> 16777216

# Set the value of SHMALL 
sudo sysctl -w kernel.shmall=16777216
kernel.shmall: 4096 -> 16777216

Aua le galo: Ina ia faia suiga tumau, faʻaopoopo nei tau i /etc/sysctl.conf

Itulau Tele

E fa'aoga e Linux le 4 KB itulau manatua ona o le faaletonu, BSD fa'aoga XNUMX KB itulau manatua. Super Pages, ma luga o Windows - Itulau Tele. O le itulau o se vaega o le RAM e faʻasoa i se gaioiga. O se faagasologa e mafai ona tele itulau e faalagolago i mana'oga manatua. O le tele o le manatua e mana'omia e se faagasologa, o le tele foi lea o itulau e fa'asoa. O lo'o fa'atumauina e le OS se laulau fa'asoa itulau mo faiga. O le laʻititi o le itulau, o le tele o le laulau, o le umi e suʻe ai se itulau i lena itulau laulau. O itulau lapopo'a e mafai ai ona fa'aaoga le tele o manatua e fa'aitiitia ai le ulu; itiiti le va'aiga o itulau, fa'aitiitia itulau fa'aletonu, fa'avavevave le faitau/tusi fa'agaioiga i luga ole pa'u tetele. O le taunuuga o le faʻaleleia o le faʻatinoga.

PostgreSQL na'o le lagolagoina o itulau tetele ile Linux. Ona o le faaletonu, e faʻaaogaina e Linux le 4 KB itulau manatua, o lea i tulaga e tele naua gaioiga manatua, e tatau ona seti itulau tetele. O fa'amanuiaga fa'atinoga e matauina pe a fa'aogaina itulau tetele o le 2 MB ma o'o atu i le 1 GB. E mafai ona seti le lapopoa o le itulau i le taimi o le ta'avale. E faigofie ona e siaki itulau lapopoa ma latou faʻaogaina i lau masini Linux e faʻaaoga ai le poloaiga pusi /proc/meminfo | grep -i tele.

Mauaina o fa'amatalaga e uiga i itulau tetele (Linux na'o)

Note: This is only for Linux, for other OS this operation is ignored$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

I lenei faʻataʻitaʻiga, e ui o le lapopoa o le itulau e seti i le 2048 (2 MB), o le aofaʻi o le numera o itulau tetele e seti i le 0. O lona uiga o itulau lapopoa e le atoatoa.

Tusi mo le fuafuaina o le numera o itulau tetele

O lenei fa'amatalaga faigofie e toe fa'afo'i ai le numera mana'omia o itulau tetele. Fa'asolo le tusitusiga i luga o lau server Linux a'o fa'agasolo le PostgreSQL. Ia mautinoa mo le fesuiaiga o le siosiomaga $PGDATA PostgreSQL faʻamaumauga faʻamaumauga ua faʻamaonia.

Maua le numera o itulau tetele mana'omia

#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid:            $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak:            $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize:   $hps kB"
hp=$((peak/hps))
echo Set Huge Pages:     $hp

O le faʻasologa o tusitusiga e pei o lenei:

Fa'asologa o tusitusiga

Pid:            12737
VmPeak:         180932 kB
Hugepagesize:   2048 kB
Set Huge Pages: 88

O le tau fautuaina mo itulau tetele o le 88, o lea e tatau ona e seti i le 88.

Fa'apipi'i itulau tetele

sysctl -w vm.nr_hugepages=88

Siaki itulau tetele i le taimi nei, o le a e vaʻaia e le faʻaaogaina itulau tetele (HugePages_Free = HugePages_Total).

Itulau tetele ua toe asia (Na'o Linux)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       88
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

Ia seti nei le large_pages parameter i le "on" i le $PGDATA/postgresql.conf ma toe amata le server.

Toe tasi, faʻamatalaga e uiga i itulau tetele (Linux naʻo)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       81
HugePages_Rsvd:       64
HugePages_Surp:        0
Hugepagesize:       2048 kB

O lea la ua mafai ona e va'aia e itiiti lava itulau tetele o lo'o fa'aaogaina. Se'i o tatou taumafai nei e fa'aopoopo nisi fa'amatalaga ile fa'amaumauga.

O nisi fa'agaioiga fa'amaumauga e toe fa'aaoga itulau tetele

postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000

Se'i tatou va'ai pe o tatou fa'aogaina ni itulau tetele i le taimi nei nai lo le taimi muamua.

Fa'amatalaga atili i itulau tetele (Linux na'o)

$ cat /proc/meminfo | grep -i huge
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
HugePages_Total:      88
HugePages_Free:       18
HugePages_Rsvd:        1
HugePages_Surp:        0
Hugepagesize:       2048 kB

O lea ua mafai ona e vaʻaia o le tele o itulau tetele o loʻo faʻaaogaina.

Manatua: O le tau faʻatatau mo HugePages faʻaaogaina iinei e matua maualalo, e le o se tau masani mo se masini o loʻo faʻaogaina se siosiomaga o oloa. Fa'amolemole fa'atatau le numera mana'omia o itulau mo lau masini ma fa'atulaga fa'atatau ile uta ma punaoa.

vm.swappiness

vm.swappiness o le isi fa'amaufa'ailoga fatu e mafai ona a'afia ai fa'atinoga o fa'amaumauga. O lenei filifiliga e faʻaaogaina e pulea ai le amio o le swappiness (swapping itulau i totonu ma fafo o le manatua) i Linux. O le tau e amata mai i le 0 i le 100. E iloa ai le tele o le manatua o le a fa'apipi'i pe fa'asolo i fafo. Zero o lona uiga e leai se fesuiaiga ma le 100 o lona uiga o fefaʻatauaiga faʻamalosi.

E mafai ona e maua se faatinoga lelei e ala i le setiina o tau maualalo.

O le setiina o lenei mea i le 0 i luga o fatu fou e ono mafua ai le OOM Killer (Linux's memory cleaning process) e tape ai le faagasologa. O lea e saogalemu le seti i le 1 pe a e manaʻo e faʻaitiitia le fesuiaiga. Ole tau fa'aletonu ile Linux ole 60. O se tau maualuga e mafua ai ona fa'aogaina e le MMU (unite fa'atonuga manatua) le tele o avanoa swap nai lo le RAM, a'o le tau maualalo e fa'atumauina ai fa'amatalaga/code i le manatua.

O se tau maualalo o se peti lelei mo le faʻaleleia o faʻatinoga i PostgreSQL.

vm.overcommit_memory / vm.overcommit_ratio

O talosaga e maua le manatua ma tatala pe a le toe mana'omia. Ae i nisi tulaga, o le talosaga e tele naua manatuaga ma e le tatalaina. Ole mea lea e ono mafua ai le fa'amamateina ole OOM. O mea ia e mafai ona fai vm.overcommit_memory faʻatasi ai ma faʻamatalaga mo taʻitasi:

Fa'atosina fa'atupu fa'ama'i (fa'aletonu); heuristic fa'avae fatu
Fa'ataga pea le fa'atosina
Aua le soona fai, aua le sili atu i le fua faatatau o le overcommit.

Faʻamatalaga: https://www.kernel.org/doc/Documentation/vm/overcommit-accounting

vm.overcommit_ratio - pasene o le RAM avanoa mo le mamafa. Ole tau ole 50% ile faiga ile 2 GB ole RAM e mafai ona fa'asoa ile 3 GB ole RAM.

Ole tau ole 2 mo vm.overcommit_memory e maua ai le fa'atinoga sili atu mo PostgreSQL. O lenei tau e faʻateleina ai le faʻaogaina o le RAM a le server process e aunoa ma se tulaga lamatia tele o le fasiotia e le OOM killer process. O le talosaga o le a mafai ona toe faʻaleleia, ae naʻo totonu o tapulaʻa o le overrun, lea e faʻaitiitia ai le lamatiaga o le fasioti tagata a le OOM e fasiotia le faagasologa. O le mea lea, o le tau o le 2 e tuʻuina atu ai le sili atu o le faʻatinoga nai lo le faʻaogaina o le 0. Ae ui i lea, e mafai ona faʻaleleia le faʻamaoni e ala i le faʻamautinoaina o le manatua i fafo atu o le mamao e le o mamafa. Ole mea lea e fa'ate'aina ai le fa'alavelave ole fa'agasologa o le fa'amateina e se tagata fasioti tagata OOM.

I luga o faiga e aunoa ma le fesuiai, o se faʻafitauli i le vm.overcommit_memory e tutusa ma le 2 e ono tupu.

https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

vm.dirty_background_ratio / vm.dirty_background_bytes

vm.dirty_background_ratio o le pasene o manatua e tumu i itulau palapala e tatau ona tusia i le tisiki. O le fufula i le tisiki e tupu i tua. O le tau o lenei parakalafa mai le 0 i le 100; ae ui i lea, o se tau i lalo ole 5 atonu e le aoga ma e le lagolagoina e nisi fatu. 10 o le faaletonu i le tele o faiga Linux. E mafai ona e faʻaleleia le faʻatinoga mo faʻagaioiga tusitusi-malosi e ala i se laʻititi laʻititi, o lona uiga o le a fufuluina e Linux itulau palapala i tua.

E tatau ona e setiina le tau vm.dirty_background_bytes fa'atatau i le saoasaoa o lau ta'avale.

E leai ni tau "lelei" mo nei vaega e lua ona o mea uma e lua e faʻalagolago i meafaigaluega. Ae ui i lea, seti vm.dirty_background_ratio i le 5 ma vm.dirty_background_bytes i le 25% o le saoasaoa o le tisiki e faʻaleleia ai le faʻatinoga i le ~ 25% i le tele o tulaga.

vm.dirty_ratio/dirty_bytes

E tutusa lava ma vm.dirty_background_ratio/dirty_background_bytes, vagana ai o le toe setiina o loʻo faia i se taimi faigaluega, poloka le talosaga. O lea e tatau ai ona maualuga atu le vm.dirty_ratio nai lo vm.dirty_background_ratio. Ole mea lea e fa'amautinoa ai o fa'agasologa o tala'aga e amata muamua e 'alofia ai le polokaina ole talosaga ile tele e mafai ai. E mafai ona e fetuutuuna'i le eseesega i le va o nei fua faatatau e lua e faalagolago i le tisiki I / O uta.

Le iʻuga

E mafai ona e tweak isi tulaga e faʻaleleia ai le faʻatinoga, ae o le faʻaleleia o le a laʻititi ma e te le vaʻaia le tele o faʻamanuiaga. E tatau ona tatou manatua e le o filifiliga uma e faʻaoga i ituaiga uma o talosaga. O nisi polokalama e sili atu ona lelei pe a tatou fetuutuunai nisi tulaga, ae o nisi e leai. E tatau ona e su'eina le paleni sa'o i le va o le fa'atulagaina o nei fa'atulagaga mo lau galuega fa'amoemoeina ma le ituaiga talosaga, ma e tatau fo'i ona e mafaufau i amioga a le OS pe a fa'alogo. O le fa'atulagaina o fa'amaufa'ailoga fatu e le faigofie e pei o le fa'atulagaina o fa'amaumauga fa'amaumauga; e sili atu ona faigata le faia o fautuaga.

puna: www.habr.com

Fa'atulagaina o Linux Kernel Options e fa'alelei ai PostgreSQL