<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<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 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>
</body>
</html>