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 5631555F03 for ; Tue, 8 Mar 2005 20:14:03 +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 82987-06 for ; Tue, 8 Mar 2005 20:13:58 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 015EF55916 for ; Tue, 8 Mar 2005 20:13:57 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [64.81.245.111] (account josh@agliodbs.com HELO temoku.sf.agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7106189 for pgsql-performance@postgresql.org; Tue, 08 Mar 2005 12:15:48 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: "pgsql-performance@postgresql.org" Subject: Why would writes to pgsql_tmp bottleneck at 1mb/s? Date: Tue, 8 Mar 2005 12:18:24 -0800 User-Agent: KMail/1.7.1 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200503081218.24093.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.284 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, TO_ADDRESS_EQ_REAL X-Spam-Level: X-Archive-Number: 200503/139 X-Sequence-Number: 10844 PG Hackers, What follows is iostat output from a TPC-H test on Solaris 10. The machine is creating indexes on a table which is 50G in size, so it needs to use pgsql_tmp for internal swapping: tty md15 sd1 sd2 sd3 cpu tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id 0 84 22526 1211 1 1024 1 5 0 0 0 5634 337 1 30 8 0 61 0 242 24004 1337 1 1024 1 5 0 0 0 6007 355 1 33 8 0 59 0 85 22687 1277 1 1024 1 5 0 0 0 5656 322 1 31 8 0 62 0 85 20876 1099 1 1024 2 9 0 0 0 5185 292 1 28 7 0 64 md15 is WAL (pg_xlog). sd3 is PGDATA. sd1 i pgsql_tmp. As you can see, we're getting a nice 23mb/s peak for WAL (thanks to forcedirectio) and database writes peak at 6mb/s. However, pgsql_tmp, which is being used heavily, hovers around 1mb/s, and never goes above 1.5mb/s. This seems to be throttling the whole system. Any suggestions on why this should be? Do we have a performance bug in the pg_tmp code? -- --Josh Josh Berkus Aglio Database Solutions San Francisco