Re: [PERFORM] scaling up postgres

[Sven Geisler]

Hi Mario,

I did run pgbench on several production servers:
HP DL585 - 4-way AMD Opteron 875
HP DL585 - 4-way AMD Opteron 880
HP DL580 G3 - 4-way Intel XEON MP 3.0 GHz
FSC RX600 S2 - 4-way Intel XEON MP DC 2.66 GHz
FSC RX600 - 4-way Intel XEON MP 2.5 GHz

This test has been done with 8.1.4. I increased the number of clients.
I attached the result as diagram. I included not all test system but the 
gap between XEON and Opteron is always the same.


The experiences with production systems were the same. We replaced the 
XEON box with Opteron box with a dramatic change of performance.


Best regards
Sven.


Mario Splivalo schrieb:

On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote:

On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote:

I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC)
One beast will be apache, and the other will be postgres.
I'm using httperf/autobench for measurments and the best result I can get 
is that my system can handle a trafiic of almost 1600 New con/sec.

What version of PostgreSQL? (8.1 is better than 8.0 is much better than 7.4.)
Have you remembered to turn HT off? Have you considered Opterons instead of
Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind of queries


Could you point out to some more detailed reading on why Xeons are
poorer choice than Opterons when used with PostgreSQL?

Mario


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


--
/This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you are not the intended recipient, you should not
copy it, re-transmit it, use it or disclose its contents, but should
return it to the sender immediately and delete your copy from your
system. Thank you for your cooperation./

Sven Geisler <[EMAIL PROTECTED]> Tel +49.30.5362.1627 Fax .1638
Senior Developer,AEC/communications GmbHBerlin,   Germany


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] scaling up postgres

[Alex Stapleton]

On 12 Jun 2006, at 00:21, Joshua D. Drake wrote:


Mario Splivalo wrote:

On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote:

On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote:

I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC)
One beast will be apache, and the other will be postgres.
I'm using httperf/autobench for measurments and the best result  
I can get is that my system can handle a trafiic of almost 1600  
New con/sec.
What version of PostgreSQL? (8.1 is better than 8.0 is much  
better than 7.4.)
Have you remembered to turn HT off? Have you considered Opterons  
instead of
Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind  
of queries

Could you point out to some more detailed reading on why Xeons are
poorer choice than Opterons when used with PostgreSQL?


It isn't just PostgreSQL. It is any database. Opterons can move  
memory and whole lot faster then Xeons.


A whole lot faster indeed.

http://www.amd.com/us-en/Processors/ProductInformation/ 
0,,30_118_8796_8799,00.html

http://www.theinquirer.net/?article=10797

Although apparently the dual core ones are a little better than the  
old ones


http://www.anandtech.com/IT/showdoc.aspx?i=2644

(Just to provide some evidence ;)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] scaling up postgres

[Sven Geisler]

Hi all,

Joshua D. Drake schrieb:

Mario Splivalo wrote:

Could you point out to some more detailed reading on why Xeons are
poorer choice than Opterons when used with PostgreSQL?


It isn't just PostgreSQL. It is any database. Opterons can move memory 
and whole lot faster then Xeons.


Yes. You can run good old memtest86 and you see the difference.
Here my numbers with memtest86 (blocksize 128 MB).
HP DL580 G3 (4-way XEON MP - DDR RAM) => 670 MByte/sec
FSC RX600 S2 (4-way XEOM MP DC - DDR2-400 PC2-3200) => 1300 MByte/sec
HP DL585 (4-way Opteron DDR2-400 PC2-3200) => 1500 MByte/sec
I used memxfer5b.cpp.

Cheers Sven.

---(end of broadcast)---
TIP 6: explain analyze is your friend

[PERFORM] Posrgres speed problem

[Ruben Rubio Rey]

Hi,

Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel 
2.6.9-1.667smp)


I have two similar servers, one in production and another for testing 
purposes.

Databases are equal (with a difference of some hours)

In the testing server, an sql sentence takes arround 1 sec.
In production server (low server load) takes arround 50 secs, and uses 
too much resources.


Explain analyze takes too much load, i had to cancel it!

Could it be a  it a bug?
Any ideas?

Thanks in advance



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Posrgres speed problem

[Dave Dutcher]

Do you run analyze on the production server regularly?


> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of 
> Ruben Rubio Rey
> Sent: Monday, June 12, 2006 9:39 AM
> To: pgsql-performance@postgresql.org
> Subject: [PERFORM] Posrgres speed problem
> 
> 
> 
> Hi,
> 
> Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel 
> 2.6.9-1.667smp)
> 
> I have two similar servers, one in production and another for testing 
> purposes.
> Databases are equal (with a difference of some hours)
> 
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs, 
> and uses 
> too much resources.
> 
> Explain analyze takes too much load, i had to cancel it!
> 
> Could it be a  it a bug?
> Any ideas?
> 
> Thanks in advance
> 
> 
> 
> ---(end of 
> broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
   http://www.postgresql.org/docs/faq


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Posrgres speed problem

[Ruben Rubio Rey]

Gábriel Ákos wrote:


Ruben Rubio Rey wrote:



Hi,

Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel 
2.6.9-1.667smp)


I have two similar servers, one in production and another for testing 
purposes.

Databases are equal (with a difference of some hours)

In the testing server, an sql sentence takes arround 1 sec.
In production server (low server load) takes arround 50 secs, and 
uses too much resources.


Explain analyze takes too much load, i had to cancel it!

Could it be a  it a bug?
Any ideas?



vacuum full analyse the database.



I use to do it all nights
Its an script with content:

DIREC=/usr/local/pgsql/bin/
DIRLOGS=/var/log/rentalia
LOGBIN=/usr/sbin/cronolog
echo "vacuum vacadb..." | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN 
$DIRLOGS/%Y-%m-%d_limpieza.log
echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN 
$DIRLOGS/%Y-%m-%d_limpieza.log

date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log

No errors or warnings are reported. instead repeating it now, I preffer 
to wait at tomorrow to check again the logs


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Posrgres speed problem

[Ruben Rubio Rey]

Jonah H. Harris wrote:


