X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6FFE0D1B4EC for ; Thu, 23 Oct 2003 21:14:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 34204-10 for ; Thu, 23 Oct 2003 18:14:12 -0300 (ADT) Received: from lorax.kcilink.com (lorax.kciLink.com [206.112.95.1]) by svr1.postgresql.org (Postfix) with ESMTP id 53333D1B4E4 for ; Thu, 23 Oct 2003 18:14:10 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id A0BDA3E2D for ; Thu, 23 Oct 2003 17:14:12 -0400 (EDT) Received: from lorax.kcilink.com ([127.0.0.1]) by localhost (lorax.kcilink.com [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 15355-02-2 for ; Thu, 23 Oct 2003 17:14:11 -0400 (EDT) Received: from lorax.kciLink.com (localhost [127.0.0.1]) by lorax.kcilink.com (Postfix) with ESMTP id CE3933E23 for ; Thu, 23 Oct 2003 17:14:11 -0400 (EDT) Received: (from news@localhost) by lorax.kciLink.com (8.12.8p1/8.12.8/Submit) id h9NLEBiE018696 for pgsql-performance@postgresql.org; Thu, 23 Oct 2003 17:14:11 -0400 (EDT) To: pgsql-performance@postgresql.org Path: not-for-mail From: Vivek Khera Newsgroups: ml.postgres.performance Subject: Re: My own performance/tuning q&a Date: Thu, 23 Oct 2003 17:14:11 -0400 Organization: Khera Communications, Inc., Rockville, MD Lines: 98 Message-ID: References: <6.0.0.22.0.20031023090320.0242d6c8@pop.hotpop.com> NNTP-Posting-Host: yertle.kcilink.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Trace: lorax.kcilink.com 1066943651 17547 216.194.193.105 (23 Oct 2003 21:14:11 GMT) X-Complaints-To: daemon@kciLink.com NNTP-Posting-Date: Thu, 23 Oct 2003 21:14:11 +0000 (UTC) User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Portable Code, berkeley-unix) Cancel-Lock: sha1:kszcR+LcJ5Z4iLiinjMNdw0Lss0= X-Virus-Scanned: by amavisd-new at kciLink.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/610 X-Sequence-Number: 4358 >>>>> "AL" == Allen Landsidel writes: AL> I recently built a rather powerful machine to be used in a heavily AL> accessed database.. the machine is a dual AthlonMP 2800+, 2GB of AL> PC2100 ECC, and a 4x18GB RAID-0 using 15k rpm fujitsu MAS drives on a AL> 4ch u160 ICP-Vortex card with 256MB of cache. The only recommendation I'd make is to switch from RAID0 to RAID10, unless you can afford the downtime (and loss of data) when one of your drives takes a vacation. Also, is your RAID card cache battery backed up? If no, then you lose the ability to use write-back and this costs *dearly* in performance. AL> The box runs FreeBSD, tracking RELENG_4 (-STABLE) and PostGreSQL 7.3.4 AL> from ports (7.3.4_1) An excellent choice. :-) [[ ... ]] AL> I run a 'vacuum analyze verbose' on the database in question every AL> hour, and a reindex on every table in the database every six hours, AL> 'vacuum full' is run manually as required perhaps anywhere from once a AL> week to once a month. I realize the analyze may not be running often AL> enough and the reindex more often than need be, but I don't think AL> these are adversely affecting performance very much; degredation over AL> time does not appear to be an issue. Personally, I don't think you need to reindex that much. And I don't think you need to vacuum full *ever* if you vacuum often like you do. Perhaps reducing the vacuum frequency may let you reach a steady state of disk usage? Depending on how many concurrent actions you process, perhaps you can use a temporary table for each, so you don't have to delete many rows when you're done. On my busy tables, I vacuum every 6 hours. The vacuum analyze is run on the entire DB nightly. I reindex every month or so my most often updated tables that show index bloat. Watch for bloat by monitoring the size of your indexes: SELECT relname,relpages FROM pg_class WHERE relname LIKE 'some_table%' ORDER BY relname; AL> Related kernel configuration options: AL> ... AL> cpu I686_CPU AL> maxusers 256 let the system autoconfigure maxusers... AL> ... AL> options MAXDSIZ="(1024UL*1024*1024)" AL> options MAXSSIZ="(512UL*1024*1024)" AL> options DFLDSIZ="(512UL*1024*1024)" above are ok at defaults. AL> options SHMMAXPGS=65536 perhaps bump this and increase your shared buffers. I find that if you do lots of writes, having a few more shared buffers helps. AL> options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)" you don't need to explicitly set this... it is automatically set based on the above setting. AL> relevant postgresql.conf options: AL> max_fsm_pages = 2000000 this may be overkill. I currently run with 1000000 AL> effective_cache_size = 49152 # 384MB, this could probably be higher the current recommendation for freebsd is to set this to: `sysctl -n vfs.hibufspace` / 8192 where 8192 is the blocksize used by postgres. You may also want to increase the max buffer space used by FreeBSD, which apparently is capped at 200M (I think) by dafault. I'll have to look up how to bump that, as most likely you have plenty of RAM sitting around unused. What does "top" say about that when you're busy? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/