Re: Fun little performance IMPROVEMENT...

От: Ivan Voras
Тема: Re: Fun little performance IMPROVEMENT...
Дата: ,
Msg-id: ihmq4j$438$1@dough.gmane.org
(см: обсуждение, исходный текст)
Ответ на: Fun little performance IMPROVEMENT...  ()
Список: pgsql-performance

Скрыть дерево обсуждения

Fun little performance IMPROVEMENT...  (, )
 Re: Fun little performance IMPROVEMENT...  (Andy Colson, )
  Re: Fun little performance IMPROVEMENT...  (, )
  Re: Fun little performance IMPROVEMENT...  (, )
 Re: Fun little performance IMPROVEMENT...  (Tom Lane, )
  Re: Fun little performance IMPROVEMENT...  (, )
   Re: Fun little performance IMPROVEMENT...  (Scott Carey, )
    Re: Fun little performance IMPROVEMENT...  (, )
 Re: Fun little performance IMPROVEMENT...  (Greg Smith, )
 Re: Fun little performance IMPROVEMENT...  (Ivan Voras, )

On 21/01/2011 19:12,  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.


В списке pgsql-performance по дате сообщения:

От: Josh Berkus
Дата:
Сообщение: Re: Bloat issue on 8.3; autovac ignores HOT page splits?
От: Mark Kirkwood
Дата:
Сообщение: Re: Bloat issue on 8.3; autovac ignores HOT page splits?