On 6/12/06, Ruben Rubio Rey <[EMAIL PROTECTED]> wrote:


I have two similar servers, one in production and another
for testing purposes.  In testing server ~1sec ... in
production ~50 secs



What ver of PostgreSQL?


Version 8.1.3


  Same ver on both systems?


Yes


Are there any
locks currently held on the resources needed in your Production
environment?


How to check it?


Have you analyzed both databases?


I have restores testing server today. Full Analyce included.
Production server all nights is done. (i have posted the script in other 
message to the mailing list)



Any sequential scans
running?


In the table, there is several scans.

vacadb=# \d grupoforo
   Table "public.grupoforo"
 Column  |Type 
|   Modifiers

--+-+---
idmensaje| integer | not null default 
nextval('grupoforo_idmensaje_seq'::regclass)

idusuario| integer | not null
idgrupo  | integer | not null
idmensajetema| integer | not null default -1
mensaje  | character varying(4000) |
asunto   | character varying(255)  | not null
fechalocal   | timestamp without time zone | default now()
webenabled   | integer | not null default 1
por  | character varying(255)  |
estadocomentario | character(1)| default 'D'::bpchar
idlenguaje   | character(2)| default 'ES'::bpchar
fechacreacion| timestamp without time zone | default now()
hijos| integer |
hijoreciente | timestamp without time zone |
valoracion   | integer | default 0
codigo   | character varying(100)  |
Indexes:
   "pk_grupoforo" PRIMARY KEY, btree (idmensaje)
   "grupoforo_asunto_idx" btree (asunto)
   "grupoforo_codigo_idx" btree (codigo)
   "grupoforo_estadocomentario_idx" btree (estadocomentario)
   "grupoforo_idgrupo_idx" btree (idgrupo)
   "grupoforo_idlenguaje_idx" btree (idlenguaje)
   "grupoforo_idmensajetema_idx" btree (idmensajetema)
   "grupoforo_idusuario_idx" btree (idusuario)
   "idx_grupoforo_webenabled" btree (webenabled)



If so, have you vacuumed?


Yes.



Send the explain analyze from your test database.


Tomorrow morning i ll send it ... now it could be a disaster ...







---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] Posrgres speed problem

[Michael Fuhr]

On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
> I have two similar servers, one in production and another for testing 
> purposes.
> Databases are equal (with a difference of some hours)
> 
> In the testing server, an sql sentence takes arround 1 sec.
> In production server (low server load) takes arround 50 secs, and uses 
> too much resources.
> 
> Explain analyze takes too much load, i had to cancel it!

The EXPLAIN ANALYZE output would be helpful, but if you don't want
to run it to completion then please post the output of EXPLAIN
ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
slow one.

As someone else asked, are you running ANALYZE regularly?  What
about VACUUM?

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] pgsql_tmp and postgres settings

[Jim C. Nasby]

On Mon, Jun 12, 2006 at 11:26:23AM +0530, Gourish Singbal wrote:
> Where is the pgsql_tmp folder present ?.  i am unable to see it in the data
> directory of postgresql.

It will be under the *database* directory, under $PGDATA/base. SELECT
oid,* FROM pg_database; will tell you what directory to look in for your
database.

> On 6/9/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >On Fri, Jun 09, 2006 at 02:23:04PM +0200, Domenico - Sal. F.lli Riva
> >wrote:
> >> Hello,
> >>
> >> During insert or update, potgresql write in pgsql_tmp directory and so
> >> performance are very poor.
> >
> >pgsql_tmp is used if a query runs out of work_mem, so you can try
> >increasing that.
> >
> >> My configuration is:
> >>
> >> Work mem10240
> >>
> >> Effective_cache_size  3
> >You're off by a factor of 10.
> >
> >> Shared buffers  9000
> >I'd suggest bumping that up to at least 3.
> >
> >> Postgresql (RPM from official website) 8.1.0
> >
> >You should upgrade to 8.1.4. There's a number of data loss bugs waiting
> >to bite you.
> >--
> >Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
> >Pervasive Software  http://pervasive.comwork: 512-231-6117
> >vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461
> >
> >---(end of broadcast)---
> >TIP 4: Have you searched our list archives?
> >
> >  http://archives.postgresql.org
> >
> 
> 
> 
> -- 
> Best,
> Gourish Singbal

-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Posrgres speed problem

[Jim C. Nasby]

On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
> $DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN 
> $DIRLOGS/%Y-%m-%d_limpieza.log
> echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN 
> $DIRLOGS/%Y-%m-%d_limpieza.log
> date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log

Ugh. Is there some reason you're not using the built-in autovacuum? If
you enable it and cut the thresholds in half you'll most likely never
need to vacuum manually, let alone reindex.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Posrgres speed problem

[Ruben Rubio Rey]

Jim C. Nasby wrote:


On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
 

$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN 
$DIRLOGS/%Y-%m-%d_limpieza.log
echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN 
$DIRLOGS/%Y-%m-%d_limpieza.log

date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
   



Ugh. Is there some reason you're not using the built-in autovacuum?


How do I execute built-in autovacuum?



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Posrgres speed problem

[Jim C. Nasby]

On Mon, Jun 12, 2006 at 09:05:06AM -0600, Michael Fuhr wrote:
> On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
> > I have two similar servers, one in production and another for testing 
> > purposes.
> > Databases are equal (with a difference of some hours)
> > 
> > In the testing server, an sql sentence takes arround 1 sec.
> > In production server (low server load) takes arround 50 secs, and uses 
> > too much resources.
> > 
> > Explain analyze takes too much load, i had to cancel it!
> 
> The EXPLAIN ANALYZE output would be helpful, but if you don't want
> to run it to completion then please post the output of EXPLAIN
> ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
> slow one.
> 
> As someone else asked, are you running ANALYZE regularly?  What
> about VACUUM?

For the next vacuum, can you add the -v (verbose) switch and email the
last few lines of output?

INFO:  free space map contains 39 pages in 56 relations
DETAIL:  A total of 896 page slots are in use (including overhead).
896 page slots are required to track all free space.
Current limits are:  2 page slots, 1000 relations, using 223 KB.
VACUUM
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] Posrgres speed problem

