Re: Fsync IO issue

Hi,

On 2023-05-04 19:31:45 +0200, ProfiVPS Support wrote:
> We are collecting around 400k values each 5 minutes into a hypertable. (We
> use timescaledb extension, I also shared this on timescale forum but then I
> realised the issue is postgresql related.)

I don't know how timescale does its storage - how did you conclude this is
about postgres, not about timescale? Obviously WAL write patterns depend on
the way records are inserted and flushed.

> I also found that the 16MB WAL segment got 80+ MB written into it before
> being closed. So what's happening here? Does fsync cause the whole file to
> be written out again and again?

One possible reason for this is that you are committing small transactions
very frequently. When a transaction commits, the commit records needs to be
flushed to disk. If the transactions are small, the next commit might reside
on the same page - which needs to be written out again. Which of course can
increase the write rate considerably.

Your workload does not sound like it actually needs to commit in tiny
transactions? Some larger batching / using longer lived transactions might
help a lot.

Another possibility is that timescale does flush WAL too frequently for some
reason...

Greetings,

Andres Freund