Thread: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
markw@osdl.org
Date:
I thought someone might be interested in a data point I have comparing
7.3.4 and 7.4beta5 with results from our DBT-2 workload. Keep in mind I
haven't done much tuning with either version.  The following links have
references iostat, vmstat, sar, readprofile (linux kernel profile), and
oprofile (postgresql profile) statistics.

Results from 7.3.4:http://developer.osdl.org/markw/dbt2-pgsql/184/- metric 1354.58

Results from 7.4beta5http://developer.osdl.org/markw/dbt2-pgsql/188/- metric 1446.01

7.4beta5 offers more throughput.  One significant difference I see is in
the oprofile for the database.  For the additional 7% increase in the
metric, there are about 32% less ticks in SearchCatCache.

These are the only database parameters I've explicitly set for each one,
any other differences will be differences in default values:- shared_buffers = 40000- tcpip_socket = true-
checkpoint_segments= 200- checkpoint_timeout = 1800- stats_start_collector = true- stats_command_string = true-
stats_block_level= true- stats_row_level = true- stats_reset_on_server_start = true
 

If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
happy to try them.  Or if anyone wants to be able to poke around on the
system, we can arrange that too.  Feel free to ask any questions.

-- 
Mark Wong - - markw@osdl.org
Open Source Development Lab Inc - A non-profit corporation
12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005
(503) 626-2455 x 32 (office)
(503) 626-2436      (fax)


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Rod Taylor
Date:
Excellent.

I just noticed that most of the numbers in the system are given the
numeric data type. Is there any particular reason you don't use integer
(test enforced?)?

On Fri, 2003-10-31 at 19:18, markw@osdl.org wrote:
> I thought someone might be interested in a data point I have comparing
> 7.3.4 and 7.4beta5 with results from our DBT-2 workload. Keep in mind I
> haven't done much tuning with either version.  The following links have
> references iostat, vmstat, sar, readprofile (linux kernel profile), and
> oprofile (postgresql profile) statistics.
>
> Results from 7.3.4:
>     http://developer.osdl.org/markw/dbt2-pgsql/184/
>     - metric 1354.58
>
> Results from 7.4beta5
>     http://developer.osdl.org/markw/dbt2-pgsql/188/
>     - metric 1446.01
>
> 7.4beta5 offers more throughput.  One significant difference I see is in
> the oprofile for the database.  For the additional 7% increase in the
> metric, there are about 32% less ticks in SearchCatCache.
>
> These are the only database parameters I've explicitly set for each one,
> any other differences will be differences in default values:
>     - shared_buffers = 40000
>     - tcpip_socket = true
>     - checkpoint_segments = 200
>     - checkpoint_timeout = 1800
>     - stats_start_collector = true
>     - stats_command_string = true
>     - stats_block_level = true
>     - stats_row_level = true
>     - stats_reset_on_server_start = true
>
> If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
> happy to try them.  Or if anyone wants to be able to poke around on the
> system, we can arrange that too.  Feel free to ask any questions.

Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Tom Lane
Date:
markw@osdl.org writes:
> 7.4beta5 offers more throughput.  One significant difference I see is in
> the oprofile for the database.  For the additional 7% increase in the
> metric, there are about 32% less ticks in SearchCatCache.

Hmm.  I have been profiling PG for some years now, and I cannot remember
ever seeing a profile in which SearchCatCache topped everything else
(the usual suspects for me are palloc/pfree support code).  Can you give
any explanation why it looks like that?  Can your profiling code tell
where the hotspot call sites of SearchCatCache are?
        regards, tom lane


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
markw@osdl.org wrote:

>Results from 7.4beta5
>    http://developer.osdl.org/markw/dbt2-pgsql/188/
>    - metric 1446.01
>  
>

CPU: P4 / Xeon with 2 hyper-threads, speed 1497.51 MHz (estimated)
Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped) with a unit mask of 0x01 (count cycles
whenprocessor is active) count 100000
 
samples  %        app name                 symbol name
15369575  9.6780  postgres                 SearchCatCache
13714258  8.6357  vmlinux                  .text.lock.signal
10611912  6.6822  vmlinux                  do_sigaction
4400461   2.7709  vmlinux                  rm_from_queue

18% cpu time in the kernel signal handlers.

What are signals used for by postgres? I've seen the sigalarm to 
implement timeouts, what else?

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Tom Lane wrote:

>markw@osdl.org writes:
>  
>
>>7.4beta5 offers more throughput.  One significant difference I see is in
>>the oprofile for the database.  For the additional 7% increase in the
>>metric, there are about 32% less ticks in SearchCatCache.
>>    
>>
>
>Hmm.  I have been profiling PG for some years now, and I cannot remember
>ever seeing a profile in which SearchCatCache topped everything else
>(the usual suspects for me are palloc/pfree support code).  Can you give
>any explanation why it looks like that?  Can your profiling code tell
>where the hotspot call sites of SearchCatCache are?
>
If I understand the docs correctly, op_to_source -a can do that - the 
result is annotated assembly, with percentage numbers for each 
instruction. If the sources were compiled with -g2, even source level 
annotation is possible.

Mark, do you still have the oprofile output? I don't understand why so 
much time is spent in the kernel signal handlers, i.e. I could use 
annotated assembly or source of linux/kernel/signal.c.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
I've straced
$ pgbench -c 5 -s 6 -t 1000

total 157k syscalls, 70k of them are rt_sigaction(SIGPIPE):

