Re: lseek/read/write overhead becomes visible at scale ..

Hi,

On 2017-01-24 18:11:09 +0100, Tobias Oberstein wrote:
> I have done lots of benchmarking over the last days on a massive box, and I
> can provide numbers that I think show that the impact can be significant.

> Above number was using psync FIO engine .. with libaio, it's at 9.7 mio with
> much lower CPU load - but this doesn't apply to PG of course.

> Switching to sync engine, it drops to 9.1 mio - but the system load then is
> also much higher!

I doubt those have very much to do with postgres - I'd quite strongly
assume that it'd get more than swamped with doing actualy work, and with
buffering the frequently accessed stuff in memory.

> What I am trying to say is: the syscall overhead of doing lseek/read/write
> instead of pread/pwrite do become visible and hurt at a certain point.

Sure - but the question is whether it's measurable when you do actual
work.

I'm much less against this change than Tom, but doing artificial syscall
microbenchmark seems unlikely to make a big case for using it in
postgres, where it's part of vastly more expensive operations (like
actually reading data afterwards, exclusive locks, ...).

> This isn't academic, as we have experience (in prod) with a similarily
> designed box and PostgreSQL used as a data-warehouse.
>
> We are using an internal tool to parallelize via sessions and this box is
> completely CPU bound (same NVMes, 3TB RAM as the new one, but only 48 cores
> and no HT).

I'd welcome seeing profiles of that - I'm working quite heavily on
speeding up analytics workloads for pg.

Greetings,

Andres Freund