New Opteron box, dedicated to PostgreSQL

[Oliver Fromme]

Axel Rau wrote:
 > while configuring my 1st PostgreSQL box with dual Opterons (2212) on  
 > FreeBSD, I have some questions:
 > [...]
 > 3. What are the recommendations for tuning I/O)?
 >     - setting sysctl vfs.read_max to 16 or 32
 >     - rebuilding the relevant filesystem with 32K blocks and 4K frags
 >     Are these reliable?

Personally I would leave the FS parameters at the defaults.
The bsize/fsize defaults of 16k/2k are actually quite well-
suited for PostgreSQL, as far as I can tell.  (If there are
people who have made different experience, I'd like to hear
about it.)

If you expect that only few, large tables will be used,
then reducing the inode density (i.e. increasing the value
of the -i option to newfs) might be a good idea.  Note that
PostgreSQL stores each object (table, index etc.) in its
own file.

Be sure to disable background fsck via /etc/rc.conf.  I've
seen it breaking file systems under certain circumstances.
Instead, you might want want to give PWD's new gjournal
code a try (it's in -current, but I think there's a port
to RELENG_6, too).  On a related note, setting up gmirror
for RAID-1 has worked very well for me, including postgres
machines (the balance algorithm "load" seems to work best
with pgsql databases).

Of course, the usual PostgreSQL tuning advices apply, e.g.
increase the SysV IPC resources (i.e. kernel parameters
for semaphores and shared memory), optimize postgresql.conf
etc.  There's currently a known bottleneck regarding SysV
IPC on FreeBSD, if a lot of processes are waiting on the
same semaphore, which can affect PostgreSQL under very high
load.  I don't know what the status of that is, but I think
nobody is currently working on a fix.

Best regards
   Oliver

-- 
Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing b. M.
Handelsregister: Registergericht Muenchen, HRA 74606,  Geschäftsfuehrung:
secnetix Verwaltungsgesellsch. mbH, Handelsregister: Registergericht Mün-
chen, HRB 125758,  Geschäftsführer: Maik Bachmann, Olaf Erb, Ralf Gebhart

FreeBSD-Dienstleistungen, -Produkte und mehr:  http://www.secnetix.de/bsd

PI:
int f[9814],b,c=9814,g,i;long a=1e4,d,e,h;
main(){for(;b=c,c-=14;i=printf("%04d",e+d/a),e=d%a)
while(g=--b*2)d=h*b+a*(i?f[b]:a/5),h=d/--g,f[b]=d%g;}