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
Everything else is default. We run vacuum manually rather than using
autovacuum for historical reasons.
With upcoming 8.2 release I plan to experiment with higher
shared_buffers settings. But for best help, pose your load details
and hardware details and query info to the pgsql-performance list.
Very smart folk there to help.
