Subject: Re: Speed up Clog Access by increasing CLOG buffers
To: Robert Haas <robertmhaas@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>
References: 
 <CAA4eK1+8=X9mSNeVeHg_NqMsOR-XKsjuqrYzQf=iCsdh3U4EOA@mail.gmail.com>
 <CA+TgmobcD95v4QeD76qWcwTGE7P_31KVxBQGyS6yLh=dubvSfw@mail.gmail.com>
 <3f169562-4544-7b2b-9d25-b058da029ffb@2ndquadrant.com>
 <CA+TgmoY-sXi9u=Bg9U6sPBU3ec9W51bQRn-O6LUniE9zq04j9A@mail.gmail.com>
 <CAFiTN-ufKuY4JkNJV1LREmbuaZD7LS=fNxAjkgKGjKja6ekFpg@mail.gmail.com>
 <3cc206fa-7d36-f020-3856-12ed405e2535@2ndquadrant.com>
 <CAA4eK1JiOOKN73yC3rHqF_F+yKEFfn15Gh9iitT-=7Q5BuCs=Q@mail.gmail.com>
 <65f47a46-f1b2-aee1-d56d-b67de49e3a3b@2ndquadrant.com>
 <CAA4eK1JPVwPW0X8Ss+Rz+VQcPTYxCMGQuHEHfOcCTOtGqE_=ZA@mail.gmail.com>
 <09275f4d-f78d-dd69-22de-57c784dc410e@2ndquadrant.com>
 <CAFiTN-uQ+Jbd31cXvRbj48Ba6TqDUDpLKSPnsUCCYRju0Y0U8Q@mail.gmail.com>
 <CA+TgmobjO01bJ2caVWOzU=PuoKfx1X6epGokhHD8fc9hVwkhKw@mail.gmail.com>
 <CAFiTN-taV4iVkPHrxg=YCicKjBS6=QZm_cM4hbS_2q2ryLhUUw@mail.gmail.com>
 <CA+TgmoYq5vrfd3fJORXzLXUx6y2TYFEyPPfWEQ7vHp9DRMrcsQ@mail.gmail.com>
 <CA+TgmobJBv0qYEMazPEqsit4zkk_ECvafYdu8X=jAnVei0yaYg@mail.gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>, Andres Freund
 <andres@anarazel.de>, pgsql-hackers <pgsql-hackers@postgresql.org>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com>
Date: Thu, 20 Oct 2016 22:04:58 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: 
 <CA+TgmobJBv0qYEMazPEqsit4zkk_ECvafYdu8X=jAnVei0yaYg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 10/20/2016 07:59 PM, Robert Haas wrote:
> On Thu, Oct 20, 2016 at 11:45 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>> On Thu, Oct 20, 2016 at 3:36 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
>>> On Thu, Oct 13, 2016 at 12:25 AM, Robert Haas <robertmhaas@gmail.com> wrote:
 >>
>> ...
>>
>> So here's my theory.  The whole reason why Tomas is having difficulty
>> seeing any big effect from these patches is because he's testing on
>> x86.  When Dilip tests on x86, he doesn't see a big effect either,
>> regardless of workload.  But when Dilip tests on POWER, which I think
>> is where he's mostly been testing, he sees a huge effect, because for
>> some reason POWER has major problems with this lock that don't exist
>> on x86.
>>
>> If that's so, then we ought to be able to reproduce the big gains on
>> hydra, a community POWER server.  In fact, I think I'll go run a quick
>> test over there right now...
>
> And ... nope.  I ran a 30-minute pgbench test on unpatched master
> using unlogged tables at scale factor 300 with 64 clients and got
> these results:
>
>      14  LWLockTranche   | wal_insert
>      36  LWLockTranche   | lock_manager
>      45  LWLockTranche   | buffer_content
>     223  Lock            | tuple
>     527  LWLockNamed     | CLogControlLock
>     921  Lock            | extend
>    1195  LWLockNamed     | XidGenLock
>    1248  LWLockNamed     | ProcArrayLock
>    3349  Lock            | transactionid
>   85957  Client          | ClientRead
>  135935                  |
>
> I then started a run at 96 clients which I accidentally killed shortly
> before it was scheduled to finish, but the results are not much
> different; there is no hint of the runaway CLogControlLock contention
> that Dilip sees on power2.
>

What shared_buffer size were you using? I assume the data set fit into 
shared buffers, right?

FWIW as I explained in the lengthy post earlier today, I can actually 
reproduce the significant CLogControlLock contention (and the patches do 
reduce it), even on x86_64.

For example consider these two tests:

* http://tvondra.bitbucket.org/#dilip-300-unlogged-sync
* http://tvondra.bitbucket.org/#pgbench-300-unlogged-sync-skip

However, it seems I can also reproduce fairly bad regressions, like for 
example this case with data set exceeding shared_buffers:

* http://tvondra.bitbucket.org/#pgbench-3000-unlogged-sync-skip

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers