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