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(at)ProfiVPS(dot)hu

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

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