[Jim C. Nasby]

On Mon, Jun 12, 2006 at 05:22:05PM +0200, Ruben Rubio Rey wrote:
> Jim C. Nasby wrote:
> 
> >On Mon, Jun 12, 2006 at 04:58:49PM +0200, Ruben Rubio Rey wrote:
> > 
> >
> >>$DIREC/vacuumdb -f -v --analyze vacadb 2>&1 | $LOGBIN 
> >>$DIRLOGS/%Y-%m-%d_limpieza.log
> >>echo "reindex database vacadb;" | $DIREC/psql vacadb 2>&1 | $LOGBIN 
> >>$DIRLOGS/%Y-%m-%d_limpieza.log
> >>date | $LOGBIN $DIRLOGS/%Y-%m-%d_limpieza.log
> >>   
> >>
> >
> >Ugh. Is there some reason you're not using the built-in autovacuum?
> >
> How do I execute built-in autovacuum?

Make the following changes to postgresql.conf:

autovacuum = on # enable autovacuum subprocess?
autovacuum_vacuum_threshold = 500   # min # of tuple updates before
# vacuum
autovacuum_analyze_threshold = 200  # min # of tuple updates before 
autovacuum_vacuum_scale_factor = 0.2# fraction of rel size before 
# vacuum
autovacuum_analyze_scale_factor = 0.1   # fraction of rel size before 
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Posrgres speed problem

[Sven Geisler]

Hi Ruben,

Ruben Rubio Rey schrieb:


Hi,

Im having a problem with postgres 8.1.3 on a Fedora Core 3 (kernel 
2.6.9-1.667smp)


I have two similar servers, one in production and another for testing 
purposes.

Databases are equal (with a difference of some hours)

In the testing server, an sql sentence takes arround 1 sec.
In production server (low server load) takes arround 50 secs, and uses 
too much resources.


Explain analyze takes too much load, i had to cancel it!

Could it be a  it a bug?
Any ideas?


How do you load the data to the testing server? (Dump, Copy, etc)
As you wrote the difference are some hours. I think you copy something.

It is possible that you production database as too much deleted tuples.
Vacuum full does only rebuild the table an not the index. You may also 
run reindex on certain tables. I guess, this may the issue if you use 
dump/restore to get your production copy.


Is three a huge difference in the result of this queries:
select relname,relpages,reltuples from pg_class order by relpages desc;
and
select relname,relpages,reltuples from pg_class where relname like 
'%index' order by relpages desc;


Cheers Sven.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[PERFORM] Interesting slow query

[PFC]

	Here are two ways to phrase a query... the planner choses very different  
plans as you will see. Everything is freshly ANALYZEd.



EXPLAIN ANALYZE SELECT r.* FROM raw_annonces r LEFT JOIN annonces a ON  
a.id=r.id LEFT JOIN archive_data d ON d.id=r.id WHERE a.id IS NULL AND  
d.id IS NULL AND r.id >1130306 order by id limit 1;
  QUERY  
PLAN

--
 Limit  (cost=0.00..2.54 rows=1 width=627) (actual time=708.167..708.168  
rows=1 loops=1)
   ->  Merge Left Join  (cost=0.00..128497.77 rows=50539 width=627)  
(actual time=708.165..708.165 rows=1 loops=1)

 Merge Cond: ("outer".id = "inner".id)
 Filter: ("inner".id IS NULL)
 ->  Merge Left Join  (cost=0.00..27918.92 rows=50539 width=627)  
(actual time=144.519..144.519 rows=1 loops=1)

   Merge Cond: ("outer".id = "inner".id)
   Filter: ("inner".id IS NULL)
   ->  Index Scan using raw_annonces_pkey on raw_annonces r   
(cost=0.00..11222.32 rows=50539 width=627) (actual time=0.040..0.040  
rows=1 loops=1)

 Index Cond: (id > 1130306)
   ->  Index Scan using annonces_pkey on annonces a   
(cost=0.00..16118.96 rows=65376 width=4) (actual time=0.045..133.272  
rows=65376 loops=1)
 ->  Index Scan using archive_data_pkey on archive_data d   
(cost=0.00..98761.01 rows=474438 width=4) (actual time=0.060..459.995  
rows=474438 loops=1)

 Total runtime: 708.316 ms

EXPLAIN ANALYZE SELECT * FROM raw_annonces r WHERE r.id>1130306 AND NOT  
EXISTS( SELECT id FROM annonces WHERE id=r.id ) AND NOT EXISTS( SELECT id  
FROM archive_data WHERE id=r.id ) ORDER BY id LIMIT 1;
QUERY  
PLAN

---
 Limit  (cost=0.00..38.12 rows=1 width=627) (actual time=0.040..0.041  
rows=1 loops=1)
   ->  Index Scan using raw_annonces_pkey on raw_annonces r   
(cost=0.00..481652.07 rows=12635 width=627) (actual time=0.039..0.039  
rows=1 loops=1)

 Index Cond: (id > 1130306)
 Filter: ((NOT (subplan)) AND (NOT (subplan)))
 SubPlan
   ->  Index Scan using archive_data_pkey on archive_data   
(cost=0.00..3.66 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)

 Index Cond: (id = $0)
   ->  Index Scan using annonces_pkey on annonces   
(cost=0.00..5.65 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

 Index Cond: (id = $0)
 Total runtime: 0.121 ms


Ideas ?

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] Interesting slow query

[Tom Lane]

PFC <[EMAIL PROTECTED]> writes:
>   Here are two ways to phrase a query... the planner choses very 
> different  
> plans as you will see. Everything is freshly ANALYZEd.

Usually we get complaints the other way around (that the NOT EXISTS
approach is a lot slower).  You did not show any statistics, but I
suspect the key point here is that the condition id > 1130306 excludes
most or all of the A and D tables.  The planner is not smart about
making transitive inequality deductions, but you could help it along
by adding the implied clauses yourself:

