Re: [GENERAL] Persistent connections in PHP
Hannes Dorbath schrieb: On 14.08.2007 23:13, Dmitry Koterov wrote: Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on each DB connection. Too much pain. We've been using pconnect for exactly the same reason. Though startup time for our dictionary is even higher (around 2 seconds). The problem is that persistent connections in PHP are not clean implemented, they can get randomly garbage collected. The problem seems well known, though I'm unaware of any fix. I think it's best to use pgbouncer and plain connect ATM. Additionally, as mentioned earlier, using pconnect under the Apache webserver is not a good idea at all, at least not with it's current architecture. If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Another advantage of the solution is, that you have one dictionary for all php-childs - so you do not waste memory by loading the dictionary each request. Greetings, Torsten ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Persistent connections in PHP
Hannes Dorbath schrieb: On 15.08.2007 10:53, Torsten Zühlsdorff wrote: If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Uhm, but how does TSearch get it from there? And even if it does, it will still held its own copy? In this case I misunderstood you. I believed, that you get a dictionary from pg and use it. -.- Because often people load a dictionary from db to get the translation of $content, shared memory can make this very fast. My advise based on this assumption. Greetings, Torsten ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Persistent connections in PHP
On 14.08.2007 23:13, Dmitry Koterov wrote: Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on each DB connection. Too much pain. We've been using pconnect for exactly the same reason. Though startup time for our dictionary is even higher (around 2 seconds). The problem is that persistent connections in PHP are not clean implemented, they can get randomly garbage collected. The problem seems well known, though I'm unaware of any fix. I think it's best to use pgbouncer and plain connect ATM. Additionally, as mentioned earlier, using pconnect under the Apache webserver is not a good idea at all, at least not with it's current architecture. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Persistent connections in PHP
On 15.08.2007 10:53, Torsten Zühlsdorff wrote: If the dictionary is not too large, you should store it directly in the memory of the server. Therefore you can use Shared Memory (http://www.php.net/shmop, http://de3.php.net/manual/en/ref.sem.php). Uhm, but how does TSearch get it from there? And even if it does, it will still held its own copy? -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Persistent connections in PHP
On Mon, 13 Aug 2007 11:30:37 -0500 Scott Marlowe [EMAIL PROTECTED] wrote: Oh, one other thing that contributes to the problem Thanks for the replies - all of this was very useful info. Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Persistent connections in PHP
Pconnects are absolutely necessary if we use tsearch2, because it initializes its dictionaries on a first query in a session. It's a very heavy process (500 ms and more). So, if we do not use pconnect, we waste about 500 ms on each DB connection. Too much pain. Or, of course, pconnect may be replaced with pgbouncer. It's even better. On 8/13/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 8/13/07, Josh Trutwin [EMAIL PROTECTED] wrote: On Mon, 13 Aug 2007 09:44:26 -0500 Erik Jones [EMAIL PROTECTED] wrote: I'll agree with Scott on this one. (Not that I can recall specifically ever disagreeing with him before...). Unless you know all of the potential caveats associated with php's persisent postgres connections and have a use case that fits them, don't use them. If you need something to pool connections, look at pgpool. Could elaborate a little on the problems with using php's persistent connections? Personally I use ADODB php abstraction library (adodb.sf.net) for my database stuff and I think there's a way to enable persistent connections though I just use the default connection. I've heard before that php's persistent connections are to be avoided, was just curious as to why though? OK, there are a few things that gather together to make php's persistant connections a problem. 1: Each apache / php process maintains its own connections, not sharing with others. So it's NOT connection pooling, but people tend to think it is. 2: Each unique connection creates another persistent connection for an apache/php child process. If you routinely connect to multiple servers / databases or as 1 user, then each one of those combinations that is unique makes another persistent connection. 3: There's no facility in PHP to clean an old connection out and make sure it's in some kind of consistent state when you get it. It's in exactly the same state it was when the previous php script finished with it. Half completed transactions, partial sql statements, sequence functions like currval() may have values that don't apply to you. 4: pg_close can't close a persistent connection. Once it's open, it stays open until the child process is harvested. 5: Apache, by default, is configured for 150 child processes. Postgresql, and many other databases for that matter, are configured for 100 or less. Even if apache only opens one connection to one database with one user account, it will eventually try to open the 101st connection to postgresql and fail. So, the default configuration of apache / postgresql for number of connections is unsafe for pconnect. 6: The reason for connection pooling is primarily to twofold. One is to allow very fast connections to your database when doing lots of small things where connection time will cost too much. The other is to prevent your database from having lots of stale / idle connections that cause it to waste memory and to be slower since each backend needs to communicate with every other backend some amount of data some times. pconnect takes care of the first problem, but exacerbates the second. P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the time, and it's really great for simple small scripts that need to be done NOW and need to be lightweight. I even use pconnect a bit. But my machine is set for 50 or fewer apache children and 150 postgresql connects, and I only use pconnect on small, lightweight things that need to zoom. Everything else gets regular old connect. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Persistent connections in PHP
2007/8/13, Naz Gassiep [EMAIL PROTECTED]: Hi, Does the connection pooling feature of PHP cause the persistent connections to keep the properties between accesses? E.g., if a user takes a connection, sets a timezone to it using SET TIMEZONE, will the next user who happens to take this connection get it in that same state, or will it be reset to a blank or starting state as though it had been opened? Also, what about temp tables? Will they be present to the second user if the first user set some up? - Naz. Yes, it's works like you write. But connection assigning is little bit random and you cannot count with it. Regards Pavel Stehule ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Persistent connections in PHP
On Mon, 13 Aug 2007 09:44:26 -0500 Erik Jones [EMAIL PROTECTED] wrote: I'll agree with Scott on this one. (Not that I can recall specifically ever disagreeing with him before...). Unless you know all of the potential caveats associated with php's persisent postgres connections and have a use case that fits them, don't use them. If you need something to pool connections, look at pgpool. Could elaborate a little on the problems with using php's persistent connections? Personally I use ADODB php abstraction library (adodb.sf.net) for my database stuff and I think there's a way to enable persistent connections though I just use the default connection. I've heard before that php's persistent connections are to be avoided, was just curious as to why though? Thanks! Josh ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Persistent connections in PHP
On Aug 13, 2007, at 9:35 AM, Scott Marlowe wrote: On 8/13/07, Naz Gassiep [EMAIL PROTECTED] wrote: Hi, Does the connection pooling feature of PHP cause the persistent connections to keep the properties between accesses? E.g., if a user takes a connection, sets a timezone to it using SET TIMEZONE, will the next user who happens to take this connection get it in that same state, or will it be reset to a blank or starting state as though it had been opened? Also, what about temp tables? Will they be present to the second user if the first user set some up? Except for a few special uses, persistent connections in php are more of a misfeature. They are NOT pooling, in the standard sense. Most of the time they cause more problems than they solve. I'll agree with Scott on this one. (Not that I can recall specifically ever disagreeing with him before...). Unless you know all of the potential caveats associated with php's persisent postgres connections and have a use case that fits them, don't use them. If you need something to pool connections, look at pgpool. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Persistent connections in PHP
On 8/13/07, Naz Gassiep [EMAIL PROTECTED] wrote: Hi, Does the connection pooling feature of PHP cause the persistent connections to keep the properties between accesses? E.g., if a user takes a connection, sets a timezone to it using SET TIMEZONE, will the next user who happens to take this connection get it in that same state, or will it be reset to a blank or starting state as though it had been opened? Also, what about temp tables? Will they be present to the second user if the first user set some up? Except for a few special uses, persistent connections in php are more of a misfeature. They are NOT pooling, in the standard sense. Most of the time they cause more problems than they solve. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Persistent connections in PHP
On 8/13/07, Josh Trutwin [EMAIL PROTECTED] wrote: On Mon, 13 Aug 2007 09:44:26 -0500 Erik Jones [EMAIL PROTECTED] wrote: I'll agree with Scott on this one. (Not that I can recall specifically ever disagreeing with him before...). Unless you know all of the potential caveats associated with php's persisent postgres connections and have a use case that fits them, don't use them. If you need something to pool connections, look at pgpool. Could elaborate a little on the problems with using php's persistent connections? Personally I use ADODB php abstraction library (adodb.sf.net) for my database stuff and I think there's a way to enable persistent connections though I just use the default connection. I've heard before that php's persistent connections are to be avoided, was just curious as to why though? OK, there are a few things that gather together to make php's persistant connections a problem. 1: Each apache / php process maintains its own connections, not sharing with others. So it's NOT connection pooling, but people tend to think it is. 2: Each unique connection creates another persistent connection for an apache/php child process. If you routinely connect to multiple servers / databases or as 1 user, then each one of those combinations that is unique makes another persistent connection. 3: There's no facility in PHP to clean an old connection out and make sure it's in some kind of consistent state when you get it. It's in exactly the same state it was when the previous php script finished with it. Half completed transactions, partial sql statements, sequence functions like currval() may have values that don't apply to you. 4: pg_close can't close a persistent connection. Once it's open, it stays open until the child process is harvested. 5: Apache, by default, is configured for 150 child processes. Postgresql, and many other databases for that matter, are configured for 100 or less. Even if apache only opens one connection to one database with one user account, it will eventually try to open the 101st connection to postgresql and fail. So, the default configuration of apache / postgresql for number of connections is unsafe for pconnect. 6: The reason for connection pooling is primarily to twofold. One is to allow very fast connections to your database when doing lots of small things where connection time will cost too much. The other is to prevent your database from having lots of stale / idle connections that cause it to waste memory and to be slower since each backend needs to communicate with every other backend some amount of data some times. pconnect takes care of the first problem, but exacerbates the second. P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the time, and it's really great for simple small scripts that need to be done NOW and need to be lightweight. I even use pconnect a bit. But my machine is set for 50 or fewer apache children and 150 postgresql connects, and I only use pconnect on small, lightweight things that need to zoom. Everything else gets regular old connect. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Persistent connections in PHP
Hi, Does the connection pooling feature of PHP cause the persistent connections to keep the properties between accesses? E.g., if a user takes a connection, sets a timezone to it using SET TIMEZONE, will the next user who happens to take this connection get it in that same state, or will it be reset to a blank or starting state as though it had been opened? Also, what about temp tables? Will they be present to the second user if the first user set some up? - Naz. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Persistent connections in PHP with PDO
Has anyone played around with the new PHP ODO drivers and been able to successfully set up an object using persistent connections? I tried to follow the documentation in the PHP manual to send an array in the PDO constructor but receive a warning message that the underlying driver does not support this. I mucked around in the source code for the driver in the PHP source tree and it appears there is no facility in the driver for sending such options, which leaves me to wonder if you simply set the php.ini value to support persistent connections that would be all you need to do? Can anyone shed any light on this problem? There seems to be little if any documentation on this. Thanks in advance. Alan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
