Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0539D15C40DF for ; Mon, 7 Nov 2011 06:54:28 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 39449-03 for ; Mon, 7 Nov 2011 10:54:08 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from elizabeth.gransy.com (ns2.gransy.com [89.187.132.100]) by mail.postgresql.org (Postfix) with ESMTP id 795F415C40CD for ; Mon, 7 Nov 2011 06:54:07 -0400 (AST) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 4D3A815E4119; Mon, 7 Nov 2011 11:54:02 +0100 (CET) Received: from 85.162.69.73 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Mon, 7 Nov 2011 11:54:02 +0100 Message-ID: In-Reply-To: References: Date: Mon, 7 Nov 2011 11:54:02 +0100 Subject: Re: PostgreSQL perform poorly on VMware ESXi From: "Tomas Vondra" To: "Lucas Mocellin" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201111/93 X-Sequence-Number: 45604 On 7 Listopad 2011, 11:36, Lucas Mocellin wrote: > Hi everybody, > > I'm having some issues with PostgreSQL 9.03 running on FreeBSD 8.2 on top > of VMware ESXi 4.1 U1. > > The problem is query are taking too long, and some times one query > "blocks" > everybody else to use the DB as well. > > I'm a network administrator, not a DBA, so many things here can be > "newbie" > for you guys, so please be patient. :) > > Clonning this database to another machine not virtualized, any "crap" > machine, it runs a way fastar, I've measured one specific "SELECT" and at > the virtualized system (4GB RAM, 4 processors, SATA disk, virtualized), it > took 15minutes!, in the crap machine (2GB RAM, 1 processor, SATA disk, NOT > virtualized), it took only 2!!! What is this "cloning" thing? Dump/restore? Something at the filesystem-device level? My wild guess is that the autovacuum is not working, thus the database is bloated and the cloning removes the bloat. Post EXPLAIN ANALYZE output of the query for both machines (use explain.depesz.com). Have you done any benchmarking on the virtualized machine to check the basic I/O performance? A simple "dd test", bonnie? Tomas