Tuning for PostGreSQL Database

[Sean Chittenden]

> > > 	Are there optimal parameters for sizing on the disk
> > > throughput?
> > 
> > Increase your file system cache.
>  
> How? I've been wondering if there's a way to do this, and all the
> settings I've played with seem to have no effect; it just sits at
> 199BUF according to top.

You might want to get in the habit of using sysctl for getting that
kind of info.  `sysctl -d vfs.bufspace`

#!/bin/sh
echo "Max Buffer space == $((`sysctl -n vfs.maxbufspace` / 1024 / 1024))MB"

The sysctl's that are going to be of most interest (in terms of
monitoring) are:

sysctl -a| grep bufspace

However, none of those will let you adjust your buffer size.  To do
that, you want to change kern.nbuf in /boot/loader.conf (*grumble*
Undocumented loader option).

     kern.maxbcache
                   Limits the amount of KVM reserved for use by the buffer
                   cache, specified in bytes.  The default maximum is 200MB.
                   This parameter is used to prevent the buffer cache from
                   eating too much KVM in large-memory machine configurations.
                   Only mess around with this parameter if you need to greatly
                   extend the KVM reservation for other resources such as the
                   swap zone or NMBCLUSTERS.  Note that the NBUF parameter
                   will override this limit.  Modifies VM_BCACHE_SIZE_MAX.


By default, the number of NBUF's on a system is auto sized and takes
up to kern.maxbcache.  There was a post by a guy to the performance@
list where he was trying to optimize a 4GB machine for squid and he'd
set it kern.nbuf to 16384 with 512 KVA_PAGES.  *shrug* YMMV with this
guy's settings, but:

http://lists.freebsd.org/pipermail/freebsd-performance/2003-June/000262.html

This post in the developers handbook should also be of interest to
most people playing with the NBUF setting:

http://www.freebsd.org/doc/en_US.ISO8859-1/books/developers-handbook/x8987.html

I've attached an untested patch that lets you see what your kern.nbufs
are via a sysctl.  At the moment, determining what kern.nbuf is set to
is a bit ellusive.  If someone with src foo can bless this patch, I'll
commit it.

> Windows: "Where do you want to go today?"
> Linux: "Where do you want to go tomorrow?"
> FreeBSD: "Are you guys coming, or what?"

Cool signature.

-sc

-- 
Sean Chittenden
-------------- next part --------------
Index: boot/common/help.common
===================================================================
RCS file: /home/ncvs/src/sys/boot/common/help.common,v
retrieving revision 1.25
diff -u -r1.25 help.common
--- boot/common/help.common	5 May 2003 07:33:12 -0000	1.25
+++ boot/common/help.common	20 Jul 2003 20:38:49 -0000
@@ -244,6 +244,11 @@
 		Set the number of sendfile buffers to be allocated.  This
 		overrides the value determined when the kernel was compiled.
 
+	set kern.nbuf=<value>			NBUF
+
+		Sets the amount of KVA the kernel can use to map filesystem
+		buffers for I/O.
+
 	set kern.vm.kmem.size=<value>		VM_KMEM_SIZE
 
 		Sets the size of kernel memory (bytes).  This overrides
Index: boot/common/loader.8
===================================================================
RCS file: /home/ncvs/src/sys/boot/common/loader.8,v
retrieving revision 1.57
diff -u -r1.57 loader.8
--- boot/common/loader.8	29 Jun 2003 20:57:55 -0000	1.57
+++ boot/common/loader.8	20 Jul 2003 20:51:31 -0000
@@ -450,7 +450,7 @@
 .Xr sendfile 2
 buffers to be allocated.
 Overrides
-.Dv NSFBUFS .
+.Va NSFBUFS .
 .It Va kern.vm.kmem.size
 Sets the size of kernel memory (bytes).
 This overrides the value determined when the kernel was compiled.
@@ -491,6 +491,13 @@
 the NBUF parameter will override this limit.
 Modifies
 .Va VM_BCACHE_SIZE_MAX .
+.It Va kern.nbuf
+Sets the amount of KVA the system can use to map filesystem
+buffers for I/O.  This parameter is normally automatically
+tuned by the kernel at startup and it is recommended that
+this parameter be unused unless the administrator knows
+exactly what adjusting this value influences.  Overrides
+.Va NBUF .
 .It Va machdep.disable_mtrrs
 Disable the use of i686 MTRRs (x86 only).
 .It Va net.inet.tcp.tcbhashsize
Index: kern/kern_mib.c
===================================================================
RCS file: /home/ncvs/src/sys/kern/kern_mib.c,v
retrieving revision 1.68
diff -u -r1.68 kern_mib.c
--- kern/kern_mib.c	11 Jun 2003 00:56:56 -0000	1.68
+++ kern/kern_mib.c	20 Jul 2003 20:37:11 -0000
@@ -127,6 +127,9 @@
 SYSCTL_INT(_kern, KERN_JOB_CONTROL, job_control, CTLFLAG_RD,
     0, 1, "Whether job control is available");
 
+SYSCTL_INT(_kern, KERN_NBUF, nbuf, CTLFLAG_RD,
+    &nbuf, 0, "Amount of KVA used to map filesystem buffers for I/O");
+
 #ifdef _POSIX_SAVED_IDS
 SYSCTL_INT(_kern, KERN_SAVED_IDS, saved_ids, CTLFLAG_RD,
     0, 1, "Whether saved set-group/user ID is available");
Index: sys/sysctl.h
===================================================================
RCS file: /home/ncvs/src/sys/sys/sysctl.h,v
retrieving revision 1.118
diff -u -r1.118 sysctl.h
--- sys/sysctl.h	12 Jul 2003 02:00:16 -0000	1.118
+++ sys/sysctl.h	20 Jul 2003 20:26:20 -0000
@@ -355,6 +355,7 @@
 #define	KERN_LOGSIGEXIT		34	/* int: do we log sigexit procs? */
 #define	KERN_IOV_MAX		35	/* int: value of UIO_MAXIOV */
 #define KERN_MAXID		36	/* number of valid kern ids */
+#define KERN_NBUFS		37	/* number of kernel buffers */
 
 #define CTL_KERN_NAMES { \
 	{ 0, 0 }, \
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 202 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-database/attachments/20030720/1b8e0c0d/attachment.bin