Skip site navigation (1) Skip section navigation (2)

Re: Performance degrade running on multicore computer

From: Dave Youatt <dave(at)meteorsolutions(dot)com>
To: afancy <groupme(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance degrade running on multicore computer
Date: 2009-11-23 19:10:55
Message-ID: 4B0ADE3F.2080703@meteorsolutions.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 01/-10/-28163 11:59 AM, afancy wrote:
> Hi,
>
> My PostgreSQL server has two CPUs (OS: Fedora 11), each with 4 cores.
> Total is 8cores.  Now I have several clients running at the same time
> to do insert and update on the same table, each client having its own
> connection.  I have made  two testing with  clients running in
> parallel to load 20M data in total. Each testing, the data is split
> evenly by the client number such that each client only loads a piece
> of data.
>
> 1) Long transaction: A client does the commit at the end of loading.
> Result: Each postgres consumes 95% CPU. The more clients run in
> parallel, the slower the total runing time is (when 8 clients, it is
> slowest). However, I expect the more clients run in parallel, it
> should be faster to load all the data.
>
> 2) Short transaction: I set the clients to do a commit on loading
> every 500 records. Results:  Each postgres consumes about 50%CPU. Now
> the total running is as what i have expected; the more clients run in
> parallel, the faster it is (when 8 clients, it is fastest).
>
> Could anybody help to why when I do the long transaction with 8
> clients, it is slowest? How can I solve this problem?  As I don't want
> to use the 2), in which I have to set the commit size each time.
>
> Thanks a lot!!
>
> -Afancy
>

Since you have 2 cpus, you may want to try setting the processor
affinity for postgres (server and client programs) to the 4 cores on one
of the cpus (taskset command on linux).  Here's an excerpt from a
modified /etc/init.d/postgresql:

   $SU -l postgres -c "taskset -c 4-7 $PGENGINE/postmaster -p '$PGPORT' -D '$PGDATA' ${PGOPTS} &" >> "$PGLOG" 2>&1 < /dev/null 


Thanks to Greg Smith to pointing this out when we had a similar issue
w/a 2-cpu server.
NB:  This was with postgresql 8.3.  Don't know if 8.4+ has built-in
processor affinity.

(Apologies in advance for the email formatting.)

In response to

pgsql-performance by date

Next:From: Lorenzo AllegrucciDate: 2009-11-23 20:46:41
Subject: Re: Strange performance degradation
Previous:From: Robert HaasDate: 2009-11-23 18:11:32
Subject: Re: Query times change by orders of magnitude as DB ages

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group