Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 9614A633D19 for ; Mon, 16 Mar 2009 15:49:07 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 08618-02 for ; Mon, 16 Mar 2009 15:48:55 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.26]) by mail.postgresql.org (Postfix) with ESMTP id AB77D632BA0 for ; Mon, 16 Mar 2009 15:48:52 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 8so663572qwh.61 for ; Mon, 16 Mar 2009 11:48:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:from:to :content-type:content-transfer-encoding:mime-version:subject:date :x-mailer; bh=vCwwBMc0Fe0vTQX0DPyBh6XyF5eFGfgMPSxkzZkxLag=; b=yBAKetpsKHBbS+7/vy8GOKtujkJ4JDHwhTe92VtGOlRdYJyCTlZGntCwSIknfwuJF2 cRtGreb/80FQy0WOT6IreG72vnmYWY20LTQt7RBeqRPY7zYN9YcORmgUNEQanRR8aFKj 2uB3b6QYMCzr6aEvpEtsFMTb+FcUZlRo9nqOs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:from:to:content-type:content-transfer-encoding :mime-version:subject:date:x-mailer; b=sHhcawL2WjHERZw7Mz59z6oZLclAu3qgj6vDHRVtZXwCXWMUrYg2rO8X0/5FC9QhQ6 ZYPkr7wZ0Z9iDH2m3chyxT0DyLaOJDkiayoA7TThnDO/h3d+t+Kg1iQ9alb30TMk+4fz X342TjKWUXWn5IeYXKndS9cgQC3tYYLY7jpa0= Received: by 10.220.95.83 with SMTP id c19mr1725322vcn.88.1237229329661; Mon, 16 Mar 2009 11:48:49 -0700 (PDT) Received: from iad16341atl.atl0.cbeyond.net ([74.7.6.178]) by mx.google.com with ESMTPS id 5sm9015381yxt.24.2009.03.16.11.48.49 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 16 Mar 2009 11:48:49 -0700 (PDT) Message-Id: <8609D1C0-3ECD-4A4F-B9C6-1FC8496EDC7A@gmail.com> From: Joe Uhl To: pgsql-performance@postgresql.org Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit Mime-Version: 1.0 (Apple Message framework v930.3) Subject: High CPU Utilization Date: Mon, 16 Mar 2009 14:48:48 -0400 X-Mailer: Apple Mail (2.930.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/215 X-Sequence-Number: 33137 Our production database is seeing very heavy CPU utilization - anyone have any ideas/input considering the following? CPU utilization gradually increases during the day until it approaches 90%-100% at our peak time. When this happens our transactions/sec drops and our site becomes very slow. When in this state, I can see hundreds of queries in pg_stat_activity that are not waiting on locks but sit there for minutes. When the database is not in this state, those same queries can complete in fractions of a second - faster that my script that watches pg_stat_activity can keep track of them. This server has dual quad core xeon 5310s, 32 GB RAM, and a few different disk arrays (all managed in hardware by either the Perc5/i or Perc5/e adapter). The Postgres data is on a 14 disk 7.2k SATA raid 10. This server runs nothing but Postgres. The PostgreSQL database (according to pg_database_size) is 55GB and we are running PostgreSQL 8.3.5 and the 2.6.28.7-2 kernel under Arch Linux. Right now (not under peak load) this server is running at 68% CPU utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/ s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads, so we are barely using the available IO. Further when I run dd the CPU utilization of that process only approaches 20%-30% of one core. Additionally, when I view "top -c" I generally see a dozen or so "idle" postgres processes (they appear and drop away quickly though) consuming very large chunks of CPU (as much as 60% of a core). At any given time we have hundreds of idle postgres processes due to the JDBC connection pooling but most of them are 0% as I would expect them to be. I also see selects and inserts consuming very large percentages of CPU but I view that as less strange since they are doing work. Any ideas as to what is causing our CPUs to struggle? Is the fact that our RAM covers a significant portion of the database causing our CPUs to do a bunch of thrashing as they work with memory while our disk controllers sit idle? According to top we barely use any swap. We currently have max_connections set to 1000 (roughly the sum of the JDBC pools on our application servers). Would decreasing this value help? We can decrease the JDBC pools or switch to pgbouncer for pooling if this is the case. Really just looking for any input/ideas. Our workload is primarily OLTP in nature - essentially a social network. By transactions/sec at the start I am using the xact_commit value in pg_stat_database. Please let me know if this value is not appropriate for getting a tps guess. Right now with the 60% CPU utilization and low IO use xact_commit is increasing at a rate of 1070 a second. I have an identical PITR slave I can pause the PITR sync on to run any test against. I will happily provide any additional information that would be helpful. Any assistance is greatly appreciated. Joe Uhl