pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL perform poorly on VMware ESXi
4+ messages / 4 participants
[nested] [flat]
* PostgreSQL perform poorly on VMware ESXi
@ 2011-11-07 10:36 Lucas Mocellin <lucasmocellin@gmail.com>
0 siblings, 3 replies; 4+ messages in thread
From: Lucas Mocellin @ 2011-11-07 10:36 UTC (permalink / raw)
To: pgsql-performance
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!!!
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.
how do you guys deal with virtualization? any tips/recommendations? does
that make sense the disk I/O? any other sugestion?
thanks in advance!
Lucas.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL perform poorly on VMware ESXi
@ 2011-11-07 10:54 Tomas Vondra <tv@fuzzy.cz>
parent: Lucas Mocellin <lucasmocellin@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tomas Vondra @ 2011-11-07 10:54 UTC (permalink / raw)
To: Lucas Mocellin <lucasmocellin@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL perform poorly on VMware ESXi
@ 2011-11-07 14:15 ktm@rice.edu <ktm@rice.edu>
parent: Lucas Mocellin <lucasmocellin@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: ktm@rice.edu @ 2011-11-07 14:15 UTC (permalink / raw)
To: Lucas Mocellin <lucasmocellin@gmail.com>; +Cc: pgsql-performance
On Mon, Nov 07, 2011 at 08:36:10AM -0200, 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!!!
>
> 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.
>
> how do you guys deal with virtualization? any tips/recommendations? does
> that make sense the disk I/O? any other sugestion?
>
> thanks in advance!
>
> Lucas.
Hi Lucas,
Virtualization is not a magic bullet. It has many advantages but also
has disadvantages. The resources of the virtual machine are always a
subset of the host machine resources. In addition, the second layer of
disk I/O indirection through the virtual disk can effectively turn
a sequential I/O pattern into a random I/O pattern with the accompanying
10:1 decrease in I/O throughput.
I would recommend testing your I/O on your virtual machine.
Regards,
Ken
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: PostgreSQL perform poorly on VMware ESXi
@ 2011-11-07 15:21 Ivan Voras <ivoras@freebsd.org>
parent: Lucas Mocellin <lucasmocellin@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ivan Voras @ 2011-11-07 15:21 UTC (permalink / raw)
To: pgsql-performance
On 07/11/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.
I hope your hardware is Nehalem-based or newer...
> The problem is query are taking too long, and some times one query "blocks"
> 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 "newbie"
> 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=8388608
vfs.lorunningspace=6291456
vfs.read_max=128
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 VMs?
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? does
> 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.
Attachments:
signature.asc (application/pgp-signature, 259B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-11-07 15:21 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-07 10:36 PostgreSQL perform poorly on VMware ESXi Lucas Mocellin <lucasmocellin@gmail.com>
2011-11-07 10:54 ` Tomas Vondra <tv@fuzzy.cz>
2011-11-07 14:15 ` ktm@rice.edu <ktm@rice.edu>
2011-11-07 15:21 ` Ivan Voras <ivoras@freebsd.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox