Re: Re: re : PHP and persistent connections - Mailing list pgsql-novice

On Fri, Nov 24, 2000 at 04:52:27AM -0700, some SMTP stream spewed forth:
> We're in quote hell.
> Yay.

Ah, but now the hell thickens. ;-)

>
> GH wrote:
> >
> > On Fri, Nov 24, 2000 at 12:52:34AM -0700, some SMTP stream spewed forth:
> > > GH wrote:
> > > > On Fri, Nov 24, 2000 at 03:17:59PM +1100, some SMTP stream spewed forth:
> > > > > Howdy,
> > > Give them their own apache? You can set up two apache instances on one box,
> > > set up one with lots of backends, set up the other to match the applicable
> > > db usage...
> > > You could make a postgres+apache box for these few clients...
> > Er, I think I missed something.
> > You mean give them their own Apache instance using a seperate ip?
>
> Yes.
>
> Apache one, httpd, serves 14 domains, conf files in /usr/local/apache/conf.
> pgsql.max_persistent = 1
> MaxClients 8
>
> Apache two, httpd2, serves 327 domains, conf files in /usr/local/apache2/conf.
> Max clients 150 (no postgres backends, no PHP)

I see.

>
> > Is it /possible/ to have a group of httpd processes (Apache) share a
> > group of Postgres backends without having one backend to one httpd?
> > That would be connection pooling, correct? Which is not yet possible?
>
> Apache's process management, AFAIK, makes this fairly difficult. As in:
> "I've never seen it, and I can't find docs on on, maybe v.2 will
> have better support for children sharing common resources".
>

Just checking. I had heard (and expected) that it did not -- for the same
reason.

> > > Depends on the load. I'm serving 429 domains off of PHP/PostgreSQL,
> > > using non-persistant connections (even though almost every page has
> > > a select or two), and it's working just fine. My biggest selects only
> > > return a few hundred rows, my small inserts/updates are done in PHP,
> > > the big ones (4,000+ rows) are just parsed into files that a Perl/cron job
> > > takes care of them. It also depends, obviously, on how you write your
> > > code for all of this, how good the hardware is, etc.
> > > (PII/500, 512Mb of RAM, RH 6.2 for the above)
> > That makes sense. The only reason I am so zealous about persistent
> > connections is that I have seen them be 3 times as fast as regular
> > connections.
>
> Hm.
>
> I havn't. In PHP, one connection for the duration of a single
> page (pg_connect()) takes as much time as a new persistant connection
> (pg_pconnect()). Since you're often only creating one connection per page,
> and running a single transaction on it, the main difference
> would be in your connection setup... how did you test this? (I'm just
> curious). Is it a usage test (real, live, use) or a bench test (push
> to a limit that won't be reached in actual use.) I have one horribly
> written app, that does maybe 50 _different_ selects on one page,
> and it's still under two seconds per user....

"Test" is a strong word. ;-) I have a timer set on a page.
The overall exec time is less that 1-tenth of a second using persistent
connections, so long as a connection exists. Using regular connections,
the exec time soars (;-)) to a whopping 3-tenths or so.
So, no big fat deal. The exec time is low enough that the effects of the
connections shine, but in general are insignificant.
If the script in discussion did anything worthwhile, I doubt that I would
notice anything even close to 3x.

>
> > > > > Obviously this isn't the most efficient use of backends because a fair
> > > > > amount of the time the Apache processes won't be using them at all
> > My main question now is, how can I avoid this?
>
> Serve the postgres pages from a different server instance, on the same
> machine, or a different one.
>
> > I would have to go to non-persistent connections, correct?
>
> You could use persistant connections on a different server/instance,
> or use non-persistant and loose ~10ms per page, less time than your average
> 10K GIF takes up on a 56K download.
>
> You see, persistant PHP connections offer *no other value*, at all. None.
> (it's a common error for new PHP folks to think that a web server
> will somehow track their connections.) All it does is reduce setup time on a
> page. No "session", no "tracking", nada. It reduces your connection
> time for the page, but not significanly enough for users to know,
> or care (IME). In web-page uses, the time is pretty much irrelevant,
> because you only need one or two connections per page to get most
> of your data out. Persistant connections are an interesting idea,
> but they don't offer much. See:
> http://www.php.net/manual/features.persistent-connections.php

I have read it (note: the phrasing seems to be a bit "messy"), but
for some reason I must have missed what it was saying. I "get it" now.

>
> > So, persistent connections create a one-to-one ratio of
> > db-using Apache processes and Postgres backends, no matter what?
>
> Almost. You can have more persistant connections for each apache
> child, but each child may look for one. So it may be 5 apache
> to 5 postgres, or 5 apache to 50 postgres, if needed (of course,
> if you had that many conections, you may want to re-architect anyways)
>
> > The only way to avoid such a one-to-one setup would be to
> > use non-persistent connections or do connection pooling?
>
> I'm still not following you on the "pooling". Apache doesn't, AFAICT,

I almost knew that it did not. But I was trying to re-affirm my grasp of
just what "pooling" would do.

> offer this in each child. Each child is its own application, it's own
> apache+php+postgres. Postgres doesn't care. PHP doesn't care. Apache
> cares. If you give each child piece 5 postgres connections, and have
> 10 children, you need up to 50 backends.
>
> > So, even if the database were running on a seperate server,
> > each apache procees on the main server would require one backend process
> > on the db server?
>
> Yup. If it was going to pull a postgres+PHP page, it would. You see,
> apache doesn't work in a space where one apache process can crash the
> whole thing. Each piece is isolated. This means that each piece needs
> it's own resources. Compare this to other engines, where a single
> crash on one serving instance takes down the _entire_ server, and
> it makes sense (if the pool is down, it all goes down, a la IIS).
>
> "It scales, but not that way". :-(

Got it. Maybe this thread will finally pass away now. ;-)

Thanks again.

gh

>
> -Ronabop
>
> --
> Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine,
> which is currently in MacOS land.  Your bopping may vary.