Re: Tuning Postgresql on FreeBSD 5.1

• Previous message: Paul Pathiakis: "Tuning Postgresql on FreeBSD 5.1"
• In reply to: Paul Pathiakis: "Tuning Postgresql on FreeBSD 5.1"
• Next in thread: Rod Taylor: "Re: Tuning Postgresql on FreeBSD 5.1"
• Reply: Rod Taylor: "Re: Tuning Postgresql on FreeBSD 5.1"
• Reply: Maciej Freudenheim: "Re: Tuning Postgresql on FreeBSD 5.1"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: Mon, 25 Aug 2003 20:05:28 -0700
To: Paul Pathiakis <paul@pathiakis.com>

> 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
_______________________________________________
freebsd-performance@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-performance
To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org"

• Previous message: Paul Pathiakis: "Tuning Postgresql on FreeBSD 5.1"
• In reply to: Paul Pathiakis: "Tuning Postgresql on FreeBSD 5.1"
• Next in thread: Rod Taylor: "Re: Tuning Postgresql on FreeBSD 5.1"
• Reply: Rod Taylor: "Re: Tuning Postgresql on FreeBSD 5.1"
• Reply: Maciej Freudenheim: "Re: Tuning Postgresql on FreeBSD 5.1"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]