Sunday, January 22, 2023

PHP’s PDO, Single-Process Testing, and 'Too Many Connections'

Quite some time ago now, I ran into a problem with running a test suite: at some point, it would fail to connect to the database, due to too many connections in use.

Architecturally, each connection sent a PSR-7 Request through the HTTP layer, which caused the back-end code under test to connect to the database in order to fulfill the request.  All of these resources (statement handles and the database handle itself) should have been out of scope be the end of the request.

But every PDOStatement has a reference to its parent PDO object, and apparently each PDO keeps a reference to all of its PDOStatements.  There was no memory pressure (virtually all other allocations were being cleaned up between tests), so PHP wasn’t trying to collect cycles, and the PDO objects were keeping connections open the whole duration of the test suite.

Lowering the connection limit in the database engine (a local, anonymized copy of production data) caused the failure to occur much sooner in testing, proving that it was an environmental factor and not simply “unlucky test ordering” that caused the failure.

Using phpunit’s --process-isolation cured the problem entirely, but at the cost of a lot of time overhead.  This was also expected: with the PHP engine shut down entirely between tests, all of its resources (including open database connections) were cleaned up by the OS.

Fortunately, I already had a database connection helper for other reasons: loading credentials securely, setting up exceptions as the error mode, choosing the character set, and retrying on failure if AWS was in the middle of a failover event (“Host not found”, “connection refused”, etc.) It was a relatively small matter to detect “Too many connections” and, if it was the first such error, issue gc_collect_cycles() before trying again.

(Despite the “phpunit” name, there are functional and integration test suites for the project which are also built on phpunit.  Then, the actual tests to run are chosen using phpunit --testsuite functional, or left at the default for the unit test suite.)

No comments: