Date: Thu, 10 Sep 2020 20:06:10 +0200
From: Julien Rouhaud <rjuju123@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Justin Pryzby <pryzby@telsasoft.com>,
	Masahiko Sawada <masahiko.sawada@2ndquadrant.com>,
	Robert Haas <robertmhaas@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Masahiko Sawada <sawada.mshk@gmail.com>
Subject: Re: Online checksums verification in the backend
Message-ID: <20200910180610.GA62228@nol>
References: 
 <CA+fd4k5VS_QAkdGwU-mwsr1z78XM7egixZ2bDhfeSJm4HCrRGg@mail.gmail.com>
 <20200405094459.GG1206@nol>
 <20200712173403.GI26220@telsasoft.com>
 <20200714090808.GA20780@nol>
 <20200907065858.GC4867@paquier.xyz>
 <CAOBaU_bJEYemqwzQ4p8+txuT64AXUg2L0p8qHgs5vNGuZ0_kVw@mail.gmail.com>
 <20200907085038.GA19261@paquier.xyz>
 <20200909092524.GC57691@nol>
 <20200909134130.GA29325@nol>
 <20200910074723.GA91494@nol>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200910074723.GA91494@nol>
Precedence: bulk

On Thu, Sep 10, 2020 at 09:47:23AM +0200, Julien Rouhaud wrote:
> On Wed, Sep 09, 2020 at 03:41:30PM +0200, Julien Rouhaud wrote:
> > On Wed, Sep 09, 2020 at 11:25:29AM +0200, Julien Rouhaud wrote:
> > > 
> > > I'll do some becnhmarking and see if I can get some figures, but it'll probably
> > > take some time.  In the meantime I'm attaching v11 of the patch that should
> > > address all other comments.
> > 
> > I just realized that I forgot to update one of the Makefile when moving the TAP
> > test folder.  v12 attached should fix that.
> 
> 
> And the cfbot just reported a new error for Windows build.  Attached v13 should
> fix that.


I did some benchmarking using the following environnment:

- 16MB shared buffers
- 490MB table (10M rows)
- synchronized_seqscan to off
- table in OS cache

I don't have a big machine so I went with a very small shared_buffers and a
small table, to make sure that all data is in OS cache but the table more than
an order bigger than the shared_buffers, to simulate some plausible environment.

I used a simple read only query that performs a sequential scan of the table (a
simple SELECT * FROM table), run using 10 concurrent connections, 5 runs of 700
seconds.  I did that without any other activity, with a \watch of the original
pg_check_relation function using \watch .1, and a modified version of that
function without the optimisation, still with a \watch .1

The TPS is obviously overall extremely bad, but I can see that the submitted
version added an overhead of ~3.9% (average of 5 runs), while the version
without the optimisation added an overhead of ~6.57%.

This is supposed to be a relatively fair benchmark as all the data are cached
on the OS side, so IO done while holding the bufmapping lock aren't too long,
but we can see that we already get a non negligible benefit from this
optimisation.  Should I do additional benchmarking, like dropping the OS cache
and/or adding some write activity?  This would probably only make the
unoptimized version perform even worse.