EXPLAIN ANALYZE SELECT r.* FROM raw_annonces r
  LEFT JOIN annonces a ON (a.id=r.id AND a.id > 1130306)
  LEFT JOIN archive_data d ON (d.id=r.id AND d.id > 1130306)
  WHERE a.id IS NULL AND d.id IS NULL AND r.id > 1130306
  order by id limit 1;

Whether this is worth doing in your app depends on how often you do
searches at the end of the ID range ...

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[PERFORM] 64-bit vs 32-bit performance ... backwards?

[Anthony Presley]

Hi all!

I had an interesting discussion today w/ an Enterprise DB developer and
sales person, and was told, twice, that the 64-bit linux version of
Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
are PostgreSQL . has anyone seen that the 64-bit is slower than the
32-bit version?

I was told that the added 32-bits puts a "strain" and extra "overhead"
on the processor / etc which actually slows down the pointers and
necessary back-end "stuff" on the database.

I'm curious if anyone can back this up  or debunk it.  It's about
the polar opposite of everything I've heard from every other database
vendor for the past several years, and would be quite an eye-opener for
me.

Anyone?

Thanks.

--
Anthony


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Josh Berkus]

Anthony,

> I'm curious if anyone can back this up  or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

I generally see a 20% "free" gain in performance on 64-bit (Opteron, 
actually).  Possibly EDB is still using ICC to compile, and ICC is bad at 
64-bit?

I have seen some applications which failed to gain any performance from 
64-bit, but have never personally dealt with one which was slower.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[David Boreham]

Anthony Presley wrote:


I had an interesting discussion today w/ an Enterprise DB developer and
sales person, and was told, twice, that the 64-bit linux version of
Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
are PostgreSQL . has anyone seen that the 64-bit is slower than the
32-bit version?

I was told that the added 32-bits puts a "strain" and extra "overhead"
on the processor / etc which actually slows down the pointers and
necessary back-end "stuff" on the database.

I'm curious if anyone can back this up  or debunk it.  It's about
the polar opposite of everything I've heard from every other database
vendor for the past several years, and would be quite an eye-opener for
me.
 

What they are saying is strictly true : 64-bit pointers tend to increase 
the working set size
of an application vs. 32-bit pointers. This means that any caches will 
have somewhat lower
hit ratio. Also the bytes/s between the CPU and memory will be higher 
due to moving those larger pointers.
In the case of a 32-bit OS this also applies to the kernel so the effect 
will be system-wide.


However, an application that needs to work on > around 2G of data will 
in the end be
much faster 64-bit due to reduced I/O (it can keep more of the data in 
memory).


I worked on porting a large database application from 32-bit to 64-bit. One
of our customers required us to retain the 32-bit version because of 
this phenomenon.


In measurements I conducted on that application, the performance 
difference wasn't

great (10% or so), but it was measurable. This was with Sun Sparc hardware.
It is possible that more modern CPU designs have more efficient 64-bit
implementation than 32-bit, so the opposite might be seen too.

Whether or not PG would show the same thing I can't say for sure. 
Probably it would though.











---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Steve Atkins]

On Jun 12, 2006, at 3:28 PM, Anthony Presley wrote:


Hi all!

I had an interesting discussion today w/ an Enterprise DB developer  
and

sales person, and was told, twice, that the 64-bit linux version of
Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of  
EDB
are PostgreSQL . has anyone seen that the 64-bit is slower than  
the

32-bit version?

I was told that the added 32-bits puts a "strain" and extra "overhead"
on the processor / etc which actually slows down the pointers and
necessary back-end "stuff" on the database.

I'm curious if anyone can back this up  or debunk it.  It's about
the polar opposite of everything I've heard from every other database
vendor for the past several years, and would be quite an eye-opener  
for

me.

Anyone?


It's unsurprising for code written with 64 bit pointers ("64 bit  
code") to be a little
slower than 32 bit code. The code and data structures are bigger,  
more has to

be copied from main memory, fewer cache hits, all those bad things.

On CPUs with a uniform instructions set in both 32 and 64 bit modes  
you're

only likely to see improved performance in 64 bit mode if your code can
take advantage of the larger address space (postgresql doesn't).

Some x86-esque architectures provide a somewhat different instruction
set in their 64 bit mode, with more programmer visible registers. The
increase in performance they can offer (with the right compiler) can  
offset

the reduction due to pointer bloat, in some cases.

Empirically... postgresql built for 64 bits is marginally slower than  
that built

for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested on  
any

of those architectures.

Cheers,
  Steve

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Tom Lane]

Anthony Presley <[EMAIL PROTECTED]> writes:
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL . has anyone seen that the 64-bit is slower than the
> 32-bit version?

That is a content-free statement, since they didn't mention what
architectures they are comparing, what compilers (and compiler options)
they are using, or what test cases they are measuring on.

Theoretically speaking, 64-bit *should* be slower than 32-bit (because
more data to transfer between memory and CPU to accomplish the same
work), except when considering workloads that can profit from having
direct access to more than 4Gb of memory.  However the theoretical
advantage is probably completely swamped by implementation details,
ie, how tensely did the designers of your 64-bit chip optimize its
32-bit behavior.

I believe that Red Hat generally recommends using 32-bit mode for
small-memory applications on PPC machines, because PPC32 is indeed
measurably faster than PPC64, but finds no such advantage on x86_64,
ia64 or s390x.  I don't know what applications they tested to come
to that conclusion, though.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Stephen Frost]

* Anthony Presley ([EMAIL PROTECTED]) wrote:
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL . has anyone seen that the 64-bit is slower than the
> 32-bit version?

Alot of it depends on what you're doing in the database, exactly, and
just which 32/64-bit platform is under discussion..  They're not all the
same (not even just amoung the ones Linux runs on :).

> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc which actually slows down the pointers and
> necessary back-end "stuff" on the database.

That's so hand-wavy that I'd be disinclined to believe the speaker, so
I'll assume you're (poorly) paraphrasing...  It's true that running
64bit means that you've got 64bit pointers, which are physically
larger than 32bit pointers.  Larger pointers means more effort to keep
track of them, copy them around, etc.  This is mitigated on some
platforms (ie: amd64) where there are extra registers available in
'64bit' mode (which is really more than just a 64bit mode of a 32bit
platform, unlike a platform like PPC or sparc).

