Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

PDO connections non persistent & not close

I think i have a problem with Phalcon framework

I have this class :

<?php

class Testmysql extends \Phalcon\Db\Adapter\Pdo\Mysql {

    static $id = null;
    /**
    * Creates the Adapter
    */
  public function __construct(array $descriptor = NULL)
  {
      self::$id = uniqid(true);

      error_log(self::$id."\tMySQL::__construct()\n", 3, '/usr/share/nginx/html/test.log'); 

      return parent::__construct($descriptor);
  }

  /**
   * Connect
   */
  public function connect(array $descriptor = NULL)
  {
      error_log("MySQL::connect()\n", 3, '/usr/share/nginx/html/test.log');
      return parent::connect($descriptor);
  }

  /**
   * Close
   */
  public function close()
  {
      error_log(self::$id."\tMySQL::close()\n", 3, '/usr/share/nginx/html/test.log');
      return parent::close();
  }

}

and this test script :

<?php
require '../../public/_bootStrap.php';

$mysql_host = "localhost";
$mysql_user = "root";
$mysql_pass = "";

while(true) {

   $db = new Testmysql(["host"       => $mysql_host,
                        "username"   => $mysql_user,
                        "password"   => $mysql_pass,
                        "dbname"     => "db_test",
                        "persistent" => true,
                        "options"    => [],
    ]);
   echo ".";

   if($i++ >= 100) {
    break;
   }
}

In my log i have 200 lines :

157cfe92d7447e  MySQL::__construct()
MySQL::connect()
157cfe92d744e6  MySQL::__construct()
MySQL::connect()

So first problem : i don't have any close connection Secondly : I set persistent connection but i have different connection each time !

So ? What do you think ?

edited Sep '16

You don't have to call close() explicitly as it is being called by the framework implicitly. Phalcon automatically closes and destroys active connections when the request ends

If you want to take advantage of persistent DB connections, for MySQL you need to set this option in options flag: PDO::ATTR_PERSISTENT => 1

 $db = new Testmysql(["host"       => $mysql_host,
                        "username"   => $mysql_user,
                        "password"   => $mysql_pass,
                        "dbname"     => "db_test",
                        "persistent" => true,
                        "options"    => [\PDO::ATTR_PERSISTENT => 1],
    ]);


1.4k
edited Sep '16

In syslog in production i have many (many many many :

Sep  7 12:10:45 sd- mysqld: 2016-09-07 12:10:45 140069590919936 [Warning] Aborted connection 969 to db: 'db_test' user: 'root' host: 'localhost' (Unknown error)
Sep  7 12:10:45 sd- mysqld: 2016-09-07 12:10:45 140069658635008 [Warning] Aborted connection 899 to db: 'db_test' user: 'root' host: 'localhost' (Unknown error)

50 time per seconds... and i think it's a problem with phalcon which don't close connection...

You don't have to call close() explicitly as it is being called by the framework implicitly. Phalcon automatically closes and destroys active connections when the request ends



1.4k

How can i be sure that's the same connection ?

You don't have to call close() explicitly as it is being called by the framework implicitly. Phalcon automatically closes and destroys active connections when the request ends

If you want to take advantage of persistent DB connections, for MySQL you need to set this option in options flag: PDO::ATTR_PERSISTENT => 1

```php $db = new Testmysql(["host" => $mysqlhost, "username" => $mysqluser, "password" => $mysqlpass, "dbname" => "dbtest", "persistent" => true, "options" => [\PDO::ATTR_PERSISTENT => 1], ]); ```

edited Sep '16

You need to take into account following:

  • set your DB as shared service $di->setShared("db", function () use ($config)....
  • set PDO::ATTR_PERSISTENT to true

Then in your app:

  • put some sleep() on your app so you can catch the result more easily
  • on your remote DB server watch for TCP connections, you'll see that one bound port to MySQL server is kept open (ESTABLISHED state)

repeat - run your app again, ESTABLISHED connection on a remote DB server will be the same.



1.4k
edited Sep '16

actually i have :

$di = new FactoryDefault();


$di->set('db', function() {

        $mysql_host = "localhost";
        $mysql_user = "root";
        $mysql_pass = "";

    $db = new PdoMysql(["host"       => $mysql_host,
                        "username"   => $mysql_user,
                        "password"   => $mysql_pass,
                        "dbname"     => "db_test",
                        "persistent" => true,
                        "options"    => [
                            \PDO::ATTR_PERSISTENT => 1
                        ],
    ]);

    return $db;
});

is it ok ?

Actually i have ~1000 lines like this :

unix  2      [ ACC ]     STREAM     LISTENING     1045901395 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107952948 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107612012 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107471100 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107436320 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107411964 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107978977 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107876398 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107872218 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107786653 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107544531 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107528842 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107734772 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107899936 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107709578 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107697844 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107460006 16007/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1107501505 16007/mysqld        /var/run/mysqld/mysqld.sock
...

You need to take into account following:

  • set your DB as shared service $di->setShared("db", function () use ($config)....
  • set PDO::ATTR_PERSISTENT to true

Then in your app:

  • put some sleep() on your app so you can catch the result more easily
  • on your remote DB server watch for TCP connections, you'll see that one bound port to MySQL server is kept open (ESTABLISHED state)

repeat - run your app again, ESTABLISHED connection on a remote DB server will be the same.

Your both DB server and APP server reside on the same machine. So in case of using localhost as your PDO hostname / constructor param, you'll connect always through local UNIX domain socket, i.e. /var/run/mysqld/mysqld.sock. You should restart your DB, and then check for active connections.



1.4k
edited Sep '16

even if i restart my database i have the same problem... (~1000 lines of « unix 3 [ ] STREAM CONNECTED 1119175450 19957/mysqld /var/run/mysqld/mysqld.sock » )

server version : Server version: 10.1.12-MariaDB-1~trusty mariadb.org binary distribution

[email protected]:/usr/share/nginx/html/#netstat -anp | grep mysql 
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      19957/mysqld    
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      19957/mysqld    
tcp        0      0 10.0.0.1:65266      10.0.0.2:4567       ESTABLISHED 19957/mysqld    
unix  2      [ ACC ]     STREAM     LISTENING     1119137947 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1120798773 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1121058760 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1121006534 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1120942686 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1120926831 19957/mysqld        /var/run/mysqld/mysqld.sock
....

Your both DB server and APP server reside on the same machine. So in case of using localhost as your PDO hostname / constructor param, you'll connect always through local UNIX domain socket, i.e. /var/run/mysqld/mysqld.sock. You should restart your DB, and then check for active connections.

edited Sep '16

To watch live MySQL opened connections through UNIX socket, run (as root):

watch -n1 "netstat -np | grep -i mysqld"



1.4k
edited Sep '16

Yes ok, but i have many many connections ...

tcp        0      0 10.0.0.1:65266      10.0.0.2:4567       ESTABLISHED 19957/mysqld
unix  3      [ ]         STREAM     CONNECTED     1128931262 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1128854224 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1128804375 19957/mysqld        /var/run/mysqld/mysqld.sock
...

To watch live MySQL opened connections through UNIX socket, run (as root):

watch -n1 "netstat -np | grep -i mysqld"

edited Sep '16

You probabbly have many dead/broken connections.

I just compiled this shell one line script to check both TCP and UNIX socket MySQL connections which are in active state:

ss -nt4 | grep -i "estab.*:3306"; sudo netstat -np | grep -i "connect.*mysqld"

Result:

ESTAB      0      0           192.168.200.200:3306       192.168.200.210:33988 
ESTAB      0      0           192.168.200.200:3306       192.168.200.210:33996 
unix  3      [ ]         STREAM     CONNECTED     1148777  1476/mysqld         /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1148778  1476/mysqld         /var/run/mysqld/mysqld.sock

So I have 2 TCP connections opened, and 2 connections from the same machine through local UNIX domain socket :)

edited Sep '16

Watch it interactively:

watch -n1 @ ss -nt4 | grep -i "estab.*:3306"; sudo netstat -np | grep -i "connect.*mysqld" @

NOTE: change @ to single quote (')



1.4k
edited Sep '16

Ok, but i have other result lol :

ESTAB      0      0             xx.xx.xx.xx:443         xx.xx.xx.xx:3306  
unix  3      [ ]         STREAM     CONNECTED     1158082023 -                   /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1158076519 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157916691 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157658709 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157413697 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157939256 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157655739 19957/mysqld        /var/run/mysqld/mysqld.sock
unix  3      [ ]         STREAM     CONNECTED     1157643512 19957/mysqld        /var/run/mysqld/mysqld.sock
... ~1000 same lines

each time i call my index.php new connection created. Not reused...

First row from your result is there by coincidence, i.e. it is not MySQL TCP connection from remote host to local machine.

You have many UNIX sockets, that's not good as you must be doing something wrong. Or it might be there are other users of your app who are using same app?



1.4k

i have PHP-FPM and index.php

each time one visitor go to index.php i have one mysql query :

$mysql_conn   = $app->getDi()->getShared('db');
        $query = "select column
                  from table
                  where column = x";

        $var  = $mysql_conn->fetchColumn($query, ['x' => $params['x'] ]);

that's all...

First row from your result is there by coincidence, i.e. it is not MySQL TCP connection from remote host to local machine.

You have many UNIX sockets, that's not good as you must be doing something wrong. Or it might be there are other users of your app who are using same app?



1.4k

Ok my problem is fixed if i disabled persistent connection ( persistent => true & PDO::ATTR_PERSISTENT => 0 )

edited Sep '16

That's not a fix, since you actually wanted to use persistent DB connections?



1.4k
edited Sep '16

yes, cause i have 1000 hits / sec on my index. this index do one simple mysql query. so i thinked that if i used persistent connection i will not have 1000 connections / sec. it's why i have defined persistent => true in my code. But now, if i disabled persistent connection, when i check connections list with netstat, i have ~4/5 socket unix opened, instead of 1000 before. and in my syslog i have no error about aborted connection...

i don't understand why but my problem it solved if i disabled persistent connection in my app code.

That's not a fix, since you actually wanted to use persistent DB connections?

edited Sep '16

Option to \Phalcon\Db\Adapter constructor "persistent" => true have no meaning at all, it's not supported that way.



1.4k
edited Sep '16

it's not right... if i defined only persistent => true in my constructor, i have many errors about aborted connections. if i remove it, i have no error in log, so it inevitably changes something...

Option to \Phalcon\Db\Adapter constructor "persistent" => true have no meaning at all, it's not supported that way.

edited Sep '16

Correct. I failed to check all of the classes xD Here it is: https://github.com/phalcon/cphalcon/blob/master/phalcon/db/adapter/pdo.zep#L130

So it will set PDO option \Pdo::ATTR_PERSISTENT] = true; for you, great.

In general:

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.



1.4k
Accepted
answer
edited Sep '16

trust me :

if i comment the line :

'persistent' => true

i have no error, if i uncomment the line i have errors.

aptitude show php5-phalcon 
Package: php5-phalcon                    
State: installed
Automatically installed: no
Version: 3.0.0-build10-ppa1~trusty

I have 1000 hits per seconds on my app code index, so i thinked that persistent connection can help me...

There's no treatment of such option anywhere in Zephir code. Which version of PhalconPHP do you say you run? 2.0.x or 3.0.x?

In general:

Many web applications will benefit from making persistent connections to database servers. Persistent connections are not closed at the end of the script, but are cached and re-used when another script requests a connection using the same credentials. The persistent connection cache allows you to avoid the overhead of establishing a new connection every time a script needs to talk to a database, resulting in a faster web application.

edited Sep '16

I just set persistent flag on my app.

I have a total of 4 TCP connections established between my app & db server (both residing on a dedicated VMs).

ESTAB      0      0           192.168.200.204:3306       192.168.200.201:42048
ESTAB      0      0           192.168.200.204:3306       192.168.200.201:42046
ESTAB      0      0           192.168.200.204:3306       192.168.200.201:42050
ESTAB      0      0           192.168.200.204:3306       192.168.200.201:42049
unix  3      [ ]         STREAM     CONNECTED     1512417  58594/mysqld        /var/run/mysqld/mysqld.sock

Each time another user hits the app, it will reuse connection from this 'pool'.



1.4k

ok, so why only 4 connections ? is it a variable in your configuration ?

I just set persistent flag on my app.

I have a total of 4 TCP connections established between my app & db server (both residing a dedicated VMs).

``` ESTAB 0 0 192.168.200.204:3306 192.168.200.201:42048 ESTAB 0 0 192.168.200.204:3306 192.168.200.201:42046 ESTAB 0 0 192.168.200.204:3306 192.168.200.201:42050 ESTAB 0 0 192.168.200.204:3306 192.168.200.201:42049 unix 3 [ ] STREAM CONNECTED 1512417 58594/mysqld /var/run/mysqld/mysqld.sock ```

Each time another user hits the app, it will reuse connection from this 'pool'.

edited Sep '16

I guess because 4 php-fpm processes ? Persistent connection means it will reuse connection, but obviously one php-fpm can connect only once.

Exactly. Worker processes are set to auto, so now there are 3 connections from app to db server.

Maybe a little bit out of topic. Just curious. Can I use 'persistent' => true for PostgreSql database as well?



1.4k

Ok, so now i understood :

In my php fpm configuration i have :

pm.process_idle_timeout=10s
pm.max_requests=50

So when php-fpm process was created, one persistent connection was created too, but after 50 requests, php-fpm process was kill and also connection → aborted connection on persistent connection.

I guess because 4 php-fpm processes ? Persistent connection means it will reuse connection, but obviously one php-fpm can connect only once.

edited Sep '16

Yes you can, as long as the underlying RDBMS supports such feature. I guess PostgreSQL does. This option is defined on PDO/Adapter level, so it's database type agnostic.

Maybe a little bit out of topic. Just curious. Can I use 'persistent' => true for PostgreSql database as well?

@Ange7: pm.process_idle_timeout is used only when pm is set to 'ondemand', are you sure you have this setting enabled instead of 'dynamic'?



1.4k

Yes, i'm « ondemand »

Yes you can, as long as the underlying RDBMS supports such feature. I guess PostgreSQL does. This option is defined on PDO/Adapter level, so it's database type agnostic.

Maybe a little bit out of topic. Just curious. Can I use 'persistent' => true for PostgreSql database as well?

@Ange7: pm.process_idle_timeout is used only when pm is set to 'ondemand', are you sure you have this setting enabled instead of 'dynamic'?

In that scenario / worker mode, it's hard to re-use peristent database connections I'm afraid. For my setup it's working fine, even through there's only a tiny benefit of doing it if you use localhost i.e. UNIX domain socket for MySQL (both app & db server on the same machine) where overhead is negligible.