Thread: sun solaris & postgres

sun solaris & postgres

From
Cindy
Date:
OK, we have two different solaris machines:

SunOS att 5.8 Generic sun4u sparc SUNW,Ultra-60
and
SunOS steph 5.8 Generic_108528-06 sun4u sparc SUNW,Ultra-4

att is running
psql (PostgreSQL) 7.0.3
contains readline, history, multibyte support

and steph is running
psql (PostgreSQL) 7.2
contains support for: readline, history, multibyte

We've got a web server using the postgres database for its queries.
We're seeing some contradictory data, some of which suggests that
att is faster than steph and at other times the other way around.

If we search on an item that is not to be found in our entries,
steph goes through the postgres entries faster than att, perhaps
by about 10% (not a markedly faster rate, but distinctly faster).
If we search on an item that is commonly found and display 100
results at a time on each page, then what takes att 5 seconds to
generate and display takes steph 30 seconds to do the same thing.

Our tech support is convinced that postgres is running slower on
steph.  I don't think that's the entire story, given the different
results for the different kinds of searches, but I'd appreciate any
suggestions or feedback any of you have, particularly in regards to
what might improve performance on steph.

Is there any indication that 7.2 is slower overall than 7.0.3?

We use the same script to set up the postgres files on each machine,
so they're both configured the same way with respect to indexing
and vaccum/analyse.

Thanks,
--Cindy
--
ctmoore@uci.edu

Re: sun solaris & postgres

From
Date:
Difference specification ??
How many memory in att and steph?
>
> OK, we have two different solaris machines:
>
> SunOS att 5.8 Generic sun4u sparc SUNW,Ultra-60
> and
> SunOS steph 5.8 Generic_108528-06 sun4u sparc SUNW,Ultra-4
>
> att is running
> psql (PostgreSQL) 7.0.3
> contains readline, history, multibyte support
>
> and steph is running
> psql (PostgreSQL) 7.2
> contains support for: readline, history, multibyte
>
> We've got a web server using the postgres database for its queries.
> We're seeing some contradictory data, some of which suggests that
> att is faster than steph and at other times the other way around.
>
> If we search on an item that is not to be found in our entries,
> steph goes through the postgres entries faster than att, perhaps
> by about 10% (not a markedly faster rate, but distinctly faster).
> If we search on an item that is commonly found and display 100
> results at a time on each page, then what takes att 5 seconds to
> generate and display takes steph 30 seconds to do the same thing.
>
> Our tech support is convinced that postgres is running slower on
> steph.  I don't think that's the entire story, given the different
> results for the different kinds of searches, but I'd appreciate any
> suggestions or feedback any of you have, particularly in regards to
> what might improve performance on steph.
>
> Is there any indication that 7.2 is slower overall than 7.0.3?
>
> We use the same script to set up the postgres files on each machine,
> so they're both configured the same way with respect to indexing
> and vaccum/analyse.
>
> Thanks,
> --Cindy
> --
> ctmoore@uci.edu
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>


Re: sun solaris & postgres

From
Neil Conway
Date:
On Sun, 19 May 2002 00:31:49 -0700
"Cindy" <ctmoore@uci.edu> wrote:
> If we search on an item that is not to be found in our entries,
> steph goes through the postgres entries faster than att, perhaps
> by about 10% (not a markedly faster rate, but distinctly faster).
> If we search on an item that is commonly found and display 100
> results at a time on each page, then what takes att 5 seconds to
> generate and display takes steph 30 seconds to do the same thing.

