Обсуждение: [Patch] Make block and file size for WAL and relations defined atcluster creation

On Sun, Dec 31, 2017 at 12:00 PM, Remi Colinet <remi.colinet@gmail.com> wrote:
> Below patch makes block and file sizes defined at cluster creation for both
> the WAL and the relations. This avoids having different server builds for
> each possible combination of block size and file sizes.\

The email thread where we discussed making the WAL segment size
configurable at initdb time contained a detailed rationale, explaining
why it was useful to be able to make such a change.  The very short
version is that, if a system is generating WAL at a very high rate,
being able to group that WAL into fewer, larger files makes life
easier since, for example, the latency requirements for
archive_command are not as tight, and "ls pg_wal" doesn't have to go
into the tank just trying to read the directory contents.

Your email doesn't seem to contain a rationale explaining why the
block and file sizes should be run-time configurable.  There may be a
very good reason, but can you explain what it is?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

On 2018-01-03 21:43:51 +0100, Remi Colinet wrote:
> - we may test different combinations of file and block sizes, for the
> relation and the WAL in order to have the better performances of the server.
> Avoiding a compilation for each combination of values seems to make sense.

That's something you need to proof to beneficial *before* we make this
change.


> - Selecting the correct values for file and block sizes is a DBA task, and
> not a developer task.
> For instance, when someone wants to create a Linux filesystem with a given
> block size, he is not forced to accept a given value chosed by the
> developer of the filesystem driver when this later was compiled.

I'm unconvinced there's as much value syncing up fs in pg as some
conventional wisdom says.


> - The file and block sizes should depend mostly of the physical server and
> physical storage.
> Not of the database software itself.

Citation needed.


> Regarding the cost of using run-time configurable values for file and block
> sizes of the WAL and relations, this cost is low both :
> 
> - from a developer point of view: the source code changes are spread in
> many files but only a few one have significant changes.
> Mainly the tidbitmap.c is concerned the change. Other changes are minor
> changes.
> 
> - from a run-time point of view. The overhead is only at the start of the
> database instance.
> And moreover, the overhead is still very low at the start of the server,
> with only a few more dynamic memory allocations.

That's to some degree because you rely on stack allocation of variable
sided amounts of data - we can't rely on that. E.g. you allocate stack
variables sized by rel_block_size, that's unfortunately not
ok. Additionally some of the size calculations will have some
performance impact.


- Andres

On Wed, Jan 3, 2018 at 3:43 PM, Remi Colinet <remi.colinet@gmail.com> wrote:
> Justifications are:

I think this is all missing the point.  If varying the block size (or
whatever) is beneficial, then having it configurable at initdb is
clearly useful.  But, as Andres says, you haven't submitted any
evidence that this is the case.  You need to describe scenarios in
which (1) a non-default blocksize performs better and (2) there's no
reasonable way to obtain the same performance improvement without
changing the block size.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Wed, January 3, 2018 4:04 pm, Robert Haas wrote:
> On Wed, Jan 3, 2018 at 3:43 PM, Remi Colinet <remi.colinet@gmail.com>
> wrote:
>> Justifications are:
>
> I think this is all missing the point.  If varying the block size (or
> whatever) is beneficial, then having it configurable at initdb is
> clearly useful.  But, as Andres says, you haven't submitted any
> evidence that this is the case.  You need to describe scenarios in
> which (1) a non-default blocksize performs better and (2) there's no
> reasonable way to obtain the same performance improvement without
> changing the block size.

(Note: I gather that "block size" here is the page size, but I'm not
entirely sure. So this detail might make my arguments moot :)

First, I do think there is a chicken-and-egg problem here. If you can't
vary the values except by re-compiling, almost no-one does. So you don't
get any benchmarks, or data.

And even if the author of the patch does this, he can only provide very
spotty data, which might not be enough to draw the right conclusions.

Plus, if the goal is to "select the optimal size" (as opposed to Remi's
goal, which seems to me os "make it easier to select the optimial size for
one system at hand"), you might not be able to do this, as the "optimial"
size is different for different conditions, but you can't try different
values if the value is compiled in...

Plus, isn't almost all advancement in computing that you replace "1 + 1"
with "x + y"? :)

So, I do think the patch has some merits, because at least it allows much
easer benchmarking. And if the value was configurable at initdb time, I
think more people would experiment and settle for their optimium. So for
me the question isn't here "does it benefit everyone", but "does it
benefit some, and what is the cost for others".

