Fsync IO issue

Hi there,

I've been struggling with very high write load on a server.

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.)

When running iostat I see a constant 7-10MB/s write by postgres, and
this just doesn't add up for me and I'm fully stuck with this. Even
with the row overhead it should be around 20Mb / 5 mins ! Even with
indeces this 7-10MB/s constant write is inexplicable for me.

The writes may trigger an update in an other table, but not all of
them do (I use a time filter). Let's say 70% does (which I dont think).
There we update two timestamps, and two ints. This still doesnt add up
for me. Even if we talk about 50MB of records, that should be 0,16MB/s
at most!

So I dag in and found it was WAL, of course, what else.

Tweaking all around the config, reading forums and docs, to no avail.
The only thing that made the scenario realistic is disabling fsync
(which I know I must not, but for the experiment I did). That eased the
write load to 0.6MB/s.

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?

I checked with pg_dump, the content is as expected.

We are talking about some insane data overhead here, two magnitudes
more is being written to WAL than the actual useful data.

All help is greatly appreciated.

Thanks!

András

---
Olcsó Virtuális szerver:
http://www.ProfiVPS.hu

Támogatás: Support(at)ProfiVPS(dot)hu