MIME-Version: 1.0
Date: Thu, 04 May 2023 19:31:45 +0200
From: ProfiVPS Support <support@profivps.hu>
To: pgsql-performance@lists.postgresql.org
Subject: Fsync IO issue
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <060c533e971eec98e1f179a2b2846454@profivps.hu>
Organization: ProfiVPS.hu
Content-Type: multipart/alternative;
 boundary="=_2e9186444c9bdebb9502bc0f7fb8e5b3"
Archived-At: 
 <https://www.postgresql.org/message-id/060c533e971eec98e1f179a2b2846454%40profivps.hu>
Precedence: bulk

--=_2e9186444c9bdebb9502bc0f7fb8e5b3
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: quoted-printable

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.=20
(We use timescaledb extension, I also shared this on timescale forum but=20
then I realised the issue is postgresql related.)

  When running iostat I see a constant 7-10MB/s write by postgres, and=20
this just doesn't add up for me  and I'm fully stuck with this. Even=20
with the row overhead it should be around 20Mb / 5 mins ! Even with=20
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=20
them do (I use a time filter). Let's say 70% does (which I dont think).=20
There we update two timestamps, and two ints. This still doesnt add up=20
for me. Even if we talk about 50MB of records, that should be 0,16MB/s=20
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.=20
The only thing that made the scenario realistic is disabling fsync=20
(which I know I must not, but for the experiment I did). That eased the=20
write load to 0.6MB/s.

  I also found that the 16MB WAL segment got 80+ MB written into it=20
before being closed. So what's happening here? Does fsync cause the=20
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=20
more is being written to WAL than the actual useful data.

All help is greatly appreciated.

Thanks!

Andr=C3=A1s

  ---
Olcs=C3=B3 Virtu=C3=A1lis szerver:
http://www.ProfiVPS.hu

T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu
--=_2e9186444c9bdebb9502bc0f7fb8e5b3
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset=UTF-8

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; charset=
=3DUTF-8" /></head><body style=3D'font-size: 10pt; font-family: Verdana,Gen=
eva,sans-serif'>
<p>Hi there,&nbsp;</p>
<p>&nbsp;I've been struggling with very high write load on a server.&nbsp;<=
/p>
<p><span>&nbsp;We are collecting around 400k values each 5 minutes into a h=
ypertable. (We use timescaledb extension, I also shared this on timescale f=
orum but then I realised the issue is postgresql related.)&nbsp;</span></p>
<div id=3D"signature">
<div class=3D"pre" style=3D"margin: 0; padding: 0; font-family: monospace">
<p>&nbsp;When running iostat I see a constant 7-10MB/s write by postgres, a=
nd this just doesn&rsquo;t add up for me<span>&nbsp;</span><img class=3D"em=
oji" title=3D":frowning:" src=3D"https://www.timescale.com/forum/images/emo=
ji/twitter/frowning.png?v=3D12" alt=3D":frowning:" width=3D"20" height=3D"2=
0" /><span>&nbsp;</span>and I&rsquo;m fully stuck with this. Even with the =
row overhead it should be around 20Mb / 5 mins ! Even with indeces this 7-1=
0MB/s constant write is inexplicable for me.</p>
<p>&nbsp; The writes may trigger an update in an other table, but not all o=
f them do (I use a time filter). Let&rsquo;s say 70% does (which I dont thi=
nk). 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!</p>
<p>&nbsp;So I dag in and found it was WAL, of course, what else.&nbsp;</p>
<p>&nbsp;Tweaking all around the config, reading forums and docs, to no ava=
il. The only thing that made the scenario realistic is disabling fsync (whi=
ch I know I must not, but for the experiment I did). That eased the write l=
oad to 0.6MB/s.</p>
<p>&nbsp;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 f=
ile to be written out again and again?&nbsp;</p>
<p>&nbsp;I checked with pg_dump, the content is as expected.&nbsp;</p>
<p>&nbsp;We are talking about some insane data overhead here, two magnitude=
s more is being written to WAL than the actual useful data.&nbsp;</p>
<p><br /></p>
<p>All help is greatly appreciated.&nbsp;</p>
<p>Thanks!</p>
<p>Andr&aacute;s</p>
<p><br /></p>
<p><br /></p>
---<br />Olcs&oacute; Virtu&aacute;lis szerver:<br /><a href=3D"http://www=
=2EProfiVPS.hu" target=3D"_blank" rel=3D"noopener noreferrer">http://www.Pr=
ofiVPS.hu</a><br /><br />T&aacute;mogat&aacute;s: <a href=3D"mailto:Support=
@ProfiVPS.hu">Support@ProfiVPS.hu</a></div>
</div>
</body></html>

--=_2e9186444c9bdebb9502bc0f7fb8e5b3--