ααΆαα’αα»αααα PostgreSQL ααα’αααα»αα’αΆαααααααΎαααΆαααΆααααααααααααααααααα·ααααα·ααΆααααααΆαααααααααΆαααααΉαααααΌαα ααΆααααααααΊααα OS αααααΆαααααααα ααΆαααααααααα·αααα’α’αΆα αααααΆαα±ααααααΎαααΆααααΆαααΈαααααΌαααααΆααα·αααααααααααα ααΌα αααα ααΆααΆααΆαα αΆαααΆα ααααααΆααααααααΆαααααααααΌαααΆαααααααα ααΆαααααααααααααααα ααΆααααΆαααΈαααααΌαααααΆααα·αααααα αα·αααααα»αααΆαααΆαααααααΆα αα αααα»αααΆααααααΆαααα ααΎαααΉααα·ααΆααααΆα’αααΈαααΆαααΆααααααααΊαααααΈαα»α ααααΆααααα½αα ααα½ααααα’αΆα αααααΆαααααααααΎαααΆααααΆαααΈαααααΌαααααΆααα·αααααα αα·αααααααααααα ααΆαααααααααα½αααΆα
SHMMAX / SHMALL
SHMMAX ααΊααΆαααΆαααΆααααααααΊααααααααααΎααΎααααΈαααααααα αα’αα·ααααΆαααααααα’αααα αα αΆααααααΆαα ααααααααααα½ααααααααΎαααΆαααΈαα»α α’αΆα αααα ααααΆαα αα»αααααααα 9.2 PostgreSQL ααΆαααααΎαααααααα V (SysV) ααααααααΌαα±ααααΆαααΆαααααα SHMMAX α αααααΆααααΈ 9.2, PostgreSQL ααΆαααααΌααα α’αααα αα αΆααααααΆαα ααααααα POSIX α ααΌα ααααα₯α‘αΌαααα αααΈααα·α ααα’αααα αα αΆααααααΆαα ααααααααααα System V ααααΌαααΆαααΆαααΆαα
αα»ααααα 9.3 SHMMAX ααΊααΆαααΆαααΆααααααααΊαααααααΆαααααα»αα ααααα SHMMAX ααααΌαααΆααααααΆααααΆααα
ααααααααααΆαααααα SHMALL ααΊααΆαααΆαααΆααααααααΊααααα½ααααααααααααααααΎααΎααααΈααααα
ααα·ααΆαααααααααααααααααααααα·αααααΆαα
αααααααα ααΎααααΈααΎαααααα SHMMAX, SHMALL α¬ SHMMIN αα
αα
α»αααααα ααΌαααααΎααΆααααααααΆ α’αΆαααΈα’αα.
SHM * ααααααΆααααα’α·α - ααΈαα»α
$ 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* ααααααΆααααα’α·α - 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 ααααΎ αααααααα V IPC ααΎααααΈαααα ααααΆαα αα αΆααααααΆαα αααααααα αααΆαααΆαααααααααααΊααΆαααΆαααΆααααααααΊαααααααΆαααααα»ααα½αα αα αααααΆαααα’αααααα½αααΆαααΆαααα α»αααΆαααααα ααΆααΆααααααΆα’αααααΆααααα PostgreSQL α αΆαα α αΎαααααα SHMMAX ααααα’αααααΊααΆαααΆααα α’αααβααααΎβααααΌαβααΆαβααβααααΉαβααΆβααΉαβααβααααα½αβαα·αβαααααΎαβαααααβαααβααΎβαααβααΆαβα’αααβα αα αΆαβαα½αβαααβαα½αααβα ααβααααΎα
ααα α»ααααα»αααΆαααααααα ααΆαααααααααα»ααααα’αΆα ααΎαααΆα
ααααα·αααΎ SHMMAX αα·αααααΌαααΆαααααααα ααΆααααααααααααΉαααααΌααα α’αααα’αΆα ααα½αααΆαααα α»ααα αααααααΆααΆαα αΆααααααΎααααα»α PostgreSQL αααααααΎααΆααααααααΆ initdb.
initdb αααΆααα
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
ααΌα ααααΆαααααα α’αααα’αΆα ααα½αααΆαααα α»ααα αααα αΆααααααΎααααΆαααΈααα PostgreSQL αααααααΎααΆααααααααΆ pg_ctl.
pg_ctl αααΆααα
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.
ααΆααααααΉαααΈααΆααα»αααααΆαααα·ααααα
ααΆαααααααααΆαααΆαααααα SHMMAX/SHMALL ααΊαα»αααααΆααααα·α ααααα½α αα ααΎ Linux αα·α MacOS Xα
- ααΈαα»α α kernel.shmmax, kernel.shmall
- MacOS Xα kern.sysv.shmmax, kern.sysv.shmall
αααα»αααΆαααΆα α’αααααΈα α’αΆα ααααΌαααΆαααααΎααΎααααΈααααΆααααααΌααααααααΆααααααα’αΆααααα ααΎααααΈααααααααααααα ααααααααΆαα»αα /etc/sysctl.conf. ααααααΆααααα’α·αααΆααα ααΆααααααα
ααΆαααααΆααααααΌαααΆααααααααΊααααα ααΎ 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
ααΆαααααΆααααααΌααααΆαααΆααααααααΊααααα ααΎααΈαα»α
# 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
αα»αβααααα α ααΎααααΈααααΎααΆαααααΆααααααΌαααΆα’α α·αααααααα αααααααααααααΆααααααα /etc/sysctl.conf
ααααααααα
ααΈαα»α ααααΎαααααα’αααα αα αΆα 4 KB ααΆαααααΆαααΎα BSD ααααΎαααααα’αααα αα αΆα XNUMX KB α αααααααααΎααα·ααα ααΎ Windows - ααααααα. αααααααΊααΆααααααα RAM αααααααΌαααΆαααααα»ααα»ααααααΆααααααΎαααΆααα½αα ααααΎαααΆααα½αα’αΆα ααΆαα αααΎαααααα α’αΆαααααααΎαααααΌαααΆαα’αααα αα αΆαα ααααΎαααΆαααααΌαααΆαα’αααα αα αΆαααΆααααα αααΎα αααααααΆααααα αααΎαααααΌαααΆααααα ααα OS αααααΆααΆααΆααααα ααααααααααααΆααααααΎαααΆαα ααα ααααααααΆααααααΌα ααΆααΆαααΆαααααα ααΆααααΌαααΆααααααΌαααΎααααΈαααααααααααααααα»αααΆααΆαααααααααα ααΌα αααα αααααααα’αα»ααααΆαα±ααααααΎα’αααα αα αΆαα αααΎαααΆαα½αααΉαααΆαααΆαααααααααΎαα α ααα½αααΎαααααααα·α ααΆα ααα α»αααααααα·α ααΆα ααααα·ααααα·ααΆαα’αΆα/αααααααΏαααΆαααΎααααα»αααααΆαα ααααααααΊααΆααααααα’ααΆαα’αα»ααααα
PostgreSQL ααΆααααααααααααααα ααΎααΈαα»α ααα»αααααα ααΆαααααΆαααΎα ααΈαα»α ααααΎαααααα’αααα αα αΆα 4 KB ααΌα αααααααα»αααααΈαααααΆαααααα·ααααα·ααΆαα’αααα αα αΆαα αααΎαααα α αΆαααΆα αααααΌαααααααααααααααΆαα ααΆαααΎαα‘αΎαααααΆαα’αα»ααααααααΌαααΆαααααααααααΎααα αααααααΎααααααα 2 MB αα·ααα αΌαααα 1 GB α ααα ααααααααα’αΆα ααααΌαααΆαααααααα αααα αΆααααααΎαα α’αααα’αΆα αα·αα·αααααΎααααΆαααΆααααααααααααα αα·αααΆαααααΎααααΆααααααααΆαααΆαααΆααααα½ααα ααΎαααΆαααΈαααΈαα»α ααααα’ααααααααααΎααΆααααααααΆ ααααΆ /proc/meminfo | grep - αααα»ααα.
ααα½αααΆαααααααΆαα’αααΈααααααα (ααΈαα»α ααααα»ααααα)
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
αααα»αα§ααΆα αααααα αααααΈααΆααα ααααααααααααΌαααΆαααααααα 2048 (2 MB) ααα»ααααα ααα½ααααααααααα»αααααΌαααΆαααααααα 0α αααααΆααααααΆαααααααααααΌαααΆααα·αα
ααααααΈααααααΆαααααααα ααα½αααααααα
ααααααΈαααΆααααααααααα‘ααα ααα½αααααααααααααααΌαααΆαα ααααΎαααΆαααααααΈααα ααΎαααΆαααΈαααααΈαα»α ααααα’αααααααααααα PostgreSQL αααα»αααααΎαααΆαα ααααΌαααααΆααααΆαααααΆααααα·ααααΆααααααααα½α $PGDATA αααα·αααααα PostgreSQL ααααΌαααΆααααααΆααα
ααα½αααΆαα ααα½αααααααααααααααΌαααΆα
#!/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
ααααααααααααΈαααΎααα ααΌα αααα
ααααααααααααΈα
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88
ααααααααααΆαααααΆααααααΆαααααααααααΊ 88 ααΌα ααααα’ααααα½ααααααααααΆαα ααΆ 88α
ααΆαααα‘αΎαααααααα
sysctl -w vm.nr_hugepages=88
ααΌααα·αα·αααααΎααααααααα₯α‘αΌαααα α’αααααΉαααΎαααΆααααααααα·αααααΌαααΆαααααΎαα (HugePages_Free = HugePages_Total)α
αααααααααΆαααΎαα‘αΎααα·α (ααΈαα»α ααααα»ααααα)
$ 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
α₯α‘αΌααααααααααααΆαααΆαααααα huge_pages αα "ααΎα" αα αααα»α $PGDATA/postgresql.conf α αΎαα αΆααααααΎααααΆαααΈαααα‘αΎααα·αα
ααΆααααΈααααααα ααααααΆαα’αααΈααααααα (ααΈαα»α ααααα»ααααα)
$ 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
α₯α‘αΌαβααα α’αααβα’αΆα βααΎαβααΆβαααααβαααβαα·α αα½α βααΆααβααααΌαβααΆαβααβααααΎα α₯α‘αΌαααα α αΌαααΎαααααΆααΆααααααααα·αααααααα½αα ααα½ααα αααα»αααΌαααααΆααα·ααααααα
ααααα·ααααα·ααΆαααΌαααααΆααα·αααααααα½αα ααα½αααΎααααΈααα αααααααααα
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000
ααΌαααΎαααΆααΎααΎααααα»αααααΎαααααααααΆααα»αα₯α‘αΌααααα¬α’ααα
ααααααΆααααααααα ααΎααααααα (ααΈαα»α ααααα»ααααα)
$ 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
α₯α‘αΌααααα’αααα’αΆα ααΎαααΎαααΆααΆαα αααΎαααααααααααααα»αααααΌαααΆαααααΎααααΆααα
α αααΆαα ααααααααΆααααααΆααααααΆαα HugePages αααααααΎαα ααΈαααααΊααΆαααΆαα ααααα·ααααααΆαααααααααααΆαααααΆαααααΆαααΈαααααααα»αααααΎαααΆαααα·ααααΆαααα·αααα ααΌααααΆαααααααΆαα ααα½αααααααααααααΌαααΆααααααΆααααααααααααααα’ααα α αΎααααααααΆαα ααΆαααΆααααα»α αα·αααααΆαα
vm. swappiness
vm. swappiness ααΊααΆαααΆαααΆααααααααΊααααα½ααααααααααααα’αΆα αααααΆαααααααααΎαααΆαααΌαααααΆααα·ααααααα αααααΎαβαααβααααΌαβααΆαβααααΎβααΎααααΈβαααααααααβα₯αα·ααΆααβααβααΆαβααααΌαβααααα (ααααΌαβαααααβαααα»α αα·αβαααα βα’αααα αα αΆα) αααα»αβααΈαα»α α αααααααΆαα αΆααααΈ 0 ααα 100α ααΆαααααααΆααΎα ααα½αα’αααα αα αΆαααΉαααααΌαααΆααααααα α¬αα·ααααααα ααΌαααααΆααααααΆααααΆαααΆαααααΆααααααΌα α αΎα 100 ααΆααααααΆααΆααααααΌαααααααΆαααΆαα
α’αααα’αΆα ααα½αααΆαααααΎαααΆαααα’αααααααααααααααΆαα
ααΆααααααααααα 0 αα ααΎααΊαααααααΈα’αΆα αααααΆαα±αα OOM Killer (ααααΎαααΆααααα’αΆαα’αααα αα αΆαααααααΈαα»α ) ααΎααααΈαααααΆααααααΎαααΆααααα ααΌα ααααααΆααΆααα»ααααα·ααΆααααα»αααΆααααααααΆαα 1 ααααα·αααΎα’αααα ααααΆαααααααααΆααααααΌαα αααααααααΆαααΎααα αααα»αααΈαα»α ααΊ 60α ααααααααααααααΎα±αα MMU (α’αααααΆααααααααααα’αααα αα αΆα) ααααΎααα αααααα αααΎαααΆα RAM αααααααααααααΆααααααΆαα·αααααα/ααΌαααΆααααα αααΎααα αααα»αα’αααα αα αΆαα
αααααααΆαααΊααΆααΆαααααΆααααααα’αααααΆααααααΎαααΆααααααΎαα‘αΎααα αααα»α PostgreSQL α
vm.overcommit_memory / vm.overcommit_ratio
αααααα·ααΈααα½αααΆαα’αααα αα αΆα α αΎααααα ααααΆαα ααααααααΆαααααααΌαααΆαα ααα»αααααααα»αααααΈαααααααααα·ααΈααα½αααΆαα’αααα αα αΆαα αααΎααααα αΎααα·ααααα ααααΆααα αααα’αΆα αααααΆαα±ααααΆααα OOM α αααααΊααΆααααααααΆαααΆαααααααααα’αΆα ααααΎααΆα vm.overcommit_memory ααΆαα½αααΉαααΆααα·αααααΆαααααΆααααΈαα½ααα
- Heuristic overcommit (ααααΆαααΎα); αααΈααα·ααααΆααα’ααααΎααΊααα
- α’αα»ααααΆαβα±ααβααΎαβααΈβαααβαααβ
- αα»αααααΎα±ααααΆα α½ααααααΆα αα»αα±ααααΎαααΈαααΆααΆααααααααΎαα
ααα
ααααΈααα:
vm.overcommit_ratio - ααΆααααα RAM αααα’αΆα ααααΎααΆααααααΆααααΆααααα»αααΎαααααααα ααααα 50% αα ααΎαααααααααααααΆα RAM 2 GB α’αΆα αααα αα RAM αα αΌαααα 3 GB α
ααααα 2 αααααΆαα vm.overcommit_memory αααααααΌαααααΎαααΆααααααΎαααΆααα»ααααααΆαα PostgreSQL α αααααααααααααΎαααΆαααααΎααααΆαα RAM ααααααααΎαααΆααααΆαααΈααα αααααααΆαα αΆαα·ααααααααααααααΆαα½ααααααΉαααααΌαααΆααααααΆαααααααααΎαααΆαααΆααα OOM α αααααα·ααΈααΉαα’αΆα ααααΎαααΆαα‘αΎααα·αααΆα ααα»ααααααΆααααα αααα»αααααααααααααΆααααα α½αααααα·αααα»ααααα αααααΆαααααααα αΆαα·αααααααΆααα OOM ααααααααΆααααααΎαααΆααααα ααΌα ααααααααααα 2 αααααααΌαααααΎαααΆααααααΎαααΆααααααααααΆαααΎααα 0α αααααΆαααΆαααΆααααα ααΆαααΏααΆααα’αΆα ααααΌαααΆααααααα’αααααΆααΆααΆα’αααα αα αΆααααα αα½ααα·αααααΌαααΆααααα»αααΎααααααααααααα ααααα»αααααΆααα αΆαα·αααααααααΎαααΆααααααααΌαααΆααααααΆαααααααΆααα OOM α
αα ααΎααααααααααααα·αααΆαααΆαααααΆααααααΌα αααα αΆααΆαα½α vm.overcommit_memory ααααΎααΉα 2 α’αΆα ααΎαα‘αΎαα
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio ααΊβααΆβααΆαααβααβα’αααβα αα αΆαβαααβααααααβαααβαααααβααααααβαααβααααΌαβαααααβαα βααΆαα ααΆαα αΌααα ααΆαααΎαα‘αΎααα ααααααΆααααααα ααααααααααΆαααΆαααααααααααΆαα αΆααααΈ 0 ααα 100; ααααααΆαααΆααααα αααααααααα 5 α’αΆα ααΉαααααΆαααααα·αααααΆα α αΎαααΊααααααααα·αααΆααααααΆααα 10 ααΊααΆααααΆαααΎααα ααΎααααααααααΈαα»α ααΆαα αααΎαα α’αααα’αΆα αααααα’ααΆαα’αα»αααααααααΆααααααα·ααααα·ααΆααααααΉαααα’ααααααΆααααΎααΆααααααααααααααΆααΌα ααΆα αααααΉαααΆααααααΆααΈαα»α ααΉααα»αααααααααααααα αααα»αααααααΆααααααα
α’αααααααΌααααααααααα vm.dirty_background_bytes α’αΆαααααααΎααααΏαααααααΆαααααα’αααα
αα·αααΆαααααα "ααα’" αααααΆαααααΆαααΆααααααααΆααααΈαααααα αααααααΆααααΈαααΊα’αΆαααααααΎαααααααΉαα ααααααΆαααΆααααα ααΆαααααα vm.dirty_background_ratio αα 5 αα·α vm.dirty_background_bytes ααα 25% ααααααΏαααΈαααααΎα’αααααααΎαα‘αΎαααΌαααααΎαααΆααα ~25% αααα»αααααΈααΆαα αααΎαα
vm.dirty_ratio/dirty_bytes
ααΆααΌα ααααΆααΉα vm.dirty_background_ratio/dirty_background_bytesααΎααααααααΆααααααα‘αΎααα·αααααΌαααΆαα’αα»αααααα αααα»ααααααααααα αααααΆααΆαααααααα·ααΈα ααΌα αααα vm.dirty_ratio αα½ααααααααααΆα vm.dirty_background_ratio. αααααΆααΆααΆααααΎαααΆαααααααΆααααααα αΆααααααΎααα»αααα ααΎααααΈαααααΆαααΆααααααααΆαααααααα·ααΈααΆααααα’αΆα ααααΎαα ααΆαα α’αααα’αΆα αααααααΌαααΆααα»αααααΆαααΆααααΆααΆαααααΆααααΈαααα α’αΆαααααααΎααΆααααα»α I/O ααααααΆαα
αααααα
α’αααα’αΆα
ααααααααΆααααααααααααααααΎααααΈαααααα’ααααΎαααΆα ααα»ααααααΆααααααα’ααΉαααΆααα·α
αα½α
α αΎαα’αααααΉααα·αααΎαα’αααααααααααα
αααΎαααα ααΎαααααΌαααα
αα
αΆαααΆ αα·ααααααααααααααΎαααΆααα’ααα’αα»ααααα
αααααααααα·ααΈααααααααααααααααα αααααα·ααΈαα½αα
ααα½αααααΎαααΆααααααΎαααΆααα
αααααΎααααααααΌαααΆαααααααα½αα
ααα½α α αΎααααααα·ααΈαααααα·αααααΎαααΆαα α’αααααααΌαααααααααααααα»αααααααααΉαααααΌααααΆαααΆαααααααα
ααΆααααααααααΆααααααααααΆααααααα»αααΆαααΆααααααααΉααα»α αα·ααααααααααααα·ααΈααααα’ααα α αΎαα’αααααααααΌααα·α
αΆαααΆα’αααΈα₯αα·ααΆααααααααααααααααααα·ααααα·ααΆαααααααα
ααααααααααΌαα ααΆαβαααααβαα
ααΆααααααααβαααΆαααΆααααααβααΊαααβαα·αβααΆααααα½αβααΌα
βααΆαβαααααβαααΆαααΆααααααβααΌαααααΆαβαα·ααααααβαα ααΆβαα·ααΆαβααΆαβαααα»αβααΆαβααααΆαα
ααααα: www.habr.com