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

Tatsuo Ishii ishii at postgresql.org
Wed Jun 20 23:08:20 JST 2012


> 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?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp


More information about the pgpool-general mailing list