1754  poll([{fd=3, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, -1) = 1
1754  rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
1754  send(3, "\0\0\0%\0\3\0\0user\0postgres\0database\0t"..., 37, 0) = 37
1754  rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
1754  poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
1754  recv(3, "R\0\0\0\10\0\0\0\0S\0\0\0\36client_encoding\0SQ"..., 
16384, 0) = 169
1754  rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
1754  send(3, "Q\0\0\0\35SET search_path = public\0", 30, 0) = 30
1754  rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
1754  poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
1754  recv(3, "C\0\0\0\10SET\0Z\0\0\0\5I", 16384, 0) = 15
1754  rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0

and so on. Is that really necessary?

Mark: could you strace your dbt2 app? I guess your app creates a similar 
streams of rt_sigaction calls.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> Is that really necessary?

Unfortunately, yes.  libpq can't change the global setting of SIGPIPE
without breaking the surrounding application, but we don't want to crash
the app if the server connection has disappeared, either.  So we have to
set the SIGPIPE handler and then restore it around every send().

On some platforms there might be a better way, but this is the only
portable way I know about.
        regards, tom lane


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Tom Lane wrote:

>Manfred Spraul <manfred@colorfullife.com> writes:
>  
>
>>Is that really necessary?
>>    
>>
>
>Unfortunately, yes.  libpq can't change the global setting of SIGPIPE
>without breaking the surrounding application, but we don't want to crash
>the app if the server connection has disappeared, either.  So we have to
>set the SIGPIPE handler and then restore it around every send().
>  
>
Ok. Ahm. No, wait. libpq is multi-threaded, right?

signal handlers are a process property, not a thread property - that 
code is broken for multi-threaded apps.
At least that's how I understand the opengroup man page, and a quick 
google confirmed that:
http://groups.google.de/groups?selm=353662BF.9D70F63A%40brighttiger.com

I haven't found a reliable thread-safe approach yet:
My first idea was block with pthread_sigmask, after send check if 
pending with sigpending, and then delete with sigwait, and restore 
blocked state. But that breaks if SIGPIPE is blocked and a signal is 
already pending: there is no way to remove our additional SIGPIPE. I 
don't see how we can avoid destroying the realtime signal info.

Mark: Is your dbt2 testapp multithreaded? I don't see the signal 
functions near the top in the profiles on the osdl website.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> signal handlers are a process property, not a thread property - that 
> code is broken for multi-threaded apps.

Yeah, that's been mentioned before, but I don't see any way around it.
What we really want is to turn off SIGPIPE delivery on our socket
(only), but AFAIK there is no API to do that.
        regards, tom lane


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Tom Lane wrote:

>Manfred Spraul <manfred@colorfullife.com> writes:
>  
>
>>signal handlers are a process property, not a thread property - that 
>>code is broken for multi-threaded apps.
>>    
>>
>
>Yeah, that's been mentioned before, but I don't see any way around it.
>
Do not handle SIGPIPE on multithreaded apps, and ask the caller to do 
that? The current code doesn't block SIGPIPE reliably, which makes it 
totally useless (except that it's a debugging nightmare, because 
triggering it depends on the right timing).

>What we really want is to turn off SIGPIPE delivery on our socket
>(only), but AFAIK there is no API to do that.
>  
>
Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux 
specific.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
On Sat, Nov 01, 2003 at 02:37:21PM +0100, Manfred Spraul wrote:
> Tom Lane wrote:
> 
> >markw@osdl.org writes:
> >  
> >
> >>7.4beta5 offers more throughput.  One significant difference I see is in
> >>the oprofile for the database.  For the additional 7% increase in the
> >>metric, there are about 32% less ticks in SearchCatCache.
> >>    
> >>
> >
> >Hmm.  I have been profiling PG for some years now, and I cannot remember
> >ever seeing a profile in which SearchCatCache topped everything else
> >(the usual suspects for me are palloc/pfree support code).  Can you give
> >any explanation why it looks like that?  Can your profiling code tell
> >where the hotspot call sites of SearchCatCache are?
> >
> If I understand the docs correctly, op_to_source -a can do that - the 
> result is annotated assembly, with percentage numbers for each 
> instruction. If the sources were compiled with -g2, even source level 
> annotation is possible.
> 
> Mark, do you still have the oprofile output? I don't understand why so 
> much time is spent in the kernel signal handlers, i.e. I could use 
> annotated assembly or source of linux/kernel/signal.c.

I haven't been saving the raw output, but I will start.  I'll try to get
some annotated source for the kernel going too.

Mark


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
On Sat, Nov 01, 2003 at 07:27:01PM +0100, Manfred Spraul wrote:
> Tom Lane wrote:
> 
> >Manfred Spraul <manfred@colorfullife.com> writes:
> >  
> >
> >>Is that really necessary?
> >>    
> >>
> >
> >Unfortunately, yes.  libpq can't change the global setting of SIGPIPE
> >without breaking the surrounding application, but we don't want to crash
> >the app if the server connection has disappeared, either.  So we have to
> >set the SIGPIPE handler and then restore it around every send().
> >  
> >
> Ok. Ahm. No, wait. libpq is multi-threaded, right?
> 
> signal handlers are a process property, not a thread property - that 
> code is broken for multi-threaded apps.
> At least that's how I understand the opengroup man page, and a quick 
> google confirmed that:
> http://groups.google.de/groups?selm=353662BF.9D70F63A%40brighttiger.com
> 
> I haven't found a reliable thread-safe approach yet:
> My first idea was block with pthread_sigmask, after send check if 
> pending with sigpending, and then delete with sigwait, and restore 
> blocked state. But that breaks if SIGPIPE is blocked and a signal is 
> already pending: there is no way to remove our additional SIGPIPE. I 
> don't see how we can avoid destroying the realtime signal info.
> 
> Mark: Is your dbt2 testapp multithreaded? I don't see the signal 
> functions near the top in the profiles on the osdl website.

Yeah, my dbt2 applications are multithreaded.

Mark


Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5)

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> Tom Lane wrote:
>> What we really want is to turn off SIGPIPE delivery on our socket
>> (only), but AFAIK there is no API to do that.
>> 
> Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux 
> specific.

Hmm ... a Linux-specific solution would be better than none at all.

A bigger objection is that we couldn't get libssl to use it (AFAIK).
The flag really needs to be settable on the socket (eg, via fcntl),
not per-send.
        regards, tom lane


Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Manfred Spraul
Date:
Tom Lane wrote:

>A bigger objection is that we couldn't get libssl to use it (AFAIK).
>The flag really needs to be settable on the socket (eg, via fcntl),
>not per-send.
>
It's a per-send flag, it's not possible to force it on with a fcntl :-(

What about an option to skip the sigaction calls for apps that can
handle SIGPIPE? I'm not sure if an option at connect time, or a flag
accessible through a function like PQsetnonblocking() is the better
approach.

Attached is a patch that adds a connstr option, but I don't like it.

--
    Manfred
Index: fe-connect.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.260
diff -c -r1.260 fe-connect.c
*** fe-connect.c    5 Sep 2003 02:08:36 -0000    1.260
--- fe-connect.c    1 Nov 2003 21:02:04 -0000
***************
*** 65,70 ****
--- 65,71 ----
  #else
  #define DefaultSSLMode    "disable"
  #endif
+ #define DefaultSIGPIPEMode    "sigaction"


  /* ----------
***************
*** 152,157 ****
--- 153,161 ----
      {"sslmode", "PGSSLMODE", DefaultSSLMode, NULL,
      "SSL-Mode", "", 8},            /* sizeof("disable") == 8 */

+     {"sigpipemode", "PGSIGPIPEMODE", DefaultSIGPIPEMode, NULL,
+     "SIGPIPE-Mode", "", 10},        /* sizeof("sigaction") == 10 */
+
      /* Terminating entry --- MUST BE LAST */
      {NULL, NULL, NULL, NULL,
      NULL, NULL, 0}
***************
*** 369,374 ****
--- 373,380 ----
          conn->sslmode = strdup("require");
      }
  #endif
+     tmp = conninfo_getval(connOptions, "sigpipemode");
+     conn->sigpipemode = tmp ? strdup(tmp) : NULL;

      /*
       * Free the option info - all is in conn now
***************
*** 478,483 ****
--- 484,508 ----
      else
          conn->sslmode = strdup(DefaultSSLMode);

+     /*
+      * validate sigpipemode option
+      */
+     if (conn->sigpipemode)
+     {
+         if (strcmp(conn->sigpipemode, "caller") != 0
+             && strcmp(conn->sigpipemode, "sigaction") != 0)
+         {
+             conn->status = CONNECTION_BAD;
+             printfPQExpBuffer(&conn->errorMessage,
+                          libpq_gettext("unrecognized sigpipemode: \"%s\"\n"),
+                               conn->sigpipemode);
+             return false;
+         }
+     }
+     else
+         conn->sigpipemode = strdup(DefaultSIGPIPEMode);
+
+
      return true;
  }

