Mafi kyawun aikin PostgreSQL ya dogara da ingantattun sigogin tsarin aiki. Tsarin saitunan kwaya na OS na iya haifar da mummunan aikin uwar garken bayanai. Don haka, ya zama wajibi a daidaita wadannan saituna bisa ga uwar garken bayanai da nauyin aikin sa. A cikin wannan sakon, za mu tattauna wasu mahimman sigogin kernel na Linux waɗanda zasu iya shafar aikin uwar garken bayanai da yadda ake daidaita su.
SHMMAX / SHMALL
SHMMAX sigar kernel ce da ake amfani da ita don tantance matsakaicin girman ɓangaren ƙwaƙwalwar ajiyar da aka raba wanda tsarin Linux zai iya kasaftawa. Kafin sigar 9.2, PostgreSQL yayi amfani da System V (SysV), wanda ke buƙatar saitin SHMMAX. Bayan 9.2, PostgreSQL ya canza zuwa ƙwaƙwalwar ajiyar POSIX. Don haka yanzu ana buƙatar ƙarancin bytes na System V shared memory.
Kafin sigar 9.3, SHMMAX shine mafi mahimmancin ma'aunin kwaya. An ƙayyade ƙimar SHMMAX a cikin bytes.
Hakazalika, SHMALL shine wani siga na kwaya da ake amfani dashi don tantancewa
girman tsarin faxi na shafukan ƙwaƙwalwar ajiya da aka raba. Don duba ƙimar SHMMAX, SHMALL, ko SHMMIN, yi amfani da umarnin ipcs.
SHM* Cikakkun bayanai - 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* Cikakken bayani - 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 yana amfani da shi Tsarin V IPC don rarraba ƙwaƙwalwar ajiya. Wannan siga yana ɗaya daga cikin mahimman sigogin kwaya. Duk lokacin da kuka karɓi waɗannan saƙonnin kuskure masu zuwa, yana nufin cewa kuna da tsohuwar sigar PostgreSQL kuma ƙimar SHMMAX ɗin ku tayi ƙasa sosai. Ana sa ran masu amfani za su daidaita da haɓaka ƙimar gwargwadon ƙwaƙwalwar ajiyar da suke son amfani da su.
Matsalolin kuskuren daidaitawa
Idan SHMMAX ba a daidaita shi daidai ba, zaku iya samun kuskure lokacin ƙoƙarin fara gungu na PostgreSQL ta amfani da umarnin. intdb.
initdb Kasawa
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
Hakanan, zaku iya karɓar kuskure lokacin fara sabar PostgreSQL ta amfani da umarnin pg_ctl.
pg_ctl Kasawa
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.
Fahimtar bambance-bambance a cikin ma'anar
Ƙayyade sigogin SHMMAX/SHMALL ya ɗan bambanta akan Linux da MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
tawagar sysctl za a iya amfani da su don canza ƙima na ɗan lokaci. Don saita ƙididdiga akai-akai, ƙara shigarwa zuwa /etc/sysctl.conf. Cikakken bayani yana ƙasa.
Canza Saitunan Kernel akan 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 -> 16777216Canza Sigar Kernel akan 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 -> 16777216Kar a manta: Don yin canje-canje na dindindin, ƙara waɗannan ƙimar zuwa /etc/sysctl.conf
Manyan Shafuka
Linux yana amfani da shafukan ƙwaƙwalwar ajiya 4 KB ta tsohuwa, BSD yana amfani da shafukan ƙwaƙwalwar ajiya XNUMX KB. Manyan Shafuka, kuma a kan Windows - Manyan Shafuka. Shafi yanki ne na RAM da aka keɓe don tsari. Tsari na iya samun shafuka masu yawa dangane da buƙatun ƙwaƙwalwar ajiya. Yawan ƙwaƙwalwar ajiya da tsari ke buƙata, ƙarin shafuka ana keɓe shi. OS yana kula da teburin rarraba shafi don matakai. Karamin girman shafin, mafi girman tebur, yana ɗaukar tsawon lokaci don nemo shafi a cikin teburin shafin. Don haka manyan shafuka suna ba da damar yin amfani da adadi mai yawa na ƙwaƙwalwar ajiya tare da rage sama da ƙasa; ƴan ra'ayoyin shafi, ƴan kurakuran shafi, saurin karantawa/rubutu ayyuka akan manyan buffers. Sakamakon ya inganta aiki.
PostgreSQL kawai yana goyan bayan manyan shafuka akan Linux. Ta hanyar tsoho, Linux yana amfani da shafukan ƙwaƙwalwar ajiya 4 KB, don haka a lokuta inda ayyukan ƙwaƙwalwar ajiya suka yi yawa, ya zama dole a saita manyan shafuka. Ana lura da nasarorin aiki yayin amfani da manyan shafuka na 2 MB kuma har zuwa 1 GB. Ana iya saita girman girman shafin a lokacin taya. Kuna iya bincika manyan sigogin shafi cikin sauƙi da amfanin su akan injin Linux ɗinku ta amfani da umarnin cat /proc/meminfo | grep - i babba.
Samun bayanai game da manyan shafuka (Linux kawai)
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 kBA cikin wannan misali, kodayake girman girman shafin an saita zuwa 2048 (2 MB), jimlar adadin manyan shafuka an saita zuwa 0. Wannan yana nufin cewa manyan shafuka suna kashe.
Rubutun don tantance adadin manyan shafuka
Wannan rubutun mai sauƙi yana mayar da adadin da ake buƙata na manyan shafuka. Gudun rubutun akan sabar Linux ɗinku yayin da PostgreSQL ke gudana. Tabbatar cewa don canjin yanayi $PGDATA An ƙayyadadden kundin bayanan bayanan PostgreSQL.
Samun adadin manyan shafukan da ake buƙata
#!/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: $hpFitowar rubutun yayi kama da haka:
Fitowar rubutun
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Ƙimar da aka ba da shawarar don manyan shafuka 88, don haka ya kamata ku saita ta zuwa 88.
Sanya Manyan Shafuka
sysctl -w vm.nr_hugepages=88Duba manyan shafuka yanzu, zaku ga cewa ba a amfani da manyan shafuka (HugePages_Free = HugePages_Total).
An Sake Ziyartar Manyan Shafuka (Linux Kawai)
$ 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 kBYanzu saita ma'aunin manyan_shafukan zuwa "akan" a cikin $PGDATA/postgresql.conf kuma sake kunna uwar garken.
Har yanzu, bayanai game da manyan shafuka (Linux kawai)
$ 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 kBYanzu za ku ga cewa ana amfani da manyan shafuka kaɗan kaɗan. Yanzu bari mu yi ƙoƙarin ƙara wasu bayanai a cikin ma'ajin bayanai.
Wasu ayyukan bayanai don sake sarrafa manyan shafuka
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Bari mu ga ko muna amfani da manyan shafuka yanzu fiye da da.
Ƙarin bayani akan manyan shafuka (Linux kawai)
$ 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 kBYanzu za ku ga cewa ana amfani da yawancin manyan shafuka.
Lura: Ƙimar da aka kiyasta don HugePages da aka yi amfani da ita a nan ta yi ƙasa sosai, wanda ba ƙima ba ce ta al'ada ga na'ura da ke tafiyar da yanayin samfur. Da fatan za a ƙididdige adadin shafukan da ake buƙata don tsarin ku kuma saita su daidai gwargwadon kaya da albarkatu.
vm.swappiness
vm.swappiness wani ma'aunin kwaya ne wanda zai iya shafar aikin bayanai. Ana amfani da wannan zaɓin don sarrafa halayen swappiness (swapping shafuka a ciki da waje na ƙwaƙwalwar ajiya) akan Linux. Ƙimar tana daga 0 zuwa 100. Yana ƙayyade adadin ƙwaƙwalwar ajiya da za a yi shafi ko fitar da shi. Sifili yana nufin babu musanya kuma 100 yana nufin musanya mai ƙarfi.
Kuna iya samun kyakkyawan aiki ta saita ƙananan ƙima.
Saita wannan zuwa 0 akan sabbin kernels na iya haifar da OOM Killer (Tsarin tsaftace ƙwaƙwalwar ajiyar Linux) don kashe tsarin. Don haka yana da aminci a saita shi zuwa 1 idan kuna son rage musanyawa. Ƙimar da ta dace a cikin Linux ita ce 60. Ƙimar da ta fi girma tana sa MMU (nau'in sarrafa ƙwaƙwalwar ajiya) don amfani da sararin swap fiye da RAM, yayin da ƙananan ƙima yana adana ƙarin bayanai / lambar a ƙwaƙwalwar ajiya.
Ƙananan ƙima shine fare mai kyau don ingantaccen aiki a cikin PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Aikace-aikace suna samun ƙwaƙwalwar ajiya kuma su sake shi lokacin da ba a buƙata. Amma a wasu lokuta, aikace-aikacen yana samun ƙwaƙwalwar ajiya da yawa kuma baya sakin shi. Wannan na iya haifar da kisa OOM. Anan akwai yuwuwar ƙimar siga vm.overcommit_memory tare da bayanin kowane:
- Heuristic overcommit (tsoho); kernel tushen heuristic
- Bada izinin wuce gona da iri
- Kada ku wuce gona da iri, kar ku wuce ƙimar wuce gona da iri.
Ссылка:
vm.overcommit_rabo - yawan adadin RAM akwai don yin kiba. Ƙimar 50% akan tsarin da 2 GB na RAM zai iya rarraba har zuwa 3 GB na RAM.
Ƙimar 2 don vm.overcommit_memory yana ba da kyakkyawan aiki don PostgreSQL. Wannan ƙimar tana haɓaka amfani da RAM na tsarin uwar garken ba tare da wani gagarumin haɗarin kashe shi ta hanyar kisa na OOM ba. Aikace-aikacen za ta iya sake yin lodawa, amma kawai a cikin iyakokin wuce gona da iri, wanda ke rage haɗarin mai kashe OOM ya kashe tsarin. Sabili da haka, ƙimar 2 yana ba da kyakkyawan aiki fiye da ƙimar tsoho na 0. Duk da haka, ana iya inganta aminci ta hanyar tabbatar da cewa ƙwaƙwalwar ajiyar waje ba ta da yawa. Wannan yana kawar da haɗarin tsarin da mai kashe OOM ya kashe.
A kan tsarin ba tare da musanya ba, matsala tare da vm.overcommit_memory daidai da 2 na iya faruwa.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.datti_bayan_ratio shine yawan adadin ƙwaƙwalwar ajiya da ke cike da ƙazantattun shafuka waɗanda ke buƙatar rubutawa zuwa faifai. Juyawa zuwa faifai yana faruwa a bango. Darajar wannan siga ya bambanta daga 0 zuwa 100; duk da haka, ƙimar da ke ƙasa 5 na iya zama mara amfani kuma wasu kernels ba sa goyan bayan ta. 10 shine tsoho akan yawancin tsarin Linux. Kuna iya haɓaka aiki don ayyukan rubutu mai ƙarfi ta ƙaramin ma'ana, wanda ke nufin Linux za ta zubar da ƙazantattun shafuka a bango.
Kuna buƙatar saita ƙimar vm.datti_bayan_bytes ya danganta da saurin tuƙi.
Babu "kyakkyawan" dabi'u don waɗannan sigogi guda biyu saboda duka sun dogara da hardware. Koyaya, saita vm.dirty_background_ratio zuwa 5 da vm.dirty_background_bytes zuwa 25% na saurin diski yana inganta aiki zuwa ~25% a mafi yawan lokuta.
vm.dirty_ratio/dirty_bytes
Daidai yake da vm.dirty_background_ratio/dirty_background_bytes, sai dai an yi sake saiti a cikin zaman ma'aikaci, yana toshe aikace-aikacen. Saboda haka vm.dirty_ratio yakamata ya zama sama da vm.datti_bayan_ratio. Wannan yana tabbatar da cewa tsarin baya yana farawa da wuri don guje wa toshe aikace-aikacen gwargwadon yiwuwa. Kuna iya daidaita bambanci tsakanin waɗannan ma'auni guda biyu dangane da nauyin I/O faifai.
Sakamakon
Kuna iya tweak wasu saitunan don inganta aiki, amma haɓakawa zai zama kaɗan kuma ba za ku ga fa'ida mai yawa ba. Dole ne mu tuna cewa ba duk zaɓuɓɓuka sun shafi kowane nau'in aikace-aikacen ba. Wasu ƙa'idodin suna aiki mafi kyau idan muka daidaita wasu saitunan, wasu kuma ba sa. Dole ne ku nemo ma'auni mai dacewa tsakanin daidaita waɗannan saitunan don aikin da ake tsammanin ku da nau'in aikace-aikacen, kuma dole ne ku yi la'akari da halin OS lokacin kunnawa. Daidaita sigogin kwaya baya da sauƙi kamar daidaita sigogin bayanai; yana da wahala a ba da shawarwari.
source: www.habr.com