From: Justin Pryzby <pryzby@telsasoft.com>
To: ldh@laurent-hasson.com
Cc: pgsql-performance@postgresql.org
Subject: Re: An I/O error occurred while sending to the backend (PG 13.4)
Date: Wed, 23 Feb 2022 19:04:15 -0600
Message-ID: <20220224010415.GS9008@telsasoft.com> (raw)
In-Reply-To: <MN2PR15MB256034941F279136BA555236853D9@MN2PR15MB2560.namprd15.prod.outlook.com>
References: <MN2PR15MB2560DC71C217FF03AC9082F5856B9@MN2PR15MB2560.namprd15.prod.outlook.com>
<20211204175926.GC17618@telsasoft.com>
<MN2PR15MB256038B438B8990D08B4A991856B9@MN2PR15MB2560.namprd15.prod.outlook.com>
<MN2PR15MB256034941F279136BA555236853D9@MN2PR15MB2560.namprd15.prod.outlook.com>
On Thu, Feb 24, 2022 at 12:47:42AM +0000, ldh@laurent-hasson.com wrote:
> On Sat, Dec 04, 2021 at 05:32:10PM +0000, ldh@laurent-hasson.com wrote:
> > I have a data warehouse with a fairly complex ETL process that has
> > been running for years now across PG 9.6, 11.2 and now 13.4 for the
> > past couple of months. I have been getting the error "An I/O error
> > occurred while sending to the backend" quite often under load in 13.4
> > which I never used to get on 11.2. I have applied some tricks, particularly
> > with the socketTimeout JDBC configuration.
> It'd be nice to see a network trace for this too. Using tcpdump or
> wireshark. Preferably from the client side.
>
> Hello Justin,
>
> It has been ages! The issue has been happening a bit more often recently, as much as once every 10 days or so. As a reminder, the set up is Postgres 13.4 on Windows Server with 16cores and 64GB memory. The scenario where this occurs is an ETL tool called Pentaho Kettle (V7) connecting to the DB for DataWarehouse workloads. The tool is Java-based and connects via JDBC using postgresql-42.2.5.jar. There are no particular settings besides the socketTimeout setting mentioned above.
>
> The workload has some steps being lots of quick transactions for dimension tables for example, but some fact table calculations, especially large pivots, can make queries run for 40mn up to over an hour (a few of those).
>
> I caught these in the logs at the time of a failure but unsure what to make of that:
>
> 2022-02-21 02:10:43.605 EST [1368] LOG: unexpected EOF on client connection with an open transaction
> 2022-02-21 02:10:43.605 EST [3304] LOG: could not receive data from client: An existing connection was forcibly closed by the remote host.
>
> 2022-02-21 02:10:43.605 EST [3304] LOG: unexpected EOF on client connection with an open transaction
> 2022-02-21 02:31:38.808 EST [1704] LOG: setsockopt(TCP_USER_TIMEOUT) not supported
I suggest to enable CSV logging, which has many more columns of data.
Some of them might provide an insight - I'm not sure.
log_destination=csvlog (in addition to whatever else you have set).
And the aforementioned network trace. You could set a capture filter on TCP
SYN|RST so it's not absurdly large. From my notes, it might look like this:
(tcp[tcpflags]&(tcp-rst|tcp-syn|tcp-fin)!=0)
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, ldh@laurent-hasson.com
Subject: Re: An I/O error occurred while sending to the backend (PG 13.4)
In-Reply-To: <20220224010415.GS9008@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox