Date: Sat, 11 Nov 2006 02:35:23 +0300 (MSK) From: Nguyen Tam Chinh <chinhngt@sectorb.msk.ru> To: Vivek Khera <vivek@khera.org> Cc: stable@freebsd.org Subject: Re: Running large DB's on FreeBSD Message-ID: <20061111022925.Q953@it.hackers> In-Reply-To: <6611C68B-1492-48A7-9425-3E23271CC940@khera.org> References: <453D49D2.1010705@rogers.com> <6DBE5906-CD84-44C5-AF40-FFCC78C7561E@khera.org> <20061024202408.U923@it.hackers> <6611C68B-1492-48A7-9425-3E23271CC940@khera.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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 |
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20061111022925.Q953>