[pgpool-general: 655] Re: Pgpool PHP PDO connection not closing after script execution

[Tatsuo Ishii]

> On 20/06/12 17:08, Tatsuo Ishii wrote:
>>> On 20/06/12 09:23, Tatsuo Ishii wrote:
>>>>>>> Hello,
>>>>>>>        Several days ago I faced with a pgpool (3.1.3) + php
>>>>>>>        pdo(5.3.9-1.ius.el5) problem. My project has a lot of connection per
>>>>>>>        second (about 40 in a second), so we are using pgpool in load balancer
>>>>>>>        and master/slave mode. Not a long time ago we decided to rewrite our
>>>>>>>        php code to support PDO, but after all test went good we faced with a
>>>>>>>        problem, when pgpool reaches connection maximum in a several seconds
>>>>>>>        with a 90% of idle connections. We have such a configuration:
>>>>>>>
>>>>>>> num_init_children = 100
>>>>>>> max_pool = 2
>>>>>>> child_life_time = 300
>>>>>>> child_max_connections = 20
>>>>>>> connection_life_time = 20
>>>>>>> client_idle_limit = 20
>>>>>>>
>>>>>>> Without a PDO extension, everything works great, we have 40-45 pgpool
>>>>>>> processes running on a server at an average. After we enabling PDO in
>>>>>>> our code, pgpool process starting to grow very fast until
>>>>>>> num_init_children and 90 of them are with idle status. The problem
>>>>>>> does not exist without pgpool in a midle (if we connect to database
>>>>>>> directly, we are using postgresql 9.1).
>>>>>>>
>>>>>>>     We also tried to increase num_init_children but no effect, maximum is
>>>>>>>     reached in a several seconds..
>>>>>>>
>>>>>>> Our php script uses  such a logic:
>>>>>>> $this->_connection = new PDO();
>>>>>>>
>>>>>>> Using $this->_connection
>>>>>>>
>>>>>>> $this->_connection = NULL;
>>>>>>>
>>>>>>> And we are not using persistence mode..
>>>>>>>
>>>>>>> May be someone know the solution?
>>>>>> I think by setting NULL to $this->connection PDO does not disconnect
>>>>>> connection to pgpool immediately. That's the reason why connections to
>>>>>> pgpool are filled up. In the past I confirmed this by using strace to
>>>>>> see what PDO was doing. PDO did not send "close connection" packet to
>>>>>> pgpool immediately when the PDO script set NULL to connection
>>>>>> object. After creating new PDO object, eventually it sent the packet.
>>>>>>
>>>>>> I'm not sure this is a bug or feature of PDO though.
>>>>>>
>>>>> So, there is no solution for "workarounding" this "feature"?
>>>> If I were you, I would modify PDO to add new method "disconnect". It
>>>> should not be hard.
>>>>
>>>> In reality, you might want to modify your PDO code. I think the reason
>>>> why setting NULL to connection object does not release the connnection
>>>> immediately is, the connection is not removed by destructor until the
>>>> execution goes out of the scope of the connection object.
>>>>
>>>>> And why
>>>>> postgress itself does not complaint on such a behaviour of PDO and
>>>>> pgpool does?
>>>> Probably due to the difference of connection establishing speed.  To
>>>> confirm this you could insert sleep() after:
>>>>
>>>> $this->_connection = new PDO();
>>>>
>>>> and see if something changes when using pgpool.
>>>>
>>> Hello,
>>>      I have tried using several sleep values, 0.5 sec and 1 sec, the higher
>>>      values are not possible in our system with average load... No result
>>>      at all, the idle connections are dropped only after
>>>
>>> client_idle_limit time = 20 sec
>>>
>>> In such a condition there is no possibility to use pgpool with php pdo
>>> at all...
>> Ok, the sleep is useless then. What about:
>>
>>> the connection is not removed by destructor until the
>>> execution goes out of the scope of the connection object
>> Can you modify your script?
>>
> 
> According to
> 
>>If I were you, I would modify PDO to add new method "disconnect". It
>>should not be hard.
>>
>>In reality, you might want to modify your PDO code. I think the reason
>>why setting NULL to connection object does not release the connnection
>>immediately is, the connection is not removed by destructor until the
>>execution goes out of the scope of the connection object.
> 
> But PHP PDO has no disconnect method by default, this is from php
> manual:
> 
> Upon successful connection to the database, an instance of the PDO
> class is returned to your script. The connection remains active for
> the lifetime of that PDO object. To close the connection, you need to
> destroy the object by ensuring that all remaining references to it are
> deleted--you do this by assigning NULL to the variable that holds the
> object. If you don't do this explicitly, PHP will automatically close
> the connection when your script ends.
> (http://us.php.net/manual/en/pdo.connections.php)
> 
> So the $pdo = null must close connection, or connection must be
> dropped after the script ends.
> But as I understand from my situation, connection only is closed to
> postgres directly but not to pgpool.
> 
> Or when you said
> 
>>If I were you, I would modify PDO to add new method "disconnect". It
>>should not be hard.
> 
> You kept in mind, modify php pdo source?

Ok, I did test myself and saw no evidence that many pgpool process in
idle state (actially all of them are in "wait for connection" state
right after the test).

Here is the software versions I used:

$ uname -a
Linux localhost.localdomain 2.6.35-21vl6 #1 SMP Sun Jan 1 18:40:00 JST 2012 x86_64 x86_64 x86_64 GNU/Linux
$ php --version
PHP 5.3.14 (cli) (built: Jun 15 2012 05:34:54) 
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies

Here is the trivial test program I used. I ran the program from php command line.

<?php
  function db() {
    $db = new PDO("pgsql:host=localhost;port=11002;dbname=test");
	return $db;
  }

  $n = 100;
  for($i=0;$i < $n;$i++)
  {
    $db = db();
    $stmt = $db->prepare("SELECT :num::int");
    $stmt->execute(array(':num' => $i));
    print_r($stmt->fetch());
    //sleep(1);
    $db = NULL;
    echo date(DATE_RFC822);
  }
?>
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp