Ts'ebetso e nepahetseng ea PostgreSQL e ipapisitse le liparamente tse hlalositsoeng hantle tsa sistimi e sebetsang. Litlhophiso tse sa lokisoang hantle tsa OS kernel li ka baka ts'ebetso e mpe ea seva ea database. Ka hona, hoa hlokahala hore litlhophiso tsena li hlophisoe ho latela seva sa database le mosebetsi oa eona. Ka poso ena, re tla tšohla lintlha tse ling tsa bohlokoa tsa Linux kernel tse ka amang ts'ebetso ea seva sa database le mokhoa oa ho li hlophisa.
SHMMAX / SHMALL
SHMMAX ke parameter ea kernel e sebelisetsoang ho fumana boholo ba boholo ba karolo e le 'ngoe ea memori e arolelanoang eo ts'ebetso ea Linux e ka e abelang. Pele ho mofuta oa 9.2, PostgreSQL e ne e sebelisa System V (SysV), e hlokang tlhophiso ea SHMMAX. Kamora 9.2, PostgreSQL e fetohetse ho memori e arolelanoang ea POSIX. Kahoo joale ho hlokahala li-byte tse fokolang tsa memori e arolelanoang ea System V.
Pele ho mofuta oa 9.3, SHMMAX e ne e le paramente ea bohlokoahali ea kernel. Boleng ba SHMMAX bo hlalositsoe ka li-byte.
Ka mokhoa o ts'oanang, TS'ELISO ke parameter e 'ngoe ea kernel e sebelisetsoang ho tseba
palo e pharalletseng ea sistimi ea maqephe a memori a arolelanoang. Ho sheba SHMMAX, SHMALL, kapa SHMMIN boleng ba hajoale, sebelisa taelo ipcs.
SHM* Lintlha - 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* Lintlha - 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 e sebelisa Sistimi ea V IPC ho aba mohopolo o arolelanoeng. Parameter ena ke e 'ngoe ea likarolo tsa bohlokoa ka ho fetisisa tsa kernel. Nako le nako ha u fumana melaetsa e latelang ea liphoso, ho bolela hore u na le mofuta oa khale oa PostgreSQL mme boleng ba hau ba SHMMAX bo tlase haholo. Basebelisi ba lebelletsoe ho fetola le ho eketsa boleng ho latela mohopolo o arolelanoang oo ba ikemiselitseng ho o sebelisa.
Liphoso tse ka bang teng tsa tlhophiso e fosahetseng
Haeba SHMMAX e sa hlophisoa hantle, u ka fumana phoso ha u leka ho qala sehlopha sa PostgreSQL u sebelisa taelo. initdb.
initdb Ho hloleha
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
Ka mokhoa o ts'oanang, o ka fumana phoso ha o qala seva sa PostgreSQL o sebelisa taelo pg_ctl.
pg_ctl Ho hloleha
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.
Ho utloisisa phapang ea litlhaloso
Ho hlalosa liparamente tsa SHMMAX/SHMALL ho fapane hanyane ho Linux le MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
sehlopha sysctl e ka sebelisoa ho fetola boleng ba nakoana. Ho seta boleng bo sa fetoheng, eketsa kenyo ho /etc/sysctl.conf. Lintlha li ka tlase.
Ho fetola Litlhophiso tsa Kernel ho 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 -> 16777216Ho fetola liparamente tsa Kernel ho 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 -> 16777216U seke oa lebala: Ho etsa liphetoho ka ho sa feleng, eketsa litekanyetso tsena ho /etc/sysctl.conf
Maqephe a Maholo
Linux e sebelisa maqephe a memori a 4 KB ka ho sa feleng, BSD e sebelisa maqephe a XNUMX KB a memori. Maqephe a Super, le ho Windows - Maqephe a Maholo. Leqephe ke karolo ea RAM e abetsoeng ts'ebetso. Ts'ebetso e ka ba le maqephe a mangata ho latela litlhoko tsa memori. Ha ts'ebetso e hloka memori e ngata, e fana ka maqephe a mangata. OS e boloka tafole ea kabo ea leqephe bakeng sa lits'ebetso. Ha boholo ba leqephe le le nyane, tafole e kholo, ho nka nako e telele ho fumana leqephe lethathamong leo la leqephe. Ka hona maqephe a maholo a lumella hore ho sebelisoe mohopolo o moholo ka ho fokotsa holimo; maikutlo a maqheku a fokolang, liphoso tse fokolang tsa maqephe, ho bala/ngola ka potlako lits'ebetso ho feta li-buffers tse kholoanyane. Sephetho ke ts'ebetso e ntlafetseng.
PostgreSQL e ts'ehetsa maqephe a maholo ho Linux feela. Ka nako e sa lekanyetsoang, Linux e sebelisa maqephe a memori a 4 KB, kahoo maemong ao ho nang le ts'ebetso ea memori e mengata haholo, hoa hlokahala ho beha maqephe a maholo. Melemo ea ts'ebetso e bonoa ha u sebelisa maqephe a maholo a 2 MB le ho fihla ho 1 GB. Boholo ba leqephe bo ka hlophisoa ka nako ea ho qala. U ka sheba habonolo liparamente tse kholo tsa leqephe le ts'ebeliso ea tsona mochining oa hau oa Linux u sebelisa taelo katse /proc/meminfo | grep - ke kholo.
Ho fumana leseli mabapi le maqephe a maholo (Linux feela)
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 kBMohlala ona, le hoja boholo ba leqephe le leholo bo behiloe ho 2048 (2 MB), palo eohle ea maqephe a maholo e behiloe ho 0. Sena se bolela hore maqephe a maholo a holofetse.
Script bakeng sa ho khetholla palo ea maqephe a maholo
Mongolo ona o bonolo o khutlisa palo e hlokahalang ea maqephe a maholo. Matha mongolo ho seva sa hau sa Linux ha PostgreSQL e ntse e sebetsa. Etsa bonnete ba hore bakeng sa ho feto-fetoha ha tikoloho $PGDATA Lenane la data la PostgreSQL le hlalositsoe.
Ho fumana palo ea maqephe a maholo a hlokahalang
#!/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: $hpSephetho sa script se shebahala tjena:
Sephetho sa script
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Theko e khothaletsoang bakeng sa maqephe a maholo ke 88, kahoo u lokela ho e beha ho 88.
Ho kenya Maqephe a Maholo
sysctl -w vm.nr_hugepages=88Sheba maqephe a maholo hona joale, u tla bona hore maqephe a maholo ha a sebelisoe (HugePages_Free = HugePages_Total).
Maqephe a Maholo a Khethiloeng hape (Linux Feela)
$ 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 kBJoale beha parameter e kholo ho "ho" ho $PGDATA/postgresql.conf 'me u qale seva bocha.
Hape, tlhahisoleseling mabapi le maqephe a maholo (Linux feela)
$ 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 kBJoale ua bona hore ho sebelisoa maqephe a seng makae a maholo. Ha re leke hona joale ho kenya data ho database.
Lits'ebetso tse ling tsa database ho sebelisa maqephe a maholo hape
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Ha re boneng hore na re sebelisa maqephe a maholo ho feta pele.
Lintlha tse ling maqepheng a maholo (Linux feela)
$ 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 kBJoale u ka bona hore boholo ba maqephe a maholo a sebelisoa.
Tlhokomeliso: Theko e lekantsoeng ea HugePages e sebelisitsoeng mona e tlase haholo, eo e seng boleng bo tloaelehileng bakeng sa mochini o tsamaisang tikoloho ea sehlahisoa. Ka kopo, hakanya palo e hlokahalang ea maqephe bakeng sa sistimi ea hau 'me u a behe ho latela mojaro le lisebelisoa.
vm.swappiness
vm.swappiness ke parameter e 'ngoe ea kernel e ka amang ts'ebetso ea database. Khetho ena e sebelisoa ho laola boitšoaro ba swappiness (ho fapanyetsana maqephe ka hare le ho tsoa mohopolong) Linux. Boleng bo tloha ho 0 ho isa ho 100. E etsa qeto ea hore na memori e kae e tla behoa leqepheng kapa e tla lokolloa. Zero e bolela hore ha ho phapanyetsano 'me 100 e bolela phapanyetsano e mabifi.
U ka fumana ts'ebetso e ntle ka ho beha litekanyetso tse tlase.
Ho beha sena ho 0 ho li-kernel tse ncha ho ka baka OOM Killer (mokhoa oa ho hloekisa mohopolo oa Linux) ho bolaea ts'ebetso. Kahoo ho bolokehile ho e beha ho 1 haeba u batla ho fokotsa ho fapanyetsana. Boleng ba kamehla ho Linux ke 60. Boleng bo phahameng bo etsa hore MMU (yuniti ea tsamaiso ea memori) e sebelise sebaka se ngata sa swap ho feta RAM, ha boleng bo tlaase bo boloka boitsebiso bo bongata / khoutu.
Boleng bo tlase ke bethe e ntle bakeng sa ts'ebetso e ntlafalitsoeng ho PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Lisebelisoa li fumana memori le ho e lokolla ha e se e sa hlokehe. Empa maemong a mang, ts'ebeliso e fumana mohopolo o mongata haholo mme ha e e lokolle. Sena se ka baka 'molai oa OOM. Mona ke lintlha tse ka bang teng tsa parameter vm.overcommit_memory ka tlhaloso ea e 'ngoe le e 'ngoe:
- Heuristic overcommit (ka ho sa feleng); kernel-based heuristic
- Lumella boitlamo leha ho le joalo
- U se ke ua e fetelletsa, u se ke ua feta tekanyo ea overcommit.
Tlhaloso:
vm.overcommit_ratio - peresente ea RAM e fumanehang bakeng sa ho jara haholo. Boleng ba 50% tsamaisong e nang le 2 GB ea RAM e ka abela ho fihla ho 3 GB ea RAM.
Boleng ba 2 bakeng sa vm.overcommit_memory bo fana ka tshebetso e betere bakeng sa PostgreSQL. Boleng bona bo eketsa ts'ebeliso ea RAM ea seva ntle le kotsi e kholo ea ho bolaoa ke ts'ebetso ea 'molai oa OOM. Kopo e tla khona ho kenya hape, empa feela ka har'a meeli ea ho fetella, e fokotsang kotsi ea 'molai oa OOM ea bolaeang ts'ebetso. Ka hona, boleng ba 2 bo fana ka ts'ebetso e ntlafetseng ho feta boleng ba kamehla ba 0. Leha ho le joalo, ho tšepahala ho ka ntlafatsoa ka ho etsa bonnete ba hore memori e tsoileng matsoho ha e imetsoe. Sena se felisa kotsi ea hore mokhoa ona o bolaoe ke 'molai oa OOM.
Lits'ebetsong ntle le ho fapanyetsana, bothata ba vm.overcommit_memory bo lekanang le 2 bo ka etsahala.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio ke peresente ea memori e tlatsitsoeng ka maqephe a litšila a hlokang ho ngolloa disk. The flush to disk e etsahala ka morao. Boleng ba parameter ena bo tloha ho 0 ho isa ho 100; leha ho le joalo, boleng bo ka tlase ho 5 bo kanna ba se sebetse 'me lithollo tse ling ha li e tšehetse. 10 ke ea kamehla lits'ebetsong tse ngata tsa Linux. O ka ntlafatsa ts'ebetso bakeng sa ts'ebetso e matla ea ho ngola ka ntlha e nyane, e tla bolela hore Linux e tla hlakola maqephe a litšila ka morao.
U hloka ho beha boleng vm.dirty_background_byte ho ipapisitse le lebelo la koloi ea hau.
Ha ho na litekanyetso tse "ntle" bakeng sa liparamente tsena tse peli kaha ka bobeli li itšetlehile ka hardware. Leha ho le joalo, ho beha vm.dirty_background_ratio ho 5 le vm.dirty_background_bytes ho 25% ea lebelo la disk ho ntlafatsa tshebetso ho ~ 25% maemong a mangata.
vm.dirty_ratio/dirty_bytes
Sena se ts'oana le vm.dirty_background_ratio/dirty_background_bytes, ntle le hore reset e etsoa sebokeng sa basebetsi, ho thibela kopo. Ka hona vm.dirty_ratio e lokela ho ba holimo ho feta vm.dirty_background_ratio. Sena se tiisa hore lits'ebetso tsa morao-rao li qala pejana ho qoba ho thibela ts'ebeliso ka hohle kamoo ho ka khonehang. O ka fetola phapang pakeng tsa likarolelano tsena tse peli ho latela mojaro oa disk I / O.
Phello
U ka fetola litlhophiso tse ling ho ntlafatsa ts'ebetso, empa lintlafatso li tla fokola 'me u ke ke ua bona molemo o mongata. Re tlameha ho hopola hore ha se likhetho tsohle tse sebetsang ho mefuta eohle ea lits'ebetso. Lisebelisoa tse ling li sebetsa betere ha re lokisa litlhophiso tse ling, 'me tse ling ha li etse joalo. U tlameha ho fumana tekanyo e nepahetseng lipakeng tsa ho hlophisa litlhophiso tsena bakeng sa mojaro o lebelletsoeng oa mosebetsi le mofuta oa ts'ebeliso, hape o tlameha ho nahana ka boits'oaro ba OS ha o lokisa. Ho hlophisa liparamente tsa kernel ha ho bonolo joalo ka ho hlophisa liparamente tsa database; ho thata le ho feta ho etsa likhothaletso.
Source: www.habr.com