Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 4D97E9FC5C3 for ; Fri, 1 Jun 2007 16:40:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 89680-08 for ; Fri, 1 Jun 2007 16:40:26 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from mu-out-0910.google.com (mu-out-0910.google.com [209.85.134.189]) by postgresql.org (Postfix) with ESMTP id 117B89FC5EF for ; Fri, 1 Jun 2007 16:40:21 -0300 (ADT) Received: by mu-out-0910.google.com with SMTP id i10so815178mue for ; Fri, 01 Jun 2007 12:40:20 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=dPBTgiBgEIHhJiSor2WXGMcjyiePbWEcIBut2q5Vfgk9MBSUSAHfiTjHM+ZhlCCqKP/3+LzyIkS5MyXfPu39kVU7dRnXKHJ6I5FbGrqHNhw5hrlIBMJoipAbK/g/37Lx6pDd4Q/zK20PWelp1Zt0Id+VpHkS9Z7/cyw56eYR7HA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=gmXJoQ9pngTQxwXg0GwuadjFj0pdhQVnL7nheJaf+pa9TWt24tHmtgnhGyTvEaXbRzNDUQMCZAwWFbZO67/49Uow0C/rsp0ZY+xXoMn/mgyazIDi2zPTGMwcsqr0nbNuMlsaL94bYkPXOMIVpBGUwsIy838lmDF0w9v6CjBo90I= Received: by 10.82.100.1 with SMTP id x1mr2515185bub.1180726819880; Fri, 01 Jun 2007 12:40:19 -0700 (PDT) Received: by 10.82.158.8 with HTTP; Fri, 1 Jun 2007 12:40:19 -0700 (PDT) Message-ID: Date: Fri, 1 Jun 2007 14:40:19 -0500 From: "Gregory Stewart" To: pgsql-performance@postgresql.org Subject: PostgreSQL not fully utilizing system resources? MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/52 X-Sequence-Number: 25085 Hello List, We've been running PostgreSQL as our web application database for almost a year and it has noticeably slowed down over last few months. Our current setup and pgsql configuration looks like this: 8.1.2 on Ubuntu 4 on Opteron Dual Core with 2 GBytes RAM. This is a dedicated DB server. We currently have about 3.5 million rows in 91 tables. Besides the requests coming from the web server, we have batch processes running every 15 minutes from another internal machine that do a lot of UPDATE, DELETE and INSERT queries on thousands of rows. Many of the SELECT queries coming from the web server contain large JOINS and aggregate calculations. We are running a financial application which is very data intensive and calculates a lot on the SQL side. Anyways, watching the system processes we realized that PostgreSQL is only using about 300 Mbytes for itself. Also, both cores are usually maxed out to 100% usage. Are we expecting too much from our server? Our non-default configuration settings are: max_connections = 100 shared_buffers = 17500 work_mem = 2048 maintenance_work_mem = 40000 max_fsm_pages = 35000 autovacuum = on What can I do to make best use of my db server? Is our configuration flawed? Or are we already at a point where we need consider clustering / load balancing? Any ideas and suggestions are welcome. Regards, Gregory Stewart