Re: Limited performance on multi core server - Mailing list pgsql-performance

Hi Matthew,

Please not that is no official patch, but it works with our Opteron
server without any problems. You should run the regression test after
you adapt it for Pg 7.4.

Sven.

Matthew Lunnon schrieb:
> Hi Sven,
>
> yes the patch would be great if you could send it to me, we have already
> had to compile postgres to up the number of function parameters from 32
> to 64.
>
> Meanwhile I will try and persuade my colleagues to consider the upgrade
> option.
>
> Thanks
> Matthew
>
> Sven Geisler wrote:
>> Hi Matthew,
>>
>> I remember that I also an issue with AMD Opterons before Pg 8.1
>> There is a specific Opteron behaviour on shared memory locks which adds
>> a extra "penalty" during the execution time for Pg code before 8.1.
>> I can you provide my patch for Pg 8.0 which should be adaptable for Pg
>> 7.4 if you can compile PostgreSQL.
>>
>> But if you can upgrade you should upgrade to Pg 8.2.5 64-bit. The scale
>> up for your concurrent queries will be great.
>>
>> Sven.
>>
>> Matthew Lunnon schrieb:
>>
>>> Hi,
>>>
>>> I have a 4 * dual core 64bit AMD OPTERON server with 16G of RAM, running
>>> postgres 7.4.3.  This has been recompiled on the server for 64 stored
>>> procedure parameters, (I assume this makes postgres 64 bit but are not
>>> sure).  When the server gets under load from database connections
>>> executing reads, lets say 20 - 40 concurrent reads, the CPU's seem to
>>> limit at about 30-35% usage with no iowait reported. If I run a simple
>>> select at this time it takes 5 seconds, the same query runs in 300
>>> millis when the server is not under load so it seems that the database
>>> is not performing well even though there is plenty of spare CPU.  There
>>> does not appear to be large amounts of disk IO and my database is about
>>> 5.5G so this should fit comfortably in RAM.
>>>
>>> changes to postgresql.sql:
>>>
>>> max_connections = 500
>>> shared_buffers = 96000
>>> sort_mem = 10240
>>> effective_cache_size = 1000000
>>>
>>> Does anyone have any ideas what my bottle neck might be and what I can
>>> do about it?
>>>
>>> Thanks for any help.
>>>
>>> Matthew.
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 6: explain analyze is your friend
>>>
>>
>>
>
> --
> Matthew Lunnon
> Technical Consultant
> RWA Ltd.
>
>  mlunnon@rwa-net.co.uk
>  Tel: +44 (0)29 2081 5056
>  www.rwa-net.co.uk
> --
>

--
Sven Geisler <sgeisler@aeccom.com>   Tel +49.30.921017.81  Fax .50
Senior Developer, AEC/communications GmbH & Co. KG Berlin, Germany
*** postgresql-8.0.3/src/include/storage/s_lock.h.orig    Sun Aug 28 20:41:44 2005
--- postgresql-8.0.3/src/include/storage/s_lock.h    Fri Sep  9 14:58:44 2005
***************
*** 120,132 ****
  {
      register slock_t _res = 1;

!     /* Use a non-locking test before asserting the bus lock */
      __asm__ __volatile__(
-         "    cmpb    $0,%1    \n"
-         "    jne        1f        \n"
-         "    lock            \n"
          "    xchgb    %0,%1    \n"
-         "1: \n"
  :        "+q"(_res), "+m"(*lock)
  :
  :        "memory", "cc");
--- 120,128 ----
  {
      register slock_t _res = 1;

!     /* xchg implies a LOCK prefix, so no need to say LOCK explicitly */
      __asm__ __volatile__(
          "    xchgb    %0,%1    \n"
  :        "+q"(_res), "+m"(*lock)
  :
  :        "memory", "cc");
*** postgresql-8.0.3/src/backend/storage/lmgr/s_lock.c.orig    Fri Aug 26 10:47:35 2005
--- postgresql-8.0.3/src/backend/storage/lmgr/s_lock.c    Sat Sep 10 19:19:46 2005
***************
*** 17,22 ****
--- 17,23 ----

  #include <time.h>
  #include <unistd.h>
+ #include <sched.h>

  #include "storage/s_lock.h"
  #include "miscadmin.h"
***************
*** 83,89 ****

      int            spins = 0;
      int            delays = 0;
!     int            cur_delay = MIN_DELAY_CSEC;

      while (TAS(lock))
      {
--- 84,90 ----

      int            spins = 0;
      int            delays = 0;
!     int            cur_delay = 0;

      while (TAS(lock))
      {
***************
*** 96,102 ****
              if (++delays > NUM_DELAYS)
                  s_lock_stuck(lock, file, line);

!             pg_usleep(cur_delay * 10000L);

  #if defined(S_LOCK_TEST)
              fprintf(stdout, "*");
--- 97,110 ----
              if (++delays > NUM_DELAYS)
                  s_lock_stuck(lock, file, line);

!             if (cur_delay == 0)
!             {
!               /* first time through, try just a sched_yield */
!               sched_yield();
!               cur_delay = MIN_DELAY_CSEC;
!             }
!             else
!               pg_usleep(cur_delay * 10000L);

  #if defined(S_LOCK_TEST)
              fprintf(stdout, "*");