Re: WIP: [[Parallel] Shared] Hash

From: Rafia Sabih <rafia(dot)sabih(at)enterprisedb(dot)com>
To: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: Peter Geoghegan <pg(at)bowt(dot)ie>, Andres Freund <andres(at)anarazel(dot)de>, Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, Haribabu Kommi <kommi(dot)haribabu(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org>, Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: WIP: [[Parallel] Shared] Hash
Date: 2017-03-28 05:41:03
Message-ID: CAOGQiiM8kNS2Sz_r37pyJrxW3ntPYUpzdXhi+5TjBO=rQ8pL4A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Mar 27, 2017 at 12:20 PM, Thomas Munro
<thomas(dot)munro(at)enterprisedb(dot)com> wrote:
>
> On Sun, Mar 26, 2017 at 3:56 PM, Thomas Munro
> <thomas(dot)munro(at)enterprisedb(dot)com> wrote:
> > But... what you said above must be a problem for Windows. I believe
> > it doesn't allow files to be unlinked if they are open, and I see that
> > DSM segments are cleaned up in resowner's phase ==
> > RESOURCE_RELEASE_BEFORE_LOCKS and files are closed in phase ==
> > RESOURCE_RELEASE_AFTER_LOCKS.
>
> I thought this last point about Windows might be fatal to my design,
> but it seems that Windows since at least version 2000 has support for
> Unixoid unlinkability via the special flag FILE_SHARE_DELETE.

On testing v10 of this patch over commit
b54aad8e34bd6299093e965c50f4a23da96d7cc3 and applying the tweak
mentioned in [1], for TPC-H queries I found the results quite
encouraging,

Experimental setup:
TPC-H scale factor - 20
work_mem = 1GB
shared_buffers = 10GB
effective_cache_size = 10GB
random_page_cost = seq_page_cost = 0.1
max_parallel_workers_per_gather = 4

Performance numbers:
(Time in seconds)
Query | Head | Patch |
-------------------------------
Q3 | 73 | 37 |
Q5 | 56 | 31 |
Q7 | 40 | 30 |
Q8 | 8 | 8 |
Q9 | 85 | 42 |
Q10 | 86 | 46 |
Q14 | 11 | 6 |
Q16 | 32 | 11 |
Q21 | 53 | 56 |

Please find the attached file for the explain analyse output of these
queries on head as well as patch.
Would be working on analysing the performance of this patch on 300 scale factor.

[1] https://www.postgresql.org/message-id/flat/CAEepm%3D270ze2hVxWkJw-5eKzc3AB4C9KpH3L2kih75R5pdSogg%40mail.gmail.com
--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/

Attachment Content-Type Size
ph_v10_performance.tar.gz application/x-gzip 129.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2017-03-28 06:03:40 Re: Speedup twophase transactions
Previous Message Andres Freund 2017-03-28 05:33:03 Re: WIP: [[Parallel] Shared] Hash