On Sat, Feb 27, 2016 at 12:41 AM, Andres Freund <and...@anarazel.de> wrote: > > 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. >
It varies between restarts. > > 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? > Not tried, but if this is alignment issue as you are suspecting above, then does it make sense to try this out? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
