Re: Performance degradation in commit 6150a1b0

Hi,

On 2016-02-25 12:56:39 +0530, Amit Kapila wrote:
> From past few weeks, we were facing some performance degradation in the
> read-only performance bench marks in high-end machines. My colleague
> Mithun, has tried by reverting commit ac1d794 which seems to degrade the
> performance in HEAD on high-end m/c's as reported previously[1], but still
> we were getting degradation, then we have done some profiling to see what
> has caused it and we found that it's mainly caused by spin lock when
> called via pin/unpin buffer and then we tried by reverting commit 6150a1b0
> which has recently changed the structures in that area and it turns out
> that reverting that patch, we don't see any degradation in performance.
> The important point to note is that the performance degradation doesn't
> occur every time, but if the tests are repeated twice or thrice, it
> is easily visible.

> m/c details
> IBM POWER-8
> 24 cores,192 hardware threads
> RAM - 492GB
>
> Non-default postgresql.conf settings-
> shared_buffers=16GB
> max_connections=200
> min_wal_size=15GB
> max_wal_size=20GB
> checkpoint_timeout=900
> maintenance_work_mem=1GB
> checkpoint_completion_target=0.9
>
> scale_factor - 300
>
> Performance at commit 43cd468cf01007f39312af05c4c92ceb6de8afd8 is 469002 at
> 64-client count and then at 6150a1b08a9fe7ead2b25240be46dddeae9d98e1, it
> went down to 200807. This performance numbers are median of 3 15-min
> pgbench read-only tests. The similar data is seen even when we revert the
> patch on latest commit. We have yet to perform detail analysis as to why
> the commit 6150a1b08a9fe7ead2b25240be46dddeae9d98e1 lead to degradation,
> but any ideas are welcome.

Ugh. Especially the varying performance is odd. Does it vary between
restarts, or is it just happenstance? If it's the former, we might be
dealing with some alignment issues.

If not, I wonder if the issue is massive buffer header contention. As a
LL/SC architecture acquiring the content lock might interrupt buffer
spinlock acquisition and vice versa.

Does applying the patch from http://archives.postgresql.org/message-id/CAPpHfdu77FUi5eiNb%2BjRPFh5S%2B1U%2B8ax4Zw%3DAUYgt%2BCPsKiyWw%40mail.gmail.com
change the picture?

Regards,

Andres