Optimization of Mysql connections

Question

I have a pertinent question regarding the connections in my database.

I was told that every new connection to Mysql a portion of the RAM is reserved for that connection. I have several applications developed in PHP and that connect in the same database. My question is: how do connections with the database work in PHP and whether there is a way to optimize them.

As far as I know, every time the PHP script runs, it opens a connection to the database and this connection usually lasts until the end of the script execution. Following this reasoning, if the same script is executed 10 times by different clients or not at the same time, even if by thousandths of a second, we will have 10 different connections with the bank. Am I right!? This is what happens in practice?

If the above reasoning is correct and new connections to the database are opened every time my PHP script is run, then a portion of RAM is dynamically allocated to each execution of the script, correct? Is there any way to optimize connections with the database in order to reuse open connections? If possible, open a single connection where the script will communicate with the bank as many times as necessary. This is possible?

From now on I appreciate any help. ;)

Answer 1

Hello, I’m not sure if this is valid in your case but I bring you a tip: use the Singleton design pattern. Basically it seeks to use the same instance of an object, and thus the same connection to the database:

class Conexao {
//utilizando singleton
public static $instance;

public function __construct() {
    //construtor vazio singleton
}

public static function getInstance(){
    if(!isset(self::$instance)){
        self::$instance = new PDO('mysql:host=SERVIDOR;dbname=BANCO', 'USUARIO', 'SENHA', array(PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES utf8'));
        self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }
    return self::$instance;
}}

And replace SERVER, DATABASE, USER, PASSWORD with your data, to start a new connection use the following code:

$conexao = Conexao::getInstance();

So you don’t "build" a new object if you already have one built.

Answer 2

Use persistent connections:

Persistent connections are connections that do not close when your script runs out. When a persistent connection is requested, PHP checks for an identical persistent connection (which was previously kept open) - and, if it exists, it uses it. If it doesn’t exist, it creates the connection. An 'identical' connection is a connection that has been opened to the same host, with the same username and password (where applicable).

Source: Persistent Connections to the Database.

---

If you use PDO just enter the option PDO::ATTR_PERSISTENT in the builder:

<?php
$conexao = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));

Is used mysqli add p: at the server address:

<?php
$conexao = new mysqli('p:localhost', $user, $pass, $database);

---

Note: I didn’t add the shape to using mysql_connect() because the extension was discontinued: /a/4675/13561