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@ProfiVPS.hu