> I'm curious if anyone can back this up  or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

PostgreSQL doesn't generally operate on >2G resident memory.  I'm not
sure if it's possible for it to (I havn't really tried to find out,
though I have systems where it'd be possible to want to sort a >2G table
or similar, I don't have the work_mem set high enough for it to try, I
don't think).  This is because Postgres lets the OS handle most of the
cacheing, so as long as your OS can see all the memory you have in the
box, that benefit of running 64bit isn't going to be seen on Postgres.
On many other database systems (notably the 800-pound gorillas...) the
database handle the cacheing and so wants to basically have control over
all the memory in the box, which means running 64bit if you have more
than 2G in your system.

Just my 2c.

Enjoy,

Stephen


signature.asc
Description: Digital signature

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Bill Moran]

Anthony Presley <[EMAIL PROTECTED]> wrote:

> Hi all!
> 
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL . has anyone seen that the 64-bit is slower than the
> 32-bit version?
> 
> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc which actually slows down the pointers and
> necessary back-end "stuff" on the database.
> 
> I'm curious if anyone can back this up  or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.

We did some tests on with identical hardware in both EMT64 and ia32 mode.
(Dell 2850, if you're curious)  This was PostgreSQL 8.1 running on
FreeBSD 6.

We found 64 bit to be ~5% slower than 32 bit mode in the (very) limited
tests that we did.  We pulled the plug before doing any extensive
testing, because it just didn't seem as if it was going to be worth it.

-- 
Bill Moran

I already know the ending it's the part that makes your face implode.
I don't know what makes your face implode, but that's the way the movie ends.

TMBG


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Joshua D. Drake]

Empirically... postgresql built for 64 bits is marginally slower than 
that built

for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested on any
of those architectures.


Really? All of our customers are migrating to Opteron and I have many 
that have been using Opteron for over 12 months happily.


Joshua D. Drake



Cheers,
  Steve

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq




--

=== The PostgreSQL Company: Command Prompt, Inc. ===
  Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
  Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Steve Atkins]

On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:



Empirically... postgresql built for 64 bits is marginally slower  
than that built

for a 32 bit api on sparc. None of my customers have found 64 bit x86
systems to be suitable for production use, yet, so I've not tested  
on any

of those architectures.


Really? All of our customers are migrating to Opteron and I have  
many that have been using Opteron for over 12 months happily.


An Opteron is 64 bit capable; that doesn't mean you have to run 64 bit
code on it.

Mine're mostly reasonably conservative users, with hundreds of machines
to support. Using 64 bit capable hardware, such as Opterons, is one  
thing,
but using an entirely different linux installation and userspace  
code, say, is

a much bigger change in support terms. In the extreme case it makes no
sense to double your OS support overheads to get a single digit  
percentage

performance improvement on one database system.

That's not to say that linux/x86-64 isn't production ready for some  
users, just
that it's not necessarily a good operational decision for my  
customers. Given
my internal workloads aren't really stressing the hardware they're on  
I don't

have much incentive to benchmark x86-64 yet - by the time the numbers
might be useful to me we'll be on a different postgresql, likely a  
different

gcc/icc and so on.

Cheers,
  Steve


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Luke Lonergan]

Title: Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?




Opteron is ~20% faster at executing code in 64-bit mode than 32-bit because of the extra registers made available with their 64-bit mode:
  http://www.tomshardware.com/2003/04/22/duel_of_the_titans/page7.html

Doubling the GPRs from 8 to 16 has generally made a 20%-30% difference in CPU-bound work:
  http://www.tomshardware.com/2003/04/22/duel_of_the_titans/page18.html

If the task is memory bandwidth bound, there should be an advantage to using less memory for the same task, but if the database is using types that are the same width for either execution mode, you wouldn't expect a significant difference just from wider pointer arithmetic.

- Luke 

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[mark]

I've been trying to track this stuff - in fact, I'll likely be
switching from AMD32 to AMD64 in the next few weeks.

I believe I have a handle on the + vs - of 64-bit. It makes sense that
full 64-bit would be slower. At an extreme it halfs the amount of
available memory or doubles the required memory bandwidth, depending
on the work load.

Has anybody taken a look at PostgreSQL to ensure that it uses 32-bit
integers instead of 64-bit integers where only 32-bit is necessary?
32-bit offsets instead of 64-bit pointers? This sort of thing?

I haven't. I'm meaning to take a look. Within registers, 64-bit should
be equal speed to 32-bit. Outside the registers, it would make sense
to only deal with the lower 32-bits where 32-bits is all that is
required.

Cheers,
mark

-- 
[EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] 
__
.  .  _  ._  . .   .__.  . ._. .__ .   . . .__  | Neighbourhood Coder
|\/| |_| |_| |/|_ |\/|  |  |_  |   |/  |_   | 
|  | | | | \ | \   |__ .  |  | .|. |__ |__ | \ |__  | Ottawa, Ontario, Canada

  One ring to rule them all, one ring to find them, one ring to bring them all
   and in the darkness bind them...

   http://mark.mielke.cc/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Alex Turner]

Anyone who has tried x86-64 linux knows what a royal pain in the ass it is.   They didn't do anything sensible, like just make the whole OS 64 bit, no, they had to split it up, and put 64-bit libs in a new directory /lib64.  This means that a great many applications don't know to check in there for libs, and don't compile pleasantly, php is one among them.  I forget what others, it's been awhile now.  Of course if you actualy want to use more than 4gig RAM in a pleasant way, it's pretty much essential.
Alex.On 6/12/06, Steve Atkins <[EMAIL PROTECTED]> wrote:
On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:>>> Empirically... postgresql built for 64 bits is marginally slower>> than that built>> for a 32 bit api on sparc. None of my customers have found 64 bit x86
>> systems to be suitable for production use, yet, so I've not tested>> on any>> of those architectures.>> Really? All of our customers are migrating to Opteron and I have> many that have been using Opteron for over 12 months happily.
An Opteron is 64 bit capable; that doesn't mean you have to run 64 bitcode on it.Mine're mostly reasonably conservative users, with hundreds of machinesto support. Using 64 bit capable hardware, such as Opterons, is one
thing,but using an entirely different linux installation and userspacecode, say, isa much bigger change in support terms. In the extreme case it makes nosense to double your OS support overheads to get a single digit
percentageperformance improvement on one database system.That's not to say that linux/x86-64 isn't production ready for someusers, justthat it's not necessarily a good operational decision for my
customers. Givenmy internal workloads aren't really stressing the hardware they're onI don'thave much incentive to benchmark x86-64 yet - by the time the numbersmight be useful to me we'll be on a different postgresql, likely a
differentgcc/icc and so on.Cheers,   Steve---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Christopher Browne]

