Обсуждение: sun solaris & postgres
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
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 >
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
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
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 >
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
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