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, thewait_event_type
wasIO
and thewait_event
wasBufFileWrite
- 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 isIO
it means that some IO operation is in progress- Since the
wait_event
wasBufFileWrite
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.
andEnsure your sorts largely occur in memory rather than writing temp files by using explain analyze on your query ...
- I checked our
work_mem
value withshow 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 withuse_remote_estimate: true
, which meansWhen 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
) tofalse
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
SSL SYSCALL error: EOF detected server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.
andFATAL: connection to client lost
psql
? Or with psycopg2, but without sqlalchemy? Also monitor the db server withtop
for example to see if it is actually trying to do any work? (or connect to it withpsql
and monitor by selecting from pg_stat_activity.)active
state (running the same amount of time using sqlalchemny) and thewait_event_type
isIO
, thewait_event
is 'BufFileWrite'. AboutBufFileWrite
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.