Re: Fsync IO issue

Oh, sorry, we are using PostgreSQL 13.10 (Debian 13.10-1.pgdg100+1)  on the server with with TimescaleDB 2.5.1 on Debian 10. 

2023-05-04 19:31 időpontban ProfiVPS Support ezt írta:

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