Static Varible vs PDO::ATTR_PERSISTENT

Question

I have a database class that I developed. But I have doubts about performance in case of load. There are two issues that I was curious about and couldn't find the answer even though I searched.

When the database connection is bound to a static variable in the class,

class DB
{
    static $connect;
    ......
    function __construct() 
    {      
        try {
             self::$connect = new PDO("{$this->db_database}:host={$this->db_host};dbname={$this->db_name};charset=utf8mb4", "{$this->db_username}", "{$this->db_password}");
             self::$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
             self::$connect->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8mb4");
        } catch ( PDOException $e ){
             echo  '<b>ERROR: </b>'.$e->getMessage();
             exit;
        }
    }
}

PDO::ATTR_PERSISTENT => true

Does it have an equivalent ability?

Also, I didn't fully understand the pdo permalink logic, it uses the existing connection instead of opening a separate connection for each user. But how does he use the existing link here? For example "ip address" etc.

Thank you for your help.

Answer 1

Let me approach the issues from a different direction.

A program should have only one connection to the database. (There are rare exceptions.) Your code, as it stands, seems to be inconsistent. It has a single ("static") connection, yet the class can be instantiated multiple times, thereby connecting multiple times. (I don't want to depend on anything "persist" to clean up the inconsistency.)

Either make the class a singleton or otherwise avoid being able to call __construct a second time. One approach goes something like this:

class DB {
    private static $connect;
    ......
    public function __construct() {      
        if (! self::$connect) {
             self::$connect = ...
        }
    }
    public function Fetch(...) {
        self::$connect->...
        return ...;
    }

$con = new DB();
$data = $con->Fetch(...);

(plus suitable try/catch)

Note that that allows you to sub-class as needed.

Another approach might involve preventing the use of new:

    private function __construct() { ... }

plus having some public method invoke that constructor.

Here's another approach. It can be used on an existing class that you don't want to (or can't) modify:

function GetConnection() {
    static $db;
    if (! $db) {
        $db = new ...;
    }
    return $db;
}

$db = GetConnection();
$db->Fetch(...)'

As for "connection pooling", it is of limited use with MySQL. (Other products need it much more than MySQL does.) In my opinion, don't worry about such.

Do not use "auto-reconnect". If the connection dies in the middle of a transaction and is automatically restarted, then the first part of the transaction will be rolled back while the rest might get committed. That is likely to lead to data inconsistency.

Singletons, statics, globals, void*, critical sections all make me cringe. When I need such, I rush to find a way to "hide" it, even if that means writing cryptic code in some class(es).

For performance, MySQL really needs a single connection throughout the program. I compromise by hiding the connection in a "static" that serves at a "global". Then I hide that inside the class that I use to abstract the object(s).

I agree with Karwin's [now delete] Answer -- that this discussion is "much ado about nothing". MySQL performance is mostly about indexing, query formulation, and even the architecture of the application. Not about connections, common code elimination, redundant function calls, etc.