Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B4FA664FCBF for ; Thu, 18 Sep 2008 17:01:07 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18081-04 for ; Thu, 18 Sep 2008 17:01:04 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from po-out-1718.google.com (po-out-1718.google.com [72.14.252.157]) by postgresql.org (Postfix) with ESMTP id DC9D664FCC2 for ; Thu, 18 Sep 2008 17:01:03 -0300 (ADT) Received: by po-out-1718.google.com with SMTP id b23so83199poe.3 for ; Thu, 18 Sep 2008 13:01:02 -0700 (PDT) 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:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=oXQuOdkl2ZCJLHNeVwlxa9HHriHH61NzLnMPlpz7pBY=; b=v3Nmv1O0rvMdWUUbpDyzzkDIPxcRQLndiH54YsbO2GvSyfaFH5dSR3tZvCD/vRjXrD VMizpJi+iJMKVReFl4bWxCK5TSw96ZlJuF6GBkZQ76m0ZZ9iKPUTPzPDS1iZoSKAd17H 8v26SvSTiy0PB356H7t8uV4QTlqNEtAIsDy1E= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=YETHNDmMVeZzwv1tQ3T3S42qv53CUfkYLybfsrbGq68OWUOuLd3G+sCk8xCQ1nmOrb sjL5/pTLV071ZAH1Z/V1P7djxVXt19Fk/tEJ9zwczbb0y5YViW0+DJONlh1eDmBs+B2Y yvvyW1dlxrhtOL/kXbY9092C9sr0FJS765uCY= Received: by 10.141.74.18 with SMTP id b18mr7878514rvl.80.1221768062271; Thu, 18 Sep 2008 13:01:02 -0700 (PDT) Received: by 10.141.74.11 with HTTP; Thu, 18 Sep 2008 13:01:02 -0700 (PDT) Message-ID: Date: Thu, 18 Sep 2008 14:01:02 -0600 From: "Scott Marlowe" To: "Mauri Sahlberg" Subject: Re: Help request: how to tune performance? Cc: pgsql-admin@postgresql.org In-Reply-To: <48D2972E.9000604@claymountain.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1221553908.3410.11.camel@fault.in.tuolla.net> <48D2972E.9000604@claymountain.com> 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: 200809/202 X-Sequence-Number: 30295 On Thu, Sep 18, 2008 at 12:00 PM, Mauri Sahlberg wrote: >> So, you built it its own machine, but you didn't upgrade to at least 8.2? >> >> > > Now it is: 8.4devel_15092008 I don't think I'd be running production data on a dev version of the db. Not that it's likely to crash and eat all your data, which is a distinct possibility, but that you might have to dump and reload a couple of times before you get to 8.4 production. Plus if there's a weird performance corner case you might get to be the lucky one to report it. 8.3.3 is quite stable and quite a bit faster than 8.1. I haven't had a chance to even test 8.4 yet, but I'm sure it's got its own performance enhancements as well. > The machine was installed by the production team from the standard CentOS > template. I tried to adhere to the standard and installed the standard > CentOS binary for Postgresql. I am not part of production team so I try to > be extra careful with the "rule book". Understood... I prefer to install the PGDG rpms on centos / redhat, as it lets me choose the version I want instead of using the old version that rh/centos supports for that version. They're easy to install and uninstall. So, how's the performance of 8.4 now compared to 8.1? > When I upgraded to 8.4 I also checked newer Postgresql manual for the memory > consumption and found comment by Steven Citron-Pousty and increased > accordingly: > - shared_buffers to 320MB > - wal_buffers to 8MB > - effective_cache_size to 2048MB > - maintenance_work_mem to 384MB Seems reasonable. What's work_mem set to? I'd suggest something in the 4 to 8 meg range for starters, unless you're trying to handle hundreds and hundreds of connections. > Sorry, I do not understand what you mean by bloating. Every time pgsql updates or deletes a row it leaves a dead row in its place. Enough of these without vacuuming up the dead tuples and you wind up with a table with 90% dead space etc... Bad for performance. > The db size is: > rt=# select pg_size_pretty(pg_database_size('rt')); > pg_size_pretty > ---------------- > 350 MB > (1 row) Cool, between OS kernel cache and pgsql's shared_buffers it should all be in memory after a bit. >> Are you running on a single SATA hard drive? How big's the database >> directory? I'm guessing from your top output that the db is about 500 >> meg or so. it should all fit in memory. >> >> > > -bash-3.2$ du --si -s data > 524M data > > I don't know what kind of drives there actually are. The machine is vmware > virtual with two virtual CPU's clocking 2,33GHz, 4 GB ram, 1 GB swap. The > disk is probably given from either MSA or from EVA. The disk shows up as one > virtual drive and everything is on it. Filesystem is ext3 on lvm. Database > data is on /var which is it's own volume. The one thing that should NEVER run on a VM or on an LVM vol is a database. This is because most VMs and LVM for sure, do NOT provide proper write barriers, which means a crash could cost you your database being corrupted beyond repair. also, VMs tend to slow down heavily switched apps like databases and LVM has a maximum throughput in the 300Meg/sec range. Not a big deal for a couple of mirrored disks, but a big deal if you're running a 32 disk RAID-10 array under it. > For me the results look promising. Opening search builder went from 42 > seconds to 4 seconds and opening one particular long chain takes now only 27 > seconds. But again I am not from the support team either so I do not get to > define what is fast enough. The verdict is now in for the jury to decide. hehe. I know how that works. best of luck. I'd push or a dedicated db server. They can't give you a pick up truck and be upset it's not a dragster later on.