Kuchita bwino kwa PostgreSQL kumatengera magawo omwe amafotokozedwa bwino. Zokonda zosasinthika za OS kernel zitha kupangitsa kuti seva ya database isagwire bwino. Chifukwa chake, ndikofunikira kuti zosinthazi zikhazikitsidwe molingana ndi seva ya database ndi kuchuluka kwake. Mu positi iyi, tikambirana magawo ofunikira a Linux kernel omwe angakhudze magwiridwe antchito a seva ya database ndi momwe angawakhazikitsire.
SHMMAX / SHMALL
Mtengo wa SHMMAX ndi gawo la kernel lomwe limagwiritsidwa ntchito kudziwa kukula kwakukulu kwa gawo limodzi logawana lomwe limagawidwa ndi Linux. Asanatuluke 9.2, PostgreSQL idagwiritsa ntchito System V (SysV), yomwe imafuna makonda a SHMMAX. Pambuyo pa 9.2, PostgreSQL idasinthira ku POSIX kukumbukira komwe adagawana. Chifukwa chake tsopano ma byte ochepa a System V omwe adagawana nawo amafunikira.
Asanatuluke 9.3, SHMMAX inali gawo lofunikira kwambiri la kernel. Mtengo wa SHMMAX umatchulidwa mu ma byte.
Mofananamo, SHMALL ndi kernel parameter ina yomwe imagwiritsidwa ntchito kudziwa
dongosolo lonse lamasamba ogawana nawo kukumbukira. Kuti muwone SHMMAX, SHMALL, kapena SHMMIN zomwe zilipo, gwiritsani ntchito lamuloli ipcs.
SHM* Tsatanetsatane - 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* Tsatanetsatane - 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 imagwiritsa ntchito Dongosolo V IPC kugawa kukumbukira nawo. Parameter iyi ndi imodzi mwazinthu zofunika kwambiri za kernel. Nthawi zonse mukalandira mauthenga olakwika otsatirawa, zikutanthauza kuti muli ndi mtundu wakale wa PostgreSQL ndipo mtengo wanu wa SHMMAX ndiwotsika kwambiri. Ogwiritsa ntchito akuyembekezeredwa kuti asinthe ndikuwonjezera mtengo malinga ndi kukumbukira komwe akugawana nawo omwe akufuna kugwiritsa ntchito.
Zolakwika zosasinthika zotheka
Ngati SHMMAX sinakonzedwe bwino, mutha kulandira cholakwika poyesa kuyambitsa gulu la PostgreSQL pogwiritsa ntchito lamulo. inedb.
initdb Kulephera
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
Momwemonso, mutha kulandira cholakwika mukayambitsa seva ya PostgreSQL pogwiritsa ntchito lamulo pg_ctl.
pg_ctl Kulephera
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.
Kumvetsetsa kusiyana kwa matanthauzo
Kufotokozera magawo a SHMMAX/SHMALL ndikosiyana pang'ono pa Linux ndi MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
timu sysctl angagwiritsidwe ntchito kwa kanthawi kusintha mtengo. Kuti mukhazikitse zikhalidwe zokhazikika, onjezani cholowera /etc/sysctl.conf. Tsatanetsatane ndi pansipa.
Kusintha Kernel Zosintha pa 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 -> 16777216Kusintha Kernel Parameters pa 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 -> 16777216Osayiwala: Kuti kusintha kukhale kokhazikika, onjezani izi ku /etc/sysctl.conf
Masamba Aakulu
Linux imagwiritsa ntchito masamba a 4 KB mokhazikika, BSD imagwiritsa ntchito masamba a XNUMX KB. Masamba Akuluakulu, ndi pa Windows - Masamba Aakulu. Tsamba ndi gawo la RAM lomwe laperekedwa kuti lichite. Njira ikhoza kukhala ndi masamba angapo kutengera zomwe mukufuna kukumbukira. Kuchuluka kwa kukumbukira kumafunika, masamba ambiri amaperekedwa. OS imasunga tebulo logawa masamba pamachitidwe. Kuchepetsa kukula kwa tsamba, kukula kwa tebulo, kumatenga nthawi yayitali kuti mupeze tsamba patsambalo. Masamba akuluakulu motero amalola kukumbukira kwakukulu kuti kugwiritsidwe ntchito ndi kuchepetsedwa kwamutu; masamba ocheperako, zolakwika zochepa zamasamba, kuwerenga/kulemba mwachangu pamabafa akuluakulu. Zotsatira zake ndikuchita bwino.
PostgreSQL imathandizira masamba akulu okha pa Linux. Mwachikhazikitso, Linux imagwiritsa ntchito masamba okumbukira 4 KB, kotero ngati pali ntchito zambiri zokumbukira, ndikofunikira kukhazikitsa masamba akulu. Kupindula kwa magwiridwe antchito kumawonedwa mukamagwiritsa ntchito masamba akulu a 2 MB mpaka 1 GB. Kukula kwakukulu kwa tsamba kumatha kukhazikitsidwa panthawi yoyambira. Mutha kuyang'ana magawo akulu atsamba ndi kugwiritsa ntchito kwawo pamakina anu a Linux pogwiritsa ntchito lamulo mphaka /proc/meminfo | grep -ndi wamkulu.
Kupeza zambiri zamasamba akulu (Linux okha)
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 kBMuchitsanzo ichi, ngakhale kukula kwa tsamba lalikulu kumayikidwa ku 2048 (2 MB), chiwerengero cha masamba akuluakulu chimayikidwa ku 0. Izi zikutanthauza kuti masamba akuluakulu ndi olemala.
Script yotsimikizira kuchuluka kwa masamba akulu
Zolemba zosavutazi zimabwezera chiwerengero chofunikira cha masamba akuluakulu. Thamangani script pa seva yanu ya Linux pomwe PostgreSQL ikugwira ntchito. Onetsetsani kuti kwa kusintha kwa chilengedwe $PGDATA Chikwatu cha data cha PostgreSQL chafotokozedwa.
Kupeza nambala yamasamba akulu ofunikira
#!/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: $hpKutulutsa kwa script kumawoneka motere:
Kutulutsa kwa script
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Mtengo wovomerezeka wamasamba akulu ndi 88, ndiye muyenera kuyiyika ku 88.
Kuyika Masamba Aakulu
sysctl -w vm.nr_hugepages=88Onani masamba akulu tsopano, muwona kuti masamba akulu sagwiritsidwa ntchito (HugePages_Free = HugePages_Total).
Masamba Aakulu Abwerezedwanso (Linux Only)
$ 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 kBTsopano ikani big_pages parameter kuti "pa" mu $PGDATA/postgresql.conf ndikuyambitsanso seva.
Apanso, zambiri zamasamba akulu (Linux okha)
$ 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 kBTsopano mutha kuwona kuti masamba akulu ochepa kwambiri akugwiritsidwa ntchito. Tiyeni tsopano tiyese kuwonjezera deta ku database.
Ntchito zina za database zobwezeretsanso masamba akulu
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Tiyeni tiwone ngati tikugwiritsa ntchito masamba akulu kwambiri tsopano kuposa kale.
Zambiri pamasamba akulu (Linux okha)
$ 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 kBTsopano mutha kuwona kuti masamba ambiri akulu akugwiritsidwa ntchito.
Zindikirani: Mtengo woyerekeza wa HugePages womwe umagwiritsidwa ntchito pano ndiwotsika kwambiri, womwe siwofunikanso pamakina omwe ali ndi chilengedwe. Chonde yerekezerani kuchuluka kwamasamba ofunikira padongosolo lanu ndikuwakhazikitsa molingana ndi katundu ndi zothandizira.
vm.swappiness
vm.swappiness ndi gawo lina la kernel lomwe lingakhudze magwiridwe antchito a database. Izi zimagwiritsidwa ntchito kuwongolera machitidwe a swappiness (kusinthana masamba mkati ndi kukumbukira) mu Linux. Mtengowo umachokera ku 0 mpaka 100. Zimatsimikizira kuchuluka kwa kukumbukira komwe kudzapeji kapena kutulutsidwa. Zero amatanthauza kuti palibe kusinthana ndipo 100 amatanthauza kusinthanitsa mwaukali.
Mutha kuchita bwino pokhazikitsa zotsika.
Kuyika izi ku 0 pamakina atsopano kungayambitse OOM Killer (njira yoyeretsa kukumbukira ya Linux) kupha njirayi. Chifukwa chake ndizotetezeka kuyiyika ku 1 ngati mukufuna kuchepetsa kusinthanitsa. Mtengo wosasinthika mu Linux ndi 60. Mtengo wapamwamba umapangitsa MMU (memory management unit) kuti igwiritse ntchito malo osinthana kuposa RAM, pamene mtengo wotsika umasunga deta / code mu kukumbukira.
Mtengo wotsika ndi kubetcha kwabwino pakuchita bwino mu PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Mapulogalamu amapeza kukumbukira ndikumasula ngati sikukufunikanso. Koma nthawi zina, kugwiritsa ntchito kumakumbukira kwambiri ndipo sikumamasula. Izi zitha kuyambitsa wakupha OOM. Nawa ma parameter omwe angathe vm.overcommit_memory ndi kufotokozera kwa aliyense:
- Heuristic overcommit (zosasintha); kernel-based heuristic
- Lolani kugonja mulimonse
- Musapitirire, musapitirire chiΕ΅erengero cha overcommit.
Tsamba:
vm.overcommit_ratio - peresenti ya RAM yopezeka kuti ichulukidwe. Mtengo wa 50% pamakina omwe ali ndi 2 GB ya RAM amatha kugawa mpaka 3 GB ya RAM.
Mtengo wa 2 wa vm.overcommit_memory umapereka magwiridwe antchito abwino a PostgreSQL. Mtengo uwu umakulitsa kugwiritsa ntchito kwa RAM kwa seva popanda chiopsezo chachikulu chophedwa ndi njira yakupha ya OOM. Ntchitoyi idzatha kuyikanso, koma mkati mwa malire a overrun, zomwe zimachepetsa chiopsezo cha wakupha OOM kupha ndondomekoyi. Choncho, mtengo wa 2 umapereka ntchito yabwino kusiyana ndi mtengo wosasinthika wa 0. Komabe, kudalirika kungathe kuwongolera poonetsetsa kuti kukumbukira kunja kwautali sikukulemetsedwa. Izi zimathetsa chiopsezo cha ndondomekoyi kuphedwa ndi wakupha wa OOM.
Pa machitidwe osasinthana, vuto la vm.overcommit_memory lofanana ndi 2 likhoza kuchitika.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio ndi kuchuluka kwa kukumbukira kodzazidwa ndi masamba akuda omwe amafunika kulembedwa ku disk. Kuthamanga kwa disk kumachitika kumbuyo. Mtengo wa chizindikiro ichi umachokera ku 0 mpaka 100; komabe, mtengo wochepera 5 ukhoza kukhala wosagwira ntchito ndipo maso ena samathandizira. 10 ndiyosakhazikika pamakina ambiri a Linux. Mutha kusintha magwiridwe antchito olembera kwambiri pogwiritsa ntchito chinthu chaching'ono, zomwe zikutanthauza kuti Linux ichotsa masamba akuda kumbuyo.
Muyenera kukhazikitsa mtengo vm.dirty_background_bytes kutengera liwiro la galimoto yanu.
Palibe "zabwino" zamagawo awiriwa chifukwa onse amadalira hardware. Komabe, kukhazikitsa vm.dirty_background_ratio ku 5 ndi vm.dirty_background_bytes kufika 25% ya liwiro la disk kumapangitsa kuti ntchito ikhale ~ 25% nthawi zambiri.
vm.dirty_ratio/dirty_bytes
Ndi chimodzimodzi vm.dirty_background_ratio/dirty_background_bytes, kupatula kuti kukonzanso kumachitika mu gawo la ogwira ntchito, kutsekereza kugwiritsa ntchito. Chifukwa chake vm.dirty_ratio iyenera kukhala yoposa vm.dirty_background_ratio. Izi zimawonetsetsa kuti njira zakumbuyo zimayamba kale kuti asatseke pulogalamuyo momwe ndingathere. Mutha kusintha kusiyana pakati pa magawo awiriwa kutengera katundu wa disk I / O.
Zotsatira
Mutha kusintha makonda ena kuti muwongolere magwiridwe antchito, koma zosinthazo zidzakhala zochepa ndipo simudzapindula kwambiri. Tiyenera kukumbukira kuti sizinthu zonse zomwe zimagwiritsidwa ntchito pamitundu yonse ya mapulogalamu. Mapulogalamu ena amagwira bwino ntchito tikasintha zina, ndipo ena satero. Muyenera kupeza kulinganiza koyenera pakati pa kukonza zosinthazi za kuchuluka kwa ntchito zomwe mukuyembekezera ndi mtundu wa ntchito, komanso muyenera kuganizira kachitidwe ka OS mukamakonza. Kukonza magawo a kernel sikophweka monga kukonza magawo a database; ndikovuta kupanga malingaliro.
Source: www.habr.com