Re: parametric block size?

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: parametric block size?
Date: 2014-07-26 17:06:58
Message-ID: alpine.DEB.2.10.1407261832410.13352@sto
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


>> The rationale, which may be proven false, is that with a SSD the
>> latency penalty for reading and writing randomly vs sequentially is
>> much lower than for HDD, so there is less insentive to group stuff in
>> larger chunks on that account.
>
> A higher number of blocks has overhead unrelated to this though:
> Increased waste/lower storage density as it gets more frequently that
> tuples don't fit into a page; more locks; higher number of buffer
> headers; more toasted rows; smaller toast chunks; more vacuuming/heap
> pruning WAL records, ...
>
> Now obviously there's also a inverse to this, otherwise we'd all be
> using 1GB page sizes. But I don't think storage latency has much to do
> with it - it's imo more about write amplification (i.e. turning a single
> row update into a 8/4/16/32 kb write).

I agree with your interesting above discussion. I do not think that is
altogether fully invalidates my reasonning about latency, page size &
performance, but I may be wrong. On a HDD, writing a page takes +- the
same time whatever the size of the page, so the insentive is to try to
benefit as much as possible from this write, thus to use larger pages. On
a SSD, the insentive is not so, you can write smaller pages at a lower
cost.

Anyway, this needs measures, not just words.

ISTM that there is a tradeoff. Whether the current 8 kB page size is the
best possible compromise, given the various effects and the evoluting
hardware, and that the compromise would happen to be the same for a HDD
and a SSD, does not look obvious to me.

>> These benchs have the merit to exist, to be consistent (the smaller the
>> blocksize, the better the performance), and ISTM that the performance
>> results suggest that this is worth investigating.
>
> Well, it's easy to make claims that aren't meaningful with bad
> benchmarks.

Sure.

The basic claim that I'm making wrt to this benchmark is that there may be
a significant impact on performance with changing the block size, thus
this is worth investigating. I think this claim is quite safe, even if the
benchmark is not the best possible.

>> What would you suggest as meaningful for scale and run time, say on a
>> dual-core 8GB memory 256GB SSD laptop?
>
> At the very least scale hundred - then it likely doesn't fit into
> internal caches on common consumer drives anymore. But more importantly
> the test has to run over several checkpoint cycles, so hot pruning and
> vacuuming are also measured.

Ok.

--
Fabien.

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Marko Tiikkaja 2014-07-26 17:14:01 PL/PgSQL: EXIT USING ROLLBACK
Previous Message Andrew Dunstan 2014-07-26 17:04:25 building pdfs