Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 2844E9FB471 for ; Thu, 4 Oct 2007 14:43:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 09737-03 for ; Thu, 4 Oct 2007 14:43:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.172]) by postgresql.org (Postfix) with ESMTP id 0D3529FB43F for ; Thu, 4 Oct 2007 14:43:00 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so405483ugf for ; Thu, 04 Oct 2007 10:42:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; 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=2A6IrYoTxSWNPF6/CR9TRt6+ZbQXX1NWMlXs/8iFlXU=; b=NBbI6fnabX/Ba/km3p40OFYOJP2lSkfuGeVt405HgVxg7xLnHfDrI/ztkl46CBhcG6JkeOWBQHTBADcKFXEg8x6LvdrusUwwkKIK4TNKoSsfYoki4+K51GPIqN4U8KRp9tV9iTqGmwV3r4Q83uZN/OjKS4k6FXyjdMWkzDxu/2I= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=eE7wrWMLnCJeEUVhSjkHr9Ah2UxGINATyCxLySH9ccuA7+DlqQpcDuXwl4bq8uKI9v3FrzgYVkWAi7kmc88YjMTtmgaAKgNO5Z4uPMpucpdwh+LSLzwGOb44Po9N7hV5+IC4K/nXW9/+t1WB6Yh235gz/hY7Paf5DNA/yGDSp7Q= Received: by 10.66.249.14 with SMTP id w14mr2261395ugh.1191519774004; Thu, 04 Oct 2007 10:42:54 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Thu, 4 Oct 2007 10:42:53 -0700 (PDT) Message-ID: Date: Thu, 4 Oct 2007 12:42:53 -0500 From: "Scott Marlowe" To: "Josh Trutwin" Subject: Re: Tuning Help - What did I do wrong? Cc: pgsql-performance@postgresql.org In-Reply-To: <20071004120027.0c1b0bd4@joplin.trutwins.homeip.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20071004102804.3418912e@joplin.trutwins.homeip.net> <20071004120027.0c1b0bd4@joplin.trutwins.homeip.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/33 X-Sequence-Number: 27055 On 10/4/07, Josh Trutwin wrote: > On Thu, 4 Oct 2007 11:19:22 -0500 > "Scott Marlowe" wrote: > > > We need to see examples of what's slow, including explain analyze > > output for slow queries. Also a brief explanation of the type of > > load your database server is seeing. I.e. is it a lot of little > > transactions, mostly read, batch processing, lots of users, one > > user, etc... Right now we don't have enough info to really help > > you. > > Sorry, this server is for a few (100+?) websites so it's running > along site apache, php. All connections to postgresql (except for > the occaional psql console login) are done from php requests, using > the same user (basically there are two users, the one php uses and > postgres). The bulk of the activity would be reads, but > certainly inesrts/updates/deletes would be interspersed in there. > Most of the activity is done via auto-commits, not many long > transactions. So, are there certain queries that are much slower than the others? Run them from psql with explain analyze in front of them and post the query and the output here. > From your followup email: > > > ... you should use tools like vmstat, iostat and top to get an idea > > of what your server is doing. > > # vmstat > procs memory swap io > system cpu > r b w swpd free buff cache si so bi bo in cs > us sy id > 3 1 0 268 68332 39016 2201436 0 0 3 3 4 > 2 3 4 2 vmstat needs to be run for a while to give you useful numbers. try: vmstat 5 and let it run for a few minutes. The first line won't count so much, but after that you'll get more reasonable numbers. > iostat is not found - will see if I can download it. top typically > shows postmaster as the top process with 10-15% of the CPU, followed > by apache threads. What OS are you on? > 12:01pm up 104 days, 12:05, 2 users, load average: 9.75, 9.30, > 7.70 That's pretty heavy load. I notice there's no wait % listed for CPU, so I assume it's not a late model Linux kernel or anything. > 215 processes: 214 sleeping, 1 running, 0 zombie, 0 stopped > CPU states: 0.1% user, 0.0% system, 0.0% nice, 0.4% idle > Mem: 3617400K av, 3552784K used, 64616K free, 0K shrd, 37456K > buff > Swap: 2457928K av, 264K used, 2457664K free > 2273664K cached > > PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME > COMMAND > 31797 postgres 17 0 28836 28M 1784 S 0 8.5 0.7 10:15 > postmaster Are the postmasters using most of the CPU? OR the other processes? > > What kind of drive subsystem do you have? What kind of raid > > controller? etc... > > Gathering more information on this - Raid is a software > RAID-1. Some information: OK, given that it's read mostly, it's likely not a problem that a faster RAID controller would help. Possibly more drives in a RAID 10 would help a little, but let's look at optimizing your query and postmaster first. Do you have the postmaster configured to log long running queries? That's a good starting point. also google pg_fouine (I think I spelt it right) for analyzing your logs. It's quite likely the issue here is one long running query that chewing all your I/O or CPU and making everything else slow. Once we find that query things should get better and we can worry about performance tuning in a more leisurely manner.