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 64429D1B191 for ; Sat, 26 Jun 2004 20:03:23 -0300 (ADT) 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 09316-03 for ; Sat, 26 Jun 2004 23:03:21 +0000 (GMT) Received: from mproxy.gmail.com (mproxy.gmail.com [216.239.56.251]) by svr1.postgresql.org (Postfix) with SMTP id E9523D1B19C for ; Sat, 26 Jun 2004 20:03:16 -0300 (ADT) Received: by mproxy.gmail.com with SMTP id w41so813539cwb for ; Sat, 26 Jun 2004 16:03:19 -0700 (PDT) Received: by 10.38.164.51 with SMTP id m51mr68807rne; Sat, 26 Jun 2004 16:03:19 -0700 (PDT) Message-ID: Date: Sat, 26 Jun 2004 16:03:19 -0700 From: Chris Cheston To: "Joshua D. Drake" Subject: Re: postgres 7.4 at 100% Cc: Doug McNaught , pgsql-performance@postgresql.org In-Reply-To: <40DD8425.3080108@commandprompt.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <40DBEBDF.3090605@archonet.com> <87llia78dj.fsf@asmodeus.mcnaught.org> <40DD8425.3080108@commandprompt.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/264 X-Sequence-Number: 7322 Hi all, I upped effective_cache to 16000 KB and I could only up the shared_buffers to 3000. Anything more and postgres would not start. Postmaster is still using lots of CPU. pg_stat_activity shows only query is happening at a time so the requests are probably queueing on this one thread. Is this the right way to go? Any other suggestions for me to figure out why Postmaster is using so much CPU? Thanks in advance, Chris numnet=# select * from pg_stat_activity; datid | datname | procpid | usesysid | usename | current_query | query_start -------+---------+---------+----------+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------- 17144 | numnet | 26120 | 103 | numnet | | 2004-06-26 18:01:24.02042-04 17144 | numnet | 26121 | 103 | numnet | | 2004-06-26 18:01:24.026025-04 17144 | numnet | 26122 | 103 | numnet | | 2004-06-26 18:01:24.030917-04 17144 | numnet | 26123 | 103 | numnet | | 2004-06-26 18:01:24.036266-04 17144 | numnet | 26124 | 103 | numnet | | 2004-06-26 18:01:24.041551-04 17144 | numnet | 26125 | 103 | numnet | | 2004-06-26 18:01:24.046449-04 17144 | numnet | 26126 | 103 | numnet | | 2004-06-26 18:01:24.051666-04 17144 | numnet | 26127 | 103 | numnet | | 2004-06-26 18:01:24.057398-04 17144 | numnet | 26128 | 103 | numnet | | 2004-06-26 18:01:24.06225-04 17144 | numnet | 26129 | 103 | numnet | SELECT id,name,number, systemid,pin,last,to,from,lastest,start,end,continue,type,status,duration FROM logs WHERE (((from= 'me') and (to= 'you')) and (serverip= '23.6.6.33 17144 | numnet | 26147 | 103 | numnet | | 2004-06-26 18:03:46.175789-04 (11 rows) ----- Original Message ----- From: Joshua D. Drake Date: Sat, 26 Jun 2004 07:11:49 -0700 Subject: Re: [PERFORM] postgres 7.4 at 100% To: Doug McNaught Cc: pgsql-performance@postgresql.org Hello, Not to mention upping your effective_cache. Doug McNaught wrote: Chris Cheston writes: shared_buffers = 40 # min 16, at least max_connections*2, 8KB each This is ridiculously low for any kind of production server. Try something like 5000-10000 for a start. -Doug ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL