🥇Ukulungisa iiparamitha zekernel zeLinux ukwenza iPostgreSQL

Ukusebenza okugqibeleleyo kwePostgreSQL kuxhomekeke kwiiparamitha zenkqubo echazwe ngokuchanekileyo. Izicwangciso ze-OS kernel eziqwalaselwe kakubi zinokukhokelela ekusebenzeni kakubi kweseva yedatha. Ke ngoko, kunyanzelekile ukuba ezi zicwangciso ziqwalaselwe ngokweseva yedatha kunye nomsebenzi wayo. Kule post, siza kuxoxa ngeeparameters ze-Linux kernel ezibalulekileyo ezinokuchaphazela ukusebenza kweseva yedatha kunye nendlela yokuyiqwalasela.

SHMMAX / SHMALL

SHMMAX yiparameter yekernel esetyenziswa ukumisela ubungakanani obukhulu becandelo lememori ekwabelwanayo ngayo inkqubo ye Linux inokwabiwa. Ngaphambi kwenguqulo ye-9.2, i-PostgreSQL yasebenzisa i-System V (SysV), efuna ukusetwa kwe-SHMMAX. Emva kwe-9.2, i-PostgreSQL yatshintshela kwi-POSIX kwimemori ekwabelwana ngayo. Ke ngoku ezimbalwa iibyte zeSistim V ekwabelwana ngazo kwimemori ziyafuneka.

Ngaphambi kwenguqulo 9.3, i-SHMMAX yayiyeyona parameter ibalulekileyo yekernel. Ixabiso le-SHMMAX lixelwa kwii-bytes.

Ngokufanayo, SHMALL yenye iparameter yekernel esetyenziselwa ukumisela
Umthamo obanzi wenkqubo wamaphepha enkumbulo ekwabelwana ngawo. Ukujonga amaxabiso angoku SHMMAX, SHMALL, okanye SHMMIN, sebenzisa umyalelo iipcs.

SHM* Iinkcukacha-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* Iinkcukacha-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)

I-PostgreSQL isebenzisa Inkqubo V IPC ukwaba inkumbulo ekwabelwanayo ngayo. Le parameter yenye yezona zinto zibalulekileyo zekernel parameters. Nanini na xa ufumana le miyalezo yemposiso ilandelayo, oko kuthetha ukuba unoguqulelo oludala lwePostgreSQL kwaye ixabiso lakho le-SHMMAX liphantsi kakhulu. Abasebenzisi kulindeleke ukuba bahlengahlengise kwaye banyuse ixabiso ngokwememori ekwabelwana ngayo abazimisele ukuyisebenzisa.

Iimpazamo zolungiselelo olunokwenzeka

Ukuba i-SHMMAX ayilungiswanga kakuhle, ungafumana impazamo xa uzama ukuqalisa iqela le-PostgreSQL usebenzisa umyalelo. initdb.

initdb Ukusilela
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

Ngokufanayo, unokufumana impazamo xa uqala iseva yePostgreSQL usebenzisa umyalelo pg_ctl.

pg_ctl Ukusilela
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.

Ukuqonda umahluko kwiinkcazo

Ukuchaza iiparamitha zeSHMMAX/SHMALL zahluke kancinane kwiLinux nakwiMacOS X:

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

Iqela sysctl ingasetyenziselwa ukutshintsha ixabiso okwexeshana. Ukuseta amaxabiso angatshintshiyo, yongeza ungeno ku /etc/sysctl.conf. Iinkcukacha zingezantsi.

Ukutshintsha useto lweKernel kwiMacOS 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

Ukutshintsha iiparamitha zeKernel kwiLinux

# 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

Sukulibala: Ukwenza utshintsho ngokusisigxina, yongeza la maxabiso ku/etc/sysctl.conf

Amaphepha amakhulu

I-Linux isebenzisa i-4 KB yememori yamaphepha ngokungagqibekanga, i-BSD isebenzisa i-XNUMX KB yamaphepha ememori. Amaphepha eSuper, kwaye kwiWindows - Amaphepha amakhulu. Iphepha liqhekeza le-RAM elabelwe inkqubo. Inkqubo ingaba namaphepha amaninzi ngokuxhomekeke kwiimfuno zememori. Okukhona inkqubo ifuna inkumbulo, kokukhona inikwa amaphepha amaninzi. I-OS igcina itheyibhile yolwabiwo lwephepha kwiinkqubo. Okukhona ubungakanani bephepha buncinci, inkulu itheyibhile, kokukhona kuthatha ixesha elide ukufumana iphepha kuloo theyibhile yephepha. Amaphepha amakhulu ke ngoko avumela ubungakanani obukhulu bememori ukuba isetyenziswe kunye nokuncipha kwentloko; iimboniselo zephepha ezimbalwa, iimpazamo zephepha ezimbalwa, ukufunda/ukubhala ngokukhawuleza imisebenzi ngaphezu kwezithinteli ezinkulu. Isiphumo kukuphucula ukusebenza.

