Ukusebenza okuphezulu kwe-PostgreSQL kuncike kumapharamitha wesistimu yokusebenza achazwe kahle. Izilungiselelo ze-OS kernel ezingalungiselelwe kahle zingaholela ekusebenzeni kabi kweseva yesizindalwazi. Ngakho-ke, kubalulekile ukuthi lezi zilungiselelo zimiswe ngokuya ngeseva yesizindalwazi kanye nomsebenzi wayo. Kulokhu okuthunyelwe, sizoxoxa ngamapharamitha we-Linux kernel abalulekile angathinta ukusebenza kweseva yedathabhethi nokuthi angawamisa kanjani.
SHMMAX / SHMALL
SHMMAX ipharamitha ye-kernel esetshenziselwa ukunquma usayizi omkhulu wesegimenti yenkumbulo eyabelwe eyodwa inqubo ye-Linux engayaba. Ngaphambi kwenguqulo 9.2, i-PostgreSQL yasebenzisa i-System V (SysV), edinga ukulungiselelwa kwe-SHMMAX. Ngemuva kuka-9.2, i-PostgreSQL yashintshela kumemori eyabiwe ye-POSIX. Ngakho manje kudingeka amabhayithi ambalwa ememori eyabiwe ye-System V.
Ngaphambi kwenguqulo 9.3, i-SHMMAX kwakuyipharamitha yekernel ebaluleke kakhulu. Inani le-SHMMAX licaciswe ngamabhayithi.
Ngokufanayo, SHMALL kungenye ipharamitha ye-kernel esetshenziselwa ukunquma
ivolumu yohlelo olubanzi lwamakhasi enkumbulo okwabelwana ngawo. Ukuze ubuke amanani wamanje we-SHMMAX, SHMALL, noma SHMMIN, sebenzisa umyalo ipcs.
SHM* Imininingwane - 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) = 1SHM* Imininingwane - 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 Uhlelo V IPC ukwaba inkumbulo eyabiwe. Le parameter ingenye yamapharamitha e-kernel abaluleke kakhulu. Noma nini lapho uthola imilayezo yamaphutha elandelayo, kusho ukuthi unenguqulo endala ye-PostgreSQL futhi inani lakho le-SHMMAX liphansi kakhulu. Abasebenzisi kulindeleke ukuthi balungise futhi bakhulise inani ngokuya ngenkumbulo eyabiwe abahlose ukuyisebenzisa.
Amaphutha okungenzeka angalungile
Uma i-SHMMAX ingalungiselelwe kahle, ungathola iphutha lapho uzama ukuqalisa iqoqo le-PostgreSQL usebenzisa umyalo. indb.
initdb Ukwehluleka
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, ungathola iphutha lapho uqala iseva ye-PostgreSQL usebenzisa umyalo pg_ctl.
pg_ctl Ukwehluleka
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 umehluko ezincazelweni
Ukuchaza amapharamitha we-SHMMAX/SHMALL kuhluke kancane ku-Linux naku-MacOS X:
- I-Linux: i-kernel.shmmax, i-kernel.shmall
- I-MacOS X: kern.sysv.shmmax, kern.sysv.shmall
Ithimba sysctl ingasetshenziswa ukushintsha inani okwesikhashana. Ukusetha amanani angaguquki, engeza okufakiwe kuwo /etc/sysctl.conf. Imininingwane ingezansi.
Ukushintsha izilungiselelo ze-Kernel ku-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 -> 16777216Ukushintsha amapharamitha e-Kernel ku-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 -> 16777216Ungakhohlwa: Ukuze wenze izinguquko zibe unomphela, engeza lawa manani ku-/etc/sysctl.conf
Amakhasi Amakhulu
I-Linux isebenzisa amakhasi enkumbulo angu-4 KB ngokuzenzakalelayo, i-BSD isebenzisa amakhasi enkumbulo angu-XNUMX KB. Amakhasi Super, naku-Windows - Amakhasi Amakhulu. Ikhasi ucezu lwe-RAM olwabelwe inqubo. Inqubo ingaba namakhasi amaningi kuye ngezidingo zememori. Uma inqubo idinga inkumbulo eyengeziwe, amakhasi amaningi ayabiwa. I-OS igcina ithebula lokwabiwa kwekhasi lezinqubo. Uma usayizi wekhasi elincane, ithebula elikhulu, kuthatha isikhathi eside ukuthola ikhasi kulelo thebula lekhasi. Ngakho-ke amakhasi amakhulu avumela inani elikhulu lememori ukuthi lisetshenziswe nge-overhead encishisiwe; ukubukwa kwamakhasi okumbalwa, amaphutha ambalwa wamakhasi, ukufunda/ukubhala ngokushesha ukusebenza ngamabhafa amakhulu. Umphumela uba ukusebenza okuthuthukisiwe.
I-PostgreSQL isekela kuphela amakhasi amakhulu ku-Linux. Ngokuzenzakalelayo, i-Linux isebenzisa amakhasi enkumbulo angu-4 KB, ngakho-ke ezimeni lapho kunemisebenzi eminingi yememori, kuyadingeka ukusetha amakhasi amakhulu. Izinzuzo zokusebenza zibonwa lapho kusetshenziswa amakhasi amakhulu angu-2 MB nafika ku-1 GB. Usayizi wekhasi omkhulu ungasethwa ngesikhathi sokuqalisa. Ungakwazi ukuhlola kalula amapharamitha amakhulu wekhasi nokusetshenziswa kwawo emshinini wakho we-Linux usebenzisa umyalo ikati /proc/meminfo | grep -i enkulu.
Ukuthola ulwazi mayelana namakhasi 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 kBKulesi sibonelo, nakuba usayizi wekhasi elikhulu usethelwe ku-2048 (2 MB), isamba senani lamakhasi amakhulu asethwe ku-0. Lokhu kusho ukuthi amakhasi amakhulu akhutshaziwe.
Iskripthi sokunquma inani lamakhasi amakhulu
Lesi script esilula sibuyisela inombolo edingekayo yamakhasi amakhulu. Qalisa iskripthi kuseva yakho ye-Linux ngenkathi i-PostgreSQL isebenza. Qiniseka ukuthi ushintshashintsha imvelo $PGDATA Uhlu lwedatha lwe-PostgreSQL lucacisiwe.
Ithola inombolo yamakhasi amakhulu adingekayo
#!/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: $hpOkukhishwayo kweskripthi kubukeka kanjena:
Ukukhishwa kweskripthi
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Inani elinconyiwe lamakhasi amakhulu ngu-88, ngakho-ke kufanele ukusethe ku-88.
Ifaka Amakhasi Amakhulu
sysctl -w vm.nr_hugepages=88Hlola amakhasi amakhulu manje, uzobona ukuthi amakhasi amakhulu awasetshenziswa (HugePages_Free = HugePages_Total).
Amakhasi Amakhulu Abuye Wavakashelwa (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 kBManje setha ipharamitha yamakhasi amakhulu ukuthi "ivuliwe" ku-$PGDATA/postgresql.conf bese uqala kabusha iseva.
Futhi, ulwazi mayelana namakhasi 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 kBManje usuyabona ukuthi ambalwa kakhulu amakhasi amakhulu asetshenziswayo. Manje ake sizame ukwengeza idatha kusizindalwazi.
Eminye imisebenzi yesizindalwazi ukuze kusetshenziswe kabusha amakhasi amakhulu
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Ake sibone ukuthi sisebenzisa amakhasi amakhulu kakhulu manje kunangaphambili.
Ulwazi olwengeziwe emakhasini 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 kBManje usuyabona ukuthi iningi lamakhasi amakhulu liyasetshenziswa.
Qaphela: Inani elilinganiselwe le-HugePages elisetshenziswe lapha liphansi kakhulu, okungelona inani elivamile lomshini osebenzisa indawo yomkhiqizo. Sicela ulinganisele inani elidingekayo lamakhasi esistimu yakho bese uwamisa ngokufanele ngokusekelwe kumthwalo nezisetshenziswa.
vm.swappiness
vm.swappiness kungenye ipharamitha ye-kernel engathinta ukusebenza kwesizindalwazi. Le nketho isetshenziselwa ukulawula ukuziphatha kwe-swappiness (ukushintshanisa amakhasi ngaphakathi nangaphandle kwememori) ku-Linux. Inani lisukela ku-0 kuye ku-100. Linquma ukuthi ingakanani inkumbulo ezopeyishwa noma ikhishwe. Uziro usho ukuthi akukho ukushintshana kanti u-100 usho ukushintshana okunolaka.
Ungathola ukusebenza okuhle ngokusetha amanani aphansi.
Ukusetha lokhu ku-0 kuma-kernels amasha kungase kubangele i-OOM Killer (inqubo yokuhlanza inkumbulo ye-Linux) ukuthi ibulale inqubo. Ngakho-ke kuphephile ukusethela ku-1 uma ufuna ukunciphisa ukushintshanisa. Inani elizenzakalelayo ku-Linux lingu-60. Inani eliphakeme libangela i-MMU (iyunithi yokuphatha inkumbulo) ukuthi isebenzise isikhala sokushintshisana esiningi kune-RAM, kuyilapho inani eliphansi ligcina idatha/ikhodi eningi enkumbulweni.
Inani eliphansi ukubheja okuhle kokusebenza okuthuthukisiwe ku-PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Izinhlelo zokusebenza zithola inkumbulo futhi ziyikhulule lapho ingasadingeki. Kodwa kwezinye izimo, uhlelo lokusebenza luthola inkumbulo eningi futhi aluyikhiphi. Lokhu kungase kubangele umbulali we-OOM. Nawa amanani epharamitha angenzeka vm.overcommit_memory enencazelo ngayinye:
- I-Heuristic overcommit (okuzenzakalelayo); i-kernel-based heuristic
- Vumela ukweqa noma kunjalo
- Ungeqisi, ungeqi isilinganiso se-overcommit.
Inkomba:
vm.overcommit_ratio - iphesenti le-RAM liyatholakala ukuze lilayishwe kakhulu. Inani elingu-50% kusistimu eno-2 GB we-RAM linganikeza kufika ku-3 GB we-RAM.
Inani elingu-2 le-vm.overcommit_memory linikeza ukusebenza okungcono kwe-PostgreSQL. Leli nani likhulisa ukusetshenziswa kwe-RAM yenqubo yeseva ngaphandle kwanoma iyiphi ingcuphe enkulu yokubulawa inqubo yombulali we-OOM. Uhlelo lokusebenza luzokwazi ukulayisha kabusha, kodwa kuphela ngaphakathi kwemikhawulo yokweqa, okunciphisa ubungozi bokubulala umbulali we-OOM. Ngakho-ke, inani elingu-2 linikeza ukusebenza okungcono kunevelu ezenzakalelayo engu-0. Nokho, ukwethembeka kungathuthukiswa ngokuqinisekisa ukuthi inkumbulo engaphandle kobubanzi ayilayishwa ngokweqile. Lokhu kuqeda ubungozi benqubo yokubulawa umbulali we-OOM.
Kuzinhlelo ngaphandle kokushintshashintsha, inkinga nge-vm.overcommit_memory elingana no-2 ingase ivele.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio iphesenti lememori egcwele amakhasi angcolile adinga ukubhalelwa kudiski. Ukushintshela kudiski kwenzeka ngemuva. Inani laleli pharamitha lisukela ku-0 kuye ku-100; Nokho, inani elingaphansi kuka-5 lingase lingasebenzi futhi ezinye izinhlamvu azikusekeli. 10 iyona ezenzakalelayo ezinhlelweni eziningi ze-Linux. Ungathuthukisa ukusebenza komsebenzi obhala kakhulu ngento encane, okuzosho ukuthi i-Linux izokhipha amakhasi angcolile ngemuva.
Udinga ukusetha inani vm.dirty_background_bytes kuye ngesivinini sokushayela kwakho.
Awekho amanani "amahle" kulawa mapharamitha amabili njengoba womabili encike kwihadiwe. Nokho, ukusetha i-vm.dirty_background_ratio ibe ngu-5 kanye ne-vm.dirty_background_bytes ukuya ku-25% wesivinini sediski kuthuthukisa ukusebenza ku-~25% ezimeni eziningi.
vm.dirty_ratio/dirty_bytes
Lokhu kuyefana vm.dirty_background_ratio/dirty_background_bytes, ngaphandle kokuthi ukusetha kabusha kwenziwa kuseshini yesisebenzi, kuvimbela uhlelo lokusebenza. Ngakho-ke i-vm.dirty_ratio kufanele ibe phezulu kuno vm.dirty_background_ratio. Lokhu kuqinisekisa ukuthi izinqubo zangemuva ziqala ngaphambi kwesikhathi ukugwema ukuvimba uhlelo lokusebenza ngangokunokwenzeka. Ungalungisa umehluko phakathi kwalezi zilinganiso ezimbili kuye ngomthwalo wediski I/O.
Umphumela
Ungalungisa ezinye izilungiselelo ukuze uthuthukise ukusebenza, kodwa ukuthuthukiswa kuzoba kuncane futhi ngeke ubone inzuzo enkulu. Kufanele sikhumbule ukuthi akuzona zonke izinketho ezisebenza kuzo zonke izinhlobo zezinhlelo zokusebenza. Ezinye izinhlelo zokusebenza zisebenza kangcono uma silungisa ezinye izilungiselelo, kanti ezinye azikwenzi. Kufanele uthole ibhalansi elungile phakathi kokumisa lezi zilungiselelo zomthwalo wakho wokusebenza owulindelekile kanye nohlobo lohlelo lokusebenza, futhi kufanele ucabangele nokuziphatha kwe-OS lapho ushuna. Ukulungisa amapharamitha e-kernel akulula njengokumisa imingcele yesizindalwazi; kuba nzima kakhulu ukwenza izincomo.
Source: www.habr.com