Qhov zoo tshaj PostgreSQL kev ua tau zoo nyob ntawm qhov raug txhais ua haujlwm tsis raug. Kev teeb tsa OS kernel tsis zoo tuaj yeem ua rau kev ua haujlwm tsis zoo ntawm database server. Yog li ntawd, nws yog ib qho tseem ceeb uas cov kev teeb tsa no tau teeb tsa raws li database server thiab nws cov haujlwm. Hauv cov ntawv tshaj tawm no, peb yuav tham txog qee qhov tseem ceeb ntawm Linux ntsiav tsis muaj peev xwm cuam tshuam rau database server kev ua haujlwm thiab yuav ua li cas teeb tsa lawv.
SHMMAX / SHMALL
SHMMAX yog ib qho kernel parameter siv los txiav txim siab qhov loj tshaj plaws ntawm ib qho kev sib koom ua ke nco uas ib txheej txheem Linux tuaj yeem faib. Ua ntej version 9.2, PostgreSQL siv System V (SysV), uas yuav tsum tau SHMMAX teeb tsa. Tom qab 9.2, PostgreSQL hloov mus rau POSIX sib koom nco. Yog li tam sim no tsawg bytes ntawm System V sib koom nco yuav tsum tau.
Ua ntej version 9.3, SHMMAX yog lub ntsiab lus tseem ceeb tshaj plaws. Tus nqi SHMMAX yog teev nyob rau hauv bytes.
Ib yam li ntawd SHMALL yog lwm kernel parameter siv los txiav txim
system-wide ntim ntawm cov nplooj ntawv nco sib koom. Txhawm rau saib SHMMAX, SHMALL, lossis SHMMIN tus nqi tam sim no, siv cov lus txib ib ips.
SHM * Cov Lus Qhia - 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 * Cov ntsiab lus - 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 siv System V IPC los faib cov cim xeeb sib koom. Qhov no parameter yog ib qho ntawm cov tseem ceeb tshaj plaws kernel parameters. Thaum twg koj tau txais cov lus yuam kev hauv qab no, nws txhais tau hais tias koj muaj cov ntawv qub ntawm PostgreSQL thiab koj tus nqi SHMMAX qis heev. Cov neeg siv yuav tsum tau kho thiab nce tus nqi raws li kev sib koom nco lawv xav siv.
Tej yam ua yuam kev yuam kev
Yog tias SHMMAX tsis raug teeb tsa kom raug, koj tuaj yeem tau txais qhov yuam kev thaum sim pib lub PostgreSQL pawg siv cov lus txib ib initdb.
initdb Failure
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
Ib yam li ntawd, koj tuaj yeem tau txais qhov yuam kev thaum pib PostgreSQL server siv cov lus txib pg_ctl.
pg_ctl Ua tsis tiav
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.
To taub qhov sib txawv ntawm cov ntsiab lus
Kev txhais cov SHMMAX/SHMALL tsis txawv me ntsis ntawm Linux thiab macOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
pab neeg sysctl tuaj yeem siv los hloov tus nqi ib ntus. Txhawm rau teeb tsa qhov tseem ceeb, ntxiv qhov nkag mus rau /etc/sysctl.conf. Cov ntsiab lus hauv qab no.
Hloov Kernel Settings ntawm 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 -> 16777216Hloov Kernel Parameters ntawm 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 -> 16777216Tsis txhob hnov ββqab: Txhawm rau hloov pauv mus tas li, ntxiv cov txiaj ntsig no rau /etc/sysctl.conf
Cov nplooj ntawv loj
Linux siv 4 KB nco nplooj ntawv los ntawm lub neej ntawd, BSD siv XNUMX KB nco nplooj ntawv. Phab Loj, thiab ntawm Windows - Cov nplooj ntawv loj. Ib nplooj ntawv yog ib daim ntawm RAM faib rau cov txheej txheem. Ib txheej txheem tuaj yeem muaj ntau nplooj ntawv nyob ntawm qhov xav tau nco. Qhov kev nco ntau dua yuav tsum muaj cov txheej txheem, ntau nplooj ntawv nws raug faib. Lub OS tuav lub rooj faib nplooj ntawv rau cov txheej txheem. Qhov me me ntawm nplooj ntawv, lub rooj loj dua, nws yuav siv sij hawm ntev mus nrhiav ib nplooj ntawv hauv nplooj ntawv ntawd. Cov nplooj ntawv loj yog li tso cai rau ntau lub cim xeeb siv nrog txo nyiaj siv ua haujlwm; Cov nplooj ntawv tsawg dua, cov nplooj ntawv tsawg dua, kev nyeem / sau nrawm dua cov buffers loj dua. Qhov tshwm sim yog txhim kho kev ua haujlwm.
PostgreSQL tsuas txhawb cov nplooj ntawv loj ntawm Linux. Los ntawm lub neej ntawd, Linux siv 4 KB nco nplooj ntawv, yog li nyob rau hauv rooj plaub uas muaj ntau lub cim xeeb ua haujlwm, nws yog qhov tsim nyog los teeb tsa nplooj ntawv loj dua. Kev ua tau zoo tau pom thaum siv nplooj ntawv loj ntawm 2 MB thiab txog li 1 GB. Cov nplooj ntawv loj loj tuaj yeem teem caij rau lub sijhawm khau raj. Koj tuaj yeem tshawb xyuas cov nplooj ntawv loj loj thiab lawv cov kev siv ntawm koj lub tshuab Linux siv cov lus txib miv /proc/meminfo | grep -i loj.
Tau txais cov ntaub ntawv hais txog nplooj ntawv loj (Linux nkaus xwb)
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 kBHauv qhov piv txwv no, txawm hais tias nplooj ntawv loj yog teem rau 2048 (2 MB), tag nrho cov nplooj ntawv loj yog teem rau 0. Qhov no txhais tau hais tias cov nplooj ntawv loj yog neeg xiam.
Tsab ntawv rau kev txiav txim siab cov nplooj ntawv loj
Tsab ntawv yooj yim no rov qab cov nplooj ntawv loj uas xav tau. Khiav tsab ntawv ntawm koj lub Linux server thaum PostgreSQL tab tom khiav. Xyuas kom meej tias rau ib puag ncig hloov pauv $PGDATA PostgreSQL cov ntaub ntawv teev npe tau teev tseg.
Tau txais cov nplooj ntawv loj uas xav tau
#!/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: $hpCov ntawv tso zis zoo li no:
Script tso zis
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Tus nqi pom zoo rau nplooj ntawv loj yog 88, yog li koj yuav tsum teem nws rau 88.
Txhim kho Cov nplooj ntawv loj
sysctl -w vm.nr_hugepages=88Xyuas cov nplooj ntawv loj tam sim no, koj yuav pom tias cov nplooj ntawv loj tsis siv (HugePages_Free = HugePages_Total).
Cov nplooj ntawv loj rov mus xyuas (Linux nkaus xwb)
$ 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 kBTam sim no teeb qhov loj_pages parameter rau "rau" hauv $PGDATA/postgresql.conf thiab rov pib lub server.
Ib zaug ntxiv, cov ntaub ntawv hais txog nplooj ntawv loj (Linux nkaus xwb)
$ 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 kBTam sim no koj tuaj yeem pom tias ob peb nplooj ntawv loj heev tau raug siv. Tam sim no cia peb sim ntxiv qee cov ntaub ntawv rau hauv database.
Qee qhov kev ua haujlwm database los rov ua cov nplooj ntawv loj
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Cia saib seb peb puas siv nplooj ntawv loj dua tam sim no.
Xav paub ntau ntxiv ntawm nplooj ntawv loj (Linux nkaus xwb)
$ 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 kBTam sim no koj tuaj yeem pom tias feem ntau ntawm cov nplooj ntawv loj tau siv.
Nco tseg: Tus nqi kwv yees rau HugePages siv ntawm no yog qhov tsawg heev, uas tsis yog tus nqi ib txwm muaj rau lub tshuab ua haujlwm ib puag ncig khoom. Thov kwv yees qhov xav tau ntawm nplooj ntawv rau koj lub kaw lus thiab teeb tsa lawv raws li kev thauj khoom thiab cov peev txheej.
vm sib
vm sib yog lwm cov kernel parameter uas yuav cuam tshuam rau database kev ua tau zoo. Qhov kev xaiv no yog siv los tswj tus cwj pwm ntawm swappiness (swapping nplooj ntawv hauv thiab tawm ntawm lub cim xeeb) hauv Linux. Tus nqi yog nyob ntawm 0 txog 100. Nws txiav txim siab npaum li cas lub cim xeeb yuav raug paged lossis paged tawm. Zero txhais tau tias tsis muaj kev sib pauv thiab 100 txhais tau tias kev sib pauv hnyav.
Koj tuaj yeem tau txais kev ua haujlwm zoo los ntawm kev teeb tsa qis dua.
Teem qhov no rau 0 ntawm cov kernels tshiab tuaj yeem ua rau OOM Killer (Linux's memory cleaning process) tua cov txheej txheem. Yog li nws muaj kev nyab xeeb los teeb nws rau 1 yog tias koj xav txo qis kev sib pauv. Lub neej ntawd tus nqi hauv Linux yog 60. Tus nqi siab dua ua rau MMU (chaw tswj kev nco) siv ntau qhov chaw sib pauv dua li RAM, thaum tus nqi qis dua khaws cov ntaub ntawv ntau dua / code hauv nco.
Tus nqi qis dua yog thawj koom ruam zoo rau kev txhim kho kev ua haujlwm hauv PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Cov ntawv thov tau txais kev nco thiab tso tawm thaum nws tsis xav tau ntxiv lawm. Tab sis qee zaus, daim ntawv thov tau txais ntau lub cim xeeb thiab tsis tso tawm. Qhov no yuav ua rau tus neeg tua neeg OOM. Ntawm no yog qhov ua tau parameter qhov tseem ceeb vm.overcommit_memory nrog cov lus piav qhia rau txhua tus:
- Heuristic overcommit (default); kernel-raws li heuristic
- Cia overcommit lawm xwb
- Tsis txhob overdo nws, tsis txhob tshaj qhov overcommit ratio.
Kev siv:
vm.overcommit_ratio - feem pua ββntawm RAM muaj rau overload. Tus nqi ntawm 50% ntawm lub kaw lus nrog 2 GB ntawm RAM tuaj yeem faib mus txog 3 GB ntawm RAM.
Tus nqi ntawm 2 rau vm.overcommit_memory muab kev ua tau zoo dua rau PostgreSQL. Tus nqi no ua rau cov txheej txheem neeg rau zaub mov siv RAM yam tsis muaj kev pheej hmoo tseem ceeb ntawm kev raug tua los ntawm OOM killer txheej txheem. Daim ntawv thov yuav tuaj yeem rov pib dua, tab sis tsuas yog nyob rau hauv qhov txwv ntawm overrun, uas txo qhov kev pheej hmoo ntawm OOM killer tua cov txheej txheem. Yog li ntawd, tus nqi ntawm 2 muab kev ua tau zoo dua li tus nqi qub ntawm 0. Txawm li cas los xij, kev ntseeg tau tuaj yeem txhim kho los ntawm kev ua kom ntseeg tau tias tsis muaj kev nco tsis dhau. Qhov no tshem tawm qhov kev pheej hmoo ntawm cov txheej txheem raug tua los ntawm OOM killer.
Ntawm cov tshuab tsis muaj kev sib pauv, teeb meem nrog vm.overcommit_memory sib npaug li 2 yuav tshwm sim.
vm.dirty_background_ratio / vm.dirty_background_bytes
vwm.dirty_background_ratio yog feem pua ββββntawm cov cim xeeb uas muaj cov nplooj ntawv qias neeg uas yuav tsum tau sau rau disk. Cov dej ntws mus rau disk tshwm sim hauv keeb kwm yav dhau. Tus nqi ntawm qhov ntsuas no nyob ntawm 0 txog 100; Txawm li cas los xij, tus nqi qis dua 5 yuav ua tsis tau zoo thiab qee cov kernels tsis txhawb nws. 10 yog lub neej ntawd ntawm feem ntau Linux systems. Koj tuaj yeem txhim kho kev ua tau zoo rau kev sau ntawv ua haujlwm hnyav los ntawm qhov me me, uas yuav txhais tau tias Linux yuav yaug cov nplooj ntawv qias neeg hauv keeb kwm yav dhau.
Koj yuav tsum tau teeb tsa tus nqi vm.dirty_background_bytes nyob ntawm qhov ceev ntawm koj tsav.
Tsis muaj qhov "zoo" qhov tseem ceeb rau ob qhov kev txwv no vim tias ob qho tib si yog kho vajtse nyob ntawm. Txawm li cas los xij, teeb tsa vm.dirty_background_ratio rau 5 thiab vm.dirty_background_bytes rau 25% ntawm qhov disk ceev txhim kho kev ua tau zoo rau ~ 25% feem ntau.
vm.dirty_ratio/dirty_bytes
Nws yog tib yam li vm.dirty_background_ratio/dirty_background_bytes, tshwj tsis yog tias qhov rov pib dua yog ua nyob rau hauv kev sib ntsib neeg ua haujlwm, thaiv daim ntawv thov. Yog li vm.dirty_ratio yuav tsum siab dua vwm.dirty_background_ratio. Qhov no ua kom cov txheej txheem keeb kwm yav dhau los pib ua ntej kom tsis txhob thaiv daim ntawv thov ntau li ntau tau. Koj tuaj yeem kho qhov sib txawv ntawm ob qhov sib piv nyob ntawm qhov disk I / O load.
Qhov no
Koj tuaj yeem tweak lwm qhov chaw los txhim kho kev ua tau zoo, tab sis kev txhim kho yuav tsawg thiab koj yuav tsis pom muaj txiaj ntsig ntau. Peb yuav tsum nco ntsoov tias tsis yog txhua txoj kev xaiv siv rau txhua hom kev siv. Qee cov apps ua haujlwm zoo dua thaum peb kho qee qhov chaw, thiab qee qhov tsis ua. Koj yuav tsum pom qhov sib npaug ntawm kev teeb tsa cov teeb tsa no rau koj qhov xav tau ua haujlwm thiab hom ntawv thov, thiab koj yuav tsum xav txog OS tus cwj pwm thaum kho. Configuring kernel parameters tsis yog ib qho yooj yim li configuring database parameters; nws nyuaj dua los ua cov lus pom zoo.
Tau qhov twg los: www.hab.com