Re: parametric block size? - Mailing list pgsql-hackers

From Fabien COELHO
Subject Re: parametric block size?
Date
Msg-id alpine.DEB.2.10.1407261832410.13352@sto
Whole thread Raw
In response to Re: parametric block size?  (Andres Freund <andres@2ndquadrant.com>)
Responses Re: parametric block size?
List 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.



pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: building pdfs
Next
From: Marko Tiikkaja
Date:
Subject: PL/PgSQL: EXIT USING ROLLBACK