🥇Fa'atulagaina o parakalafa o le kernel Linux mo le fa'aleleia atili o le PostgreSQL

O le fa'atinoga sili ona lelei a le PostgreSQL e fa'alagolago i le fa'atulagaina lelei o parakalafa o le faiga fa'aoga. O parakalafa o le kernel e le lelei ona fa'atulagaina e mafai ona i'u ai i le fa'aletonu o le fa'atinoga o le 'au'aunaga fa'amaumauga. O le mea lea, e taua tele le fa'atulagaina o nei parakalafa talafeagai mo le 'au'aunaga fa'amaumauga ma lana avega o galuega. I lenei pou, o le a tatou talanoaina ai nisi o parakalafa taua o le kernel. Linux, lea e mafai ona aʻafia ai le faʻatinoga o le 'auʻaunaga faʻamaumauga ma le auala e faʻatulaga ai.

SHMMAX / SHMALL

SHMMAX — o se parakalafa o le kernel e faʻaaogaina e fuafua ai le tele maualuga o se vaega e tasi o le manatua faʻasoa e mafai e se faʻagasologa ona vaevae. LinuxA o le'i iai le lomiga 9.2, sa fa'aaoga e le PostgreSQL le System V (SysV), lea e mana'omia ai le fa'atulagaga SHMMAX. A mae'a le 9.2, sa sui le PostgreSQL i le POSIX shared memory. O lona uiga e itiiti ifo ni bytes o le System V shared memory e mana'omia nei.

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.

Fa'amatalaga o le SHM* — 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).

FA'ATASI: O lenei mea sese e masani lava o lona uiga o le talosaga a PostgreSQL mo se vaega manatua faʻasoa na sili atu i le SHMMAX o lau kernel.
E mafai ona e fa'aitiitia le tele ole talosaga pe toe fa'aopoopo le fatu ile SHMMAX tele. Ina ia faʻaititia le tele o talosaga (i le taimi nei 2072576 bytes),
fa'aitiitia le fa'aoga manatu fa'asoa a PostgreSQL, masalo e ala i le fa'aitiitia o shared_buffers po'o max_connections.

Afai ua la'ititi le tele o le talosaga, e ono la'ititi ifo nai lo le fa'ailoga SHMMIN o lau kernel,
o le fea tulaga e mana'omia ai le si'itia o le tele o le talosaga po'o le toe fa'atulagaina o SHMMIN.

O le PostgreSQL faʻamaumauga o loʻo i ai nisi faʻamatalaga e uiga i le faʻasoaina o mafaufauga. ua alu ese le faiga a tamaiti ma le code exit 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).

FA'ATASI: O lenei mea sese e masani lava o lona uiga o le talosaga a PostgreSQL mo se vaega manatua faʻasoa na sili atu i le SHMMAX o lau kernel.

E mafai ona e fa'aitiitia le tele ole talosaga pe toe fa'aopoopo le fatu ile SHMMAX lapopoa.; Ina ia faʻaititia le tele o talosaga (i le taimi nei 14385152 bytes), faʻaitiitia le faʻaogaina o manatuaga a le PostgreSQL, atonu e ala i le faʻaitiitia o shared_buffers poʻo max_connections.

O le PostgreSQL faʻamaumauga o loʻo i ai nisi faʻamatalaga e uiga i le faʻasoaina o mafaufauga.

Malamalama i eseesega i faauigaga

E fai si eseese o le fa'amatalaga o parakalafa SHMMAX/SHMALL i Linux ma le 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 o parakalafa o le kernel i luga 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

В Linux I le tulaga masani, e faʻaaogaina itulau manatua e 4 KB, i le BSD - Super Pages, ma i totonu 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.

E na'o itulau tetele e lagolagoina e le PostgreSQL Linux. Fa'atonu Linux E fa'aaogāina le 4 KB o itulau manatua, o lea i tulaga e tele ai galuega manatua, e tatau ona setiina itulau tetele. E matauina le fa'aleleia atili o le fa'atinoga pe a fa'aaogāina itulau tetele e 2 MB ma e o'o atu i le 1 GB. E mafai ona setiina le tele o le itulau tele i le taimi e amata ai. E mafai ona e siakiina faigofie ia fa'atulagaga o itulau tetele ma lo latou fa'aaogaina i lau komepiuta. Linux-komepiuta e faʻaaogaina le poloaiga pusi /proc/meminfo | grep -i tele.

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

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 tusitusiga faigofie e toe faafoi mai ai le numera manaʻomia o itulau tetele. Faʻagaoioi le tusitusiga i lau 'auʻaunaga. Linuxa'o fa'agaoioia le PostgreSQL. Ia mautinoa o le fesuia'iga 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).

Fa'amatalaga atili e uiga i itulau tetele (na'o luga o le 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.

Ma toe fa'apea fo'i fa'amatalaga e uiga i itulau tetele (na'o luga o le Linux)

$ 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.

O le isi fa'amatalaga e uiga i itulau tetele (na'o luga o le Linux)

$ 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 se isi parakalafa o le kernel e mafai ona aʻafia ai le faʻatinoga o faʻamaumauga. O lenei parakalafa e faʻaaogaina e pulea ai amioga fesuiaʻi (fesuiaʻiga o itulau i totonu ma fafo atu o le manatua) i totonu LinuxE amata mai le 0 i le 100 le tau. E fuafua ai le tele o le manatua o le a sui pe sui. O le zero o lona uiga e leai se fesuia'iga, ma o le 100 o lona uiga o le fesuia'iga fa'amalosi.

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

O le setiina o le tau i le 0 i kernels fou e ono mafua ai le OOM Killer (faiga o le faamamaina o manatua i totonu o Linux) o le a tape ai le faagasologa. O lea la, e saogalemu le setiina o le tau i le 1 pe afai e te manaʻo e faʻaitiitia le fesuiaʻiga. O le tau masani o le Linux - 60. O se tau maualuga e mafua ai ona faʻaaoga e le MMU (memory management unit) le tele o le avanoa swap nai lo le RAM, ae o se tau maualalo e teuina ai le tele o 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 lea o le manatua ua tumu i itulau palapala e manaʻomia ona fufulu i le tisiki. O lenei fufuluina e tupu i tua. O le tau o lenei parakalafa e amata mai le 0 i le 100; peitaʻi, o se tau i lalo ifo o le 5 atonu e le aoga, ma o nisi kernels e le lagolagoina. 10 o le tau masani lea i le tele o polokalama. LinuxE mafai ona e faʻaleleia atili le faʻatinoga mo galuega faʻatino e tele ai tusitusiga ma se fua faʻatatau laʻititi, o lona uiga o le a Linux o le a fufulu ese ai 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