Re: An I/O error occurred while sending to the backend (PG 13.4)
On Sat, Dec 04, 2021 at 07:18:06PM +, l...@laurent-hasson.com wrote: > It's a remote server, but all on a local network. Network performance is I am > sure not the issue. Also, the system is on Windows Server. What are you > expecting to see out of a tcpdump? I'll try to get PG logs on the failing > query. I'd want to know if postgres sent anything to the client, like TCP RST, or if the client decided on its own that there had been an error. -- Justin
RE: An I/O error occurred while sending to the backend (PG 13.4)
> -Original Message- > From: Justin Pryzby > Sent: Saturday, December 4, 2021 12:59 > To: l...@laurent-hasson.com > Cc: pgsql-performa...@postgresql.org > Subject: Re: An I/O error occurred while sending to the backend (PG > 13.4) > > On Sat, Dec 04, 2021 at 05:32:10PM +, l...@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. > > > > So my first question is whether anyone has any idea why this is > happening? My hardware and general PG configuration have not > changed between 11.2 and 13.4 and I NEVER experienced this on 11.2 in > about 2y of production. > > > > Second, I have one stored procedure that takes a very long time to run > (40mn more or less), so obviously, I'd need to set socketTimeout to > something like 1h in order to call it and not timeout. That doesn't seem > reasonable? > > Is the DB server local or remote (TCP/IP) to the client? > > Could you collect the corresponding postgres query logs when this > happens ? > > It'd be nice to see a network trace for this too. Using tcpdump or > wireshark. > Preferably from the client side. > > FWIW, I suspect the JDBC socketTimeout is a bad workaround. > > -- > Justin It's a remote server, but all on a local network. Network performance is I am sure not the issue. Also, the system is on Windows Server. What are you expecting to see out of a tcpdump? I'll try to get PG logs on the failing query. Thank you, Laurent.
Re: An I/O error occurred while sending to the backend (PG 13.4)
On Sat, Dec 04, 2021 at 05:32:10PM +, l...@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. > > So my first question is whether anyone has any idea why this is happening? My > hardware and general PG configuration have not changed between 11.2 and 13.4 > and I NEVER experienced this on 11.2 in about 2y of production. > > Second, I have one stored procedure that takes a very long time to run (40mn > more or less), so obviously, I'd need to set socketTimeout to something like > 1h in order to call it and not timeout. That doesn't seem reasonable? Is the DB server local or remote (TCP/IP) to the client? Could you collect the corresponding postgres query logs when this happens ? It'd be nice to see a network trace for this too. Using tcpdump or wireshark. Preferably from the client side. FWIW, I suspect the JDBC socketTimeout is a bad workaround. -- Justin
An I/O error occurred while sending to the backend (PG 13.4)
Hello, I hope this email finds you all well! 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. So my first question is whether anyone has any idea why this is happening? My hardware and general PG configuration have not changed between 11.2 and 13.4 and I NEVER experienced this on 11.2 in about 2y of production. Second, I have one stored procedure that takes a very long time to run (40mn more or less), so obviously, I'd need to set socketTimeout to something like 1h in order to call it and not timeout. That doesn't seem reasonable? I understand that there is not just Postgres 13.4, but also the JDBC driver. I ran production for a several days on V42.2.19 (which had run with PG11.2 fine) to try and got the error a couple of times, i.e., the same as with 42.2.24, so I am not sure this has to do with the JDBC Driver. So I am not sure what to do now. I do not know if there are some related configuration options since 11.2 that could trigger this issue that I missed, or some other phenomenon going on. I have always had a few "long running" queries in the system (i.e., > 20mn) and never experienced this on 11.2, and experiencing this maybe once or twice a week on 13.4, seemingly randomly. So sometimes, the queries run fine, and others, they time out. Weird. Thanks, Laurent.
