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

[Aleksej Trofimov]

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...

-- 
Best regards

Aleksej Trofimov