0

We're using foreign data wrappers in a database which points to another server (which is a read-only replica). We run scheduled jobs using python ( more on this here: https://github.com/sqlalchemy/sqlalchemy/discussions/8348 ) and lately we're facing an issue with a specific query (select statement with cte) - this query runs every hour on 10+ workers (python processes) each with their own conditions. When I run this same query on the original server it takes ~6s, using fdw it's around 2-3 minutes. Since we reached 10+ workers these queries are stuck in an "active" state, I can see them is session manager, and after 20 minutes or so I get the following error: SSL SYSCALL error: EOF detected. (The max connections option is set to 200.) After a few of the workers fail with this error, the last ones fail with the following:

ERROR:app.services.cikk.main:(psycopg2.errors.ConnectionFailure) SSL SYSCALL error: EOF detected
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.
CONTEXT:  remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ

The postgres_fdw doc says:

The remote transaction uses SERIALIZABLE isolation level when the local transaction has SERIALIZABLE isolation level; otherwise it uses REPEATABLE READ isolation level. [...] That behavior would be expected anyway if the local transaction uses SERIALIZABLE or REPEATABLE READ isolation level, but it might be surprising for a READ COMMITTED local transaction.

This means that the server keeps read and write locks until the end of the transacion, and the read locks are released as soon as the select operation is performed - but it never finishes. Maybe there's a deadlock (since 10+ queries try to use the same tables on the remote server)? If so how can I overcome this issue? Does this mean I can only make queries "synchronously" using fdw to make this work?

postgres version:

  • PostgreSQL 12.10
  • (Debian 12.10-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

server keepalive settings:

  • tcp_keepalives_idle: 7200
  • tcp_keepalives_interval: 75
  • tcp_keepalives_count: 9

Thanks for the help in advance!

UPDATE:

I think I figured it out.

  • I had multiple ~3min queries running simultaneously (these queries used the same tables from a foreign server) and they wouldn't finish
  • I started these manually to monnitor what's going on using pg_stat_activity as @jjanes suggested
  • What I saw is all of the queries were in an active state, the wait_event_type was IO and the wait_event was BufFileWrite
  • Read into those a little bit to find out what's going on:
  • wait_event_type - IO: The type of event for which the backend is waiting. - which is pretty self explanatory - and if the value is IO it means that some IO operation is in progress
  • Since the wait_event was BufFileWrite I looked into it what it means exactly: Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileWrite is seen when these in memory buffers are filled and written to disk.
  • So what could cause this? One site (link down below) says: Large waits on BufFileWrite can indicate that your work_mem setting is too small to capture most of your sorts and hashes in memory and are being dumped to disk. and Ensure your sorts largely occur in memory rather than writing temp files by using explain analyze on your query ...
  • I checked our work_mem value with show work_mem; which was 20971kB - I thought it should be enough so looked further
  • The clue here for me was the explain analyze part. I created the foreign server with use_remote_estimate: true, which means When use_remote_estimate is true, postgres_fdw obtains row count and cost estimates from the remote server
  • The solution was to set this property (use_remote_estimate) to false and now it seems to be working the way it should.

Useful links: https://www.postgresql.org/docs/current/monitoring-stats.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/apg-waits.iobuffile.html https://docs.dbmarlin.com/docs/kb/wait-events/postgresql/buffilewrite/ https://www.postgresql.org/docs/current/runtime-config-resource.html https://www.postgresql.org/docs/current/postgres-fdw.html

5
  • These are the errors I was able to find: SSL SYSCALL error: EOF detected server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. and FATAL: connection to client lost
    – K. Anye
    Aug 8, 2022 at 10:14
  • If both ends claim the other end hung up, that usually means that a gateway/firewall between them is interfering with the connections.
    – jjanes
    Aug 8, 2022 at 11:59
  • Can you run the queries in parallel with something simpler, like psql? Or with psycopg2, but without sqlalchemy? Also monitor the db server with top for example to see if it is actually trying to do any work? (or connect to it with psql and monitor by selecting from pg_stat_activity.)
    – jjanes
    Aug 8, 2022 at 12:03
  • The inability to run several queries at once over different connections is not a general "feature" of PostgreSQL. You have something weird going on here.
    – jjanes
    Aug 8, 2022 at 12:08
  • I tried it in DBeaver first (it was easy to start 10 concurrent queries in it) and I experience the same thing here, some extra info is all queries are in an active state (running the same amount of time using sqlalchemny) and the wait_event_type is IO, the wait_event is 'BufFileWrite'. About BufFileWrite this is what I found: Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileWrite is seen when these in memory buffers are filled and written to disk. I'll look into it deeper.
    – K. Anye
    Aug 8, 2022 at 14:58

1 Answer 1

0

Its possible the remote server is low on resources . Python creates separate process and there might be memory crunch . Try increasing the memory of remote server or try changing the keepalives parameters of your Postgres connection. Please refer to this link and see if that solves your problem - https://www.roelpeters.be/error-ssl-syscall-error-eof-detected/

2
  • Thanks for the tips! I use SQLAlchemy and I'm not sure how to set keepalive paramters there with the sqlalchemy.Engine, but I'll look into it. The thing that I still don't understand is that this query is supposed to finish in ~4 minutes max. How come none of them finish and it needs ~20 minutes to drop one connection - and after that all of them will fail at the same time?
    – K. Anye
    Aug 5, 2022 at 13:09
  • Its possible the remote server is low on resources. - This is one part of the problem, the work_mem wasn't enough for them they they were running.
    – K. Anye
    Aug 9, 2022 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.