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

Aleksej Trofimov aleksej.trofimov at ruptela.lt
Mon Jun 25 15:38:56 JST 2012


On 21/06/12 14:28, Aleksej Trofimov wrote:
> On 21/06/12 13:58, Aleksej Trofimov wrote:
>> On 21/06/12 05:13, Tatsuo Ishii wrote:
>>>> 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);
>>>    }
>>> ?>
>>>
>> Hello,
>> we are using
>> # uname -a
>> Linux track.HTTP 2.6.18-274.7.1.el5.028stab095.1 #1 SMP Mon Oct 24 
>> 20:49:24 MSD 2011 x86_64 x86_64 x86_64 GNU/Linux
>> # php --version
>> PHP 5.3.9 (cli) (built: Jan 11 2012 10:47:45)
>> Copyright (c) 1997-2012 The PHP Group
>> Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
>>
>> All our scripts have $PDO=null; at the end
>>
>> Unforunately, after a several seconds of attaching PDO to the pgpool, 
>> we have situation like a lot of pool proccesses IDLE, and lot of pool 
>> connected. I've attache 2 log files, psql  -c "show pool_pools;" > 
>> log_pools and the other is pgpool ps aux log
>>
>> From the logs it is seen, that after the PDO enabling, pgpool is 
>> starting to accumulate IDLE connections. New connections are 
>> established with a frequency 30 connections/s
>>
>> What other debug information could I provide?
>>
>>
>
> Also I can attach pgpool log for one process where you can see, that 
> IDLE state lasts until "expire" state. As you can see, script is 
> executing from 14:08:46 due ot 14:09:17, than pgpool gets IDLE state 
> and is killed after 20 sec timeout at 14:09:37. 

The problem with pool idle connections and overhead was solved after 
disabling connection_cache at all. After such configuration, pgpool is 
not causing server high load and all connections are closed after script 
execution end.

-- 
Best regards

Aleksej Trofimov

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120625/b514e93c/attachment.html>


More information about the pgpool-general mailing list