This sounds like you might be sorting the data (of course, without
the actual query or query plan, it's difficult to tell); there was
a performance issue with Sun's implementation of qsort() analyzed
on the list recently -- check the archives.

> Is there any indication that 7.2 is slower overall than 7.0.3?

Not that I'm aware of (the inverse should be true).

Can you confirm that the relevant performance settings in postgresql.conf
are set to reasonable values? (shared_buffers, wal_buffers, sort_mem,
fsync, etc.)

Can you post the EXPLAIN results for the 30-sec/5-sec query on 7.0.3,
the EXPLAIN ANALYZE results for 7.2, and the relevant bits of the schema?

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: sun solaris & postgres

From
Cindy
Date:
mpls@ipv6.dns2go.com writes:
 >
 >Difference specification ??
 >How many memory in att and steph?

steph has
Memory size: 4096 Megabytes
att has
Memory size: 512 Megabytes

--Cindy

Re: sun solaris & postgres

From
Darren Ferguson
Date:
That could be why 7.1.3 is out performing it has 8 times more memory than
the 7.2.1 machine

What about CPU's, controllers and are both config files the same for steph
and att?

Darren Ferguson

On Mon, 20 May 2002, Cindy wrote:

>
> mpls@ipv6.dns2go.com writes:
>  >
>  >Difference specification ??
>  >How many memory in att and steph?
>
> steph has
> Memory size: 4096 Megabytes
> att has
> Memory size: 512 Megabytes
>
> --Cindy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Re: sun solaris & postgres

From
Cindy
Date:
I figured out at least part of the problem...

Neil Conway writes:

 >Can you post the EXPLAIN results for the 30-sec/5-sec query on 7.0.3,
 >the EXPLAIN ANALYZE results for 7.2, and the relevant bits of the schema?

on att:
Text=> explain SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.00 rows=0 width=0)

EXPLAIN
Text=>

on steph:
Text=# explain analyze SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=12.74..132.06 rows=1 loops=1)
Total runtime: 132.23 msec

EXPLAIN
Text=#

on both att, steph, the table is set up as follows:

      create table citations
          (aid smallint, wid smallint,
           citation varchar(50),
           byteloc integer);

      create function get_citation (int2, int2, int4)
        returns SETOF varchar AS
        'select citation from citations where
           aid = $1 and wid = $2 and byteloc = $3'
        LANGUAGE 'SQL';

      truncate citations;
      copy citations from '/data/home/tlg/src/citegen/fullIds.txt';
      create index awbyte_idx on citations(aid,wid,byteloc);
      cluster awbyte_idx on citations;
      create index awcite_idx on citations(aid,wid,citation);
      vacuum citations;
      vacuum analyze citations;

I just found the problem, based in part on the explains.  After changing
the function to

      create function get_citation (int4, int4, int4)
        returns SETOF varchar AS
        'select citation from citations where
           aid = $1::smallint and wid = $2::smallint and byteloc = $3'
        LANGUAGE 'SQL';

on steph, the performance has pretty much improved to match that of att.
Would the changes between 7.0 and 7.2 account for this?  (There's no
explain analyze in 7.0, so I can't make a more detailed analysis of the
function on att.)  But now on steph:

Text=# explain analyze SELECT get_citation(4,1,815460);
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=6.83..6.87 rows=1 loops=1)
Total runtime: 7.00 msec

EXPLAIN


--
Cindy
ctmoore@uci.edu

Re: sun solaris & postgres

From
Cindy
Date:
No, if you read more carefully, att is the one that is performing its
operations an order of magnitude faster than steph, which is why we're
so stumped.  But I think I located the problem (see other email).

Darren Ferguson writes:
 >That could be why 7.1.3 is out performing it has 8 times more memory than
 >the 7.2.1 machine
 >
 >What about CPU's, controllers and are both config files the same for steph
 >and att?
 >
 >Darren Ferguson
 >
 >On Mon, 20 May 2002, Cindy wrote:
 >
 >>
 >> mpls@ipv6.dns2go.com writes:
 >>  >
 >>  >Difference specification ??
 >>  >How many memory in att and steph?
 >>
 >> steph has
 >> Memory size: 4096 Megabytes
 >> att has
 >> Memory size: 512 Megabytes
 >>
 >> --Cindy
 >>
 >> ---------------------------(end of broadcast)---------------------------
 >> TIP 5: Have you checked our extensive FAQ?
 >>
 >> http://www.postgresql.org/users-lounge/docs/faq.html
 >>
 >
 >
 >---------------------------(end of broadcast)---------------------------
 >TIP 6: Have you searched our list archives?
 >
 >http://archives.postgresql.org
 >

--
Cindy
ctmoore@uci.edu