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

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

Támogatás: Support@ProfiVPS.hu