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 1pufho-0004A2-5p for pgsql-performance@arkaria.postgresql.org; Thu, 04 May 2023 20:37:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pufhl-0002dm-Km for pgsql-performance@arkaria.postgresql.org; Thu, 04 May 2023 20:37:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pufhl-0002d2-4l for pgsql-performance@lists.postgresql.org; Thu, 04 May 2023 20:37:33 +0000 Received: from web.profivps.hu ([2a02:730:1220::87]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pufhh-000Xvz-B9 for pgsql-performance@lists.postgresql.org; Thu, 04 May 2023 20:37:31 +0000 Received: from localhost (localhost.localdomain [127.0.0.1]) by web.profivps.hu (Postfix) with ESMTP id 19FEF52C0EB4; Thu, 4 May 2023 22:37:25 +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=1683232642; x=1685047043; bh=lTE63K3 43p1Aw5XmqmMQbPQf958g7A+XdEJoq6X/lYc=; b=g7qjnjNJdtU6c8D2sIl197C pB0derMg4uxRIqhHxZoyLS73BcSOfisnnnkLwuCEWMcafln40poSpjR9f5mlKokJ VBQ1zypGPgqWZHv4difhngkS4P1CSbrboW0foSOirEzIzic2lByaljaLX+59bem3 M1Ai/PcrUk1C3fbnN6apylfy96JB+cTjDA2RP8cdDFr7ShXZlqHqG7kY4JQDOSl+ BhsuOqTkXznjlV6sFhYp5kC4/TtbMMIVP1+hY8DLX71PeBKgOrHRlMVnkSR08+rJ XfTQMbJXetj0t132c+f2Deb3ZPtm8IHWcxStJueVig27efbTN0a2X4wpzFeBgcg= = 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 0kMgYVui2LRF; Thu, 4 May 2023 22:37:22 +0200 (CEST) Received: from profivps.hu (localhost [IPv6:::1]) by web.profivps.hu (Postfix) with ESMTP id 8E6D252C0E80; Thu, 4 May 2023 22:37:22 +0200 (CEST) MIME-Version: 1.0 Date: Thu, 04 May 2023 22:37:22 +0200 From: ProfiVPS Support To: Andres Freund Cc: pgsql-performance@lists.postgresql.org Subject: Re: Fsync IO issue In-Reply-To: <20230504192146.bkis7vuj65xks7vd@awork3.anarazel.de> References: <060c533e971eec98e1f179a2b2846454@profivps.hu> <20230504192146.bkis7vuj65xks7vd@awork3.anarazel.de> User-Agent: Roundcube Webmail/1.4.11 Message-ID: X-Sender: support@profivps.hu Organization: ProfiVPS.hu Content-Type: multipart/alternative; boundary="=_4fac4b0e4273a0527084e0f12eec43e0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_4fac4b0e4273a0527084e0f12eec43e0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: quoted-printable Hi, thank you for your response :) Yes, that's exactly what's happening and I understand the issue with=20 fsync in these cases. But I see no workaround about this as the data is=20 ingested one-by-one (sent by collectd) and a db function handles it (it=20 has to do lookup and set state in a different table based on the=20 incoming value). I feel like ANYTHING would be better than this. Even risking loosing=20 _some_ of the latest data in case of a server crash (if it crashes we=20 lose data anyways until restart, ofc we could have HA I know and we will=20 when there'll be a need) . Around 100 times the write need for wall than the useful data! That's=20 insane. This is actually endangering the whole project we've been=20 working on for the last 1.5 years and I face this issue after 100k=20 devices have been added for a client. So I'm between a rock and a hard=20 place :( Ye, I think this is called "experience", but I must be honest, I was=20 not expecting this at all :( However, collectd's plugin does have an option to increase commit=20 interval, but that kept the records locked and it caused strange issues,=20 that's why I disabled it. I tried now to add that setting back and it=20 does ease the situation somewhat with write spikes on commit. All in all, thank you for your help. Honestly, after todays journey I=20 thought that's the issue, but didn't want to believe it. Thanks, Andr=C3=A1s 2023-05-04 21:21 id=C5=91pontban Andres Freund ezt =C3=ADrta: > Hi, >=20 > On 2023-05-04 19:31:45 +0200, ProfiVPS Support wrote: >=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 but=20 >> then I >> realised the issue is postgresql related.) >=20 > I don't know how timescale does its storage - how did you conclude this= =20 > is > about postgres, not about timescale? Obviously WAL write patterns=20 > depend on > the way records are inserted and flushed. >=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 whole=20 >> file to >> be written out again and again? >=20 > One possible reason for this is that you are committing small=20 > transactions > very frequently. When a transaction commits, the commit records needs=20 > to be > flushed to disk. If the transactions are small, the next commit might=20 > reside > on the same page - which needs to be written out again. Which of course= =20 > can > increase the write rate considerably. >=20 > Your workload does not sound like it actually needs to commit in tiny > transactions? Some larger batching / using longer lived transactions=20 > might > help a lot. >=20 > Another possibility is that timescale does flush WAL too frequently for= =20 > some > reason... >=20 > Greetings, >=20 > Andres Freund --- Olcs=C3=B3 Virtu=C3=A1lis szerver: http://www.ProfiVPS.hu T=C3=A1mogat=C3=A1s: Support@ProfiVPS.hu --=_4fac4b0e4273a0527084e0f12eec43e0 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=UTF-8

Hi, 

 thank you for your response :)

 Yes, that's exactly what's happening and I understand the issue wi= th fsync in these cases. But I see no workaround about this as the data is = ingested one-by-one (sent by collectd) and a db function handles it (it has= to do lookup and set state in a different table based on the incoming valu= e). 

 I feel like ANYTHING would be better than this. Even risking loosi= ng _some_ of the latest data in case of a server crash (if it crashes we lo= se data anyways until restart, ofc we could have HA I know and we will when= there'll be a need) . 

 Around 100 times the write need for wall than the useful data! Tha= t's insane. This is actually endangering the whole project we've been worki= ng on for the last 1.5 years and I face this issue after 100k devices have = been added for a client. So I'm between a rock and a hard place :( 

 Ye, I think this is called "experience", but I must be honest, I w= as not expecting this at all :(

 However,  collectd's plugin does have an option to increase c= ommit interval, but that kept the records locked and it caused strange issu= es, that's why I disabled it. I tried now to add that setting back and it d= oes ease the situation somewhat with write spikes on commit. 

 All in all, thank you for your help. Honestly, after todays journe= y I thought that's the issue, but didn't want to believe it.


Thanks,

András


2023-05-04 21:21 id=C5=91pontban Andres Freund ezt &i= acute;rta:

= Hi,

On 2023-05-04 19:31:45 +0200, ProfiVPS Support wrote:
 We are collecting around 400k values each 5 minu= tes into a hypertable. (We
use timescaledb extension, I also shared th= is on timescale forum but then I
realised the issue is postgresql rela= ted.)

I don't know how timescale does its storage - how did you conclude th= is is
about postgres, not about timescale? Obviously WAL write pattern= s depend on
the way records are inserted and flushed.


 I also found that the 16MB WAL segment got 80+ M= B written into it before
being closed. So what's happening here? Does = fsync cause the whole file to
be written out again and again?
One possible reason for this is that you are committing small transac= tions
very frequently. When a transaction commits, the commit records = needs to be
flushed to disk. If the transactions are small, the next c= ommit might reside
on the same page - which needs to be written out ag= ain. Which of course can
increase the write rate considerably.
Your workload does not sound like it actually needs to commit in tinytransactions? Some larger batching / using longer lived transactions mi= ght
help a lot.

Another possibility is that timescale does = flush WAL too frequently for some
reason...

Greetings,

Andres Freund



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

Támogatás: Support@ProfiVPS.hu
--=_4fac4b0e4273a0527084e0f12eec43e0--