Date: Fri, 16 Mar 2007 09:08:15 +0100 (CET) From: Oliver Fromme <olli@lurza.secnetix.de> To: freebsd-database@FreeBSD.ORG, Axel.Rau@chaos1.de, freebsd-amd64@FreeBSD.ORG Subject: Re: New Opteron box, dedicated to PostgreSQL Message-ID: <200703160808.l2G88FBN030412@lurza.secnetix.de> In-Reply-To: <freebsd-database.8CDBEAF9-0945-4CD2-89C4-58AA63850ECC@Chaos1.DE>
next in thread | previous in thread | raw e-mail | index | archive | help
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;}
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200703160808.l2G88FBN030412>