Обсуждение: 6.5 connection time: too slow (?)

Hi,

 I'm finally about to upgrade our prehistoric 6.1 to 6.5.2 and took few
hours to do some highly unscientific (Perl/DBI) benchmarking just to see
the difference... I was quite shocked when I found out that PG6.1 (on
Linux 2.0.34, loaded K6/166 server) was consistently three times faster
when connecting to the database then latest PG6.5.2 (on Linux 2.2.12,
unloaded P2/233, faster disk). The difference was 0.102-0.108 sec vs.
0.293-0.296 sec. For web usage, this is quite slow, even if actual
execute is usually fast enough (0.05-0.15 sec). Does anybody have any
idea what's going on? Specifically, is it possible RedHat 6.1 RPM I'm
using was compiled with some stupid switch that slows things down? Any
advices how to speed things up?

P.S. Apache::DBI is not of much use here, because I'm using 6+ different
databases, so it would keep six backends per http child... Thanks for
your comments.

- Robert

[Charset iso-8859-2 unsupported, filtering to ASCII...]
> Hi,
>
>  I'm finally about to upgrade our prehistoric 6.1 to 6.5.2 and took few
> hours to do some highly unscientific (Perl/DBI) benchmarking just to see
> the difference... I was quite shocked when I found out that PG6.1 (on
> Linux 2.0.34, loaded K6/166 server) was consistently three times faster
> when connecting to the database then latest PG6.5.2 (on Linux 2.2.12,
> unloaded P2/233, faster disk). The difference was 0.102-0.108 sec vs.
> 0.293-0.296 sec. For web usage, this is quite slow, even if actual
> execute is usually fast enough (0.05-0.15 sec). Does anybody have any
> idea what's going on? Specifically, is it possible RedHat 6.1 RPM I'm
> using was compiled with some stupid switch that slows things down? Any
> advices how to speed things up?
>
> P.S. Apache::DBI is not of much use here, because I'm using 6+ different
> databases, so it would keep six backends per http child... Thanks for
> your comments.

Very strange.  Same postmaster flags?  I can't imagine what it would be?
We really have been improving performance, including startup
performance.


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

> Very strange.  Same postmaster flags?  I can't imagine what it would be?
> We really have been improving performance, including startup
> performance.

The only difference I see is -i flag on the 6.5 postmaster, but removing it
doesn't change a thing, connecting is consistently 0.2934-0.2939 sec (only
other flag both server use is -S). To explain, I'm running simple test script
starting

    use DBI;
    use Data::Dumper;
    use Time::HiRes qw/gettimeofday tv_interval/;

    $start = [gettimeofday];
    $db = DBI->connect("DBI:Pg:dbname=rfp") or die $DBI::errstr;
    print "connect: ", tv_interval($start, [gettimeofday]), "\n";

Maybe both servers were compiled with different options, but I don't
know how to check with which ones. Any simple way how to find ou?
BTW, the exact results were

P2/233,PG6.5.2
    connect  0.295
    prepare  0.001
    execute (select * from table)
        60 rows/10 cols     0.153
        15000 rows/4 cols     1.345
        15000 rows/4 cols/LIMIT 30   0.044
        15000 rows/4 cols/LIMIT 30 OFFSET 2000 0.050
    fetch 30  0.002
    finnish  0.000
    disconnect  0.002

K6/166,pg6.4
    connect  0.105
    prepare  0.001
    execute (select * from table)
        60 rows/10 cols     0.162
        15000 rows/4 cols     3.508 (finnish 0.091)
    fetch 30  0.008
    finnish  0.002
    disconnect  0.001

It shows up to LIMIT/OFFSET brings 30fold speed up as expected (main reason
for upgrading, after all) and also that the same (bigger) select takes 1.345
vs. 3.508 sec
which corresponds reasonably well to the slower machine and older postgres.
Only
the connection time is a mystery.

- Robert