pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Robert Haas <robertmhaas@gmail.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
Cc: Amit Kapila <amit.kapila16@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Speed up Clog Access by increasing CLOG buffers
Date: Thu, 20 Oct 2016 22:04:58 +0200
Message-ID: <ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com> (raw)
In-Reply-To: <CA+TgmobJBv0qYEMazPEqsit4zkk_ECvafYdu8X=jAnVei0yaYg@mail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

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



view thread (248+ messages)  latest in thread

Message-ID: <ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com>
Permalink:  ../ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com/
Also on:    postgresql.org/message-id/ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tomas.vondra@2ndquadrant.com, robertmhaas@gmail.com, dilipbalaut@gmail.com, amit.kapila16@gmail.com, andres@anarazel.de
  Subject: Re: Speed up Clog Access by increasing CLOG buffers
  In-Reply-To: <ecb99330-cdcc-dd53-983f-03f787c01fa4@2ndquadrant.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox