Running large DB's on FreeBSD

[Nguyen Tam Chinh]

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           |