[EMAIL PROTECTED] (Anthony Presley) wrote:
> Hi all!
>
> I had an interesting discussion today w/ an Enterprise DB developer and
> sales person, and was told, twice, that the 64-bit linux version of
> Enterprise DB (which is based on the 64-bit version of PostgreSQL 8.1)
> is SIGNIFICANTLY SLOWER than the 32-bit version.  Since the guys of EDB
> are PostgreSQL . has anyone seen that the 64-bit is slower than the
> 32-bit version?
>
> I was told that the added 32-bits puts a "strain" and extra "overhead"
> on the processor / etc which actually slows down the pointers and
> necessary back-end "stuff" on the database.
>
> I'm curious if anyone can back this up  or debunk it.  It's about
> the polar opposite of everything I've heard from every other database
> vendor for the past several years, and would be quite an eye-opener for
> me.
>
> Anyone?

Traditionally, there has been *some* truth to such assertions.

Consider:

1.  64 bit versions of things need to manipulate 64 bit address values
and such; this will bloat up the code somewhat as compared to 32 bit
versions that will be somewhat more compact.

2.  If you only have 2GB of memory, you get no particular advantage
out of 64 bittedness.

In the days when people had 64 bit Alphas with 256MB of memory, there
was considerable debate about the actual merits of running in 64 bit
mode, and the answers were unobvious.

On the other hand...

3.  Opterons tend to address memory quite a bit quicker than Intels of
pretty much any variety.

4.  64 bit CPUs offer additional registers that can be expected to
make register-bound code quicker.

5.  If you have >>2GB of memory, a 64 bit system is needful to harness
that, and that will make a *big* difference to performance.

The overall claim is somewhat content-free, in the absence of
information about the architecture of the database server.
-- 
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://linuxfinances.info/info/
"A program invented (sic) by a Finnish computer hacker and handed out free
in 1991 cost investors in Microsoft $11 billion (#6.75 billion) this week."
-- Andrew Butcher in the UK's Sunday Times, Feb 20th, 1999

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Tom Lane]

"Alex Turner" <[EMAIL PROTECTED]> writes:
> Anyone who has tried x86-64 linux knows what a royal pain in the ass it
> is.   They didn't do anything sensible, like just make the whole OS 64 bit,
> no, they had to split it up, and put 64-bit libs in a new directory /lib64.

Actually, there's nothing wrong with that.  As this thread already made
clear, there are good reasons why you might want to run 32-bit apps as
well as 64-bit apps on your 64-bit hardware.  So the 32-bit libraries
live in /usr/lib and the 64-bit ones in /usr/lib64.  If you ask me, the
really serious mistake in this design is they didn't decree separate bin
directories /usr/bin and /usr/bin64 too.  This makes it impossible to
install 32-bit and 64-bit versions of the same package at the same time,
something that curiously enough people are now demanding support for.

(Personally, if I'd designed it, the libraries would actually live in
/usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
you needed it to be at the moment.  Likewise for /usr/bin.)

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] ("Alex Turner") 
wrote:
> Anyone who has tried x86-64 linux knows what a royal pain in the ass
> it is.   They didn't do anything sensible, like just make the whole
> OS 64 bit, no, they had to split it up, and put 64-bit libs in a new
> directory /lib64.  This means that a great many applications don't
> know to check in there for libs, and don't compile pleasantly, php
> is one among them.  I forget what others, it's been awhile now.  Of
> course if you actualy want to use more than 4gig RAM in a pleasant
> way, it's pretty much essential.  Alex.

That's absolute nonsense.

I have been running the Debian AMD64 port since I can't recall when.
I have experienced NO such issues.

Packages simply install, in most cases.

When I do need to compile things, they *do* compile pleasantly.

I seem to recall hearing there being "significant issues" as to how
Red Hat's distributions of Linux coped with AMD64.  That's not a
problem with Linux, of course...
-- 
"cbbrowne","@","gmail.com"
http://linuxdatabases.info/info/spreadsheets.html
"Imagine a law so stupid that civil obedience becomes an efficient way
to fighting it" --Per Abrahamsen on the DMCA

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Luke Lonergan]

Mark,

On 6/12/06 7:16 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

> I haven't. I'm meaning to take a look. Within registers, 64-bit should
> be equal speed to 32-bit. Outside the registers, it would make sense
> to only deal with the lower 32-bits where 32-bits is all that is
> required.

The short answer to all of this as shown in many lab tests by us and
elsewhere (see prior post):

- 64-bit pgsql on Opteron is generally faster than 32-bit, often by a large
amount (20%-30%) on queries that perform sorting, aggregation, etc.  It's
generally not slower.

- 64-bit pgsql on Xeon is generally slower than 32-bit by about 5%

So if you have Opterons and you want the best performance, run in 64-bit.
If you have Xeons, you would only run in 64-bit if you use more than 4GB of
memory.

- Luke



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[David Wheeler]

On Jun 12, 2006, at 19:44, Tom Lane wrote:


(Personally, if I'd designed it, the libraries would actually live in
/usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to  
whichever

you needed it to be at the moment.  Likewise for /usr/bin.)


/me nominates Tom to create a Linux distribution.

:-)

David

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Leigh Dyer]

Alex Turner wrote:
Anyone who has tried x86-64 linux knows what a royal pain in the ass it 
is.   They didn't do anything sensible, like just make the whole OS 64 
bit, no, they had to split it up, and put 64-bit libs in a new directory 
/lib64.  This means that a great many applications don't know to check 
in there for libs, and don't compile pleasantly, php is one among them.  
I forget what others, it's been awhile now.  Of course if you actualy 
want to use more than 4gig RAM in a pleasant way, it's pretty much 
essential.


That depends entirely on what AMD64 distribution you use -- on a Debian 
or Ubuntu 64-bit system, the main system is pre 64-bit, with some 
(optional) add-on libraries in separate directories to provide some 
32-bit compatibility.


On the performance stuff, my own testing of AMD64 on AMD's chips (not 
with PostgreSQL, but with various other things) has shown it to be about 
10% faster on average. As Luke mentioned, this isn't because of any 
inherent advantage in 64-bit -- it's because AMD did some tweaking while 
they had the hood open, adding extra registers among other things.


I remember reading an article some time back comparing AMD's 
implementation to Intel's that showed that EM64T Xeons ran 64-bit code 
about 5-10% more slowly than they ran 32-bit code. I can't find the link 
now, but it may explain why some people are getting better performance 
with 64-bit (on Opterons), while others are finding it slower (on Xeons).


Thanks
Leigh


Alex.

On 6/12/06, *Steve Atkins* <[EMAIL PROTECTED] 
> wrote:



On Jun 12, 2006, at 6:15 PM, Joshua D. Drake wrote:

 >
 >> Empirically... postgresql built for 64 bits is marginally slower
 >> than that built
 >> for a 32 bit api on sparc. None of my customers have found 64
bit x86
 >> systems to be suitable for production use, yet, so I've not tested
 >> on any
 >> of those architectures.
 >
 > Really? All of our customers are migrating to Opteron and I have
 > many that have been using Opteron for over 12 months happily.

An Opteron is 64 bit capable; that doesn't mean you have to run 64 bit
code on it.

Mine're mostly reasonably conservative users, with hundreds of machines
to support. Using 64 bit capable hardware, such as Opterons, is one
thing,
but using an entirely different linux installation and userspace
code, say, is
a much bigger change in support terms. In the extreme case it makes no
sense to double your OS support overheads to get a single digit
percentage
performance improvement on one database system.

That's not to say that linux/x86-64 isn't production ready for some
users, just
that it's not necessarily a good operational decision for my
customers. Given
my internal workloads aren't really stressing the hardware they're on
I don't
have much incentive to benchmark x86-64 yet - by the time the numbers
might be useful to me we'll be on a different postgresql, likely a
different
gcc/icc and so on.

Cheers,
   Steve


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster





---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] scaling up postgres

[Zydoon]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Sven Geisler wrote:
> Hi Mario,
> 
> I did run pgbench on several production servers:
> HP DL585 - 4-way AMD Opteron 875
> HP DL585 - 4-way AMD Opteron 880
> HP DL580 G3 - 4-way Intel XEON MP 3.0 GHz
> FSC RX600 S2 - 4-way Intel XEON MP DC 2.66 GHz
> FSC RX600 - 4-way Intel XEON MP 2.5 GHz
> 
> This test has been done with 8.1.4. I increased the number of clients.
> I attached the result as diagram. I included not all test system but the
> gap between XEON and Opteron is always the same.
> 
> The experiences with production systems were the same. We replaced the
> XEON box with Opteron box with a dramatic change of performance.
> 
> Best regards
> Sven.
> 
> 
> Mario Splivalo schrieb:
>> On Sat, 2006-06-03 at 11:43 +0200, Steinar H. Gunderson wrote:
>>> On Sat, Jun 03, 2006 at 10:31:03AM +0100, [EMAIL PROTECTED] wrote:
 I do have 2 identical beasts (4G - biproc Xeon 3.2 - 2 Gig NIC)
 One beast will be apache, and the other will be postgres.
 I'm using httperf/autobench for measurments and the best result I
 can get is that my system can handle a trafiic of almost 1600 New
 con/sec.
>>> What version of PostgreSQL? (8.1 is better than 8.0 is much better
>>> than 7.4.)
>>> Have you remembered to turn HT off? Have you considered Opterons
>>> instead of
>>> Xeons? (The Xeons generally scale bad with PostgreSQL.) What kind of
>>> queries
>>
>> Could you point out to some more detailed reading on why Xeons are
>> poorer choice than Opterons when used with PostgreSQL?
>>
>> Mario
>>
>>
>> ---(end of broadcast)---
>> TIP 2: Don't 'kill -9' the postmaster
> 
> 
> 
> 
> 
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
Thank you for sharing this.
Coming back to my problem :) A very faithful partner accepted to
gracefully borrow us 3 Pseries (bi-ppc + 2G RAM not more). with linux on
them.
Now I'm trying to make my tests, and I'm not that sure I will make the
switch to the PSeries, since my dual xeon with 4 G RAM can handle 3500
concurrent postmasters consuming 3.7 G of the RAM. I cannot reach this
number on the PSeries with 2 G.

can someone give me advice ?
BTW, I promise, at the end of my tests, I'll publish my report.

- --
Zied Fakhfakh
GPG Key : gpg --keyserver subkeys.pgp.net  --recv-keys F06B55B5
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFEjeDbS1DO7ovpKz8RAnLGAJ96/1ndGoc+HhBvOfrmlQnJcfxa6QCfQK9w
i6/GGUCBGk5pdNUDAmVN5RQ=
=5Mns
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] 64-bit vs 32-bit performance ... backwards?

[Josh Berkus]

Folks,

FWIW, the applications where I did direct 32 / 64 comparison were 
a) several data warehouse tests, with databases > 100GB
b) computation-heavy applications (such as a complex calendaring app)

And, as others have pointed out, I wasn't comparing generics; I was comparing 
Athalon/Xeon to Opteron.  So it's quite possible that the improvements had 
nothing to do with going 64-bit and were because of other architecture 
improvements.

In which case, why was 64-bit such a big deal?

-- 
Josh Berkus
PostgreSQL @ Sun
San Francisco

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [PERFORM] function not called if part of aggregate

[Craig A. James]

Greg Stark wrote:

However that's not enough to explain what you've shown. How about you show the
actual query and actual plan you're working with? The plan you've shown can't
result from the query you sent.


