Ko te mahinga PostgreSQL tino pai kei runga i nga tawhā o te punaha whakahaere tika. Ko te pai o te whirihora i nga tautuhinga kernel OS ka raru pea te mahi a te tūmau pātengi raraunga. No reira, he mea nui kia whirihorahia enei tautuhinga kia rite ki te tūmau pātengi raraunga me ana mahi. I roto i tenei pou, ka matapakihia e matou etahi tawhā kernel Linux nui e pa ana ki te mahi a te tūmau pātengi raraunga me pehea te whirihora.
SHMMAX / SHMALL
SHMMAX he tawhā kakano e whakamahia ana hei whakatau i te rahinga o te wahanga mahara kotahi ka taea e te tukanga Linux te tohatoha. I mua i te putanga 9.2, i whakamahia e PostgreSQL te Pūnaha V (SysV), e hiahia ana ki te tautuhinga SHMMAX. Whai muri i te 9.2, ka huri a PostgreSQL ki te pumahara tiritahi POSIX. Inaianei kua iti ake nga paita o te pumahara tiritahi Pūnaha V e hiahiatia ana.
I mua i te putanga 9.3, ko SHMMAX te tawhā kernel tino nui. Ko te uara SHMMAX kua tohua ki nga paita.
Waihoki HAHA ko tetahi atu tawhā kernel hei whakatau
rōrahi pūnaha-whānui o ngā whārangi mahara. Hei tiro i nga uara SHMMAX, SHMALL, SHMMIN ranei, whakamahia te tono ipcs.
SHM* Taipitopito - 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* Taipitopito - 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)
Ka whakamahia e PostgreSQL Pūnaha V IPC ki te toha pūmahara tiritahi. Ko tenei tawhā tetahi o nga tawhā kernel tino nui. I nga wa katoa ka whiwhi koe i nga karere hapa e whai ake nei, ko te tikanga kei a koe he putanga tawhito o PostgreSQL me to uara SHMMAX he iti rawa. Ko nga kaiwhakamahi e tika ana ki te whakatika me te whakanui ake i te uara kia rite ki te mahara tiritahi e hiahia ana ratou ki te whakamahi.
He hapa whirihoranga pea
Mena kaore i te tika te whirihora a SHMMAX, ka raru pea koe i te wa e ngana ana koe ki te arawhiti i te kohinga PostgreSQL ma te whakamahi i te whakahau. initdb.
initdb Rahunga
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
Waihoki, ka whiwhi hapa koe i te wa e tiimata ana te tūmau PostgreSQL ma te whakamahi i te whakahau pg_ctl.
pg_ctl Rahunga
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.
Te mohio ki nga rereketanga o nga whakamaramatanga
He rereke te tautuhi i nga tawhā SHMMAX/SHMALL i runga i te Linux me te MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
rōpū sysctl ka taea te whakamahi ki te huri i te uara mo te wa poto. Hei tautuhi uara tonu, taapirihia he urunga ki /etc/sysctl.conf. Kei raro nga korero.
Hurihia nga Tautuhinga Kernel i runga i te 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 -> 16777216Te Huri i nga Tawhā Kernel i runga i te 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 -> 16777216Kaua e wareware: Kia mau tonu nga huringa, taapirihia enei uara ki /etc/sysctl.conf
Nga Wharangi Nui
Ka whakamahia e Linux nga wharangi mahara 4 KB ma te taunoa, ka whakamahia e BSD nga wharangi mahara XNUMX KB. Whārangi Super, me Windows - Nga Wharangi Nui. Ko te wharangi he waahanga RAM kua tohaina ki tetahi mahinga. Ka taea e te tukanga te maha nga wharangi i runga i nga whakaritenga mahara. Ko te nui ake o te mahara e hiahiatia ana e te tukanga, ka nui ake nga wharangi ka tohatohahia. Ka pupuri te OS i te ripanga tohatoha wharangi mo nga tukanga. Ka iti ake te rahi o te wharangi, ka nui ake te ripanga, ka roa ake te kimi wharangi kei roto i taua ripanga wharangi. Na nga wharangi nui ka taea te whakamahi i te maha o nga mahara me te iti o te upoko; He iti ake nga tirohanga wharangi, he iti ake nga hapa o te wharangi, he tere ake te panui/tuhi i nga mahi ki runga i nga paanui nui ake. Ko te hua ka pai ake te mahi.
Ka tautokohia e PostgreSQL nga wharangi nui i runga i te Linux. Ma te taunoa, ka whakamahi a Linux i nga wharangi mahara 4 KB, na i roto i nga keehi he nui rawa nga mahi mahara, me whakarite nga wharangi nui ake. Ka kitea nga hua mahi ina whakamahi i nga wharangi nui o te 2 MB tae atu ki te 1 GB. Ko te rahi o te wharangi nui ka taea te whakarite i te wa whakaoho. Ka taea e koe te tirotiro i nga tawhā wharangi nui me o raatau whakamahinga i runga i to miihini Linux ma te whakamahi i te whakahau ngeru /proc/meminfo | grep -i nui.
Te tiki korero mo nga wharangi nui (Linux anake)
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 kBI roto i tenei tauira, ahakoa kua tautuhia te rahi o te wharangi nui ki te 2048 (2 MB), ko te tapeke o nga wharangi nui kua tautuhia ki te 0. Ko te tikanga kua monoa nga wharangi nui.
Tuhituhi hei whakatau i te maha o nga wharangi nui
Ka whakahokia e tenei tuhinga ngawari te maha o nga wharangi nui e hiahiatia ana. Whakahaerehia te hōtuhi ki tō tūmau Linux i te wā e rere ana a PostgreSQL. Me whakarite mo te taurangi taiao $PGDATA Kua tohua te whaiaronga raraunga PostgreSQL.
Te tiki i te maha o nga wharangi nui e hiahiatia ana
#!/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: $hpHe penei te ahua o te putanga tuhinga:
Putanga hōtuhi
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Ko te uara e taunaki ana mo nga wharangi nui ko te 88, no reira me tautuhi koe ki te 88.
Tāuta Whārangi Nui
sysctl -w vm.nr_hugepages=88Tirohia nga wharangi nui inaianei, ka kite koe kaore e whakamahia nga wharangi nui (HugePages_Free = HugePages_Total).
Nga Wharangi Nui i Tirohia (Linux Anake)
$ 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 kBInaianei whakaritea te tawhā huge_pages ki te "on" ki te $PGDATA/postgresql.conf ka whakaara ano i te tūmau.
Ano ano, nga korero mo nga wharangi nui (Linux anake)
$ 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 kBInaianei ka kite koe he iti noa nga wharangi nunui e whakamahia ana. Me ngana inaianei ki te taapiri i etahi raraunga ki te papaa raraunga.
Ko etahi mahinga papaa raraunga hei hangarua i nga wharangi nui
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Kia kite tatou mena kei te whakamahi tatou i nga wharangi nui ake inaianei i nga waa o mua.
He korero ano mo nga wharangi nui (Linux anake)
$ 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 kBInaianei ka kite koe kei te whakamahia te nuinga o nga wharangi nunui.
Kia mahara: He iti rawa te uara mo nga HugePages e whakamahia ana i konei, ehara i te mea he uara noa mo te miihini e whakahaere ana i te taiao hua. Tena koa whakatau tatahia te maha o nga wharangi e hiahiatia ana mo to punaha me te whakarite kia rite ki te utaina me nga rauemi.
vm.swappiness
vm.swappiness Ko tetahi atu tawhā kernel ka taea te pa ki te mahinga papaa raraunga. Ka whakamahia tenei kōwhiringa ki te whakahaere i te whanonga o te swappiness (te huri i nga wharangi ki roto me waho o te mahara) i runga i te Linux. Ko te uara mai i te 0 ki te 100. Ka whakatauhia te nui o te mahara ka wharangihia, ka wharangitia ranei. Ko te tikanga ko te kore he whakawhitinga me te 100 te tikanga o te whakawhiti kaitaua.
Ka taea e koe te mahi pai ma te whakarite i nga uara iti.
Ma te tautuhi i tenei ki te 0 i runga i nga kakano hou ka mate pea te OOM Killer (te mahi horoi mahara a Linux) ki te patu i te tukanga. Na he pai ki te tautuhi ki te 1 ki te hiahia koe ki te whakaiti i te whakawhiti. Ko te uara taunoa i roto i te Linux ko te 60. Ko te uara teitei ake ka nui ake te whakamahi a te MMU (waea whakahaere mahara) i te waahi whakawhiti atu i te RAM, i te mea he iti ake te uara ka nui ake nga raraunga/waehere i roto i te mahara.
Ko te uara iti he putea pai mo te pai ake o te mahi i roto i te PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Ka maumahara nga tono ka tukuna ina kore e hiahiatia. Engari i etahi wa, ka nui rawa te mahara o te tono ka kore e tukuna. Ka puta pea he kaikohuru OOM. Anei nga uara tawhā ka taea vm.overcommit_memory me te whakaahuatanga mo ia:
- Heuristic overcommit (taunoa); heuristic e hāngai ana ki te kernel
- Whakaaetia te whakapau kaha
- Kaua e nui rawa atu, kaua e neke ake i te tauwehenga o te tauwehenga.
Tohutoro:
vm.overcommit_ratio — te ōrau o te RAM e waatea ana mo te taumaha. Ko te uara o te 50% i runga i te punaha me te 2 GB o te RAM ka taea te tohatoha ki te 3 GB o te RAM.
Ko te uara o te 2 mo te vm.overcommit_memory he pai ake te mahi mo PostgreSQL. Ko tenei uara ka whakanui ake i te whakamahinga RAM o te tukanga tūmau me te kore rawa e mate e te tukanga patu OOM. Ka taea e te tono te uta ano, engari i roto noa i nga rohe o te overrun, ka whakaiti i te tupono o te kaikohuru OOM ki te patu i te mahi. Na reira, ko te uara o te 2 ka pai ake te mahi i te uara taunoa o te 0. Heoi, ka taea te whakapai ake i te pono ma te whakarite kia kaua te mahara o waho i te taumaha. Ma tenei ka whakakore i te mate o te tukanga ka patua e te kaikohuru OOM.
I runga i nga punaha me te kore e huri, ka puta he raru ki te vm.overcommit_memory rite ki te 2.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio Ko te paheketanga o te mahara kua whakakiia ki nga wharangi paru me tuhi ki te kōpae. Ko te rere ki te kōpae ka puta ki muri. Ko te uara o tenei tawhā mai i te 0 ki te 100; heoi, ko te uara kei raro iho i te 5 karekau e whai hua, karekau hoki e tautoko etahi kakano. Ko te 10 te taunoa i runga i te nuinga o nga punaha Linux. Ka taea e koe te whakapai ake i nga mahi mo nga mahi tuhi-kaha ma te mea iti ake, ko te tikanga ka peehia e Linux nga wharangi paru kei muri.
Me whakarite e koe te uara vm.dirty_background_bytes i runga i te tere o to puku.
Karekau he uara "pai" mo enei tawhā e rua na te mea e whakawhirinaki ana nga taputapu e rua. Heoi, ko te tautuhi i te vm.dirty_background_ratio ki te 5 me te vm.dirty_background_bytes ki te 25% o te tere kōpae ka pai ake te mahi ki te ~25% i te nuinga o te waa.
vm.dirty_ratio/dirty_bytes
He rite tonu ki vm.dirty_background_ratio/dirty_background_bytes, engari ko te tautuhi ka mahia i roto i te waahi kaimahi, ka aukati i te tono. No reira kia teitei ake te vm.dirty_ratio i vm.dirty_background_ratio. Ma tenei ka tiimata nga tikanga papamuri kia kore e aukati i te tono ka taea. Ka taea e koe te whakatika i te rereketanga i waenga i enei owehenga e rua i runga i te kawenga I/O kōpae.
Ko te hua
Ka taea e koe te takawiri i etahi atu tautuhinga hei whakapai ake i te mahi, engari ka iti noa nga whakapainga ka kore koe e kite i te painga nui. Me mahara tatou kaore nga whiringa katoa e pa ana ki nga momo tono katoa. He pai ake te mahi o etahi taupānga ina whakatikahia e matou etahi tautuhinga, ko etahi kaore. Me kimihia e koe te toenga tika i waenga i te whirihora i enei tautuhinga mo to taumahatanga mahi me te momo tono, me whai whakaaro ano koe ki te whanonga OS i te wa e whakatangihia ana. Ko te whirihora i nga tawhā kernel ehara i te mea ngawari penei i te whirihora i nga tawhā pātengi raraunga; he uaua ake ki te whakatakoto tūtohutanga.
Source: will.com