Persistent

[Anon]

What are the usefullness
of persisten contect and it's drawback ?

In what situation i need to use it?

Thanks for your answer.

[Anon]

I haven't heard of any benifits...

The connection only exists for as long as the script is being parsed, anyways. with or without persistant connections.

maybe someone else can shed some more light...

---JH

[Anon]

afaik persistent connections in PHP are the equivalent of 'connection pooling' in ASP.
That means that PHP keeps a pool of connections and when a new request for opening a db conn arrives, it first check if there already is a connection in the pool that matches the request.
The benefit is that the overhead of opening a new conn disapears since the time needed to find a 'good' connection in the pool should be smaller than the time needed to create a new conn.
I don't know of any major drawback.
Anyway, I'm waiting for other opinions on this subject since it is of critical importance for the way I am handling connections in my own apps
Cheers

[Anon]

From PHP Manual:

mysql_pconnect() acts very much like mysql_connect() with two major differences.

First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection.

Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

This type of links is therefore called 'persistent'.

Sounds like what DanM said...

---JH

[Anon]

Without persistant connections, every connection to a database is lost at the end of script execution. Every new connection invokes a new php to database connection.

In environments where every page connects to the database, persistant connections make a lot of sense, assuming you have the memory to handle all the database connections required.

If you are on a machine that is tight for memory, you may find it starving if there are a lot of people on your site, slowly clicking one link after another. This is because http-keep-alive keeps the apache/php/database connection up waiting for the next mouse click.

I.e. 100 people clicking on one database driven link every 30 seconds would require 100 persistant connections to be open at once.

If they were doing the same thing with non-persistant connections, the average number of connections, and hence database backends required, would likely be only 5 to 10.

Bottom line is that persistant connections provide a modest improvement in performance, better scalability to hundreds or thousands of live connections, but they make you pay in memory on both the web server and database serverto the tune of about 1 to 5 megabytes per connection.

[Anon]

I think there are some bugs in the postgresql version at least,
that go beyond the keep-alive issue.

[Anon]

wow...so if a website with many of visits,
then we should not use persisten connection.
am i right ?

[Anon]

I wouldn't say one way or another, but you should definitely load test your setup to make sure it can handle the extra memory used by persistant connections.

[Anon]

I had some problems with WAY too many pgsql backends staying alive with an earlier version of PHP (RC1 I believe) but it doesn't seem to be an issue anymore.

[Anon]

Oh..ok ! thanks, that's mean if i have
extra memory, then i can use persistent
connection ?

And if i use persisten connection, once
connected , the connection will put
in the pool then if another client want
connect the same thing, then the php engine
search from the pool instead of open another
connect. As far as i understand from all of
you, am i right ?

[Anon]

Scott Marlowe's original answer was excellent, very precise, and everybody should read it several times to fully think it through.

The important thing to keep in mind is that identifying bottlenecks can be maddeningly difficult.

Let me give you a real-world example. I used to be editor of a major newspaper Web site that did 700,000 page views (complete pages, not just hits) per day. Probably half of those page views were individual news articles, which were built dynamically by a Perl script. (Perl, not mod_perl or FastCGI Perl.)

The Web server (actually, several redundant servers masquerading as one) was protected from net.morons by a firewall. The Sybase database was on a separate server behind yet another firewall.

Now, whenever Joe User wanted to read a story, the connection had to go through a firewall, to the Web server, which had to fire up Perl, which had to connect through another firewall and log in to Sybase, which had to process several SQL queries per article. The Perl script then would knit the results together with a template and return a page that included embedded image calls and multiple queries against a NetGravity ad server.

Let's suppose performance starts to stink. Where is the bottleneck? It could be:

• Web server CPU load from Perl startup.
• Web server memory usage.
• Firewall CPU load and/or memory (which one?)
• CPU or memory on the Sybase server.
• Overhead from logging in to Sybase.
• Sybase running out of connections. Did we remember to log out, or did we just let them expire?
• Badly written SQL.
• Oops. We dropped an index on a heavily searched table.
• Some net.moron is flooding us with a portscan.
• NetGravity might be having a bad day.
• The Internet connection. Let's not forget that.

I've listed eleven possibilities and there are probably several dozen more.

One, and only one, would be helped by persistent connections, and as Scott pointed out, the cure very well might create a different bottleneck on its own.

I'm not saying persistent connections are bad -- not at all! Just that determining whether they are going to be beneficial is very, very tricky.

By the way, in the long run the solution to the problem involved more CPU, more memory, more Internet connectivity, firewall upgrades, persistent database connectivity, upgrades to the ad server hardware and software, and temporary filesystem-based caching of data from the database.

All of them.

Every bottleneck that was eliminated led to a different bottleneck -- or, in some cases, might even create a new bottleneck. Like I said, this is maddeningly difficult, and there are no simple right/wrong answers.

[Anon]

Though I agree that to pin down the cause of performance problem is difficult and (at many times) it's a combination of different causes, we may still try to focus on each problem one at a time (divide-and-conquer technique).

Regarding the mysql_pconnect() usage, I found that it is only useful if the max. number of connections can be forced, otherwise pconnect will only be a hazard to the web site instead of a blessing.

I find that it's rather difficult to control the max. number which requires synchronized request handling implementing in PHP.

Anyone has done some work on it?

Cary