Re: Move PinBuffer and UnpinBuffer to atomics - Mailing list pgsql-hackers

On Sun, Apr 10, 2016 at 11:33 AM, Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:

On Sun, Apr 10, 2016 at 8:36 AM, Alexander Korotkov <a.korotkov@postgrespro.ru> wrote:

On Sat, Apr 9, 2016 at 10:49 PM, Andres Freund <andres@anarazel.de> wrote:

On April 9, 2016 12:43:03 PM PDT, Andres Freund <andres@anarazel.de> wrote:
>On 2016-04-09 22:38:31 +0300, Alexander Korotkov wrote:
>> There are results with 5364b357 reverted.
>
>Crazy that this has such a negative impact. Amit, can you reproduce
>that? Alexander, I guess for r/w workload 5364b357 is a benefit on that
>machine as well?

How sure are you about these measurements?

I'm pretty sure.  I've retried it multiple times by hand before re-run the script.

 

Because there really shouldn't be clog lookups one a steady state is reached...

Hm... I'm also surprised. There shouldn't be clog lookups once hint bits are set.

I also tried to run perf top during pgbench and get some interesting results.

Without 5364b357:

   5,69%  postgres                 [.] GetSnapshotData

   4,47%  postgres                 [.] LWLockAttemptLock

   3,81%  postgres                 [.] _bt_compare

   3,42%  postgres                 [.] hash_search_with_hash_value

   3,08%  postgres                 [.] LWLockRelease

   2,49%  postgres                 [.] PinBuffer.isra.3

   1,58%  postgres                 [.] AllocSetAlloc

   1,17%  [kernel]                 [k] __schedule

   1,15%  postgres                 [.] PostgresMain

   1,13%  libc-2.17.so             [.] vfprintf

   1,01%  libc-2.17.so             [.] __memcpy_ssse3_back

With 5364b357:

  18,54%  postgres                 [.] GetSnapshotData

   3,45%  postgres                 [.] LWLockRelease

   3,27%  postgres                 [.] LWLockAttemptLock

   3,21%  postgres                 [.] _bt_compare

   2,93%  postgres                 [.] hash_search_with_hash_value

   2,00%  postgres                 [.] PinBuffer.isra.3

   1,32%  postgres                 [.] AllocSetAlloc

   1,10%  libc-2.17.so             [.] vfprintf

Very surprising.  It appears that after 5364b357, GetSnapshotData consumes more time.  But I can't see anything depending on clog buffers in GetSnapshotData code...

There is a related fact presented by Mithun C Y as well [1] which suggests that Andres's idea of reducing the cost of snapshot shows noticeable gain after increasing the clog buffers.  If you read that thread you will notice that initially we didn't notice much gain by that idea, but with increased clog buffers, it started showing noticeable gain.  If by any chance, you can apply that patch and see the results (latest patch is at [2]).

[1] - http://www.postgresql.org/message-id/CAD__Ouic1Tvnwqm6Wf6j7Cz1Kk1DQgmy0isC7=OgX+3JtfGk9g@mail.gmail.com 

[2] - http://www.postgresql.org/message-id/CAD__OuiwEi5sHe2wwQCK36Ac9QMhvJuqG3CfPN+OFCMb7rdruQ@mail.gmail.com