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 CE992D1B4E6 for ; Fri, 24 Oct 2003 22:30:43 +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 86559-04 for ; Fri, 24 Oct 2003 19:30:15 -0300 (ADT) Received: from pass.bivio.com (locker.bivio.com [216.87.82.238]) by svr1.postgresql.org (Postfix) with ESMTP id C183BD1B4E1 for ; Fri, 24 Oct 2003 19:30:08 -0300 (ADT) Received: from LIFT.bivio.com (lift.bivio.com [216.87.82.233]) by pass.bivio.com (8.9.3/8.9.3) with ESMTP id QAB25057 for ; Fri, 24 Oct 2003 16:30:07 -0600 From: Rob Nagler MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <16281.42312.682000.932257@gargle.gargle.HOWL> Date: Fri, 24 Oct 2003 16:18:48 -0600 To: pgsql-performance@postgresql.org Subject: Re: vacuum locking Newsgroups: ml.postgres.performance In-Reply-To: References: <16272.318.870000.93584@gargle.gargle.HOWL> <3F9006BF.40906@persistent.co.in> <16272.4154.489000.227676@gargle.gargle.HOWL> <200310170936.25997.josh@agliodbs.com> <16272.32044.80671.948532@jump.bivio.com> <16279.4988.496000.539525@gargle.gargle.HOWL> X-Mailer: VM 6.96 under Emacs 21.1.1 Organization: bivio Software Artisans, Inc. X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/637 X-Sequence-Number: 4385 Vivek Khera writes: > Also, how close are you to the capacity of your disk bandwidth? I > don't see that in your numbers. I know in freebsd I can run "systat > -vmstat" and it gives me a percentage of utilization that lets me know > when I'm near the capacity. The vacuum totally consumes the system. It's in a constant "D". As near as I can tell, it's hitting all blocks in the database. The problem is interactive performance when vacuum is in a D state. Even with just two processes doing "stuff" (vacuum and a select, let's say), the select is very slow. My understanding of the problem is that if a query hits the disk hard (and many of my queries do) and vacuum is hitting the disk hard, they contend for the same resource and nobody wins. The query optimizer has lots of problems with my queries and ends up doing silly sorts. As a simple example, one query goes like this: select avg(f1) from t1 group by f2; This results in a plan like: Aggregate (cost=171672.95..180304.41 rows=115086 width=32) -> Group (cost=171672.95..177427.26 rows=1150862 width=32) -> Sort (cost=171672.95..174550.10 rows=1150862 width=32) Sort Key: f2 -> Seq Scan on t1 (cost=0.00..39773.62 rows=1150862 width=32) This is of course stupid, because it sorts a 1M rows, which probably means it has to hit disk (sort_mem can only be so large). Turns out there are only about 20 different values of f2, so it would be much better to aggregate without sorting. This is the type of query which runs while vacuum runs and I'm sure the two are just plain incompatible. vacuum is read intensive and this query is write intensive. Rob