I-PostgreSQL ixhasa kuphela amaphepha amakhulu kwiLinux. Ngokungagqibekanga, iLinux isebenzisa amaphepha enkumbulo e-4 KB, ngoko ke kwiimeko apho kukho imisebenzi emininzi yememori, kuyimfuneko ukuseta amaphepha amakhulu. Iinzuzo zokusebenza zibonwa xa kusetyenziswa amaphepha amakhulu e-2 MB ukuya kuthi ga kwi-1 GB. Ubungakanani bephepha elikhulu bunokusetwa ngexesha lokuqalisa. Ungajonga ngokulula iiparamitha zephepha elikhulu kunye nokusetyenziswa kwazo kumatshini wakho weLinux usebenzisa umyalelo ikati /proc/meminfo | grep -inkulu.

Ukufumana ulwazi malunga namaphepha amakhulu (Linux kuphela)

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

Kulo mzekelo, nangona ubukhulu bephepha obukhulu bubekwe kwi-2048 (2 MB), inani elipheleleyo lamaphepha amakhulu abekwe ku-0. Oku kuthetha ukuba amaphepha amakhulu avaliwe.

Iscript sokumisela inani lamaphepha amakhulu

Esi script silula sibuyisela inani elifunekayo lamaphepha amakhulu. Sebenzisa iskripthi kwiseva yakho yeLinux ngelixa iPostgreSQL isebenza. Qinisekisa ukuba oko kuguquguquka kokusingqongileyo $PGDATA Uluhlu lwedatha yePostgreSQL luchaziwe.

Ukufumana inani lamaphepha amakhulu afunekayo

#!/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

Imveliso yeskripthi ibonakala ngolu hlobo:

Imveliso yescript

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

Ixabiso elicetyiswayo lamaphepha amakhulu ngama-88, ngoko ke kufuneka ulisethe ku-88.

Ukufakela amaPhepha amakhulu

sysctl -w vm.nr_hugepages=88

Jonga amaphepha amakhulu ngoku, uya kubona ukuba amaphepha amakhulu awasetyenziswa (HugePages_Free = HugePages_Total).

Amaphepha amakhulu aphinde aqwalaselwe (Linux Kuphela)

$ 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

Ngoku seta iparameter enkulu ukuba "kwi" kwi $PGDATA/postgresql.conf kwaye uqalise kwakhona umncedisi.

Kwakhona, ulwazi malunga namaphepha amakhulu (Linux kuphela)

$ 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

Ngoku unokubona ukuba ambalwa kakhulu amaphepha amakhulu asetyenziswayo. Ngoku makhe sizame ukongeza idatha kwisiseko sedatha.

Eminye imisebenzi yogcino-lwazi lokuphinda kusetyenziswe amaphepha amakhulu

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

Makhe sibone ukuba sisebenzisa amaphepha amakhulu ngoku kunangaphambili.

Ulwazi oluninzi kumaphepha amakhulu (Linux kuphela)

$ 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

Ngoku unokubona ukuba uninzi lwamaphepha amakhulu asetyenziswa.

Qaphela: Ixabiso eliqikelelweyo le-HugePages elisetyenziswe apha liphantsi kakhulu, elingeloxabiso eliqhelekileyo kumatshini oqhuba indawo yemveliso. Nceda uqikelele inani elifunekayo lamaphepha enkqubo yakho kwaye uwamise ngokufanelekileyo ngokusekelwe kumthwalo kunye nezibonelelo.

vm.ukonwaba

vm.ukonwaba yenye iparameter yekernel enokuchaphazela ukusebenza kwedatabase. Olu khetho lusetyenziswa ukulawula ukuziphatha kokutshintsha (tshintsha amaphepha ngaphakathi nangaphandle kwinkumbulo) kwi Linux. Ixabiso lisuka ku-0 ukuya kwi-100. Imisela ukuba ingakanani inkumbulo eza kupheyithwa okanye ikhutshelwe ngaphandle. U-Zero uthetha ukuba akukho tshintshiselwano kwaye i-100 ithetha utshintshiselwano olunamandla.

Unokufumana ukusebenza kakuhle ngokumisela amaxabiso aphantsi.

Ukucwangcisa oku ku-0 kwiinkozo ezintsha kunokubangela i-OOM Killer (inkqubo yokucoca imemori yeLinux) ukuba ibulale inkqubo. Ngoko ke kukhuselekile ukuseta ku-1 ukuba ufuna ukunciphisa utshintshiselwano. Ixabiso elingagqibekanga kwi-Linux yi-60. Ixabiso eliphezulu libangela ukuba i-MMU (iyunithi yolawulo lwememori) isebenzise indawo yokutshintsha ngaphezu kwe-RAM, ngelixa ixabiso eliphantsi ligcina idatha / ikhowudi kwimemori.

Ixabiso eliphantsi libheji elihle lokuphucula ukusebenza kwi-PostgreSQL.

