Opened 7 weeks ago

Last modified 2 weeks ago

#5517 new defect

Posgresql synchronous API calls hang until TCP connections timeout when connectivity is lost

Reported by: tmark Owned by:
Priority: medium Milestone: Kea1.4
Component: database-pgsql Version: git
Keywords: Cc:
CVSS Scoring: Parent Tickets:
Sensitive: no Defect Severity: N/A
Sub-Project: DHCP Feature Depending on Ticket:
Estimated Difficulty: 0 Add Hours to Ticket: 0
Total Hours: 0 Internal?: no


Currently we use synchronous calls for executing prepared statements with all our back ends. Testing with Postgresql shows that when network connectivity is lost calls such as PQExecPrepared() will hang until the TCP time out occurs. Between two VMs hosted on my Ubuntu box this is taking 15 minutes.

There is an asynchronous version of the call, PQsendQueryPrepared, which could be used instead. We should give some consideration to doing DB operations asynchronously as the server is unresponsive during this hang time.


Change History (4)

comment:1 Changed 7 weeks ago by fdupont

Hum, I am not sure the DHCP server can do many useful things when the connection to the database is broken... And for legal logs it is even worse: broken service can be preferred to broken logs.
BTW we have some timers in not fully implemented DB parameters (2 problems: there are not in the syntax, the unit is not clear e.g. s vs ms between Cassandra and XXXsql). There is a Cassandra parameter ticket where this can (shall!) be addressed.

comment:2 Changed 7 weeks ago by tmark

This ticket calls for analysis and/or design suggestions prior to any solution attempts.

comment:3 Changed 6 weeks ago by tomek

  • Component changed from database-all to database-pgsql
  • Milestone changed from Kea-proposed to Kea1.4

As discussed on 2018-02-01 call, moving to 1.4 as medium.

comment:4 Changed 2 weeks ago by tmark

FYI, Similar testing with MySQL revealed a hang-time of almost 16 minutes. That's troubling.

I also found a way in MySQL to limit the exposure time:

+ int read_timeout = 30;
+ result = mysql_options(mysql_, MYSQL_OPT_READ_TIMEOUT, &read_timeout);
+ if (result != 0) {
+ isc_throw(DbOpenError?, "unable to set read_timeout " <<
+ mysql_error(mysql_));
+ }

Using this, the query times out in 30 seconds, if the link is dropped.

Last edited 2 weeks ago by tmark (previous) (diff)
Note: See TracTickets for help on using tickets.