Running large DB's on FreeBSD

[Vivek Khera]

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.