<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
On 21/06/12 13:58, Aleksej Trofimov wrote:
<blockquote cite="mid:4FE2FE60.5030803@ruptela.lt" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-text-flowed" style="font-family: -moz-fixed;
font-size: 12px;" lang="x-western">On 21/06/12 05:13, Tatsuo
Ishii wrote: <br>
<blockquote type="cite" style="color: #000000;">
<blockquote type="cite" style="color: #000000;">On 20/06/12
17:08, Tatsuo Ishii wrote: <br>
<blockquote type="cite" style="color: #000000;">
<blockquote type="cite" style="color: #000000;">On
20/06/12 09:23, Tatsuo Ishii wrote: <br>
<blockquote type="cite" style="color: #000000;">
<blockquote type="cite" style="color: #000000;">
<blockquote type="cite" style="color: #000000;">
<blockquote type="cite" style="color: #000000;">Hello,
<br>
Several days ago I faced with a pgpool
(3.1.3) + php <br>
pdo(5.3.9-1.ius.el5) problem. My project
has a lot of connection per <br>
second (about 40 in a second), so we are
using pgpool in load balancer <br>
and master/slave mode. Not a long time
ago we decided to rewrite our <br>
php code to support PDO, but after all
test went good we faced with a <br>
problem, when pgpool reaches connection
maximum in a several seconds <br>
with a 90% of idle connections. We have
such a configuration: <br>
<br>
num_init_children = 100 <br>
max_pool = 2 <br>
child_life_time = 300 <br>
child_max_connections = 20 <br>
connection_life_time = 20 <br>
client_idle_limit = 20 <br>
<br>
Without a PDO extension, everything works great,
we have 40-45 pgpool <br>
processes running on a server at an average.
After we enabling PDO in <br>
our code, pgpool process starting to grow very
fast until <br>
num_init_children and 90 of them are with idle
status. The problem <br>
does not exist without pgpool in a midle (if we
connect to database <br>
directly, we are using postgresql 9.1). <br>
<br>
We also tried to increase num_init_children
but no effect, maximum is <br>
reached in a several seconds.. <br>
<br>
Our php script uses such a logic: <br>
$this->_connection = new PDO(); <br>
<br>
Using $this->_connection <br>
<br>
$this->_connection = NULL; <br>
<br>
And we are not using persistence mode.. <br>
<br>
May be someone know the solution? <br>
</blockquote>
I think by setting NULL to $this->connection
PDO does not disconnect <br>
connection to pgpool immediately. That's the
reason why connections to <br>
pgpool are filled up. In the past I confirmed this
by using strace to <br>
see what PDO was doing. PDO did not send "close
connection" packet to <br>
pgpool immediately when the PDO script set NULL to
connection <br>
object. After creating new PDO object, eventually
it sent the packet. <br>
<br>
I'm not sure this is a bug or feature of PDO
though. <br>
<br>
</blockquote>
So, there is no solution for "workarounding" this
"feature"? <br>
</blockquote>
If I were you, I would modify PDO to add new method
"disconnect". It <br>
should not be hard. <br>
<br>
In reality, you might want to modify your PDO code. I
think the reason <br>
why setting NULL to connection object does not release
the connnection <br>
immediately is, the connection is not removed by
destructor until the <br>
execution goes out of the scope of the connection
object. <br>
<br>
<blockquote type="cite" style="color: #000000;">And
why <br>
postgress itself does not complaint on such a
behaviour of PDO and <br>
pgpool does? <br>
</blockquote>
Probably due to the difference of connection
establishing speed. To <br>
confirm this you could insert sleep() after: <br>
<br>
$this->_connection = new PDO(); <br>
<br>
and see if something changes when using pgpool. <br>
<br>
</blockquote>
Hello, <br>
I have tried using several sleep values, 0.5 sec
and 1 sec, the higher <br>
values are not possible in our system with average
load... No result <br>
at all, the idle connections are dropped only
after <br>
<br>
client_idle_limit time = 20 sec <br>
<br>
In such a condition there is no possibility to use
pgpool with php pdo <br>
at all... <br>
</blockquote>
Ok, the sleep is useless then. What about: <br>
<br>
<blockquote type="cite" style="color: #000000;">the
connection is not removed by destructor until the <br>
execution goes out of the scope of the connection object
<br>
</blockquote>
Can you modify your script? <br>
<br>
</blockquote>
According to <br>
<br>
<blockquote type="cite" style="color: #000000;">If I were
you, I would modify PDO to add new method "disconnect". It
<br>
should not be hard. <br>
<br>
In reality, you might want to modify your PDO code. I
think the reason <br>
why setting NULL to connection object does not release the
connnection <br>
immediately is, the connection is not removed by
destructor until the <br>
execution goes out of the scope of the connection object.
<br>
</blockquote>
But PHP PDO has no disconnect method by default, this is
from php <br>
manual: <br>
<br>
Upon successful connection to the database, an instance of
the PDO <br>
class is returned to your script. The connection remains
active for <br>
the lifetime of that PDO object. To close the connection,
you need to <br>
destroy the object by ensuring that all remaining references
to it are <br>
deleted--you do this by assigning NULL to the variable that
holds the <br>
object. If you don't do this explicitly, PHP will
automatically close <br>
the connection when your script ends. <br>
(<a moz-do-not-send="true" class="moz-txt-link-freetext"
href="http://us.php.net/manual/en/pdo.connections.php">http://us.php.net/manual/en/pdo.connections.php</a>)
<br>
<br>
So the $pdo = null must close connection, or connection must
be <br>
dropped after the script ends. <br>
But as I understand from my situation, connection only is
closed to <br>
postgres directly but not to pgpool. <br>
<br>
Or when you said <br>
<br>
<blockquote type="cite" style="color: #000000;">If I were
you, I would modify PDO to add new method "disconnect". It
<br>
should not be hard. <br>
</blockquote>
You kept in mind, modify php pdo source? <br>
</blockquote>
Ok, I did test myself and saw no evidence that many pgpool
process in <br>
idle state (actially all of them are in "wait for connection"
state <br>
right after the test). <br>
<br>
Here is the software versions I used: <br>
<br>
$ uname -a <br>
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 <br>
$ php --version <br>
PHP 5.3.14 (cli) (built: Jun 15 2012 05:34:54) <br>
Copyright (c) 1997-2012 The PHP Group <br>
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
<br>
<br>
Here is the trivial test program I used. I ran the program
from php command line. <br>
<br>
<?php <br>
function db() { <br>
$db = new
PDO("pgsql:host=localhost;port=11002;dbname=test"); <br>
return $db; <br>
} <br>
<br>
$n = 100; <br>
for($i=0;$i< $n;$i++) <br>
{ <br>
$db = db(); <br>
$stmt = $db->prepare("SELECT :num::int"); <br>
$stmt->execute(array(':num' => $i)); <br>
print_r($stmt->fetch()); <br>
//sleep(1); <br>
$db = NULL; <br>
echo date(DATE_RFC822); <br>
} <br>
?> <br>
<br>
</blockquote>
Hello, <br>
we are using <br>
# uname -a <br>
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 <br>
# php --version <br>
PHP 5.3.9 (cli) (built: Jan 11 2012 10:47:45) <br>
Copyright (c) 1997-2012 The PHP Group <br>
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies <br>
<br>
All our scripts have $PDO=null; at the end <br>
<br>
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 <br>
<br>
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 <br>
<br>
What other debug information could I provide? <br>
<br>
<div class="moz-txt-sig"><span class="moz-txt-tag">-- <br>
</span>Best regards <br>
<br>
Aleksej Trofimov <br>
<br>
</div>
</div>
<br>
</blockquote>
<br>
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.<br>
<br>
<pre class="moz-signature" cols="72">--
Best regards
Aleksej Trofimov</pre>
</body>
</html>