Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9C8FD64FD1C for ; Sat, 20 Dec 2008 20:54:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 93917-06 for ; Sat, 20 Dec 2008 20:54:41 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.232]) by mail.postgresql.org (Postfix) with ESMTP id 479E764FD0B for ; Sat, 20 Dec 2008 20:54:41 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id f9so2619487rvb.6 for ; Sat, 20 Dec 2008 16:54:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:mime-version:content-type:content-transfer-encoding :content-disposition; bh=hg63P83UU3b/v+B6L1qi3SyVd3lvGKnogRyUf6jbf5M=; b=X+N1XzN/h22CcqzVltVmqAIxmtHWVHJsevP28640PFPraVmFGZqCeIW7If75wsfJC8 dD6Zi1oNtxNg67Cux7AWqndpoM7b/W3R/zXam4jKubrVdEImQTVlpKfA1836aXadDkYO qd1ZDwVkCzpM+WLL/8SzfCWqsI0ERCAWH36Zs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:mime-version:content-type :content-transfer-encoding:content-disposition; b=KGKEziXB71YP8cPePxT/KlLGDJFgmuHTKABHav9pmozpPAFW+av4TRRpWHSIAiFrnr Z0MI6+tllUKqeCYuJjKule50xZMSyqxIHykXkS00QzLm55AqG3rfngoX53m6ZMNCGnIn 6jeSv/zf8xeSh/QsJW+WOJXkX8mQdFsGe5fA4= Received: by 10.141.136.4 with SMTP id o4mr2413507rvn.90.1229820879949; Sat, 20 Dec 2008 16:54:39 -0800 (PST) Received: by 10.141.87.11 with HTTP; Sat, 20 Dec 2008 16:54:39 -0800 (PST) Message-ID: <70c01d1d0812201654i5f55150ep619a6c7ff094470@mail.gmail.com> Date: Sat, 20 Dec 2008 16:54:39 -0800 From: "Mark Wong" To: "pgsql-performance@postgresql.org" Subject: dbt-2 tuning results with postgresql-8.3.5 Cc: "Selena Deckelmann" , "Gabrielle Roth" MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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: 200812/172 X-Sequence-Number: 32208 Hi all, So after a long hiatus after running this OLTP workload at the OSDL, many of you know the community has had some equipment donated by HP: a DL380 G5 and an MSA70 disk array. We are currently using the hardware to do some tuning exercises to show the effects of various GUC parameters. I wanted to share what I've started with for input for what is realistic to tune an OLTP database on a single large LUN. The initial goal is to show how much can (or can't) be tuned on an OLTP type workload with just database and kernel parameters before physically partitioning the database. I hope this is actually a useful exercise (it was certainly helped get the kit updated a little bit.) To recap, dbt2 is a fair-use derivative of the TPC-C benchmark. We are using a 1000 warehouse database, which amounts to about 100GB of raw text data. The DL380 G5 is an 8 core Xeon E5405 with 32GB of memory. The MSA70 is a 25-disk 15,000 RPM SAS array, currently configured as a 25-disk RAID-0 array. More specific hardware details can be found here: http://wiki.postgresql.org/wiki/HP_ProLiant_DL380_G5_Tuning_Guide#Hardware_Details So first task is to show the confidence of the results, here are a link to a few repeated runs using all default GUC values except the number of connections (250): http://pugs.postgresql.org/node/502 Here are links to how the throughput changes when increasing shared_buffers: http://pugs.postgresql.org/node/505 And another series of tests to show how throughput changes when checkpoint_segments are increased: http://pugs.postgresql.org/node/503 The links go to a graphical summary and raw data. Note that the maximum theoretical throughput at this scale factor is approximately 12000 notpm. My first glance takes tells me that the system performance is quite erratic when increasing the shared_buffers. I'm also not what to gather from increasing the checkpoint_segments. Is it simply that the more checkpoint segments you have, the more time the database spends fsyncing when at a checkpoint? Moving forward, what other parameters (or combinations of) do people feel would be valuable to illustrate with this workload? Regards, Mark