Mea culpa, sort of.  But ... in fact, the plan I sent *was* from query I sent, with the 
table/column names changed for clarity.  This time I'll send the plan "raw".  
(This is PG 8.0.1.)

chm=> explain select count(1) from (select normalize_add_salt(smiles) from
chm(> salt_smiles order by db_no) as foo;
QUERY PLAN 


Aggregate  (cost=69.95..69.95 rows=1 width=0)
  ->  Subquery Scan foo  (cost=0.00..67.93 rows=806 width=0)
->  Index Scan using salt_smiles_pkey on salt_smiles  (cost=0.00..59.87 
rows=806 width=30)
(3 rows)

As pointed out by Tom and others, this query DOES in fact call the 
normalize_add_salt() function.

Now here's the weird part. (And where my original posting went wrong -- sorry 
for the error!  I got the two queries mixed up.)

I originally had a more complex query, the purpose being to guarantee that the 
function was called on the strings in the order specified.  (More on this 
below.)  Here is the original query I used:

chm=> explain select count(1) from (select normalize_add_salt(smiles)
chm(>   from (select smiles from salt_smiles order by db_no) as foo) as bar;
QUERY PLAN 


Aggregate  (cost=67.94..67.94 rows=1 width=0)
  ->  Subquery Scan foo  (cost=0.00..65.92 rows=806 width=0)
->  Index Scan using salt_smiles_pkey on salt_smiles  (cost=0.00..57.86 
rows=806 width=30)
(3 rows)

Notice that the plans are essentially identical, yet in this one the function does NOT get called.  
I proved this by brute force, inserting "char **p = NULL; *p = "foo";" into the 
C code to guarantee a segmentation violation if the function gets called.  In the first case it 
does SIGSEGV, and in the second case it does not.

Now the reason for this more-complex query with an additional subselect is that 
the SMILES (which, by the way, are a lexical way of representing chemical 
structures - see www.daylight.com), must be passed to the function in a 
particular order (hence the ORDER BY).  In retrospect I realize the optimizer 
apparently flattens this query anyway (hence the identical plans, above).

But the weird thing is that, in spite of flattening, which would appear to make 
the queries equivalent, the function gets called in one case, and not in the 
other.

Steinar H. Gunderson asked:
 select count(1) from (select foo_init(value) from foo_init_table order by 
 value_id) as foo;

Why not just count(foo_init(value))?


Because the SMILES must be processed in a specific order, hence the more 
complex queries.

The simple answer to this whole problem is what Steinar wrote:
This works well, but it requires me to actually retrieve the function's 
value 800 times.


Is this actually a problem?


No, it's just a nuisance.  It occurs to me that in spite of the ORDER BY 
expression, Postgres is free to evaluate the function first, THEN sort the 
results, which means the SMILES would be processed in random order anyway.  
I.e. my ORDER BY clause is useless for the intended purpose.

So the only way I can see to get this right is to pull the SMILES into my application with the ORDER BY to ensure I have them in the correct order, then send them back one at a time via a "select normalize_add_salt(smiles)", meaning I'll retrieve 800 strings and then send them back. 


I just thought there ought to be a way to do this all on the PG server instead of sending 
all these strings back and forth.  I'd like to say to Postgres, "Just do it this 
way, OK?"  But the optimizer can't be turned off, so I guess I have to do it the 
slow way.  The good news is that this is just an initialization step, after which I 
typically process thousands of molecules, so the extra overhead won't kill me.

Thanks to all for your help.

Craig

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[PERFORM] Placement of 64-bit libraries (offtopic)

[Steinar H. Gunderson]

On Mon, Jun 12, 2006 at 10:44:01PM -0400, Tom Lane wrote:
> (Personally, if I'd designed it, the libraries would actually live in
> /usr/lib32 and /usr/lib64, and /usr/lib would be a symlink to whichever
> you needed it to be at the moment.  Likewise for /usr/bin.)

Actually, there have been plans for doing something like this in Debian for a
while: Let stuff live in /lib/i686-linux-gnu and /lib/x86_64-linux-gnu
(lib32 and lib64 doesn't really scale, once you start considering stuff like
"ia64 can emulate hppa"), and adjust paths and symlinks as fit. It's still a
long way to go, though.

/* Steinar */
-- 
Homepage: http://www.sesse.net/

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [PERFORM] Posrgres speed problem

[Ruben Rubio Rey]

Jim C. Nasby wrote:


On Mon, Jun 12, 2006 at 09:05:06AM -0600, Michael Fuhr wrote:
 


On Mon, Jun 12, 2006 at 04:38:57PM +0200, Ruben Rubio Rey wrote:
   

I have two similar servers, one in production and another for testing 
purposes.

Databases are equal (with a difference of some hours)

In the testing server, an sql sentence takes arround 1 sec.
In production server (low server load) takes arround 50 secs, and uses 
too much resources.


Explain analyze takes too much load, i had to cancel it!
 


The EXPLAIN ANALYZE output would be helpful, but if you don't want
to run it to completion then please post the output of EXPLAIN
ANALYZE for the fast system and EXPLAIN (without ANALYZE) for the
slow one.

As someone else asked, are you running ANALYZE regularly?  What
about VACUUM?
   



For the next vacuum, can you add the -v (verbose) switch and email the
last few lines of output?

INFO:  free space map contains 39 pages in 56 relations
DETAIL:  A total of 896 page slots are in use (including overhead).
896 page slots are required to track all free space.
Current limits are:  2 page slots, 1000 relations, using 223 KB.
VACUUM
 


INFO:  free space map contains 1624 pages in 137 relations
DETAIL:  A total of 3200 page slots are in use (including overhead).
3200 page slots are required to track all free space.
Current limits are:  2 page slots, 1000 relations, using 182 KB.


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [PERFORM] Posrgres speed problem - solved?

[Ruben Rubio Rey]

Tonight database has been vacumm full and reindex (all nights database 
do it)


Now its working fine. Speed is as spected. I ll be watching that sql ...
Maybe the problem exists when database is busy, or maybe its solved ...

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster