To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: PostgreSQL perform poorly on VMware ESXi
Date: Mon, 07 Nov 2011 16:21:08 +0100
Lines: 77
Message-ID: <j98t14$i52$1@dough.gmane.org>
References: 
 <CAFsUHaZysBW4qBfEdQkuYJY7wJ0Z_oQi9WFwANebGcP=G7poFg@mail.gmail.com>
Mime-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigE2C1447000E468BC32997738"
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64;
	rv:7.0.1) Gecko/20111004 Thunderbird/7.0.1
In-Reply-To: 
 <CAFsUHaZysBW4qBfEdQkuYJY7wJ0Z_oQi9WFwANebGcP=G7poFg@mail.gmail.com>

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigE2C1447000E468BC32997738
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

On 07/11/2011 11:36, Lucas Mocellin wrote:
> Hi everybody,
>=20
> I'm having some issues with PostgreSQL 9.03 running on FreeBSD 8.2 on t=
op
> of VMware ESXi 4.1 U1.

I hope your hardware is Nehalem-based or newer...

> The problem is query are taking too long, and some times one query "blo=
cks"
> everybody else to use the DB as well.

Ok, so multiple users connect to this one database, right?

> I'm a network administrator, not a DBA, so many things here can be "new=
bie"
> for you guys, so please be patient. :)

First, did you configure the server and PostgreSQL at all?

For FreeBSD, you'll probably need this in sysctl.conf:

vfs.hirunningspace=3D8388608
vfs.lorunningspace=3D6291456
vfs.read_max=3D128

and for PostgreSQL, read these:

http://www.revsys.com/writings/postgresql-performance.html
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

> I always think the bottleneck is disk I/O as I can see from the vSphere=

> performance view, but the virtual machine is using exclusively the SATA=

> disk with no concurrency with other machines.

I don't see why concurrency with other machines is relevant. Are you
complaining that multiple users accessing a single database are blocked
while one large query is executing or that this one query blocks other VM=
s?

If the single query generates a larger amount of IO than your VM host
can handle then you're probably out of luck. Virtualization is always
bad for IO. You might try increasing the amount of memory for the
virtual machine in the hope that more data will be cached.

> how do you guys deal with virtualization? any tips/recommendations? doe=
s
> that make sense the disk I/O? any other sugestion?

Are you sure it's IO? Run "iostat 1" for 10 seconds while the query is
executing and post the results.


--------------enigE2C1447000E468BC32997738
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.18 (FreeBSD)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk6392QACgkQldnAQVacBcjaogCgr/iDQQblLDvLUhhlWXcWJSj/
8TUAmwX1F/eNCn60Y1dosJT/J/oESBpx
=Qpg3
-----END PGP SIGNATURE-----

--------------enigE2C1447000E468BC32997738--