Išẹ PostgreSQL ti o dara julọ da lori awọn ipilẹ eto iṣẹ ṣiṣe titọ. Eto ekuro OS ti ko dara le ja si iṣẹ olupin data ko dara. Nitorinaa, o jẹ dandan pe awọn eto wọnyi ti tunto ni ibamu si olupin data ati iṣẹ ṣiṣe rẹ. Ninu ifiweranṣẹ yii, a yoo jiroro diẹ ninu awọn ipilẹ ekuro Linux pataki ti o le ni ipa iṣẹ olupin data ati bii o ṣe le tunto wọn.
SHMMAX / SHMALL
SHMMAX jẹ paramita ekuro ti a lo lati pinnu iwọn ti o pọ julọ ti apakan iranti ipin kan ti ilana Linux le pin. Ṣaaju si ẹya 9.2, PostgreSQL lo System V (SysV), eyiti o nilo eto SHMMAX. Lẹhin 9.2, PostgreSQL yipada si iranti pinpin POSIX. Nitorinaa bayi awọn baiti diẹ ti iranti pinpin System V nilo.
Ṣaaju ẹya 9.3, SHMMAX jẹ paramita ekuro pataki julọ. Iye SHMMAX jẹ pato ninu awọn baiti.
Bakan naa, SHMAL jẹ paramita ekuro miiran ti a lo lati pinnu
iwọn didun eto ti awọn oju-iwe iranti ti o pin. Lati wo SHMMAX lọwọlọwọ, SHMALL, tabi awọn iye SHMMIN, lo aṣẹ naa ipcs.
SHM * Awọn alaye - 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) = 1Awọn alaye SHM - 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 nlo Eto V IPC lati soto pín iranti. Paramita yii jẹ ọkan ninu awọn paramita ekuro pataki julọ. Nigbakugba ti o ba gba awọn ifiranṣẹ aṣiṣe wọnyi, o tumọ si pe o ni ẹya agbalagba ti PostgreSQL ati pe iye SHMMAX rẹ kere pupọ. Awọn olumulo ni a nireti lati ṣatunṣe ati mu iye pọ si ni ibamu si iranti pinpin ti wọn pinnu lati lo.
Awọn aṣiṣe atunto aṣiṣe to ṣeeṣe
Ti ko ba tunto SHMMAX ni deede, o le gba aṣiṣe kan nigbati o n gbiyanju lati pilẹṣẹ iṣupọ PostgreSQL nipa lilo aṣẹ naa. initdb.
initdb Ikuna
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
Bakanna, o le gba aṣiṣe nigbati o bẹrẹ olupin PostgreSQL nipa lilo aṣẹ naa pg_ctl.
pg_ctl Ikuna
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.
Agbọye awọn iyatọ ninu awọn asọye
Ti n ṣalaye awọn aye SHMMAX/SHMALL jẹ iyatọ diẹ lori Lainos ati MacOS X:
- Lainos: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
Egbe sysctl le ṣee lo lati yi iye akoko pada. Lati ṣeto awọn iye igbagbogbo, ṣafikun titẹ sii si /etc/sysctl.conf. Awọn alaye wa ni isalẹ.
Yiyipada awọn eto Kernel lori 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 -> 16777216Yiyipada Awọn paramita Kernel lori Lainos
# 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 -> 16777216Maṣe gbagbeLati ṣe awọn ayipada titilai, ṣafikun awọn iye wọnyi si /etc/sysctl.conf
Awọn oju-iwe nla
Lainos nlo awọn oju-iwe iranti 4 KB nipasẹ aiyipada, BSD nlo awọn oju-iwe iranti XNUMX KB. Awọn oju-iwe Superati lori Windows - Awọn oju-iwe nla. Oju-iwe kan jẹ nkan ti Ramu ti a pin si ilana kan. Ilana kan le ni awọn oju-iwe pupọ da lori awọn ibeere iranti. Awọn iranti diẹ sii ilana kan nilo, diẹ sii awọn oju-iwe ti o pin. OS n ṣetọju tabili ipin iwe fun awọn ilana. Bi iwọn oju-iwe ti o kere si, ti tabili naa tobi, yoo to gun lati wa oju-iwe kan ninu tabili oju-iwe yẹn. Awọn oju-iwe ti o tobi nitorina gba oye iranti nla laaye lati lo pẹlu idinku lori oke; Awọn iwo oju-iwe diẹ, awọn aṣiṣe oju-iwe diẹ, awọn iṣẹ ṣiṣe kika/kikọ yiyara lori awọn ifipamọ nla. Abajade jẹ ilọsiwaju iṣẹ.
PostgreSQL ṣe atilẹyin awọn oju-iwe nla nikan lori Lainos. Nipa aiyipada, Lainos nlo awọn oju-iwe iranti 4 KB, nitorinaa ni awọn ọran nibiti ọpọlọpọ awọn iṣẹ iranti ba wa, o jẹ dandan lati ṣeto awọn oju-iwe nla. Awọn anfani iṣẹ jẹ akiyesi nigba lilo awọn oju-iwe nla ti 2 MB ati to 1 GB. Iwọn oju-iwe nla le ṣeto ni akoko bata. O le ni rọọrun ṣayẹwo awọn aye oju-iwe nla ati lilo wọn lori ẹrọ Linux rẹ nipa lilo aṣẹ naa ologbo /proc/meminfo | grep-i tobi.
Gbigba alaye nipa awọn oju-iwe nla (Linux nikan)
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 kBNi apẹẹrẹ yii, botilẹjẹpe iwọn oju-iwe nla ti ṣeto si 2048 (2 MB), nọmba lapapọ ti awọn oju-iwe nla ti ṣeto si 0. Eyi tumọ si pe awọn oju-iwe nla jẹ alaabo.
Akosile fun ti npinnu awọn nọmba ti o tobi ojúewé
Iwe afọwọkọ ti o rọrun yii da nọmba ti a beere fun awọn oju-iwe nla pada. Ṣiṣe awọn iwe afọwọkọ lori olupin Lainos rẹ nigba ti PostgreSQL nṣiṣẹ. Rii daju pe fun oniyipada ayika $PGDATA Ilana data PostgreSQL ti wa ni pato.
Ngba nọmba ti awọn oju-iwe nla ti o nilo
#!/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: $hpIjade iwe afọwọkọ dabi eyi:
Ijade iwe afọwọkọ
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88Iye iṣeduro fun awọn oju-iwe nla jẹ 88, nitorina o yẹ ki o ṣeto si 88.
Fifi awọn oju-iwe nla sori ẹrọ
sysctl -w vm.nr_hugepages=88Ṣayẹwo awọn oju-iwe nla ni bayi, iwọ yoo rii pe awọn oju-iwe nla ko lo (HugePages_Free = HugePages_Total).
Atunwo Awọn oju-iwe nla (Linux nikan)
$ 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 kBNi bayi ṣeto paramita awọn oju-iwe nla si “lori” ni $PGDATA/postgresql.conf ki o tun olupin naa bẹrẹ.
Lẹẹkansi, alaye nipa awọn oju-iwe nla (Linux nikan)
$ 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 kBBayi o le rii pe awọn oju-iwe nla diẹ diẹ ni o nlo. Jẹ ki a gbiyanju bayi lati ṣafikun data diẹ si ibi ipamọ data.
Diẹ ninu awọn iṣẹ data lati tunlo awọn oju-iwe nla
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Jẹ ki a rii boya a nlo awọn oju-iwe nla diẹ sii ju ti iṣaaju lọ.
Alaye diẹ sii lori awọn oju-iwe nla (Linux nikan)
$ 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 kBBayi o le rii pe pupọ julọ awọn oju-iwe nla ti wa ni lilo.
Akiyesi: Iwọn ifoju fun HugePages ti a lo nibi jẹ kekere pupọ, eyiti kii ṣe iye deede fun ẹrọ ti n ṣiṣẹ agbegbe ọja kan. Jọwọ ṣe iṣiro nọmba awọn oju-iwe ti o nilo fun eto rẹ ki o ṣeto wọn ni ibamu da lori fifuye ati awọn orisun.
vm.swappiness
vm.swappiness jẹ paramita ekuro miiran ti o le ni ipa lori iṣẹ data data. Aṣayan yii ni a lo lati ṣakoso ihuwasi ti swappiness (awọn oju-iwe yipo sinu ati kuro ni iranti) lori Lainos. Iye naa wa lati 0 si 100. O pinnu iye iranti yoo jẹ oju-iwe tabi oju-iwe. Odo tumọ si pe ko si paṣipaarọ ati 100 tumọ si paṣipaarọ ibinu.
O le gba iṣẹ to dara nipa tito awọn iye kekere.
Ṣiṣeto eyi si 0 lori awọn kernel tuntun le fa OOM Killer (ilana mimọ iranti Linux) lati pa ilana naa. Nitorina o jẹ ailewu lati ṣeto si 1 ti o ba fẹ dinku iyipada. Iwọn aiyipada ni Lainos jẹ 60. Iwọn ti o ga julọ nfa MMU (apakan iṣakoso iranti) lati lo aaye swap diẹ sii ju Ramu, lakoko ti iye kekere ntọju data / koodu diẹ sii ni iranti.
Iye kekere jẹ tẹtẹ ti o dara fun iṣẹ ilọsiwaju ni PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Awọn ohun elo gba iranti ati tu silẹ nigbati ko nilo mọ. Ṣugbọn ni awọn igba miiran, ohun elo n gba iranti pupọ ati pe ko tu silẹ. Eyi le fa apaniyan OOM kan. Eyi ni awọn iye paramita ti o ṣeeṣe vm.overcommit_iranti pẹlu apejuwe fun kọọkan:
- Heuristic overcommit (aiyipada); ekuro-orisun heuristic
- Gba laaye overcommit lonakona
- Maṣe bori rẹ, maṣe kọja ipin overcommit.
Itọkasi:
vm.overcommit_ratio - ogorun ti Ramu wa fun apọju. Iwọn 50% lori eto pẹlu 2 GB ti Ramu le pin si 3 GB ti Ramu.
Iye kan ti 2 fun vm.overcommit_memory n pese iṣẹ ṣiṣe to dara julọ fun PostgreSQL. Iye yii mu iwọn lilo Ramu ti ilana olupin pọ si laisi ewu pataki ti pipa nipasẹ ilana apaniyan OOM. Ohun elo naa yoo ni anfani lati tun gbejade, ṣugbọn laarin awọn opin ti apọju, eyiti o dinku eewu ti apaniyan OOM lati pa ilana naa. Nitorinaa, iye kan ti 2 n fun iṣẹ ṣiṣe to dara julọ ju iye aiyipada ti 0. Sibẹsibẹ, igbẹkẹle le ni ilọsiwaju nipasẹ aridaju pe iranti ti ita gbangba ko ni apọju. Eyi yọkuro ewu ilana ti a pa nipasẹ apaniyan OOM.
Lori awọn ọna ṣiṣe laisi iyipada, iṣoro pẹlu vm.overcommit_memory ti o dọgba si 2 le waye.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio jẹ ipin ogorun iranti ti o kun pẹlu awọn oju-iwe idọti ti o nilo lati kọ si disk. Fifọ si disk waye ni abẹlẹ. Iye ti paramita yii wa lati 0 si 100; sibẹsibẹ, iye kan ti o wa ni isalẹ 5 le jẹ ailagbara ati diẹ ninu awọn kernels ko ṣe atilẹyin rẹ. 10 jẹ aiyipada lori ọpọlọpọ awọn eto Linux. O le mu iṣẹ ṣiṣe dara fun awọn iṣẹ ṣiṣe-kikọ nipasẹ ipin kekere kan, eyiti yoo tumọ si Linux yoo fọ awọn oju-iwe idọti ni abẹlẹ.
O nilo lati ṣeto iye naa vm.dirty_background_bytes da lori awọn iyara ti rẹ drive.
Ko si awọn iye “dara” fun awọn aye meji wọnyi bi awọn mejeeji ṣe gbẹkẹle ohun elo. Sibẹsibẹ, eto vm.dirty_background_ratio si 5 ati vm.dirty_background_bytes si 25% ti iyara disk mu iṣẹ ṣiṣe si ~25% ni ọpọlọpọ igba.
vm.dirty_ratio/dirty_bytes
Eyi jẹ kanna bii vm.dirty_background_ratio/dirty_background_bytes, ayafi ti atunto ti wa ni ošišẹ ti ni a Osise igba, dina awọn ohun elo. Nitorina vm.dirty_ratio yẹ ki o ga ju vm.dirty_background_ratio. Eyi ṣe idaniloju pe awọn ilana isale bẹrẹ ni iṣaaju lati yago fun idinamọ ohun elo bi o ti ṣee ṣe. O le ṣatunṣe iyatọ laarin awọn ipin meji wọnyi da lori fifuye I/O disk.
Abajade
O le tweak awọn eto miiran lati mu ilọsiwaju ṣiṣẹ, ṣugbọn awọn ilọsiwaju yoo kere ati pe iwọ kii yoo rii anfani pupọ. A gbọdọ ranti pe kii ṣe gbogbo awọn aṣayan lo si gbogbo iru awọn ohun elo. Diẹ ninu awọn lw ṣiṣẹ dara julọ nigba ti a ṣatunṣe diẹ ninu awọn eto, ati diẹ ninu awọn ko ṣe. O gbọdọ wa iwọntunwọnsi ti o tọ laarin atunto awọn eto wọnyi fun iṣẹ ṣiṣe ti o nireti ati iru ohun elo, ati pe o tun gbọdọ gbero ihuwasi OS nigbati o ba ṣatunṣe. Ṣiṣeto awọn aye kernel ko rọrun bi atunto awọn aye data; o nira diẹ sii lati ṣe awọn iṣeduro.
orisun: www.habr.com