I'm working on a Proof of Concept with QuestDB, and we're occasionally experiencing
connection drops when querying the database via Python using psycopg. The error seems to
originate from within the psycopg library, suggesting that the connection to QuestDB might
be dropping unexpectedly. The QuestDB log is quite verbose, but I'm looking for specific
keywords that could provide clues about these disconnection issues. Below is an anonymized
example of the log entries when a disconnection occurred:
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID'
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID'
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.ReaderPool closed 'table_name~ID'
[at=0:0, reason=IDLE]
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I pg-server disconnected [ip=IP_ADDRESS, fd=FILE_DESCRIPTOR,
src=idle]
This was preceded by the server opening a partition for a query:
YYYY-MM-DDTHH:MM:SS.SSSSSSZ I internal.component.TableReader open partition
/var/lib/questdb/db/table_name~ID/YYYY-MM-DD.ID
Any suggestions on how to diagnose or resolve this issue?
Searching for "disconnect" in the logs can help identify relevant
entries. If your queries are particularly long-running, consider the possibility that this
is related to the query.timeout.sec configuration property, which is set to 60 seconds
by default. You might need to increase this setting to prevent premature disconnection.
To adjust the query.timeout.sec setting, locate it in the db_root_dir/conf/server.conf
file. Also, think about whether your Python application might be pausing while processing
the queried data, as this could trigger an idle connection timeout, leading to the
disconnection.
It's also wise to examine the pg.net.connection.timeout setting, defaulting to
300,000 milliseconds (5 minutes). Consider increasing this to 900,000 milliseconds (15
minutes) to accommodate longer-running queries.
Ensure your server recognizes the new values by checking the server
logs or executing the SHOW PARAMETERS; SQL command. This command should list all current
configuration settings, including those set via environment variables.
Adjusting these settings should be carefully balanced with your specific workload and operational environment to avoid unintended side effects, such as excessively prolonged query execution or resource strain.