Skip site navigation (1) Skip section navigation (2)

Re: Fun little performance IMPROVEMENT...

From: Ivan Voras <ivoras(at)freebsd(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Fun little performance IMPROVEMENT...
Date: 2011-01-25 15:27:15
Message-ID: ihmq4j$438$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-performance
On 21/01/2011 19:12, grant(at)amadensor(dot)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.


In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2011-01-25 16:41:35
Subject: Re: Bloat issue on 8.3; autovac ignores HOT page splits?
Previous:From: C├ędric VillemainDate: 2011-01-25 13:44:17
Subject: Re: Possible to improve query plan?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group