Plus, I stumbled just by accident over a blog post from Tomas Vondra[0]
from 2015, who seems to agree that different page sizes can be useful:

https://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-pages

Me thinks the next steps would be that more benchmarks are done on more
distinct hardware to see what benefits we can see, and weight this against
the complexity the patch introduces into the code base.

Hope that does make sense,

Tels

[0]: Tomas, great blog, btw!

On Thu, Jan 4, 2018 at 5:15 PM, Remi Colinet <remi.colinet@gmail.com> wrote:
> Block size does not boils down only to performance.
>
> For instance, having a larger block size allows:
> - to avoid toasting tuples. Rows with sizes larger that the default block
> size can justify larger block sizes.
> - to reduce fragmentation in relations.

Well, I think those are things that you do to improve performance.
So, ultimately, I would argue that it does come down to performance.

> If changing the block size at initdb is useless, then why allowing developer
> to set such block size at compile time?

In my view, right now, changing BLCKSZ is only marginally supported.
It's there so that you can experiment, but it's not really something
we expect users to do.  I think we have no buildfarm coverage of
different block sizes.  I am not sure that we even consistently fix
regression test failures with other block sizes even if someone
reports them.  If there's a bug in some index AM that only manifests
with some non-default block size, we might not know about it.  I think
that if we make this an initdb-time option, we're committing to fix
all of those issues: add tests, fix bugs, and of course document how
to set the parameter properly (which means we have to know how it
should be set, which means we have to know what the effects of
changing it are on different systems and workloads).  You may or may
not be willing to do some of that work, but I suspect there's a good
chance that it will require effort from other people as well -- e.g.
if we turn up a bug in BRIN, are you going to dive into that and fix
it, or are you going to hope Alvaro does something about it?  He'll
probably have to review and commit your patch, at the least.

Of course, it's possible there are no such problems and everything
will just work.

I looked around a little for previous tests that had been run in this
area and found these:

https://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-pages
https://www.cybertec-postgresql.com/en/postgresql-block-sizes-getting-started/
http://blog.coelho.net/database/2014/08/08/postgresql-page-size-for-SSD.html

All of those seem to agree that smaller block sizes can help
performance, sometimes significantly, and larger block sizes hurt
performance, which is sort of surprising to me since that also means
that that your database will get bigger: at a 4kB page size, you have
to store at least twice as many page headers as you would with an 8kB
page size.  Some of them also mention reasons why you might want a
larger block size. I believe I recall a mailing-list discussion some
years back about how index pages might need some kind of page-internal
indexing for efficiency with large block sizes, because a simple
binary search might touch too many cache lines.  It seems like if we
want to have good performance at a variety of block sizes -- rather
than just having it technically work -- we might need to do a fair
amount of investigation of what factors account for good and bad
performance at a variety of settings and consider whether there are
design changes that might mitigate some of the problems.

I think that if you're interested in making non-default block sizes
more supported in PostgreSQL, some good first steps would be:

- run make check-world at all the supposedly-supported block sizes and
see if it passes.

- set up some buildfarm critters that run with various non-default
block sizes on various hardware and software platforms.  ideally we
should have various combinations of 32-bit and 64-bit; Linux, Windows,
and other; and the whole range of block sizes but especially the more
extreme ones.

- run performance tests with a variety of workloads, not just pgbench,
at various block sizes and on various hardware, and post or blog about
the results

If it's clear that non-default block sizes (a) work and (b) are good,
then at least IMHO it's quite likely that we would want this patch.
Maybe those things are already clear to you, but they're not
completely clear to me.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Fri, Jan 5, 2018 at 9:42 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> - run make check-world at all the supposedly-supported block sizes and
> see if it passes.

Last time I tried that with a 16kB-size block, which was some months
back, make check complained about some plan inconsistencies. Perhaps
that's something that could be improved to begin with. There is always
some margin in this area.
-- 
Michael

On Fri, Jan 5, 2018 at 7:54 AM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Fri, Jan 5, 2018 at 9:42 PM, Robert Haas <robertmhaas@gmail.com> wrote:
>> - run make check-world at all the supposedly-supported block sizes and
>> see if it passes.
>
> Last time I tried that with a 16kB-size block, which was some months
> back, make check complained about some plan inconsistencies. Perhaps
> that's something that could be improved to begin with. There is always
> some margin in this area.

Yeah, that's exactly the kind of thing that needs to be ironed out
before we can think about actually recommending that users run with
other block sizes.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company