Performance degradation in commit 6150a1b0

From: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Performance degradation in commit 6150a1b0
Date: 2016-02-25 07:26:39
Message-ID: CAA4eK1+ZeB8PMwwktf+3bRS0Pt4Ux6Rs6Aom0uip8c6shJWmyg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

[1] -
http://www.postgresql.org/message-id/CAB-SwXZh44_2ybvS5Z67p_CDz=XFn4hNAD=CnMEF+QqkXwFrGg@mail.gmail.com

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mithun Cy 2016-02-25 07:27:57 Re: POC: Cache data in GetSnapshotData()
Previous Message Robert Haas 2016-02-25 07:23:51 Re: RFC: replace pg_stat_activity.waiting with something more descriptive