I'm using PHP's PDO layer for data access in a project, and I've been reading up on it and seeing that it has good innate support for persistent DB connections. I'm wondering when/if I should use them. Would I see performance benefits in a CRUD-heavy app? Are there downsides to consider, perhaps related to security?
If it matters to you, I'm using MySQL 5.x.
Also, don't use persistent connections if you're using Apache in prefork mode. It uses one connection for each child process, which can ramp up fairly quickly.
BlaM, I'm not getting your last paragraph. Doesn't persistent connections allow you to reuse them (that's the whole point?) so you don't need 2000 connections since they will be reused? Or do you mean each client has a unique username? Even so, wouldn't the old persistent connections automatically close when they are done to make way for new connections?
Pacerier: I meant clients each with its own user name. In that case old connections will NOT close to make new connections possible, because the limit of allowed connections is managed by MySQL and the persistence of connections is managed from the PHP side - so from PHPs end of view the database just does not allow to connect. It does not know that the limit was reached and that closing old connections will help.
BlaM, Hmm, are you saying that the default C libmysql also has this problem? As for PHP, this seems to be a bug that needs to be fixed on the PHP side then. The PHP side should query the max from the server and release it's LRU if the number it has reaches max.
I never used the C mysql library, so I don't know. However I don't think clients (PHP or C) can do a lot about this fully automated. I'm not sure if there is a PHP.INI setting to allow manual configuration, but fully automated solutions would probably start to act unpredictable as soon as the situation gets more complicated (e.g. more than just one client/webserver accessing one database server).
Citation needed for "mysql_close is a no-operation (no-op) for connections that are created using mysql_pconnect". For mysqli, it's certainly not a no-op: php.net/manual/en/mysqli.persistconns.php
"Objectively Oriented" link is down.
I dislike this answer because benchmarking is often done in a very inaccurate way. It could look very good with your computer accessing it in "other than real-world access patterns" (which is typical of tests). Timing is hugely important as are several other "hard to replicate" conditions that occur often in real life. I'd suggest thinking it through and avoiding it unless you're definitely going to need it. Pre-optimization is the first sin.
This should be a comment instead of an answer.