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

[Tatsuo Ishii]

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