On Tue, 24 Oct 2006, Vivek Khera wrote:
On Oct 24, 2006, at 12:27 PM, Nguyen Tam Chinh wrote:
The size of your DB is not all that large. There are people running
terabyte DB's under postgres. Our big DB is around 60Gb with hundreds of
millions of rows spread across dozens of tables which are regularly joined
with each other for reports. It is pounded on 24x7 with lots and lots of
inserts, updates, and selects going on all the time.
Could you share with us your servers' hardware specifics and configuration
(tuning) of PostgreSQL?
This would help many in making decision.
My current favorites are the SunFire X4100 from Sun with an Adaptec 2230SLP
dual channel U320 RAID card and a 14+ disk array. These are incredibly
stable. The disk arrays I have right now are from Dell, and I would not
recommend them. I don't think they're totally U320 compliant as some drives
occasionally come up at U160 speed. The Adaptec card is the *only* dual
channel U320 SCSI card availble in low-profile size; unfortunately LSI
doesn't make a low-profile version of the 320-2X card...
I have one box with 4Gb which is good for our smaller databases, and one
which we are upgrading from 4Gb to 8Gb next week due to the high load it has.
I use 1 disk from each SCSI channel to make a mirrored RAID volume for boot +
OS + postgres transaction log, and the remaining disks in RAID10 with the
disks on each mirror pair coming from opposite SCSI channels.
For the pg configuration, I use this on a 4Gb box:
max_connections = 100
shared_buffers = 70000 # min 16 or max_connections*2, 8KB
each
work_mem = 262144 # min 64, size in KB
maintenance_work_mem = 524288 # min 1024, size in KB
max_fsm_pages = 1800000 # min max_fsm_relations*16, 6 bytes
each
vacuum_cost_delay = 25 # 0-1000 milliseconds
checkpoint_segments = 256
checkpoint_timeout = 900
effective_cache_size = 27462 # `sysctl -n vfs.hibufspace` / 8192
(BLKSZ)
random_page_cost = 2
log_min_error_statement = error
Thank you very much. And how did you set the semaphore's parameters? Do
you have any trick or experience? I just think it's just weird to
inceremently increase ipc.shm* and ipc.sem* to get the right values. The
documentation of PostGreSQL gives us some examples but without explanation
how they found those values.
-----
With best regards, | The Power to Serve
Nguyen Tam Chinh | http://www.FreeBSD.org
Loc: sp.cs.msu.su |
_______________________________________________
[email protected] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[EMAIL PROTECTED]"
