#TIL : Persistent connection to MySQL

When a PHP process connects to MySQL server, the connection can be persistent if your PHP config has mysql.allow_persistent or mysqli.allow_persistent. (PDO has the attribute ATTR_PERSISTENT)

1
$dbh = new PDO('DSN', 'KhanhDepZai', 'QuenMatKhauCMNR', [PDO::ATTR_PERSISTENT => TRUE]);

Object destruction

PHP destruct an object automatically when an object lost all its references.

Example code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<?php

$x = null;

function klog($x) {
echo $x . ' => ';
}

class A {
private $k;
function __construct($k) {
$this->k = $k;
}

function b() {
klog('[b]');
}

function __destruct() {
klog("[{$this->k} has been killed]");
}
}

function c($k) {
return new A($k);
}

function d() {
c('d')->b();
}

function e() {
global $x;
$x = c('e');
$x->b();
klog('[e]');
}

function f() {
klog('[f]');
}

d();
e();
f();

Result:

1
[b] => [d has been killed] => [b] => [e] => [f] => [e has been killed] =>

Reducing PDO persistent connections in PHP long-run process (connect to multiples databases)

Instead of using a service object, we should use a factory design pattern for each job (each connection). PHP will close MySQL connection because it destructs object PDO. Then we can reduce the number of connections to MySQL at a same time.

I learned this case when implement a web-consumer (long-run process) to run database migration for multiples databases.

Before fixing this, our MySQL server had been crashed because of a huge opened connections.

Now, everything works like a charm !

Bring it on

Comments