Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pucxK-0002Jx-J9 for pgsql-performance@arkaria.postgresql.org; Thu, 04 May 2023 17:41:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pucxI-0007EN-1y for pgsql-performance@arkaria.postgresql.org; Thu, 04 May 2023 17:41:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pucxH-0007Dm-NO for pgsql-performance@lists.postgresql.org; Thu, 04 May 2023 17:41:23 +0000 Received: from web.profivps.hu ([84.21.7.15]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pucxA-000ZiF-4q for pgsql-performance@lists.postgresql.org; Thu, 04 May 2023 17:41:22 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by web.profivps.hu (Postfix) with ESMTP id 4E22E52C11EA for ; Thu, 4 May 2023 19:41:15 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=profivps.hu; h= content-type:content-type:organization:message-id:user-agent :references:in-reply-to:subject:subject:from:from:date:date :mime-version; s=default; t=1683222073; x=1685036474; bh=A2UhPbc LohwpZJODrmVb+1UmjUQ/JsA8r4GA+RI7n6w=; b=TgICGCP6FuuYH5ZsgUhASHQ J+C3kRqZx16ROPJbpMOcsB3Nmp0+9hOGPPIQ3abB8t5be/7Ag3XPxQ3Y7HTaXYka wKHpgrmb1I5oV/GHLdjro1jJwEaK7RU5BZ4+TuZWLjJ3n66a+hlc+/LJi/sY6Pcz BVwOwEkkzYT4JwEpMVz+aC9ShHdMBs7AQril0Up7w2kkOVUX4RCKZAFQGOLElR0U xmeKcEhtJYtEiYXdRR+P1/EZd2kJVl7e+PU8tnbwkKSi7X7mN6BVDOD8Bhg6rxFX sDIwEJoYoJsSkY9+H3tq15K0UZZUyp6aZMG88FOG+jvOhpIGbYvflU4TpQ4lpmw= = X-Virus-Scanned: amavisd-new at web.profivps.hu Received: from web.profivps.hu ([127.0.0.1]) by localhost (web.profivps.hu [127.0.0.1]) (amavisd-new, port 10026) with LMTP id YLo-qu_-mpYO for ; Thu, 4 May 2023 19:41:13 +0200 (CEST) Received: from profivps.hu (localhost [IPv6:::1]) by web.profivps.hu (Postfix) with ESMTP id BC72052C1972 for ; Thu, 4 May 2023 19:41:13 +0200 (CEST) MIME-Version: 1.0 Date: Thu, 04 May 2023 19:41:13 +0200 From: ProfiVPS Support To: pgsql-performance@lists.postgresql.org Subject: Re: Fsync IO issue In-Reply-To: <060c533e971eec98e1f179a2b2846454@profivps.hu> References: <060c533e971eec98e1f179a2b2846454@profivps.hu> User-Agent: Roundcube Webmail/1.4.11 Message-ID: X-Sender: support@profivps.hu Organization: ProfiVPS.hu Content-Type: multipart/alternative; boundary="=_b81f6138d483a0fcbf2529db64505ef5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_b81f6138d483a0fcbf2529db64505ef5 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Oh, sorry, we are using PostgreSQL 13.10 (Debian 13.10-1.pgdg100+1) on=20 the server with with TimescaleDB 2.5.1 on Debian 10. 2023-05-04 19:31 id=C5=91pontban ProfiVPS Support ezt =C3=ADrta: > Hi there, >=20 > I've been struggling with very high write load on a server. >=20 > We are collecting around 400k values each 5 minutes into a hypertable.=20 > (We use timescaledb extension, I also shared this on timescale forum=20 > but then I realised the issue is postgresql related.) >=20 > 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. >=20 > The writes may trigger an update in an other table, but not all of them= =20 > 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! >=20 > So I dag in and found it was WAL, of course, what else. >=20 > 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. >=20 > 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? >=20 > I checked with pg_dump, the content is as expected. >=20 > We are talking about some insane data overhead here, two magnitudes=20 > more is being written to WAL than the actual useful data. >=20 > All help is greatly appreciated. >=20 > Thanks! >=20 > Andr=C3=A1s >=20 > --- > Olcs=C3=B3 Virtu=C3=A1lis szerver: > http://www.ProfiVPS.hu >=20 > T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu --- Olcs=C3=B3 Virtu=C3=A1lis szerver: http://www.ProfiVPS.hu T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu --=_b81f6138d483a0fcbf2529db64505ef5 Content-Type: multipart/related; boundary="=_2a0f6c0f3bd27bc8b7c0afb3327e2243" --=_2a0f6c0f3bd27bc8b7c0afb3327e2243 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8


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=C5=91pontban ProfiVPS Support ezt= írta:

Hi there, 

 I've been struggling with very high write load on a server. <= /p>

 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.) 

 When running iostat I see a constant 7-10MB/s write by postgres, a= nd this just doesn’t add up for me 3D":frowning:" 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 i= s inexplicable for me.

  The writes may trigger an update in an other table, but not all o= f them do (I use a time filter). Let’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!

 So I dag in and found it was WAL, of course, what else. 

 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.

 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? 

 I checked with pg_dump, the content is as expected. 

 We are talking about some insane data overhead here, two magnitude= s 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.Pr= ofiVPS.hu

Támogatás: Support@ProfiVPS.hu


= ---
Olcsó Virtuális szerver:
http://www.Pr= ofiVPS.hu

Támogatás: Support@ProfiVPS.hu
--=_2a0f6c0f3bd27bc8b7c0afb3327e2243 Content-Transfer-Encoding: base64 Content-ID: <16832220736453ee39afd43836072211@profivps.hu> Content-Type: image/gif; name=blocked.gif Content-Disposition: inline; filename=blocked.gif; size=118 R0lGODlhZAAyAIAAAPrOzgAAACH5BAAAAAAALAAAAABkADIAAAJNhI+py+0Po5y02ouz3rz7D4bi SJbmiabqyrbuC8fyTNf2jef6zvf+DwwKh8Si8YhMKpfMpvMJjUqn1Kr1is1qt9yu9wsOi8fksvls KwAAOw== --=_2a0f6c0f3bd27bc8b7c0afb3327e2243-- --=_b81f6138d483a0fcbf2529db64505ef5--