[pgpool-general: 660] Re: Pgpool PHP PDO connection not closing after script execution
Aleksej Trofimov
aleksej.trofimov at ruptela.lt
Mon Jun 25 18:07:07 JST 2012
On 25/06/12 09:38, Aleksej Trofimov wrote:
> 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.
>
After a some time problem is still persists, I've tried pgpool
re-configuration, but no result.
When web scripts with PDO are connecting directly through postgres (or
pgbouncer), everything works great, database server load is small,
postgres have 45-60 active connections. If i change connection method to
pgpool, active connections count jumps to 200 (max connection count in
pgpool is 200), db server starts to freeze, load average is doubled, and
in pg_stat_activity I can see a lot of SELECT count(*) FROM
pg_catalog.pg_class and SELECT count(*) FROM pg_class. It seems that
after using PDO, pgpool starts to add a lot of overhead, all the process
starts to slow down and connections do not have time to close.
By the moment I cant see only solution, not to use PDO at all..
--
Best regards
Aleksej Trofimov
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20120625/d32e13fb/attachment-0001.html>
More information about the pgpool-general
mailing list