Date: Mon, 25 Aug 2003 20:05:28 -0700 From: Sean Chittenden <sean@chittenden.org> To: Paul Pathiakis <paul@pathiakis.com> Cc: freebsd-performance@freebsd.org Subject: Re: Tuning Postgresql on FreeBSD 5.1 Message-ID: <20030826030527.GB1514@perrin.nxad.com> In-Reply-To: <200308250929.32143.paul@pathiakis.com> References: <200308250929.32143.paul@pathiakis.com>
next in thread | previous in thread | raw e-mail | index | archive | help
> I now have a bigger problem... the machine is generating some large > reports and the processes start and instantly drop off to no > utilization... they sit there and hang... seemingly resource > starved. Prove it. Uses iostat or vmstat and come with proof that it is resource starved. > I've enclosed the postgresql.conf file, the systctl.conf file, > loader.conf and the Kernel memory parameters. Looking through these aren't going to help until you figure out why things are "stalling." > Again, the machine is a twin 2.8 Xeon HTT machine. HTT is turned on > and the machine sees 4 cpus. Have you played with toggling the sysctl machdep.cpu_idle_hlt? Turn off the use of your memory disk for PG's temp files. If you tune your PostgreSQL settings correctly, your temp files need to be disk backed, not RAM backed. > I've got a bunch of people saying Linux just runs faster and the DB > group is using a Linux config file on the FreeBSD machine. (Don't > get me going) It is my belief that a BSD DB is going to run faster > on it's platform of choice for development (FreeBSD) than another > OS. *grumps* I hate this call to arms bit about people clammering for Linux if things are slow on BSD. BSD is, on average, the preferred OS of PostgreSQL developers and DBAs when reliability and speed are issues (if for no other reason than UFS+softupdates). Linux has problems with memory over commit situations and file system integrity while still maintaining reasonable file system performance (ext2 is faster than ext3 by a wide margin, but ext2 is _not_ a reliable FS). As for your postgresql.conf, please read: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html >From your postgresql.conf: > shared_buffers = 48000 # min max_connections*2 or 16, 8KB each WHOA! This is too high by a factor of about 10. You probably want a shared buffers set to 4096. > #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes > #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes You probably want to increase these both by a factor of about 10. > sort_mem = 32768 # min 64, size in KB This also seems high, divide by 8 and you're at a more reasonable level. > #fsync = true Change this to false. Let us know if your machine was swapping, I bet it was. -sc -- Sean Chittenden
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20030826030527.GB1514>