X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 67F8F3A3E55 for ; Tue, 4 Jan 2005 14:38:37 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 14366-09 for ; Tue, 4 Jan 2005 14:38:07 +0000 (GMT) Received: from mail.deg.cc (mail.deg.cc [64.139.134.201]) by svr1.postgresql.org (Postfix) with ESMTP id 147F63A3E15 for ; Tue, 4 Jan 2005 14:38:08 +0000 (GMT) Received: from deg.cc (kyle.deg.cc [198.70.16.205]) by mail.deg.cc (Postfix) with ESMTP id 984F156274; Tue, 4 Jan 2005 09:38:03 -0500 (EST) Message-ID: <41DAAA4C.2070201@deg.cc> Date: Tue, 04 Jan 2005 09:38:04 -0500 From: Pallav Kalva User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.4.2) Gecko/20040308 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pg@fastcrypt.com Cc: Postgres Performance Subject: Re: Very Bad Performance. References: <41D9C4E3.3050001@deg.cc> <41D9D8C1.4090906@fastcrypt.com> In-Reply-To: <41D9D8C1.4090906@fastcrypt.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/39 X-Sequence-Number: 9729 Dave Cramer wrote: > Well, it's not quite that simple > > the rule of thumb is 6-10% of available memory before postgres loads > is allocated to shared_buffers. > then effective cache is set to the SUM of shared_buffers + kernel buffers > > Then you have to look at individual slow queries to determine why they > are slow, fortunately you are running 7.4 so you can set > log_min_duration to some number like 1000ms and then > try to analyze why those queries are slow. I had that already set on my database , and when i look at the log for all the problem queries, most of the queries are slow from one of the table. when i look at the stats on that table they are really wrong, not sure how to fix them. i run vacuumdb and analyze daily. > > > Also hyperthreading may not be helping you.. does it do any harm to the system if it is hyperthreaded ? > > > Dave > > Pallav Kalva wrote: > >> Hi , >> >> I am experiencing a very bad performance on my production >> database lately , all my queries are slowing down. Our application is >> a webbased system with lot of selects and updates. I am running >> "vacuumdb" daily on all the databases, are the below postgres >> configuration parameters are set properly ? can anyone take a look. >> Let me know if you need anymore information. >> >> >> Postgres Version: 7.4 >> Operating System: Linux Red Hat 9 >> Cpus: 2 Hyperthreaded >> RAM: 4 gb >> Postgres Settings: >> max_fsm_pages | 20000 >> max_fsm_relations | 1000 >> shared_buffers | 65536 >> sort_mem | 16384 >> vacuum_mem | 32768 >> wal_buffers | 64 >> effective_cache_size | 393216 >> >> Thanks! >> Pallav >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Don't 'kill -9' the postmaster >> >> >