X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2D505D1D643 for ; Mon, 24 Nov 2003 21:56:51 +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 49023-06 for ; Mon, 24 Nov 2003 17:56:21 -0400 (AST) Received: from smtp.noos.fr (nan-smtp-16.noos.net [212.198.2.124]) by svr1.postgresql.org (Postfix) with ESMTP id AD30AD1B520 for ; Mon, 24 Nov 2003 17:56:18 -0400 (AST) Received: (qmail 13334 invoked by uid 0); 24 Nov 2003 21:56:20 -0000 Received: from unknown (HELO bigfoot.com) ([212.198.40.101]) (envelope-sender ) by 212.198.2.124 (qmail-ldap-1.03) with SMTP for ; 24 Nov 2003 21:56:20 -0000 Message-ID: <3FC27071.9000001@bigfoot.com> Date: Mon, 24 Nov 2003 21:56:17 +0100 From: Gaetano Mendola User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.5) Gecko/20031013 Thunderbird/0.3 X-Accept-Language: en-us, en MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Cc: Torsten Schulz Subject: Re: Optimize References: <3FC26E96.9090101@be-a-part.de> <3FC267FB.806@bigfoot.com> <3FC27911.7070403@be-a-part.de> In-Reply-To: <3FC27911.7070403@be-a-part.de> X-Enigmail-Version: 0.81.7.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200311/228 X-Sequence-Number: 4788 Torsten Schulz wrote: > Gaetano Mendola wrote: > >> Torsten Schulz wrote: >> >>> Yes, I know: very difficult question, but I don't know what to do now. >>> >>> Our Server: >>> Dual-CPU with 1.2 GHz >>> 1.5 GB RAM >>> >>> Our Problem: We are a Community. Between 19 and 21 o clock we have >>> >350 User in the Community. But then, the Database are very slow. And >>> we have per CPU ~20-30% idle-time. >> >> >> >> May we know the postgres version that you are running and >> see the query that run slow ? > > > Postgres: 7.3.2 > Query: All queries > > Configuration: > max_connections = 1000 # Must be, if lower then 500 we become > connection-errors > shared_buffers = 5000 # 2*max_connections, min 16 > max_fsm_relations = 1000 # min 10, fsm is free space map > max_fsm_pages = 2000000 # min 1000, fsm is free space map > max_locks_per_transaction = 64 # min 10 > wal_buffers = 2000 # min 4 > > sort_mem = 32768 # min 32 > vacuum_mem = 32768 # min 1024 > > fsync = false > > enable_seqscan = true > enable_indexscan = true > enable_tidscan = true > enable_sort = true > enable_nestloop = true > enable_mergejoin = true > enable_hashjoin = true > > effective_cache_size = 96000 # default in 8k pages With 500 connection at the sime time 32MB for sort_mem can be too much. What say "iostat 1" and "vmstat 1" ? Try also to reduce this costs: random_page_cost = 2.5 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.0005 BTW take a query and show us the result of explain analyze. Regards Gaetano Mendola