***************
*** 951,956 ****
--- 976,986 ----
      else if (conn->sslmode[0] == 'a')    /* "allow" */
          conn->wait_ssl_try = true;
  #endif
+     if (conn->sigpipemode[0] == 's') /* sigaction */
+         conn->do_sigaction = true;
+     else
+         conn->do_sigaction = false;
+

      /*
       * Set up to try to connect, with protocol 3.0 as the first attempt.
***************
*** 2033,2038 ****
--- 2063,2070 ----
          free(conn->pgpass);
      if (conn->sslmode)
          free(conn->sslmode);
+     if (conn->sigpipemode)
+         free(conn->sigpipemode);
      /* Note that conn->Pfdebug is not ours to close or free */
      if (conn->notifyList)
          DLFreeList(conn->notifyList);
Index: fe-secure.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.30
diff -c -r1.30 fe-secure.c
*** fe-secure.c    5 Sep 2003 02:08:36 -0000    1.30
--- fe-secure.c    1 Nov 2003 21:02:06 -0000
***************
*** 348,354 ****
      ssize_t        n;

  #ifndef WIN32
!     pqsigfunc    oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
  #endif

  #ifdef USE_SSL
--- 348,357 ----
      ssize_t        n;

  #ifndef WIN32
!     pqsigfunc    oldsighandler = NULL;
!
!     if (conn->do_sigaction)
!         oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
  #endif

  #ifdef USE_SSL
***************
*** 408,414 ****
          n = send(conn->sock, ptr, len, 0);

  #ifndef WIN32
!     pqsignal(SIGPIPE, oldsighandler);
  #endif

      return n;
--- 411,418 ----
          n = send(conn->sock, ptr, len, 0);

  #ifndef WIN32
!     if (conn->do_sigaction)
!         pqsignal(SIGPIPE, oldsighandler);
  #endif

      return n;
Index: libpq-int.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/libpq-int.h,v
retrieving revision 1.82
diff -c -r1.82 libpq-int.h
*** libpq-int.h    5 Sep 2003 02:08:36 -0000    1.82
--- libpq-int.h    1 Nov 2003 21:02:07 -0000
***************
*** 250,255 ****
--- 250,256 ----
      char       *pguser;            /* Postgres username and password, if any */
      char       *pgpass;
      char       *sslmode;        /* SSL mode (require,prefer,allow,disable) */
+     char       *sigpipemode;    /* SIGPIPE handling (caller, sigaction) */

      /* Optional file to write trace info to */
      FILE       *Pfdebug;
***************
*** 329,334 ****
--- 330,336 ----
      char        peer_dn[256 + 1];        /* peer distinguished name */
      char        peer_cn[SM_USER + 1];    /* peer common name */
  #endif
+     bool        do_sigaction;    /* set SIGPIPE to SIG_IGN around every send() call */

      /* Buffer for current error message */
      PQExpBufferData errorMessage;        /* expansible string */

Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Mark Wong wrote:

>Yeah, my dbt2 applications are multithreaded.
>
>
Do you need SIGPIPE delivery in your app? If no, could you try what
happens if you apply the attached patch to postgres, and perform the
    signal(SIGPIPE, SIG_IGN);
once in your dbt2 app?

--
    Manfred
--- pgsql.orig/src/interfaces/libpq/fe-secure.c    2003-11-01 22:28:13.000000000 +0100
+++ pgsql/src/interfaces/libpq/fe-secure.c    2003-11-01 22:27:21.000000000 +0100
@@ -348,7 +348,7 @@
     ssize_t        n;

 #ifndef WIN32
-    pqsigfunc    oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
+/*    pqsigfunc    oldsighandler = pqsignal(SIGPIPE, SIG_IGN); */
 #endif

 #ifdef USE_SSL
@@ -408,7 +408,7 @@
         n = send(conn->sock, ptr, len, 0);

 #ifndef WIN32
-    pqsignal(SIGPIPE, oldsighandler);
+/*    pqsignal(SIGPIPE, oldsighandler); */
 #endif

     return n;

Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5)

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> What about an option to skip the sigaction calls for apps that can 
> handle SIGPIPE?

If the app is ignoring SIGPIPE globally, then our calls will have no
effect anyway.  I don't see that this proposal adds any security.
        regards, tom lane


Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Manfred Spraul
Date:
Tom Lane wrote:

>Manfred Spraul <manfred@colorfullife.com> writes:
>  
>
>>What about an option to skip the sigaction calls for apps that can 
>>handle SIGPIPE?
>>    
>>
>
>If the app is ignoring SIGPIPE globally, then our calls will have no
>effect anyway.
>
Wrong. From the opengroup manpage:
<<
SIG_IGN - ignore signal
[snip]
- Setting a signal action to SIG_IGN for a signal that is pending will 
cause the pending signal to be discarded, whether or not it is blocked   <<   This is why the kernel spends 20% cpu
timeprocessing the SIG_IGN:   it must walk through all threads of the process and check if there   are any SIGPIPE
signalspending.
 

>  I don't see that this proposal adds any security.
>  
>
It's not about security: Right now multithreaded apps must call 
signal(SIGPIPE, SIG_IGN), otherwise they could get killed by sudden 
SIGPIPE signals. Additionally, they can't rely on sigpending, because 
the pendings bits are cleared regularly. On top, they get a noticable 
performance hit.

My proposal means that apps that know what they are doing (SIGPIPE 
either SIG_IGN, or blocked, or a suitable handler) can avoid the 
signal(SIGPIPE, SIG_IGN) in pqsecure_write. With backward compatibility, 
because the current system works for single threaded apps.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Robert Treat
Date:
On Friday 31 October 2003 19:18, markw@osdl.org wrote:
> These are the only database parameters I've explicitly set for each one,
> any other differences will be differences in default values:
>     - shared_buffers = 40000
>     - tcpip_socket = true
>     - checkpoint_segments = 200
>     - checkpoint_timeout = 1800

ISTM that these two are fairly unrepresentative of any real world setups.  I 
might be better to knock them way back towards there defaults and turn on 
"checkpoint_warning" to see if they should be altered. 

>     - stats_start_collector = true
>     - stats_command_string = true
>     - stats_block_level = true
>     - stats_row_level = true
>     - stats_reset_on_server_start = true
>
> If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
> happy to try them.  Or if anyone wants to be able to poke around on the
> system, we can arrange that too.  Feel free to ask any questions.

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
I don't remember making a conscious decision between the number and integer
database type.  Is that a significant oversight on my part?


On Fri, Oct 31, 2003 at 08:04:34PM -0500, Rod Taylor wrote:
> Excellent.
> 
> I just noticed that most of the numbers in the system are given the
> numeric data type. Is there any particular reason you don't use integer
> (test enforced?)?
> 
> On Fri, 2003-10-31 at 19:18, markw@osdl.org wrote:
> > I thought someone might be interested in a data point I have comparing
> > 7.3.4 and 7.4beta5 with results from our DBT-2 workload. Keep in mind I
> > haven't done much tuning with either version.  The following links have
> > references iostat, vmstat, sar, readprofile (linux kernel profile), and
> > oprofile (postgresql profile) statistics.
> > 
> > Results from 7.3.4:
> >     http://developer.osdl.org/markw/dbt2-pgsql/184/
> >     - metric 1354.58
> > 
> > Results from 7.4beta5
> >     http://developer.osdl.org/markw/dbt2-pgsql/188/
> >     - metric 1446.01
> > 
> > 7.4beta5 offers more throughput.  One significant difference I see is in
> > the oprofile for the database.  For the additional 7% increase in the
> > metric, there are about 32% less ticks in SearchCatCache.
> > 
> > These are the only database parameters I've explicitly set for each one,
> > any other differences will be differences in default values:
> >     - shared_buffers = 40000
> >     - tcpip_socket = true
> >     - checkpoint_segments = 200
> >     - checkpoint_timeout = 1800
> >     - stats_start_collector = true
> >     - stats_command_string = true
> >     - stats_block_level = true
> >     - stats_row_level = true
> >     - stats_reset_on_server_start = true
> > 
> > If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
> > happy to try them.  Or if anyone wants to be able to poke around on the
> > system, we can arrange that too.  Feel free to ask any questions.


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
On Sat, Nov 01, 2003 at 02:37:21PM +0100, Manfred Spraul wrote:
> Tom Lane wrote:
> 
> >markw@osdl.org writes:
> >  
> >
> >>7.4beta5 offers more throughput.  One significant difference I see is in
> >>the oprofile for the database.  For the additional 7% increase in the
> >>metric, there are about 32% less ticks in SearchCatCache.
> >>    
> >>
> >
> >Hmm.  I have been profiling PG for some years now, and I cannot remember
> >ever seeing a profile in which SearchCatCache topped everything else
> >(the usual suspects for me are palloc/pfree support code).  Can you give
> >any explanation why it looks like that?  Can your profiling code tell
> >where the hotspot call sites of SearchCatCache are?
> >
> If I understand the docs correctly, op_to_source -a can do that - the 
> result is annotated assembly, with percentage numbers for each 
> instruction. If the sources were compiled with -g2, even source level 
> annotation is possible.
> 
> Mark, do you still have the oprofile output? I don't understand why so 
> much time is spent in the kernel signal handlers, i.e. I could use 
> annotated assembly or source of linux/kernel/signal.c.

I've rerun a test, capturing the raw oprofile output, running opannotate for
source and assmebly output (links for each should be on the page now.)  Let
me know if I've missed anything:http://developer.osdl.org/markw/dbt2-pgsql/190/

I'm running a test with your patch now too.  I should have results shortly. 

Thanks,
Mark


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Christopher Kings-Lynne
Date:
Numerics are a LOT slower than reals.  Integers are faster than anything 
I guess.

Chris

Mark Wong wrote:

> I don't remember making a conscious decision between the number and integer
> database type.  Is that a significant oversight on my part?
> 
> 
> On Fri, Oct 31, 2003 at 08:04:34PM -0500, Rod Taylor wrote:
> 
>>Excellent.
>>
>>I just noticed that most of the numbers in the system are given the
>>numeric data type. Is there any particular reason you don't use integer
>>(test enforced?)?
>>
>>On Fri, 2003-10-31 at 19:18, markw@osdl.org wrote:
>>
>>>I thought someone might be interested in a data point I have comparing
>>>7.3.4 and 7.4beta5 with results from our DBT-2 workload. Keep in mind I
>>>haven't done much tuning with either version.  The following links have
>>>references iostat, vmstat, sar, readprofile (linux kernel profile), and
>>>oprofile (postgresql profile) statistics.
>>>
>>>Results from 7.3.4:
>>>    http://developer.osdl.org/markw/dbt2-pgsql/184/
>>>    - metric 1354.58
>>>
>>>Results from 7.4beta5
>>>    http://developer.osdl.org/markw/dbt2-pgsql/188/
>>>    - metric 1446.01
>>>
>>>7.4beta5 offers more throughput.  One significant difference I see is in
>>>the oprofile for the database.  For the additional 7% increase in the
>>>metric, there are about 32% less ticks in SearchCatCache.
>>>
>>>These are the only database parameters I've explicitly set for each one,
>>>any other differences will be differences in default values:
>>>    - shared_buffers = 40000
>>>    - tcpip_socket = true
>>>    - checkpoint_segments = 200
>>>    - checkpoint_timeout = 1800
>>>    - stats_start_collector = true
>>>    - stats_command_string = true
>>>    - stats_block_level = true
>>>    - stats_row_level = true
>>>    - stats_reset_on_server_start = true
>>>
>>>If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
>>>happy to try them.  Or if anyone wants to be able to poke around on the
>>>system, we can arrange that too.  Feel free to ask any questions.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
On Sat, Nov 01, 2003 at 10:29:34PM +0100, Manfred Spraul wrote:
> Mark Wong wrote:
> 
> >Yeah, my dbt2 applications are multithreaded.
> >  
> >
> Do you need SIGPIPE delivery in your app? If no, could you try what 
> happens if you apply the attached patch to postgres, and perform the
>     signal(SIGPIPE, SIG_IGN);
> once in your dbt2 app?

Wow, that patch made a pretty big difference:http://developer.osdl.org/markw/dbt2-pgsql/191/- metric 1605.51

So no one has to look for older mail before I applied that patch:http://developer.osdl.org/markw/dbt2-pgsql/190/-
metric1427.24
 

Looks like about a 12% improvement in the overall metric.  The first thing I
noticed is that do_sigaction in the kernel profile almost disappeared.  The
top few functions in the database profile doesn't appear to have changed much.

Mark


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Mark Wong wrote:

>On Sat, Nov 01, 2003 at 10:29:34PM +0100, Manfred Spraul wrote:
>  
>
>>Mark Wong wrote:
>>
>>    
>>
>>>Yeah, my dbt2 applications are multithreaded.
>>> 
>>>
>>>      
>>>
>>Do you need SIGPIPE delivery in your app? If no, could you try what 
>>happens if you apply the attached patch to postgres, and perform the
>>    signal(SIGPIPE, SIG_IGN);
>>once in your dbt2 app?
>>    
>>
>
>Wow, that patch made a pretty big difference:
>    http://developer.osdl.org/markw/dbt2-pgsql/191/
>    - metric 1605.51
>
>So no one has to look for older mail before I applied that patch:
>    http://developer.osdl.org/markw/dbt2-pgsql/190/
>    - metric 1427.24
>
>Looks like about a 12% improvement in the overall metric.  The first thing I
>noticed is that do_sigaction in the kernel profile almost disappeared.
>
Cool.

>  The
>top few functions in the database profile doesn't appear to have changed much.
>  
>
I've looked at the profile:
The only unusal line is the memcpy(cur_skey, cache->cc_skey, 
sizeof(cur_skey)): it copies 144 byte and needs ~5.3% global cpu time, 
from the 12.1% in SearchCatCache. The cachelines (line size 128 bytes) 
of cc_skey are shared with cc_bucket. 1.8% cpu time is spent in 
DLMoveToFront, the function that moves cache entries around.

Perhaps a scalability problem of the hash table? The implementation 
moves the entries around all the time, i.e. the worst case for cache 
line transfers.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Rod Taylor
Date:
On Sat, 2003-11-01 at 20:58, Mark Wong wrote:
> I don't remember making a conscious decision between the number and integer
> database type.  Is that a significant oversight on my part?

Numerics do exact math with support for arbitrary numbers.  Unlike
Oracle, PostgreSQL does not retype NUMBER to a faster type internally
(like int).

You may find a good sized improvement (possibly as much as 15%) by
switching to integer as it will both reduce CPU load and storage
requirements.

Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> Tom Lane wrote:
>> I don't see that this proposal adds any security.
>> 
> It's not about security:

The proposal would be more salable if it addressed the security problem
too.  As is, you are proposing putting a large wart on libpq's API in
order to work around an inefficiency that's only been shown to exist in
one version of one operating system.  I'd like to look for other
solutions before we do that.

One possibility that comes to mind is simply to test whether the SIGPIPE
handler is already SIG_IGN before we munge it.  Ideally we'd do that
once when the conn object is created, but even if it had to be done more
often, it might still be a net win.
        regards, tom lane


Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
AgentM
Date:
On Sunday, Nov 2, 2003, at 18:16 Europe/Berlin, Tom Lane wrote:
> Manfred Spraul <manfred@colorfullife.com> writes:
>> Tom Lane wrote:
>>> I don't see that this proposal adds any security.
>>>
>> It's not about security:
>
> The proposal would be more salable if it addressed the security problem
> too.  As is, you are proposing putting a large wart on libpq's API in
> order to work around an inefficiency that's only been shown to exist in
> one version of one operating system.  I'd like to look for other
> solutions before we do that.
>
> One possibility that comes to mind is simply to test whether the 
> SIGPIPE
> handler is already SIG_IGN before we munge it.  Ideally we'd do that
> once when the conn object is created, but even if it had to be done 
> more
> often, it might still be a net win.

That wouldn't offer a solution for people who use SIGPIPE for other 
things during the lifetime of the program (after creating the 
connection) and if a SIGPIPE handler is called due to the connection, 
the handler won't be expecting the source, and polling signal for state 
is essentially what you do now. Instead, I propose a 
PQsigpipeOK/PQacceptsigpipe/PQrecvsigpipe(PGconn*) or something to that 
effect which skips this check for the connection. That way, programmers 
are aware that the connection could call their SIGPIPE handler because 
they explicitly request it and the library remains backwards-compatible.



Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Manfred Spraul
Date:
AgentM wrote:

> That wouldn't offer a solution for people who use SIGPIPE for other 
> things during the lifetime of the program (after creating the 
> connection) and if a SIGPIPE handler is called due to the connection, 
> the handler won't be expecting the source, and polling signal for 
> state is essentially what you do now. Instead, I propose a 
> PQsigpipeOK/PQacceptsigpipe/PQrecvsigpipe(PGconn*) or something to 
> that effect which skips this check for the connection. That way, 
> programmers are aware that the connection could call their SIGPIPE 
> handler because they explicitly request it and the library remains 
> backwards-compatible.

If I understand libpq sources correctly, the first packets are send 
during connection setup - PQsigpipeOK(PGconn *) would be too late.
That's why I added "sigpipe=caller" as a new flag for PQconnectdb.

--   Manfred



Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> If I understand libpq sources correctly, the first packets are send 
> during connection setup - PQsigpipeOK(PGconn *) would be too late.
> That's why I added "sigpipe=caller" as a new flag for PQconnectdb.

That's definitely a problem, but "sigpipe=caller" is one of the very
worst aspects of your proposal.  In many apps the connectinfo string is
passed in more-or-less-directly from user input.  But having the user
fool with sigpipe handling would be disastrous --- either the app is
coded to handle sigpipe properly for itself, or it isn't.  If we're
going to make a hook that allows the app to tell libpq how to handle
sigpipe, then we have to make it feed in the information in some way
other than via the connectinfo string.

It strikes me that sigpipe handling will be a global affair in any
particular application --- it's unlikely that it would be correct for
some PG connections and wrong for others.  So one possibility is to make
the control variable be global (static) and thus it could be set before
creating the first PGconn.
        regards, tom lane


Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
markw@osdl.org
Date:
On  1 Nov, Tom Lane wrote:
> Manfred Spraul <manfred@colorfullife.com> writes:
>> Tom Lane wrote:
>>> What we really want is to turn off SIGPIPE delivery on our socket
>>> (only), but AFAIK there is no API to do that.
>>> 
>> Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux 
>> specific.
> 
> Hmm ... a Linux-specific solution would be better than none at all.
> 
> A bigger objection is that we couldn't get libssl to use it (AFAIK).
> The flag really needs to be settable on the socket (eg, via fcntl),
> not per-send.

I'm a bit unfamiliar with this stuff, so I wanted to ask if this was
something that Linux appears to be handling differently than other OS's,
or if this was a platform specific issue with postgresql.

Thanks,
Mark


Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Manfred Spraul
Date:
Tom Lane wrote:

>It strikes me that sigpipe handling will be a global affair in any
>particular application --- it's unlikely that it would be correct for
>some PG connections and wrong for others.  So one possibility is to make
>the control variable be global (static) and thus it could be set before
>creating the first PGconn.
>
>
What about the attached patches?
I hope I found all places that must be updated when a new function is
added to libpq.

--
    Manfred
Index: doc/src/sgml/libpq.sgml
===================================================================
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/libpq.sgml,v
retrieving revision 1.141
diff -c -r1.141 libpq.sgml
*** doc/src/sgml/libpq.sgml    1 Nov 2003 01:56:29 -0000    1.141
--- doc/src/sgml/libpq.sgml    3 Nov 2003 20:35:57 -0000
***************
*** 645,650 ****
--- 645,693 ----
    </listitem>
   </varlistentry>

+  <varlistentry>
+   <term><function>PQsetsighandling</function><indexterm><primary>PQsetsighandling</></></term>
+   <term><function>PQgetsighandling</function><indexterm><primary>PQgetsighandling</></></term>
+   <listitem>
+    <para>
+    Set/query SIGPIPE signal handling.
+ <synopsis>
+ void PQsetsighandling(int internal_sigign);
+ </synopsis>
+ <synopsis>
+ int PQgetsighandling(void);
+ </synopsis>
+ </para>
+
+ <para>
+     These functions allow to query and set the SIGPIPE signal handling
+     of libpq: by default, Unix systems generate a (fatal) SIGPIPE signal
+     on a send to a socket that lost it's connection. Most callers expect
+     a normal error return instead of the signal. A normal error return
+     can be achieved by blocking or ignoring the SIGPIPE signal. This can
+     be done either globally in the application or inside libpq.
+    </para>
+    <para>
+     If internal signal handling is enabled (this is the default), then
+     libpq sets the SIGPIPE handler to SIG_IGN before every socket send
+     operation and restores it afterwards. This prevents libpq from
+     killing the application, at the cost of a slight performance
+     decrease. This approach is not reliable for multithreaded applications.
+    </para>
+    <para>
+     If internal signal handling is disabled, then the caller is
+     responsible for blocking or handling SIGPIPE signals. This is
+     recommended for multithreaded applications.
+    </para>
+    <para>
+     The signal handler setting is a global flag, it affects all
+     connections. The setting has no effect for Win32 clients - Win32
+     doesn't generate SIGPIPE events.
+    </para>
+   </listitem>
+  </varlistentry>
+
+
   </variablelist>
  </para>
  </sect1>
Index: src/interfaces/libpq/blibpqdll.def
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/blibpqdll.def,v
retrieving revision 1.9
diff -c -r1.9 blibpqdll.def
*** src/interfaces/libpq/blibpqdll.def    13 Aug 2003 16:29:03 -0000    1.9
--- src/interfaces/libpq/blibpqdll.def    3 Nov 2003 20:35:59 -0000
***************
*** 113,118 ****
--- 113,120 ----
      _PQfformat               @ 109
      _PQexecPrepared          @ 110
      _PQsendQueryPrepared     @ 111
