X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 501253A4543 for ; Thu, 25 Nov 2004 06:01:00 +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 28773-01 for ; Thu, 25 Nov 2004 06:00:58 +0000 (GMT) Received: from gatekeeper.gmanetwork.com (mailhost.gmanetwork.com [61.9.4.50]) by svr1.postgresql.org (Postfix) with ESMTP id B29103A4531 for ; Thu, 25 Nov 2004 06:00:45 +0000 (GMT) Received: from gmanmi.tv (mailhost.gmanmi.tv [192.168.6.3]) by gatekeeper.gmanetwork.com (8.12.8/8.12.8) with ESMTP id iAP60XBV031205 for ; Thu, 25 Nov 2004 14:00:33 +0800 Received: (qmail 15183 invoked by uid 107); 25 Nov 2004 06:00:33 -0000 Received: from mail.gmanmi.tv (HELO jerome.gmanmi.tv) (192.168.6.3) by mail.gmanmi.tv with SMTP; 25 Nov 2004 06:00:33 -0000 From: JM Reply-To: jerome@gmanmi.tv To: pgsql-performance@postgresql.org Subject: HELP speed up my Postgres Date: Thu, 25 Nov 2004 14:00:32 +0800 User-Agent: KMail/1.5.4 Cc: pgsql-general@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200411251400.32752.jerome@gmanmi.tv> 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: 200411/1248 X-Sequence-Number: 69328 Hi ALL, Ive been using postgres for 3 years and now we are having problems with its performance. Here are some givens.. We have 260 subscription tables per Database. We have 2 databases. Our main client has given us 250,000 mobile numbers to deactivate. -- We we are experiencing 91,000 mobile numbers to deactive it took a week to finish for 1 DB only the second DB is still in the process of deactivating Algorithm to deactivate: we loaded all subscription tables names into a table we loaded all mobile numbers to deactivate into a table SQL: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) the script was made is "C" COFIG FILE: # This is ARA nmimain tcpip_socket = true max_connections = 150 superuser_reserved_connections = 2 port = 5433 shared_buffers = 45600 sort_mem = 40000 max_locks_per_transaction=128 #fsync = true #wal_sync_method = fsync # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8' .. DB is being vaccumed every week my box is running on a DUAL Xeon, 15K RPM with 2 G Mem. that box is running 2 instances of PG DB. TIA,