Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7FFA82E0212 for ; Mon, 3 Mar 2008 19:39:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 48094-05 for ; Mon, 3 Mar 2008 19:39:34 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.174]) by postgresql.org (Postfix) with ESMTP id 5C8822E017D for ; Mon, 3 Mar 2008 19:39:40 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so430576wff.28 for ; Mon, 03 Mar 2008 15:39:36 -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:mime-version:content-type:content-transfer-encoding:content-disposition; bh=EgQ2D4G7PSeeSoR3FNszzu5OWYAHlB2Pg/RlbWQihRA=; b=uvXuMSP3Js18CyjtOhOkRosDaXWtsSq3hdCJNa7dNvGbu3Q33toCJLiQwuabdE943MUEUjiUcuhRiaS9fF/3/zUJPDkYLa64ZFTa1GSUA8yt2S/BY6iktTcVdkyQOXaU1GBj+WO2934OzNAhOEOPzb2oizDb0em9V8qykRTI3hs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=hkPZxZ708ucQvpbkMtW60SnPzbMWSkpcfxTZ4l7R3gd/mmbTUwrQeftCGkYBqddMJnDOyARbULpxbKYc7L6qHbFGMbRZlg5BenoyEAd/YbEWPjtF8lh34RYoQVabuWcjwdjL6JDdFkmeI1vKtQ3ORUrP3ZfDuz0kL8C9sX2aYZ8= Received: by 10.142.128.6 with SMTP id a6mr398116wfd.138.1204587575397; Mon, 03 Mar 2008 15:39:35 -0800 (PST) Received: by 10.143.38.10 with HTTP; Mon, 3 Mar 2008 15:39:35 -0800 (PST) Message-ID: <892916310803031539scffd5bchcce5a848b3a57a32@mail.gmail.com> Date: Mon, 3 Mar 2008 15:39:35 -0800 From: "alan bryan" To: pgsql-performance@postgresql.org Subject: Performance tuning on FreeBSD 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: 200803/38 X-Sequence-Number: 28992 I've got a new server and am myself new to tuning postgres. Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU. It's serving as the DB for a fairly write intensive (maybe 25-30%) Web application in PHP. We are not using persistent connections, thus the high max connections. I've done the following so far: > cat /boot/loader.conf kern.ipc.semmni=256 kern.ipc.semmns=512 kern.ipc.semmnu=256 > cat /etc/sysctl.conf kern.ipc.shmall=393216 kern.ipc.shmmax=1610612736 kern.ipc.semmap=256 kern.ipc.shm_use_phys=1 postgresql.conf settings (changed from Default): max_connections = 180 shared_buffers = 1024MB maintenance_work_mem = 128MB wal_buffers = 1024kB I then set up a test database for running pgbench with scaling factor 100. I then ran: > pgbench -c 100 -t 1000 testdb and got: starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 100 number of transactions per client: 1000 number of transactions actually processed: 100000/100000 tps = 557.095867 (including connections establishing) tps = 558.013714 (excluding connections establishing) Just for testing, I tried turning off fsync and got: > pgbench -c 100 -t 1000 testdb starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 100 number of transactions per client: 1000 number of transactions actually processed: 100000/100000 tps = 4014.075114 (including connections establishing) tps = 4061.662041 (excluding connections establishing) Do these numbers sound inline with what I should be seeing? What else can I do to try to get better performance in the more general sense (knowing that specifics are tied to real world data and testing). Any hints for FreeBSD specific tuning would be helpful.