🥇Tuning paramèter kernel Linux kanggo ngoptimalake PostgreSQL

Kinerja PostgreSQL sing optimal gumantung marang paramèter sistem operasi sing ditetepake kanthi bener. Setelan kernel OS sing ora dikonfigurasi bisa nyebabake kinerja server database sing ora apik. Mulane, penting banget yen setelan kasebut dikonfigurasi miturut server database lan beban kerjane. Ing kirim iki, kita bakal ngrembug sawetara parameter kernel Linux penting sing bisa mengaruhi kinerja server database lan carane ngatur.

SHMMAX / SHMALL

SHMMAX minangka parameter kernel sing digunakake kanggo nemtokake ukuran maksimum bagean memori sing dienggo bareng sing bisa dialokasikan dening proses Linux. Sadurunge versi 9.2, PostgreSQL nggunakake Sistem V (SysV), sing mbutuhake setelan SHMMAX. Sawise 9.2, PostgreSQL ngalih menyang memori bareng POSIX. Dadi saiki luwih sithik bita saka memori bareng Sistem V dibutuhake.

Sadurunge versi 9.3, SHMMAX minangka parameter kernel sing paling penting. Nilai SHMMAX ditemtokake ing bita.

Kajaba iku SHMALL punika parameter kernel liyane digunakake kanggo nemtokake
volume saka sudhut sistem kaca memori sing dienggo bareng. Kanggo ndeleng nilai SHMMAX, SHMALL, utawa SHMMIN saiki, gunakake perintah kasebut ipcs.

SHM* Rincian - 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) = 1

SHM* Rincian - 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 nggunakake Sistem V IPC kanggo ngalokasi memori sing dienggo bareng. Parameter iki minangka salah sawijining parameter kernel sing paling penting. Saben sampeyan nampa pesen kesalahan ing ngisor iki, tegese sampeyan duwe versi PostgreSQL lawas lan nilai SHMMAX sampeyan sithik banget. Pangguna wis samesthine kanggo nyetel lan nambah nilai miturut memori sambungan sing arep digunakake.

Kemungkinan kesalahan konfigurasi

Yen SHMMAX ora dikonfigurasi kanthi bener, sampeyan bisa nampa kesalahan nalika nyoba miwiti kluster PostgreSQL nggunakake perintah kasebut. initdb.

initdb Gagal
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

Kajaba iku, sampeyan bisa uga nampa kesalahan nalika miwiti server PostgreSQL nggunakake printah kasebut pg_ctl.

pg_ctl Gagal
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.

Ngerteni bedane definisi

Nemtokake paramèter SHMMAX/SHMALL rada beda ing Linux lan MacOS X:

Linux: kernel.shmmax, kernel.shmall
MacOS X: kern.sysv.shmmax, kern.sysv.shmall

tim sysctl bisa digunakake kanggo ngganti nilai sementara. Kanggo nyetel nilai pancet, nambah entri menyang /etc/sysctl.conf. Rincian ing ngisor iki.

Ngganti Setelan Kernel ing 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 -> 16777216

Ngganti Parameter Kernel ing 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 -> 16777216

Aja lali: Kanggo nggawe owahan permanen, nambah nilai iki kanggo /etc/sysctl.conf

Ageng Kaca

Linux nggunakake 4 kaca memori KB minangka standar, BSD nggunakake XNUMX kaca memori KB. Kaca Super, lan ing Windows - Kaca Gedhe. Kaca minangka potongan RAM sing diparengake kanggo proses. Proses bisa duwe pirang-pirang kaca gumantung saka syarat memori. Luwih akeh memori sing dibutuhake proses, luwih akeh kaca sing diparengake. OS njaga tabel alokasi kaca kanggo proses. Ukuran kaca sing luwih cilik, luwih gedhe tabel, luwih suwe kanggo nemokake kaca ing tabel kaca kasebut. Kaca-kaca gedhe mula ngidini akeh memori digunakake kanthi overhead sing suda; tampilan kaca luwih sithik, kesalahan kaca luwih sithik, operasi maca / nulis luwih cepet tinimbang buffer sing luwih gedhe. Asil punika kinerja apik.

PostgreSQL mung ndhukung kaca gedhe ing Linux. Kanthi gawan, Linux nggunakake 4 kaca memori KB, supaya ing kasus ngendi ana akeh banget operasi memori, iku perlu kanggo nyetel kaca luwih gedhe. Hasil kinerja diamati nalika nggunakake kaca gedhe 2 MB lan nganti 1 GB. Ukuran kaca gedhe bisa disetel nalika boot. Sampeyan bisa kanthi gampang mriksa parameter kaca gedhe lan panggunaane ing mesin Linux sampeyan nggunakake printah kasebut cat /proc/meminfo | grep -i ageng.

Entuk informasi babagan kaca gedhe (mung Linux)

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 kB

Ing conto iki, sanajan ukuran kaca gedhe disetel dadi 2048 (2 MB), jumlah total kaca gedhe disetel dadi 0. Iki tegese kaca gedhe dipateni.

Skrip kanggo nemtokake jumlah kaca gedhe

Skrip prasaja iki ngasilake jumlah kaca gedhe sing dibutuhake. Jalanake skrip ing server Linux nalika PostgreSQL lagi mlaku. Priksa manawa kanggo variabel lingkungan $PGDATA Direktori data PostgreSQL ditemtokake.

Entuk jumlah kaca gedhe sing dibutuhake

#!/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:     $hp

Output skrip katon kaya iki:

Output skrip

Pid:            12737
VmPeak:         180932 kB
Hugepagesize:   2048 kB
Set Huge Pages: 88

Nilai sing disaranake kanggo kaca gedhe yaiku 88, dadi sampeyan kudu nyetel menyang 88.

Nginstal Kaca Gedhe

sysctl -w vm.nr_hugepages=88

Priksa kaca gedhe saiki, sampeyan bakal weruh manawa kaca gedhe ora digunakake (HugePages_Free = HugePages_Total).

Kaca Gedhe Dibukak (Mung Linux)

$ 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 kB

Saiki nyetel parameter huge_pages menyang "on" ing $PGDATA/postgresql.conf lan miwiti maneh server.

Sepisan maneh, informasi babagan kaca gedhe (mung Linux)

$ 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 kB

Saiki sampeyan bisa ndeleng manawa sawetara kaca gedhe sing digunakake. Ayo saiki nyoba nambah sawetara data menyang database.

Sawetara operasi database kanggo daur ulang kaca gedhe

postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000

Ayo ndeleng apa kita nggunakake kaca luwih gedhe saiki tinimbang sadurunge.

Info liyane babagan kaca gedhe (mung Linux)

$ 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 kB

Saiki sampeyan bisa ndeleng manawa umume kaca gedhe digunakake.

Cathetan: Nilai kira-kira kanggo HugePages sing digunakake ing kene sithik banget, sing dudu nilai normal kanggo mesin sing mbukak lingkungan produk. Mangga kira-kira jumlah kaca sing dibutuhake kanggo sistem sampeyan lan atur miturut muatan lan sumber daya.

vm.swappiness

vm.swappiness iku parameter kernel liyane sing bisa mengaruhi kinerja database. Opsi iki digunakake kanggo ngontrol prilaku swappiness (ngganti kaca lan metu saka memori) ing Linux. Nilai kisaran saka 0 kanggo 100. Iku nemtokake pinten memori bakal paged utawa paged metu. Nol tegese ora ana ijol-ijolan lan 100 tegese ijol-ijolan agresif.

Sampeyan bisa entuk kinerja apik kanthi nyetel nilai sing luwih murah.

Nyetel iki dadi 0 ing kernel anyar bisa nyebabake OOM Killer (proses pembersihan memori Linux) mateni proses kasebut. Dadi aman kanggo nyetel menyang 1 yen sampeyan pengin nyilikake swapping. Nilai standar ing Linux 60. Nilai sing luwih dhuwur nyebabake MMU (unit manajemen memori) nggunakake papan swap luwih akeh tinimbang RAM, nalika nilai sing luwih murah tetep luwih akeh data / kode ing memori.

Nilai sing luwih murah minangka taruhan sing apik kanggo ningkatake kinerja ing PostgreSQL.

vm.overcommit_memory / vm.overcommit_ratio

