Re: Parallel Seq Scan
| От | Amit Kapila |
|---|---|
| Тема | Re: Parallel Seq Scan |
| Дата | |
| Msg-id | CAA4eK1JyVNEBE8KuxKd3bJhkG6tSbpBYX_+ZtP34ZSTCSucA1A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Parallel Seq Scan (Robert Haas <robertmhaas@gmail.com>) |
| Ответы |
Re: Parallel Seq Scan
(Robert Haas <robertmhaas@gmail.com>)
Re: Parallel Seq Scan (Robert Haas <robertmhaas@gmail.com>) |
| Список | pgsql-hackers |
On Mon, Jan 19, 2015 at 6:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Mon, Jan 19, 2015 at 2:24 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> > Another thing is that I think prefetching is not supported on all platforms
> > (Windows) and for such systems as per above algorithm we need to
> > rely on block-by-block method.
>
> Well, I think we should try to set up a test to see if this is hurting
> us. First, do a sequential-scan of a related too big at least twice
> as large as RAM. Then, do a parallel sequential scan of the same
> relation with 2 workers. Repeat these in alternation several times.
> If the operating system is accomplishing meaningful readahead, and the
> parallel sequential scan is breaking it, then since the test is
> I/O-bound I would expect to see the parallel scan actually being
> slower than the normal way.
>
I have taken some performance data as per above discussion. Basically,
IBM POWER-7 16 cores, 64 hardware threads
RAM = 64GB
>
> On Mon, Jan 19, 2015 at 2:24 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>
> > Another thing is that I think prefetching is not supported on all platforms
> > (Windows) and for such systems as per above algorithm we need to
> > rely on block-by-block method.
>
> Well, I think we should try to set up a test to see if this is hurting
> us. First, do a sequential-scan of a related too big at least twice
> as large as RAM. Then, do a parallel sequential scan of the same
> relation with 2 workers. Repeat these in alternation several times.
> If the operating system is accomplishing meaningful readahead, and the
> parallel sequential scan is breaking it, then since the test is
> I/O-bound I would expect to see the parallel scan actually being
> slower than the normal way.
>
I have taken some performance data as per above discussion. Basically,
I have used parallel_count module which is part of parallel-mode patch
as that seems to be more close to verify the I/O pattern (doesn't have any
tuple communication overhead).
Script used to test is attached (parallel_count.sh)
Performance Data
----------------------------
Configuration and Db DetailsIBM POWER-7 16 cores, 64 hardware threads
RAM = 64GB
Table Size - 120GB
Used below statements to create table -
create table tbl_perf(c1 int, c2 char(1000));
insert into tbl_perf values(generate_series(1,10000000),'aaaaa');
insert into tbl_perf values(generate_series(10000001,30000000),'aaaaa');
insert into tbl_perf values(generate_series(30000001,110000000),'aaaaa');
| Block-By-Block | ||
| No. of workers/Time (ms) | 0 | 2 |
| Run-1 | 267798 | 295051 |
| Run-2 | 276646 | 296665 |
| Run-3 | 281364 | 314952 |
| Run-4 | 290231 | 326243 |
| Run-5 | 288890 | 295684 |
Then I have modified the parallel_count module such that it can scan in
fixed chunks, rather than block-by-block, the patch for same is attached
(parallel_count_fixed_chunk_v1.patch, this is a patch based on parallel
count module in parallel-mode patch [1]).
| Fixed-Chunks | ||
| No. of workers/Time (ms) | 0 | 2 |
| 286346 | 234037 | |
| 250051 | 215111 | |
| 255915 | 254934 | |
| 263754 | 242228 | |
| 251399 | 202581 |
Observations
------------------------
1. Scanning block-by-block has negative impact on performance and
I thin it will degrade more if we increase parallel count as that can lead
to more randomness.
2. Scanning in fixed chunks improves the performance. Increasing
parallel count to a very large number might impact the performance,
but I think we can have a lower bound below which we will not allow
multiple processes to scan the relation.
Now I can go-ahead and try with prefetching approach as suggested
by you, but I have a feeling that overall it might not be beneficial (mainly
due to the reason that it is not supported on all platforms, we can say
that we don't care for such platforms, but still there is no mitigation strategy
for those platforms) due to the reasons mentioned up-thread.
Thoughts?
Вложения
В списке pgsql-hackers по дате отправления: