Hi all:
I've been running small aplications over postgres 7.2.2, they are based over a tpch-like 100Mb database (which give us about 400Mb of disk usage), system is an 8-CPU SGI Power Challenge, postgres is given 80000 shared buffers and backends 32Mb of sort-memory, so all data fits in memory and we avoid disk accesses.
When we improve seq scans, systems scales well up to 8 cpus.When we improve index scan, query performance increase but system stops scaling at about 4 proccessors, profiling shows that it is due to increased memory contention, it seems strange cause query are all read only, at the beggining of the run we insert and delete some rows, but it is only 1/10 of the time.
All of that is first of all to advise of this performance drop, and second to ask what is the difference between memory usage with seqscans and indexscans.
Thanks and regards






Hi all:
I've been running small aplications over postgres 
7.2.2, they are based over a tpch-like 100Mb database (which give us about 400Mb 
of disk usage), system is an 8-CPU SGI Power Challenge, postgres is given 80000 
shared buffers and backends 32Mb of sort-memory, so all data fits in memory and 
we avoid disk accesses.
When we improve seq scans, systems scales well up 
to 8 cpus.When we improve index scan, query performance increase but system 
stops scaling at about 4 proccessors, profiling shows that it is due to 
increased memory contention, it seems strange cause query are all read only, at 
the beggining of the run we insert and delete some rows, but it is only 1/10 of 
the time.
All of that is first of all to advise of this 
performance drop, and second to ask what is the difference between memory usage 
with seqscans and indexscans.
Thanks and regards
Tue, 5 Nov 2002 09:58:50 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
a tiny question
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
> When we improve seq scans, systems scales well up to 8 cpus.When we improve index
> scan, query performance increase but system stops scaling at about 4 proccessors,
> profiling shows that it is due to increased memory contention

What do you mean by "memory contention"?

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


05 Nov 2002 10:47:06 -0500
Neil Conway <neilc@samurai.com>
Re: a tiny question
a guess it's said inter-locking, I mean data accessed exclusively.
I apologize for my english


----- Original Message -----
From: "Neil Conway" <neilc@samurai.com>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-hackers@postgresql.org>
Sent: Tuesday, November 05, 2002 4:47 PM
Subject: Re: [HACKERS] a tiny question


> "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
> > When we improve seq scans, systems scales well up to 8 cpus.When we
improve index
> > scan, query performance increase but system stops scaling at about 4
proccessors,
> > profiling shows that it is due to increased memory contention
>
> What do you mean by "memory contention"?
>
> Cheers,
>
> Neil
>
> --
> Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>



Tue, 5 Nov 2002 17:06:13 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: a tiny question
Another "tiny" question:
Is there a way to set more than one shared regions?
Thanks and regards


Wed, 6 Nov 2002 16:16:25 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: a tiny question
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es> writes:
> Is there a way to set more than one shared regions?

No, there isn't.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC


06 Nov 2002 10:47:29 -0500
Neil Conway <neilc@samurai.com>
Re: a tiny question
I supposed it, but I've not seen 7.3.
A small patch for low performance on NUMA arquitectures could be the chance
of using more than one shared region.
Several months away there was a brief talk about low performance on IRIX, it
was not real, it's low performance on Origin servers, they use ccNUMA
architecture, so using a single shared segment allocated by only one
proccess doesn't give IRIX planner a chance for attempting to optimize
memory accesses, even thought page migration is almost impossible with DB
memory accesses.
With this scenario, IRIX uses a first-touch algorith for memory placement
(all the memory is allocated on the node Postgres starts running). Due to
high latency between local and remote memory accesses, if there is data
modification, remote proccesors cache hit rate goes down, all of this makes
that all the basis of high scalability on this systems(big cache, memory
distribution, increasing bandwith and page migration) ar thrown to the
earth.
I suppose that making more than one shared segment it's not too difficult,
but with this u can do that a long time Postgres running on a NUMA system
achieve a good performance scenario.
Is it so difficult?
Thanks and regards.
Luis Amigo



Wed, 6 Nov 2002 17:02:03 +0100
"Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Re: a tiny question