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

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


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