To: pgsql-performance@postgresql.org
From: Ivan Voras <ivoras@freebsd.org>
Subject: Re: Fun little performance IMPROVEMENT...
Date: Tue, 25 Jan 2011 16:27:15 +0100
Lines: 44
Message-ID: <ihmq4j$438$1@dough.gmane.org>
References: 
 <fb95011e666ffd6528d0b57542d7097f.squirrel@grant.boldlygoingnowhere.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US;
	rv:1.9.2.12) Gecko/20101102 Thunderbird/3.1.6
In-Reply-To: 
 <fb95011e666ffd6528d0b57542d7097f.squirrel@grant.boldlygoingnowhere.org>

On 21/01/2011 19:12, grant@amadensor.com wrote:
> I was doing a little testing to see how machine load affected the
> performance of different types of queries, index range scans, hash joins,
> full scans, a mix, etc.
>
> In order to do this, I isolated different performance hits, spinning only
> CPU, loading the disk to create high I/O wait states, and using most of
> the physical memory.   This was on a 4 CPU Xen virtual machine running
> 8.1.22 on CENTOS.
>
>
> Here is the fun part.   When running 8 threads spinning calculating square
> roots (using the stress package), the full scan returned consistently 60%
> faster than the machine with no load.   It was returning 44,000 out of
> 5,000,000 rows.   Here is the explain analyze.   I am hoping that this
> triggers something (I can run more tests as needed) that can help us make
> it always better.

Looks like a virtualization artifact. Here's a list of some such noticed 
artifacts:

http://wiki.freebsd.org/WhyNotBenchmarkUnderVMWare

>
> Idling:
>                                                           QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
>   Seq Scan on schedule_details  (cost=0.00..219437.90 rows=81386 width=187)
> (actual time=0.053..2915.966 rows=44320 loops=1)
>     Filter: (schedule_type = '5X'::bpchar)
>   Total runtime: 2986.764 ms
>
> Loaded:
>                                                           QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------
>   Seq Scan on schedule_details  (cost=0.00..219437.90 rows=81386 width=187)
> (actual time=0.034..1698.068 rows=44320 loops=1)
>     Filter: (schedule_type = '5X'::bpchar)
>   Total runtime: 1733.084 ms

In this case it looks like the IO generated by the VM is causing the 
Hypervisor to frequently "sleep" the machine while waiting for the IO, 
but if the machine is also generating CPU load, it is not put to sleep 
as often.