persistent database connection PDO::ATTR_PERSISTENT

[bfinlay]

I followed the discussion in delight-im/PHP-DB#2

One of the shortcomings of the solution provided there is that it bypasses the convenient $app->db() mechanism configured by the dotenv file.

Additionally, when implementing the controller/s as a class with static methods, there is no convenient place to store a db instance created from a PDO connection. This is a minor concern b/c a static method can be used to create the db instance and return it, and b/c of the PHP architecture this does not create a performance penalty. But it gets questions from developers. The $app->db() mechanism looks more correct in addition to being more convenient.

One solution for this would be to add a configuration variable to the dotenv file to activate/deactivate ATTR_PERSISTENT.

The benchmark test results go from 983 req/sec with 461 errors due to load and timeouts without using ATTR_PERSISTENT, to ~3500 req/sec with 0 errors using ATTR_PERSISTENT.

Addressing some of the discussion points in delight-im/PHP-DB#2 regarding table locking and persistent connections, it is common for me to have API endpoints that are read-only against the database and do not lock tables.

Benchmark test is here so you can see how delight-im/PHP-DB#2 was followed to address persistent connections: https://github.com/bfinlay/FrameworkBenchmarks/blob/delight.im/frameworks/PHP/delight.im/app/Controller.php

[ocram]

Thanks!

While persistent connections may be great in benchmarks, I doubt they’re useful in real-world applications as often.

Anyway, it cannot hurt to enable this feature (while documenting that you often don’t need this). That’s why I reopened delight-im/PHP-DB#2 now.

And, of course, you’re right in that one should be able to toggle this feature via the configuration file.