X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 28CE9D1B4E6 for ; Wed, 22 Oct 2003 00:24:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 78073-06 for ; Tue, 21 Oct 2003 21:24:28 -0300 (ADT) Received: from mail.osdl.org (fw.osdl.org [65.172.181.6]) by svr1.postgresql.org (Postfix) with ESMTP id 15BFFD1B4E3 for ; Tue, 21 Oct 2003 21:24:23 -0300 (ADT) Received: from osdl.org (markw@ibm-b.pdx.osdl.net [172.20.1.51]) by mail.osdl.org (8.11.6/8.11.6) with ESMTP id h9M0O5o19190; Tue, 21 Oct 2003 17:24:06 -0700 Message-Id: <200310220024.h9M0O5o19190@mail.osdl.org> Date: Tue, 21 Oct 2003 17:24:02 -0700 (PDT) From: markw@osdl.org Subject: analyzing postgresql performance for dbt-2 To: pgsql-performance@postgresql.org Cc: osdldbt-general@lists.sourceforge.net MIME-Version: 1.0 Content-Type: TEXT/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: * X-Archive-Number: 200310/563 X-Sequence-Number: 4311 I'm running our DBT-2 workload against PostgreSQL 7.3.4 and I'm having some trouble figuring out what I should be looking for when I'm trying to tune the database. I have results for a decent baseline, but when I try to increase the load on the database, the performance drops. Nothing in the graphs (in the links listed later) sticks out to me so I'm wondering if there are other database statitics I should try to collect. Any suggestions would be great and let me know if I can answer any other questions. Here are a pair of results where I just raise the load on the database, where increasing the load increases the area of the database touched in addition to increasing the transaction rate. The overall metric increases somewhat, but the response time for most of the interactions also increases significantly: http://developer.osdl.org/markw/dbt2-pgsql/158/ [baseline] - load of 100 warehouses - metric 1249.65 http://developer.osdl.org/markw/dbt2-pgsql/149/ - load of 140 warehouses - metric 1323.90 Both of these runs had wal_buffers set to 8, checkpoint_segments 200, and checkpoint_timeout 1800. So far I've only tried various wal_buffers and checkpoint_segments settings in the next set of results for a load of 140 warehouses. http://developer.osdl.org/markw/dbt2-pgsql/148/ - metric 1279.26 - wal_buffers 8 - checkpoint_segments 100 - checkpoint_timeout 300 http://developer.osdl.org/markw/dbt2-pgsql/149/ - metric 1323.90 - wal_buffers 8 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/150/ - metric 1281.13 - wal_buffers 8 - checkpoint_segments 300 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/151/ - metric 1311.99 - wal_buffers 32 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/152/ - metric 1268.37 - wal_buffers 64 - checkpoint_segments 200 - checkpoint_timeout 1800 http://developer.osdl.org/markw/dbt2-pgsql/154/ - metric 1314.62 - wal_buffers 16 - checkpoint_segments 200 - checkpoint_timeout 1800 Thanks! -- Mark Wong - - markw@osdl.org Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax)