Fun little performance IMPROVEMENT...

От: grant@amadensor.com
Тема: Fun little performance IMPROVEMENT...
Дата: ,
Msg-id: fb95011e666ffd6528d0b57542d7097f.squirrel@grant.boldlygoingnowhere.org
(см: обсуждение, исходный текст)
Ответы: Re: Fun little performance IMPROVEMENT...  (Andy Colson)
Re: Fun little performance IMPROVEMENT...  (Tom Lane)
Re: Fun little performance IMPROVEMENT...  (Greg Smith)
Re: Fun little performance IMPROVEMENT...  (Ivan Voras)
Список: 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, )

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.

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







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

От: Madhu Ramachandran
Дата:
Сообщение: Re: copy command and blobs
От: Robert Haas
Дата:
Сообщение: Re: copy command and blobs