<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">On 25/06/12 09:38, Aleksej Trofimov
wrote:<br>
</div>
<blockquote cite="mid:4FE80780.4030409@ruptela.lt" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<div class="moz-cite-prefix">On 21/06/12 14:28, Aleksej Trofimov
wrote:<br>
</div>
<blockquote cite="mid:4FE3056A.6060309@ruptela.lt" type="cite">
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
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>
<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.
</blockquote>
<br>
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.<br>
<br>
</blockquote>
After a some time problem is still persists, I've tried pgpool
re-configuration, but no result.<br>
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. <br>
<br>
By the moment I cant see only solution, not to use PDO at all.. <br>
<br>
<br>
<pre class="moz-signature" cols="72">--
Best regards
Aleksej Trofimov</pre>
</body>
</html>