Optimizing MySQL Connections

Navigation
2

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

I was told that with each new connection to MySQL a portion of the RAM is reserved for this connection. I have several applications developed in PHP that connect in the same database. My question is: how do bank connections work in PHP and if 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 that connection usually lasts until the script is finished. Following this reasoning, if the same script is executed 10 times by different clients or not at the same time, even for milliseconds, we will have 10 different connections with the bank. Am I right!? Is this really what happens in practice?

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

Thanks in advance for any help. ;)

    
asked by anonymous 01.10.2015 / 16:09

2 answers

1

Use persistent connections:

  

Persistent connections are connections that do not close when your script runs. When a persistent connection is requested, PHP checks to see if an identical persistent connection exists (which was kept open earlier) - and if it exists, it uses it. If it does not 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 Database Connections .

If you use PDO simply enter the PDO::ATTR_PERSISTENT option in the constructor: 

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

If you use mysqli add p: to the server address: 

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

Note : I did not add the form to using mysql_connect() because the extension was deprecated: link

    
01.10.2015 / 20:53
2

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, BANK, USER, PASSWORD by your data, to start a new connection use the following code: 

$conexao = Conexao::getInstance();

So you do not "build" a new object if you already have one built.

    
01.10.2015 / 21:30
User Authentication with $ rootScope How to convert numbers 2.15600e + 03 (euler) to numeric type?