+     _PQsetsighandling        @ 112
+     _PQgetsighandling        @ 113

  ; Aliases for MS compatible names
      PQconnectdb             = _PQconnectdb
***************
*** 226,228 ****
--- 228,232 ----
      PQfformat               = _PQfformat
      PQexecPrepared          = _PQexecPrepared
      PQsendQueryPrepared     = _PQsendQueryPrepared
+     PQsetsighandling        = _PQsetsighandling
+     PQgetsighandling        = _PQgetsighandling
Index: src/interfaces/libpq/fe-secure.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.32
diff -c -r1.32 fe-secure.c
*** src/interfaces/libpq/fe-secure.c    29 Sep 2003 16:38:04 -0000    1.32
--- src/interfaces/libpq/fe-secure.c    3 Nov 2003 20:35:59 -0000
***************
*** 198,203 ****
--- 198,204 ----
  -----END DH PARAMETERS-----\n";
  #endif

+ static int do_sigaction = 1;
  /* ------------------------------------------------------------ */
  /*             Procedures common to all secure sessions            */
  /* ------------------------------------------------------------ */
***************
*** 348,354 ****
      ssize_t        n;

  #ifndef WIN32
!     pqsigfunc    oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
  #endif

  #ifdef USE_SSL
--- 349,358 ----
      ssize_t        n;

  #ifndef WIN32
!     pqsigfunc    oldsighandler = NULL;
!
!     if (do_sigaction)
!         oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
  #endif

  #ifdef USE_SSL
***************
*** 408,417 ****
          n = send(conn->sock, ptr, len, 0);

  #ifndef WIN32
!     pqsignal(SIGPIPE, oldsighandler);
  #endif

      return n;
  }

  /* ------------------------------------------------------------ */
--- 412,432 ----
          n = send(conn->sock, ptr, len, 0);

  #ifndef WIN32
!     if (do_sigaction)
!         pqsignal(SIGPIPE, oldsighandler);
  #endif

      return n;
+ }
+
+ void PQsetsighandling(int internal_sigign)
+ {
+     do_sigaction = internal_sigign;
+ }
+
+ int PQgetsighandling(void)
+ {
+     return do_sigaction;
  }

  /* ------------------------------------------------------------ */
Index: src/interfaces/libpq/libpq-fe.h
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/libpq-fe.h,v
retrieving revision 1.100
diff -c -r1.100 libpq-fe.h
*** src/interfaces/libpq/libpq-fe.h    27 Aug 2003 00:33:34 -0000    1.100
--- src/interfaces/libpq/libpq-fe.h    3 Nov 2003 20:36:00 -0000
***************
*** 221,226 ****
--- 221,232 ----
  /* free the data structure returned by PQconndefaults() */
  extern void PQconninfoFree(PQconninfoOption *connOptions);

+ /* ===    in fe-secure.c === */
+
+ /* get/set SIGPIPE handling */
+ extern void PQsetsighandling(int internal_sigign);
+ extern int PQgetsighandling(void);
+
  /*
   * close the current connection and restablish a new one with the same
   * parameters
? contrib/pgbench/pgbench
Index: contrib/pgbench/README.pgbench
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pgbench/README.pgbench,v
retrieving revision 1.9
diff -c -r1.9 README.pgbench
*** contrib/pgbench/README.pgbench    10 Jun 2003 09:07:15 -0000    1.9
--- contrib/pgbench/README.pgbench    3 Nov 2003 19:53:13 -0000
***************
*** 112,117 ****
--- 112,121 ----
          might be a security hole since ps command will
          show the password. Use this for TESTING PURPOSE ONLY.

+     -a
+         Disable SIGPIPE delivery globally instead of within each
+         libpq operation.
+
      -n
          No vacuuming and cleaning the history table prior to the
          test is performed.
Index: contrib/pgbench/pgbench.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/contrib/pgbench/pgbench.c,v
retrieving revision 1.27
diff -c -r1.27 pgbench.c
*** contrib/pgbench/pgbench.c    27 Sep 2003 19:15:34 -0000    1.27
--- contrib/pgbench/pgbench.c    3 Nov 2003 19:53:15 -0000
***************
*** 28,33 ****
--- 28,34 ----
  #else
  #include <sys/time.h>
  #include <unistd.h>
+ #include <signal.h>

  #ifdef HAVE_GETOPT_H
  #include <getopt.h>
***************
*** 105,112 ****
  static void
  usage()
  {
!     fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s
scaling_factor][-n][-C][-v][-S][-N][-l][-Ulogin][-P password][-d][dbname]\n"); 
!     fprintf(stderr, "(initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor][-U login][-P
password][-d][dbname]\n");
  }

  /* random number generator */
--- 106,113 ----
  static void
  usage()
  {
!     fprintf(stderr, "usage: pgbench [-h hostname][-p port][-c nclients][-t ntransactions][-s
scaling_factor][-n][-C][-v][-S][-N][-l][-a][-Ulogin][-P password][-d][dbname]\n"); 
!     fprintf(stderr, "(initialize mode): pgbench -i [-h hostname][-p port][-s scaling_factor][-U login][-P
password][-d][dbname][-a]\n");
  }

  /* random number generator */
***************
*** 703,712 ****
      else if ((env = getenv("PGUSER")) != NULL && *env != '\0')
          login = env;

!     while ((c = getopt(argc, argv, "ih:nvp:dc:t:s:U:P:CNSl")) != -1)
      {
          switch (c)
          {
              case 'i':
                  is_init_mode++;
                  break;
--- 704,719 ----
      else if ((env = getenv("PGUSER")) != NULL && *env != '\0')
          login = env;

!     while ((c = getopt(argc, argv, "aih:nvp:dc:t:s:U:P:CNSl")) != -1)
      {
          switch (c)
          {
+             case 'a':
+ #ifndef WIN32
+                 signal(SIGPIPE, SIG_IGN);
+ #endif
+                 PQsetsighandling(0);
+                 break;
              case 'i':
                  is_init_mode++;
                  break;

Re: Avoiding SIGPIPE (was Re: OSDL DBT-2 w/ PostgreSQL

From
Tom Lane
Date:
markw@osdl.org writes:
> I'm a bit unfamiliar with this stuff, so I wanted to ask if this was
> something that Linux appears to be handling differently than other OS's,
> or if this was a platform specific issue with postgresql.

It's generic to all Unixen.
        regards, tom lane


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
markw@osdl.org
Date:
On  1 Nov, Tom Lane wrote:
> Manfred Spraul <manfred@colorfullife.com> writes:
>> signal handlers are a process property, not a thread property - that 
>> code is broken for multi-threaded apps.
> 
> Yeah, that's been mentioned before, but I don't see any way around it.
> What we really want is to turn off SIGPIPE delivery on our socket
> (only), but AFAIK there is no API to do that.

Will this be a problem for multi-threaded apps with any of the client
interfaces?

Anyone working on making it threadsafe?

Thanks,
Mark


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
markw@osdl.org wrote:

>On  1 Nov, Tom Lane wrote:
>  
>
>>Manfred Spraul <manfred@colorfullife.com> writes:
>>    
>>
>>>signal handlers are a process property, not a thread property - that 
>>>code is broken for multi-threaded apps.
>>>      
>>>
>>Yeah, that's been mentioned before, but I don't see any way around it.
>>What we really want is to turn off SIGPIPE delivery on our socket
>>(only), but AFAIK there is no API to do that.
>>    
>>
>
>Will this be a problem for multi-threaded apps with any of the client
>interfaces?
>
>Anyone working on making it threadsafe?
>  
>
The POSIX api is not thread safe: signal handlers are per process, and 
libpq would like to block SIGPIPE for it's send() calls. For single 
threaded apps, libpq just calls sigaction and sets the handler to 
SIG_IGN around the syscalls.
For multithreaded apps, this is not possible: sigaction is per process.
Thus the calling application must handle the SIGPIPE signals for libpq - 
either by blocking or ignoring them. We are still discussing the exact 
API. Probably a global state that is accessible through a new function.

One thread-safe alternative might be the combination of sigprocmask / 
pthread_sigmask and sigwait, but I think this would be too fragile.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Tom Lane
Date:
Manfred Spraul <manfred@colorfullife.com> writes:
> For multithreaded apps, this is not possible: sigaction is per process.
> Thus the calling application must handle the SIGPIPE signals for libpq - 
> either by blocking or ignoring them. We are still discussing the exact 
> API. Probably a global state that is accessible through a new function.

I think we should also take a hard look at avoiding the problem by using
MSG_NOSIGNAL on platforms that have it, so that the right things happen
and overhead is minimized whether or not the application knows to do
this.  Besides I am not convinced that an app that *wants* SIGPIPE on
some of its output channels could reasonably solve the problem with a
custom signal handler --- how will the handler know which FD the signal
came from?

The difficulty with MSG_NOSIGNAL is in getting libssl to cooperate.
I wonder if we could convince the libssl implementors that MSG_NOSIGNAL
should be used when available in their standard release, or at least
they should provide an option to use it?  The alternative would be to
create our own BIO for libssl, which I think is doable, but would likely
be a pain in the neck to maintain.
        regards, tom lane


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Manfred Spraul
Date:
Tom Lane wrote:

>Manfred Spraul <manfred@colorfullife.com> writes:
>  
>
>>For multithreaded apps, this is not possible: sigaction is per process.
>>Thus the calling application must handle the SIGPIPE signals for libpq - 
>>either by blocking or ignoring them. We are still discussing the exact 
>>API. Probably a global state that is accessible through a new function.
>>    
>>
>
>I think we should also take a hard look at avoiding the problem by using
>MSG_NOSIGNAL on platforms that have it,
>
I think that's the second step. First we need a portable solution, then 
we can optimize it.
The fastest solution is one signal(SIGPIPE, SIG_IGN) in main(), but that 
requires a change in all libpq users. OTHO there shouldn't be that many 
multithreaded users.
sigprocmask + sigwait could work, but sigprocmask is undefined if 
multiple threads are running. Is there a portable approach for weak 
links? libpq would have to call proc_sigmask if linked against 
libpthread, and sigprocmask if not linked against libpthread. With gcc, 
I could use 'void proc_sigmask () __attribute__ ((weak, alias 
("_sigprocmask")));' or something similar, but this wouldn't be portable 
either.

--   Manfred



Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Mark Wong
Date:
On Sun, Nov 02, 2003 at 07:25:38AM -0500, Rod Taylor wrote:
> On Sat, 2003-11-01 at 20:58, Mark Wong wrote:
> > I don't remember making a conscious decision between the number and integer
> > database type.  Is that a significant oversight on my part?
> 
> Numerics do exact math with support for arbitrary numbers.  Unlike
> Oracle, PostgreSQL does not retype NUMBER to a faster type internally
> (like int).
> 
> You may find a good sized improvement (possibly as much as 15%) by
> switching to integer as it will both reduce CPU load and storage
> requirements.

I've changed all the numerics to integers and reals, where it was appropriate
to maintain the precision specificed in the TPC-C spec.  Here's a comparison
of results:

http://developer.osdl.org/markw/dbt2-pgsql/214/- using all numerics- metric 1831.78

http://developer.osdl.org/markw/dbt2-pgsql/217/- integers and reals where appropriate- metric 1972.94

Looks like I see about an 8% improvement in the metric with this instance.  A
definite decrease in user time in the processor utilization chart, which I
presume is attributed to the 70% decrease in ticks to SearchCatCache in the
database as reported by oprofile.  Can anyone explain that one?

Mark


Re: OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5

From
Tom Lane
Date:
Mark Wong <markw@osdl.org> writes:
> I've changed all the numerics to integers and reals, where it was appropriate
> to maintain the precision specificed in the TPC-C spec.
> ...
> Looks like I see about an 8% improvement in the metric with this instance.  A
> definite decrease in user time in the processor utilization chart, which I
> presume is attributed to the 70% decrease in ticks to SearchCatCache in the
> database as reported by oprofile.  Can anyone explain that one?

That's odd; I don't see why NUMERIC would be incurring extra
SearchCatCache calls.  Does your profile provide info about where the
SearchCatCache calls are coming from?
        regards, tom lane