Aplikasi entuk memori lan ngeculake yen ora dibutuhake maneh. Nanging ing sawetara kasus, aplikasi entuk akeh memori lan ora ngeculake. Iki bisa nyebabake pembunuh OOM. Iki minangka nilai parameter sing bisa ditindakake vm.overcommit_memory kanthi katrangan kanggo saben:

Heuristik overcommit (standar); heuristik berbasis kernel
Allow overcommit wae
Aja kakehan, aja ngluwihi rasio overcommit.

Wigati: https://www.kernel.org/doc/Documentation/vm/overcommit-accounting

vm.overcommit_ratio - persentasi saka RAM kasedhiya kanggo kakehan. Nilai 50% ing sistem kanthi 2 GB RAM bisa nyedhiyakake nganti 3 GB RAM.

Nilai 2 kanggo vm.overcommit_memory nyedhiyakake kinerja sing luwih apik kanggo PostgreSQL. Nilai iki nggedhekake panggunaan RAM proses server tanpa ana risiko sing bisa dipateni dening proses pembunuh OOM. Aplikasi kasebut bakal bisa diisi ulang, nanging mung ing watesan overrun, sing nyuda risiko pembunuh OOM sing mateni proses kasebut. Mulane, nilai 2 menehi kinerja sing luwih apik tinimbang nilai standar 0. Nanging, linuwih bisa nambah kanthi mesthekake yen memori metu saka sawetara ora overloaded. Iki ngilangi risiko proses sing dipateni dening pembunuh OOM.

Ing sistem tanpa swapping, masalah karo vm.overcommit_memory witjaksono kanggo 2 bisa kelakon.

https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

vm.dirty_background_ratio / vm.dirty_background_bytes

vm.dirty_background_ratio punika persentasi saka memori kapenuhan kaca reged sing kudu ditulis kanggo disk. Flush menyang disk ana ing latar mburi. Nilai parameter iki kisaran saka 0 kanggo 100; Nanging, Nilai ngisor 5 bisa uga ora efektif lan sawetara kernels ora ndhukung. 10 minangka standar ing umume sistem Linux. Sampeyan bisa nambah kinerja kanggo operasi nulis-intensif kanthi faktor sing luwih cilik, sing tegese Linux bakal mbusak kaca sing reged ing latar mburi.

Sampeyan kudu nyetel nilai vm.dirty_background_bytes gumantung saka kacepetan drive sampeyan.

Ora ana nilai "apik" kanggo rong paramèter iki amarga loro-lorone gumantung karo hardware. Nanging, nyetel vm.dirty_background_ratio kanggo 5 lan vm.dirty_background_bytes kanggo 25% saka kacepetan disk nambah kinerja kanggo ~ 25% ing paling kasus.

vm.dirty_ratio/dirty_bytes

Iki padha karo vm.dirty_background_ratio/dirty_background_bytes, kajaba reset ditindakake ing sesi buruh, ngalangi aplikasi kasebut. Mulane vm.dirty_ratio kudu luwih dhuwur tinimbang vm.dirty_background_ratio. Iki mesthekake yen proses latar mburi diwiwiti luwih awal supaya ora ngalangi aplikasi sabisa-bisa. Sampeyan bisa nyetel prabédan antarane loro rasio iki gumantung ing mbukak I / O disk.

Asile

Sampeyan bisa ngapiki setelan liyane kanggo nambah kinerja, nanging dandan bakal minimal lan sampeyan ora bakal entuk manfaat akeh. Kita kudu eling yen ora kabeh opsi ditrapake kanggo kabeh jinis aplikasi. Sawetara app bisa luwih apik nalika kita nyetel sawetara setelan, lan sawetara ora. Sampeyan kudu golek imbangan tengen antarane configuring setelan iki kanggo beban kerja samesthine lan jinis aplikasi, lan sampeyan uga kudu nimbang prilaku OS nalika tuning. Ngonfigurasi paramèter kernel ora segampang ngonfigurasi paramèter database; luwih angel kanggo menehi rekomendasi.

Source: www.habr.com

Konfigurasi Pilihan Kernel Linux kanggo Ngoptimalake PostgreSQL