vm.overcommit_memory / vm.overcommit_ratio

Iiaplikeshini zifumana imemori kwaye ziyikhulule xa ingasafuneki. Kodwa kwezinye iimeko, isicelo sifumana inkumbulo eninzi kwaye asiyikhuphi. Oku kunokubangela umbulali we-OOM. Nanga amaxabiso eparameter anokwenzeka vm.overcommit_memory ngenkcazelo nganye:

I-Heuristic overcommit (ehlala ikho); I-kernel-based heuristic
Vumela ukugqithisela nangona kunjalo
Musa ukuyigqithisa, ungadluli umlinganiselo wokuzibophelela.

Imbekiselo: https://www.kernel.org/doc/Documentation/vm/overcommit-accounting

vm.overcommit_ratio - ipesenti ye-RAM ekhoyo ngokugcwala. Ixabiso le-50% kwinkqubo ene-2 GB ye-RAM inokwaba ukuya kwi-3 GB ye-RAM.

Ixabiso le-2 ye-vm.overcommit_memory inikeza ukusebenza okungcono kwe-PostgreSQL. Eli xabiso likhulisa ukusetyenziswa kwe-RAM yenkqubo yomncedisi ngaphandle komngcipheko omkhulu wokubulawa yinkqubo yombulali we-OOM. Isicelo siya kukwazi ukulayisha kwakhona, kodwa kuphela kwimida yokugqithisa, okunciphisa umngcipheko we-OOM umbulali obulala inkqubo. Ngoko ke, ixabiso le-2 linika ukusebenza okungcono kunexabiso elingagqibekanga le-0. Nangona kunjalo, ukuthembeka kunokuphuculwa ngokuqinisekisa ukuba imemori engaphandle koluhlu ayilayishwanga. Oku kuphelisa umngcipheko wenkqubo yokubulawa ngumbulali we-OOM.

Kwiinkqubo ngaphandle kokutshintsha, ingxaki nge-vm.overcommit_memory elingana no-2 ingenzeka.

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 yipesenti yenkumbulo ezaliswe ngamaphepha amdaka afuna ukubhalwa kwidiski. Ukugungxulwa kwidiski kwenzeka ngasemva. Ixabiso le parameter ukusuka kwi-0 ukuya kwi-100; nangona kunjalo, ixabiso elingaphantsi kwe-5 lisenokungasebenzi kwaye ezinye iinkozo aziyixhasi. I-10 yinto emiselweyo kwiinkqubo ezininzi zeLinux. Unokuphucula ukusebenza kwemisebenzi yokubhala kakhulu ngento encinci, eya kuthetha ukuba iLinux iya kugungxula amaphepha amdaka ngasemva.

Kufuneka ubeke ixabiso vm.dirty_background_bytes kuxhomekeke kwisantya sokuqhuba kwakho.

Akukho maxabiso "alungileyo" kwezi parameters zimbini njengoko zombini zixhomekeke kwihardware. Nangona kunjalo, ukubeka i-vm.dirty_background_ratio ukuya kwi-5 kunye ne-vm.dirty_background_bytes ukuya kwi-25% yesantya sediski kuphucula ukusebenza kwi- ~ 25% kwiimeko ezininzi.

vm.dirty_ratio/dirty_bytes

Oku kuyafana vm.dirty_background_ratio/dirty_background_bytes, ngaphandle kokuba ukusetha kwakhona kwenziwa kwiseshoni yomsebenzi, ukuvala isicelo. Ngoko ke vm.dirty_ratio kufuneka ibephezulu kune vm.dirty_background_ratio. Oku kuqinisekisa ukuba iinkqubo zangasemva ziqala kwangethuba ukunqanda ukuvala usetyenziso kangangoko kunokwenzeka. Unokuhlengahlengisa umahluko phakathi kwezi zilinganiso zimbini ngokuxhomekeke kumthwalo wediski I/O.

Isiphumo

Unokwenza ezinye iisetingi ukuphucula ukusebenza, kodwa uphuculo luya kuba luncinci kwaye awuzukubona nzuzo ingako. Kufuneka sikhumbule ukuba ayizizo zonke iinketho ezisebenzayo kuzo zonke iintlobo zezicelo. Ezinye ii-apps zisebenza ngcono xa silungisa ezinye iisetingi, kwaye ezinye azenzi. Kufuneka ufumane ibhalansi elungileyo phakathi kokuqwalasela ezi zicwangciso zomthwalo wakho olindelweyo womsebenzi kunye nodidi lwesicelo, kwaye kufuneka uqwalasele ukuziphatha kwe-OS xa ulungisa. Ukuqwalasela iparameters zekernel akukho lula njengokuqwalasela idatabase parameters; kunzima kakhulu ukwenza iingcebiso.

umthombo: www.habr.com

Ukuqwalasela iiNketho ze-Linux Kernel zokuSebenzisa iPostgreSQL