I'm not getting something about the best way to set up a server using
PostgreSQL as a backend for a busy web server running drupal.
The postgresql performance tuning folks
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
say that in a server with more that 1GB of ram
"a reasonable starting value for shared_buffers is 1/4 of the memory in your
system."
Okay, this server has 4GB of ram. pgtune suggests the following values for
predominately web based usage:
maintenance_work_mem = 240MB # pgtune wizard 2010-01-27
checkpoint_completion_target = 0.7 # pgtune wizard 2010-01-27
effective_cache_size = 2816MB # pgtune wizard 2010-01-27
work_mem = 18MB # pgtune wizard 2010-01-27
wal_buffers = 4MB # pgtune wizard 2010-01-27
checkpoint_segments = 8 # pgtune wizard 2010-01-27
shared_buffers = 960MB # pgtune wizard 2010-01-27
max_connections = 200 # pgtune wizard 2010-01-27
As the README.OpenBSD installed with the port suggests, postgresql will not
even start with the default values.
"The default sizes in the GENERIC kernel for SysV semaphores
are not really large enough for a database with more than a
handful of connections. A server running such a database should
have at least the following in /etc/sysctl.conf:
kern.seminfo.semmni=256
kern.seminfo.semmns=2048
To serve a large number of connections (>250), you may also need
to increase the maximum shared memory segment size, on the i386
try:
kern.shminfo.shmmax=50331648 # this is 48MB.
# default on i386 is 32MB
# other archs will vary
These numbers should be tuned depending on system use. You will also
need to tune the values in the postgresql.conf file to increase the
number of connections to the backend."
Here is where I'm not doing something right. With my shared_buffers at 960MB,
I need to adjust kern.shminfo.shmmax to 1GB (1073741824) to get postgres to
start. I thought I'd need to also adjust kern.shminfo.shmmall value as well
but that seems to change automatically whenever I adjust kern.shminfo.shmmax.
$ sysctl -a | grep kern.s
kern.securelevel=1
kern.saved_ids=1
kern.somaxconn=128
kern.sominconn=80
kern.sysvmsg=1
kern.sysvsem=1
kern.sysvshm=1
kern.stackgap_random=262144
kern.splassert=1
kern.seminfo.semmni=256
kern.seminfo.semmns=2048
kern.seminfo.semmnu=30
kern.seminfo.semmsl=60
kern.seminfo.semopm=100
kern.seminfo.semume=10
kern.seminfo.semusz=100
kern.seminfo.semvmx=32767
kern.seminfo.semaem=16384
kern.shminfo.shmmax=1073741824
kern.shminfo.shmmin=1
kern.shminfo.shmmni=128
kern.shminfo.shmseg=128
kern.shminfo.shmall=262144
At these values postgres will start and top shows a large amount of memory
still free:
Memory: Real: 55M/465M act/tot Free: 3433M Swap: 0K/8197M used/tot
Running a simple select only pgbench test against it will fail with an out of
memory error as it tries to vacuum --analyze the newly created database with
7500000 tuples.
When I run this command and have top refreshing every second, I never see the
free memory drop below 3400M, so I'm not sure what memory we are running out
of. systat -i shows similar amounts of memory yet free.
I have searched (and searched) so I wonder if I'm running into the i386 1GB
limit I see referenced, as in the thread today about fsck on larger partitions.
I've already tried running the amd64 kernel on this server and it won't even
boot. Do I need to find a better use for this box and go find a real Opteron
to run PostgreSQL on?
Jeff Ross
OpenBSD 4.6-current (GENERIC.MP) #25: Mon Jan 25 21:15:48 MST 2010
r...@varley.openvistas.net:/usr/src/sys/arch/i386/compile/GENERIC.MP
cpu0: Intel(R) Xeon(TM) CPU 2.40GHz ("GenuineIntel" 686-class) 2.41 GHz
cpu0:
FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,SBF,CNXT-ID,xTPR
real mem = 4160253952 (3967MB)
avail mem = 4053823488 (3866MB)
mainbus0 at root
bios0 at mainbus0: AT/286+ BIOS, date 09/18/03, BIOS32 rev. 0 @ 0xf0010,
SMBIOS rev. 2.3 @ 0xf84b0 (39 entries)
bios0: vendor American Megatrends Inc. version "080009" date 09/18/2003
bios0: Supermicro X5DPA
acpi0 at bios0: rev 2
acpi0: tables DSDT FACP APIC OEMB
acpi0: wakeup devices PS2K(S1) PS2M(S1) SMBS(S1) AUDI(S1) MODM(S1) USB0(S1)
USB1(S1) USB2(S1) P0P1(S1) GLN1(S1) GLN2(S1) BT64(S1) PWRB(S1) SLPB(S1)
acpitimer0 at acpi0: 3579545 Hz, 24 bits
acpimadt0 at acpi0 addr 0xfee00000: PC-AT compat
cpu0 at mainbus0: apid 0 (boot processor)
cpu0: apic clock running at 133MHz
cpu1 at mainbus0: apid 6 (application processor)
cpu1: Intel(R) Xeon(TM) CPU 2.40GHz ("GenuineIntel" 686-class) 2.41 GHz
cpu1:
FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,SBF,CNXT-ID,xTPR
cpu2 at mainbus0: apid 1 (application processor)
cpu2: Intel(R) Xeon(TM) CPU 2.40GHz ("GenuineIntel" 686-class) 2.41 GHz
cpu2:
FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,SBF,CNXT-ID,xTPR
cpu3 at mainbus0: apid 7 (application processor)
cpu3: Intel(R) Xeon(TM) CPU 2.40GHz ("GenuineIntel" 686-class) 2.41 GHz
cpu3:
FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,CFLUSH,DS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,SBF,CNXT-ID,xTPR
ioapic0 at mainbus0: apid 8 pa 0xfec00000, version 20, 24 pins
ioapic1 at mainbus0: apid 9 pa 0xfec80000, version 20, 24 pins
ioapic2 at mainbus0: apid 10 pa 0xfec80400, version 20, 24 pins
acpiprt0 at acpi0: bus 0 (PCI0)
acpiprt1 at acpi0: bus 1 (P0P1)
acpiprt2 at acpi0: bus 3 (P2P3)
acpiprt3 at acpi0: bus 4 (BT64)
acpicpu0 at acpi0
acpicpu1 at acpi0
acpicpu2 at acpi0
acpicpu3 at acpi0
acpibtn0 at acpi0: PWRB
acpibtn1 at acpi0: SLPB
bios0: ROM list: 0xc0000/0x8000 0xc8000/0x1000 0xc9000/0x1000 0xca000/0x8e00
0xd3000/0x2600
pci0 at mainbus0 bus 0: configuration mode 1 (bios)
pchb0 at pci0 dev 0 function 0 "Intel E7501 Host" rev 0x01
ppb0 at pci0 dev 2 function 0 "Intel E7500 PCI" rev 0x01
pci1 at ppb0 bus 2
"Intel 82870P2 IOxAPIC" rev 0x04 at pci1 dev 28 function 0 not configured
ppb1 at pci1 dev 29 function 0 "Intel 82870P2 PCIX-PCIX" rev 0x04
pci2 at ppb1 bus 4
ami0 at pci2 dev 2 function 0 "Symbios Logic MegaRAID" rev 0x01: apic 10 int 4
(irq 10)
ami0: LSI 520, 64b/lhc, FW 1L47, BIOS vG121, 64MB RAM
ami0: 1 channels, 0 FC loops, 2 logical drives
scsibus0 at ami0: 40 targets
sd0 at scsibus0 targ 0 lun 0: <AMI, Host drive #00, > SCSI2 0/direct fixed
sd0: 140270MB, 512 bytes/sec, 287272960 sec total
sd1 at scsibus0 targ 1 lun 0: <AMI, Host drive #01, > SCSI2 0/direct fixed
sd1: 70135MB, 512 bytes/sec, 143636480 sec total
scsibus1 at ami0: 16 targets
ahd0 at pci2 dev 3 function 0 "Adaptec AIC-7902B U320" rev 0x10: apic 10 int 2
(irq 10)
ahd0: aic7902, U320 Wide Channel A, PCI 33 or 66MHz, 512 SCBs
scsibus2 at ahd0: 16 targets, initiator 7
sd2 at scsibus2 targ 0 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd2: 35046MB, 512 bytes/sec, 71775284 sec total
sd3 at scsibus2 targ 1 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd3: 35046MB, 512 bytes/sec, 71775284 sec total
sd4 at scsibus2 targ 2 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd4: 35046MB, 512 bytes/sec, 71775284 sec total
sd5 at scsibus2 targ 3 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd5: 35046MB, 512 bytes/sec, 71775284 sec total
sd6 at scsibus2 targ 4 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd6: 35046MB, 512 bytes/sec, 71775284 sec total
sd7 at scsibus2 targ 5 lun 0: <FUJITSU, MAP3367NC, 0108> SCSI3 0/direct fixed
sd7: 35046MB, 512 bytes/sec, 71775284 sec total
ses0 at scsibus2 targ 15 lun 0: <ADAPTEC, AltEvo U320, 0028> SCSI3 3/processor
removable
ahd1 at pci2 dev 3 function 1 "Adaptec AIC-7902B U320" rev 0x10: apic 10 int 3
(irq 10)
ahd1: aic7902, U320 Wide Channel B, PCI 33 or 66MHz, 512 SCBs
scsibus3 at ahd1: 16 targets, initiator 7
"Intel 82870P2 IOxAPIC" rev 0x04 at pci1 dev 30 function 0 not configured
ppb2 at pci1 dev 31 function 0 "Intel 82870P2 PCIX-PCIX" rev 0x04
pci3 at ppb2 bus 3
em0 at pci3 dev 1 function 0 "Intel PRO/1000MT (82541GI)" rev 0x00: apic 9 int
4 (irq 10), address 00:30:48:76:07:dc
em1 at pci3 dev 2 function 0 "Intel PRO/1000MT (82541GI)" rev 0x00: apic 9 int
5 (irq 10), address 00:30:48:76:07:dd
uhci0 at pci0 dev 29 function 0 "Intel 82801CA/CAM USB" rev 0x02: apic 8 int
16 (irq 11)
uhci1 at pci0 dev 29 function 1 "Intel 82801CA/CAM USB" rev 0x02: apic 8 int
19 (irq 5)
uhci2 at pci0 dev 29 function 2 "Intel 82801CA/CAM USB" rev 0x02: apic 8 int
18 (irq 9)
ppb3 at pci0 dev 30 function 0 "Intel 82801BA Hub-to-PCI" rev 0x42
pci4 at ppb3 bus 1
vga1 at pci4 dev 4 function 0 "ATI Rage XL" rev 0x27
wsdisplay0 at vga1 mux 1: console (80x25, vt100 emulation)
wsdisplay0: screen 1-5 added (80x25, vt100 emulation)
ichpcib0 at pci0 dev 31 function 0 "Intel 82801CA LPC" rev 0x02
pciide0 at pci0 dev 31 function 1 "Intel 82801CA IDE" rev 0x02: DMA, channel 0
configured to compatibility, channel 1 configured to compatibility
atapiscsi0 at pciide0 channel 0 drive 1
scsibus4 at atapiscsi0: 2 targets
cd0 at scsibus4 targ 0 lun 0: <MATSHITA, DVD-ROM SR-8178, PZ16> ATAPI 5/cdrom
removable
cd0(pciide0:0:1): using PIO mode 4, Ultra-DMA mode 2
pciide0: channel 1 disabled (no drives)
ichiic0 at pci0 dev 31 function 3 "Intel 82801CA/CAM SMBus" rev 0x02: apic 8
int 17 (irq 0)
iic0 at ichiic0
admtemp0 at iic0 addr 0x18: Xeon
admtemp1 at iic0 addr 0x1a: Xeon
lm1 at iic0 addr 0x2d: W83627HF
spdmem0 at iic0 addr 0x51: 1GB DDR SDRAM registered ECC PC2300CL2.5
spdmem1 at iic0 addr 0x52: 1GB DDR SDRAM registered ECC PC2300CL2.5
spdmem2 at iic0 addr 0x55: 1GB DDR SDRAM registered ECC PC2300CL2.5
spdmem3 at iic0 addr 0x56: 1GB DDR SDRAM registered ECC PC2300CL2.5
usb0 at uhci0: USB revision 1.0
uhub0 at usb0 "Intel UHCI root hub" rev 1.00/1.00 addr 1
usb1 at uhci1: USB revision 1.0
uhub1 at usb1 "Intel UHCI root hub" rev 1.00/1.00 addr 1
usb2 at uhci2: USB revision 1.0
uhub2 at usb2 "Intel UHCI root hub" rev 1.00/1.00 addr 1
isa0 at ichpcib0
isadma0 at isa0
com0 at isa0 port 0x3f8/8 irq 4: ns16550a, 16 byte fifo
com0: console
com1 at isa0 port 0x2f8/8 irq 3: ns16550a, 16 byte fifo
pckbc0 at isa0 port 0x60/5
pcppi0 at isa0 port 0x61
midi0 at pcppi0: <PC speaker>
spkr0 at pcppi0
lpt0 at isa0 port 0x378/4 irq 7
wbsio0 at isa0 port 0x2e/2: W83627HF rev 0x17
lm2 at wbsio0 port 0x290/8: W83627HF
lm1 detached
npx0 at isa0 port 0xf0/16: reported by CPUID; using exception 16
fdc0 at isa0 port 0x3f0/6 irq 6 drq 2
fd0 at fdc0 drive 0: 1.44MB 80 cyl, 2 head, 18 sec
mtrr: Pentium Pro MTRR support
vscsi0 at root
scsibus5 at vscsi0: 256 targets
softraid0 at root
ahd0: target 0 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
ahd0: target 1 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
ahd0: target 2 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
ahd0: target 3 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
ahd0: target 4 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
ahd0: target 5 synchronous with period = 0x8, offset = 0x7f(DT|IU|RTI|QAS)
root on sd1a swap on sd1b dump on sd1b
