Hi all,

I'm not a postgres hacker, but I' think that you must be the most
appropriate person to give me a pointer about this question.... sorry for
any possible mistake.

Now I'm trying to use postgresql plus the pgbench like a
first test to stress the interconnection system in a parallel machine. I
know that tpc-b is just a toy (no too much real... but before to do
something more complex like tpc-c y want to see the postgres behavior).

Ok...well I'm running this benchmarks in different SMP machines (SGI with 4
to 8 processors and the results are odd). The best performance is achieved
with just one backend (1 client). When I try to run more clients the tps
falls quickly.

In all cases I see that when I increase the number of clients the total CPU
usage falls. With one client I can see a 100% usage (after a warm-up to get
all data from disk - I'm running without fsync and with a large shared
buffer).My systems have a lot of memory then this is normal. But when I try
with more clients each CPU usage falls between 40% for 2 clients to 10% to 8
clients. I assume the access to the shared memory through critical regions
(lock-unlock) must be one reason... but this is too much. I've heard that
locks in postgress are at page level instead tuple level. I'm wrong?.

Some suggestion about this?.

Thanks in advance for your support.

--vpuente


Tue, 5 Jun 2001 14:39:55 +0200
"Valentin Puente" <vpuente@atc.unican.es>
Multiprocessor performance
"Valentin Puente" <vpuente@atc.unican.es> writes:
> Ok...well I'm running this benchmarks in different SMP machines (SGI with 4
> to 8 processors and the results are odd). The best performance is achieved
> with just one backend (1 client). When I try to run more clients the tps
> falls quickly.

What scale factor (-s parameter for pgbench init) are you using for the
benchmark?

At scale factor 1, there's only one "branch" row, so all the
transactions have to update the same row and naturally will spend most
of their time waiting to do so.

You want scale factor >> # of concurrent clients to avoid interlock
effects.

			regards, tom lane

Tue, 05 Jun 2001 10:04:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Multiprocessor performance 
Valentin Puente wrote:
> Hi all,
>
> I'm not a postgres hacker, but I' think that you must be the most
> appropriate person to give me a pointer about this question.... sorry for
> any possible mistake.
>
> Now I'm trying to use postgresql plus the pgbench like a
> first test to stress the interconnection system in a parallel machine. I
> know that tpc-b is just a toy (no too much real... but before to do
> something more complex like tpc-c y want to see the postgres behavior).
>
> Ok...well I'm running this benchmarks in different SMP machines (SGI with 4
> to 8 processors and the results are odd). The best performance is achieved
> with just one backend (1 client). When I try to run more clients the tps
> falls quickly.
>
> In all cases I see that when I increase the number of clients the total CPU
> usage falls. With one client I can see a 100% usage (after a warm-up to get
> all data from disk - I'm running without fsync and with a large shared
> buffer).My systems have a lot of memory then this is normal. But when I try
> with more clients each CPU usage falls between 40% for 2 clients to 10% to 8
> clients. I assume the access to the shared memory through critical regions
> (lock-unlock) must be one reason... but this is too much. I've heard that
> locks in postgress are at page level instead tuple level. I'm wrong?.
>
> Some suggestion about this?.

    What was the scaling factor on pgbench initialization? if you
    used the 1-default, your bottleneck is the single row in  the
    branches table, which everyone wants to lock for update. Try

        pgbench -i -s <10 or higher> <dbname>

    to give it a kick.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Tue, 5 Jun 2001 11:37:23 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Multiprocessor performance