Running large DB's on FreeBSD
Nguyen Tam Chinh
chinhngt at sectorb.msk.ru
Fri Nov 10 23:35:29 UTC 2006
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
> 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
> vacuum_cost_delay = 25 # 0-1000 milliseconds
> checkpoint_segments = 256
> checkpoint_timeout = 900
> effective_cache_size = 27462 # `sysctl -n vfs.hibufspace` / 8192
> 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 |
More information about the freebsd-stable