Re: Persistent Connections in Webserver Environment

On Mon, 2005-05-02 at 17:32 +0200, Hannes Dorbath wrote:
> On 02.05.2005 16:41, Marco Colombo wrote:
>
> > Have you measured the real gain in using persistent connections at all?
>
> As simple as possible:
>
> <?php
> require_once('Benchmark/Timer.php');
> $timer =& new Benchmark_Timer();
> $timer->start();
>
> pg_pconnect('host=myhost dbname=database user=user');
> pg_query("SET search_path TO myschema;");
>
> $q = "SELECT u.login FROM users WHERE u.user_id = 1;";
>
> $qr = pg_query($q);
>
> print_r(pg_fetch_all($qr));
>
> $timer->setMarker('Database');
> $timer->stop();
> $timer->display();
> ?>
>
> Results:
>
> pconnect: 0.001435995101928
> connect: 0.016793966293335
>
> It's factor 10 on such simple things on the BSD box.

Ok, but the difference is going to be unnoticed, that's not the point
at all.

The question was: have you measured any difference in the server load?
I did in the past and wasn't really able to measure it, with more than
300 http processes active. The web server load is _way_ lower than the
db server. Currently we're about at 100 processes (but with pconnect)
and:

(web) load average: 0.31, 0.27, 0.21
(db) load average: 0.24, 0.21, 0.18

and I know that turning to use simple connect won't change much as page
load time is dominated by the time spent in the queries (and the
overhead of 1/100 or 1/1000 of second in the startup time goes unnoticed
at all).

With any modern operating system, the overhead is very low (15ms is very
good actually).

In my experience, pconnect my cause RAM problems. The number of
processes is useless high. You have make provisions for a large
number of backends, and that means little RAM to single backend.

My advice is: use pconnect only when you have CPU problems,
unless your case is very degenerated one (your db host being on the
other side of the globe).

And, in my experience again, the first reasons for CPU problems on
the database server are:

- wrong/missing vacuum/analyze (or similar);
- bad coding on the application side (placing silly load on the server);
- bad queries (misuse/lack of indexes);
- bad tuning of PostgreSQL (expecially RAM);
...
...
- connect overhead.

I've never managed to reach the last item in the list in real world
cases. I think it is by far the least important item.

#1 Golden Rule for optimizing:
- Don't.

(Expecially when it causes _real_ troubles elsewhere.)

Have a nice day,
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo(at)ESI(dot)it