
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     Uh - not much time to spend if the statistics should at least
> >     be  half  accurate. And it would become worse in SMP systems.
> >     So that was a nifty idea, but I think it'd  cause  much  more
> >     statistic losses than I assumed at first.
>
> >     Back to drawing board. Maybe a SYS-V message queue can serve?
>
> That would be the same as a pipe: backends would block if the collector
> stopped accepting data.  I do like the "auto discard" aspect of this
> UDP-socket approach.

    Does  a pipe guarantee that a buffer, written with one atomic
    write(2), never can get intermixed with  other  data  on  the
    readers  end?   I know that you know what I mean, but for the
    broader audience: Let's define a message to the collector  to
    be  4byte-len,len-bytes.   Now  hundreds  of  backends hammer
    messages into the (shared) writing end of the pipe, all  with
    different     sizes.     Is     it    GUARANTEED    that    a
    read(4bytes),read(nbytes) sequence will  allways  return  one
    complete  message  and  never  intermixed  parts of different
    write(2)s?

    With message queues, this is guaranteed. Also, message queues
    would  make  it  easy  to query the collected statistics (see
    below).

> I think Philip had the right idea: each backend should send totals,
> not deltas, in its messages.  Then, it doesn't matter (much) if the
> collector loses some messages --- that just means that sometimes it
> has a slightly out-of-date idea about how much work some backends have
> done.  It should be easy to design the software so that that just makes
> a small, transient error in the currently displayed statistics.

    If we use two message queues (IPC_PRIVATE  is  enough  here),
    one  into collector and one into backend direction, this'd be
    an easy way to collect and query statistics.

    The backends send delta stats messages to  the  collector  on
    one  queue. Message queues block, by default, but the backend
    could use IPC_NOWAIT and just go on and collect up,  as  long
    as  it finally will use a blocking call before exiting. We'll
    loose  statistics  for  backends  that  go  down  in   flames
    (coredump), but who cares for statistics then?

    To  query statistics, we have a set of new builtin functions.
    All functions share  a  global  statistics  snapshot  in  the
    backend.  If  on  function call the snapshot doesn't exist or
    was generated by  another  XACT/commandcounter,  the  backend
    sends  a  statistics  request  for  his  database  ID  to the
    collector and waits for the messages to arrive on the  second
    message  queue. It can pick up the messages meant for him via
    message type, which's equal to his backend number +1, because
    the  collector will send 'em as such.  For table access stats
    for example, the snapshot will have slots identified  by  the
    tables  OID,  so  a function pg_get_tables_seqscan_count(oid)
    should be easy  to  implement.  And  setting  up  views  that
    present access stats in readable format is a nobrainer.

    Now  we  have communication only between the backends and the
    collector.  And we're  certain  that  only  someone  able  to
    SELECT from a system view will ever see this information.


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


Fri, 16 Mar 2001 14:40:24 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler
Jan Wieck <JanWieck@Yahoo.com> writes:
>     Does  a pipe guarantee that a buffer, written with one atomic
>     write(2), never can get intermixed with  other  data  on  the
>     readers  end?

Yes.  The HPUX man page for write(2) sez:

          o  Write requests of {PIPE_BUF} bytes or less will not be
             interleaved with data from other processes doing writes on the
             same pipe.  Writes of greater than {PIPE_BUF} bytes may have
             data interleaved, on arbitrary boundaries, with writes by
             other processes, whether or not the O_NONBLOCK flag of the
             file status flags is set.

Stevens' _UNIX Network Programming_ (1990) states this is true for all
pipes (nameless or named) on all flavors of Unix, and furthermore states
that PIPE_BUF is at least 4K on all systems.  I don't have any relevant
Posix standards to look at, but I'm not worried about assuming this to
be true.

>     With message queues, this is guaranteed. Also, message queues
>     would  make  it  easy  to query the collected statistics (see
>     below).

I will STRONGLY object to any proposal that we use message queues.
We've already had enough problems with the ridiculously low kernel
limits that are commonly imposed on shmem and SysV semaphores.
We don't need to buy into that silliness yet again with message queues.
I don't believe they gain us anything over pipes anyway.

The real problem with either pipes or message queues is that backends
will block if the collector stops collecting data.  I don't think we
want that.  I suppose we could have the backends write a pipe with
O_NONBLOCK and ignore failure, however:

          o  If the O_NONBLOCK flag is set, write() requests will  be
             handled differently, in the following ways:

             -  The write() function will not block the process.

             -  A write request for {PIPE_BUF} or fewer bytes  will have
                the following effect:  If there is sufficient space
                available in the pipe, write() will transfer all the data
                and return the number of bytes  requested.  Otherwise,
                write() will transfer no data and return -1 with errno set
                to EAGAIN.

Since we already ignore SIGPIPE, we don't need to worry about losing the
collector entirely.

Now this would put a pretty tight time constraint on the collector:
fall more than 4K behind, you start losing data.  I am not sure if
a UDP socket would provide more buffering or not; anyone know?

			regards, tom lane

Fri, 16 Mar 2001 15:37:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor signal handler 
Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
> >     Does  a pipe guarantee that a buffer, written with one atomic
> >     write(2), never can get intermixed with  other  data  on  the
> >     readers  end?
>
> Yes.  The HPUX man page for write(2) sez:
>
>           o  Write requests of {PIPE_BUF} bytes or less will not be
>              interleaved with data from other processes doing writes on the
>              same pipe.  Writes of greater than {PIPE_BUF} bytes may have
>              data interleaved, on arbitrary boundaries, with writes by
>              other processes, whether or not the O_NONBLOCK flag of the
>              file status flags is set.
>
> Stevens' _UNIX Network Programming_ (1990) states this is true for all
> pipes (nameless or named) on all flavors of Unix, and furthermore states
> that PIPE_BUF is at least 4K on all systems.  I don't have any relevant
> Posix standards to look at, but I'm not worried about assuming this to
> be true.

    That's good news - and maybe a Good Assumption (TM).

> >     With message queues, this is guaranteed. Also, message queues
> >     would  make  it  easy  to query the collected statistics (see
> >     below).
>
> I will STRONGLY object to any proposal that we use message queues.
> We've already had enough problems with the ridiculously low kernel
> limits that are commonly imposed on shmem and SysV semaphores.
> We don't need to buy into that silliness yet again with message queues.
> I don't believe they gain us anything over pipes anyway.

   OK.

> The real problem with either pipes or message queues is that backends
> will block if the collector stops collecting data.  I don't think we
> want that.  I suppose we could have the backends write a pipe with
> O_NONBLOCK and ignore failure, however:
>
>           o  If the O_NONBLOCK flag is set, write() requests will  be
>              handled differently, in the following ways:
>
>              -  The write() function will not block the process.
>
>              -  A write request for {PIPE_BUF} or fewer bytes  will have
>                 the following effect:  If there is sufficient space
>                 available in the pipe, write() will transfer all the data
>                 and return the number of bytes  requested.  Otherwise,
>                 write() will transfer no data and return -1 with errno set
>                 to EAGAIN.
>
> Since we already ignore SIGPIPE, we don't need to worry about losing the
> collector entirely.

    That's  not  what  the manpage said. It said that in the case
    you're inside PIPE_BUF size and using O_NONBLOCK, you  either
    send complete messages or nothing, getting an EAGAIN then.

    So  we  could  do the same here and write to the pipe. In the
    case we cannot, just count up and try  again  next  year  (or
    so).

>
> Now this would put a pretty tight time constraint on the collector:
> fall more than 4K behind, you start losing data.  I am not sure if
> a UDP socket would provide more buffering or not; anyone know?

    Again,   this   ain't  what  the  manpage  said.  If  there's
    sufficient space available in the pipe  in  combination  with
    that  PIPE_BUF  is  at least 4K doesn't necessarily mean that
    the pipes buffer space is 4K.

    Well,  what  I'm  missing  is  the  ability  to  filter   out
    statistics reports on the backend side via msgrcv(2)s msgtype
    :-(


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


Fri, 16 Mar 2001 16:03:21 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler
Tom Lane wrote:
> Now this would put a pretty tight time constraint on the collector:
> fall more than 4K behind, you start losing data.  I am not sure if
> a UDP socket would provide more buffering or not; anyone know?

    Looks  like Linux has something around 16-32K of buffer space
    for UDP sockets. Just from eyeballing the  fprintf(3)  output
    of my destructively hacked postleprechaun.


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


Fri, 16 Mar 2001 16:18:13 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler
Jan Wieck wrote:
> Tom Lane wrote:
> > Now this would put a pretty tight time constraint on the collector:
> > fall more than 4K behind, you start losing data.  I am not sure if
> > a UDP socket would provide more buffering or not; anyone know?
>
>     Looks  like Linux has something around 16-32K of buffer space
>     for UDP sockets. Just from eyeballing the  fprintf(3)  output
>     of my destructively hacked postleprechaun.

    Just  to  get  some  evidence  at hand - could some owners of
    different platforms compile and run  the  attached  little  C
    source please?

    (The  program  tests how much data can be stuffed into a pipe
    or a Sys-V message queue before the writer would block or get
    an EAGAIN error).

    My output on RedHat6.1 Linux 2.2.17 is:

        Pipe buffer is 4096 bytes
        Sys-V message queue buffer is 16384 bytes

    Seems Tom is (unfortunately) right. The pipe blocks at 4K.

    So  a  Sys-V  message  queue,  with the ability to distribute
    messages from  the  collector  to  individual  backends  with
    kernel  support  via  "mtype"  is  four  times by unestimated
    complexity better here.  What does your system say?

    I really never thought that Sys-V IPC is a good way to go  at
    all.   I  hate  it's  incompatibility to the select(2) system
    call and all these  OS/installation  dependant  restrictions.
    But I'm tempted to reevaluate it "for this case".


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
Fri, 16 Mar 2001 17:25:24 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler
Jan Wieck <JanWieck@yahoo.com> writes:
>     Just  to  get  some  evidence  at hand - could some owners of
>     different platforms compile and run  the  attached  little  C
>     source please?

HPUX 10.20:

Pipe buffer is 8192 bytes
Sys-V message queue buffer is 16384 bytes

			regards, tom lane

Fri, 16 Mar 2001 18:00:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor signal handler 

>     Just  to  get  some  evidence  at hand - could some owners of
>     different platforms compile and run  the  attached  little  C
>     source please?

$ uname -srm
FreeBSD 4.1.1-STABLE
$ ./jan
Pipe buffer is 16384 bytes
Sys-V message queue buffer is 2048 bytes

$ uname -srm
NetBSD 1.5 alpha
$ ./jan
Pipe buffer is 4096 bytes
Sys-V message queue buffer is 2048 bytes

$ uname -srm
NetBSD 1.5_BETA2 i386
$ ./jan
Pipe buffer is 4096 bytes
Sys-V message queue buffer is 2048 bytes

$ uname -srm
NetBSD 1.4.2 i386
$ ./jan
Pipe buffer is 4096 bytes
Sys-V message queue buffer is 2048 bytes

$ uname -srm
NetBSD 1.4.1 sparc
$ ./jan
Pipe buffer is 4096 bytes
Bad system call (core dumped)	# no SysV IPC in running kernel

$ uname -srm
HP-UX B.11.11 9000/800
$ ./jan
Pipe buffer is 8192 bytes
Sys-V message queue buffer is 16384 bytes

$ uname -srm
HP-UX B.11.00 9000/813
$ ./jan
Pipe buffer is 8192 bytes
Sys-V message queue buffer is 16384 bytes

$ uname -srm
HP-UX B.10.20 9000/871
$ ./jan
Pipe buffer is 8192 bytes
Sys-V message queue buffer is 16384 bytes

HP-UX can also use STREAMS based pipes if the kernel parameter
streampipes is set.  Using STREAMS based pipes increases the pipe
buffer size by a lot:

# uname -srm 
HP-UX B.11.11 9000/800
# ./jan
Pipe buffer is 131072 bytes
Sys-V message queue buffer is 16384 bytes

# uname -srm
HP-UX B.11.00 9000/800
# ./jan
Pipe buffer is 131072 bytes
Sys-V message queue buffer is 16384 bytes

Regards,

Giles

Sat, 17 Mar 2001 11:17:51 +1100
Giles Lean <giles@nemeton.com.au>
Re: Performance monitor signal handler 
* Jan Wieck <JanWieck@Yahoo.com> [010316 16:35]:
> Jan Wieck wrote:
> > Tom Lane wrote:
> > > Now this would put a pretty tight time constraint on the collector:
> > > fall more than 4K behind, you start losing data.  I am not sure if
> > > a UDP socket would provide more buffering or not; anyone know?
> >
> >     Looks  like Linux has something around 16-32K of buffer space
> >     for UDP sockets. Just from eyeballing the  fprintf(3)  output
> >     of my destructively hacked postleprechaun.
> 
>     Just  to  get  some  evidence  at hand - could some owners of
>     different platforms compile and run  the  attached  little  C
>     source please?
> 
>     (The  program  tests how much data can be stuffed into a pipe
>     or a Sys-V message queue before the writer would block or get
>     an EAGAIN error).
> 
>     My output on RedHat6.1 Linux 2.2.17 is:
> 
>         Pipe buffer is 4096 bytes
>         Sys-V message queue buffer is 16384 bytes
> 
>     Seems Tom is (unfortunately) right. The pipe blocks at 4K.
> 
>     So  a  Sys-V  message  queue,  with the ability to distribute
>     messages from  the  collector  to  individual  backends  with
>     kernel  support  via  "mtype"  is  four  times by unestimated
>     complexity better here.  What does your system say?
> 
>     I really never thought that Sys-V IPC is a good way to go  at
>     all.   I  hate  it's  incompatibility to the select(2) system
>     call and all these  OS/installation  dependant  restrictions.
>     But I'm tempted to reevaluate it "for this case".
> 
> 
> Jan
$ ./queuetest
Pipe buffer is 32768 bytes
Sys-V message queue buffer is 4096 bytes
$ uname -a
UnixWare lerami 5 7.1.1 i386 x86at SCO UNIX_SVR5
$ 

I think some of these are configurable...

LER

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Fri, 16 Mar 2001 20:43:27 -0600
Larry Rosenman <ler@lerctr.org>
Re: Performance monitor signal handler
* Larry Rosenman <ler@lerctr.org> [010316 20:47]:
> * Jan Wieck <JanWieck@Yahoo.com> [010316 16:35]:
> $ ./queuetest
> Pipe buffer is 32768 bytes
> Sys-V message queue buffer is 4096 bytes
> $ uname -a
> UnixWare lerami 5 7.1.1 i386 x86at SCO UNIX_SVR5
> $ 
> 
> I think some of these are configurable...
They both are.  FIFOBLKSIZE and MSGMNB or some such kernel tunable.

I can get more info if you need it.

LER

-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Fri, 16 Mar 2001 21:07:53 -0600
Larry Rosenman <ler@lerctr.org>
Re: Performance monitor signal handler
On Fri, Mar 16, 2001 at 05:25:24PM -0500, Jan Wieck wrote:
> Jan Wieck wrote:
...
>     Just  to  get  some  evidence  at hand - could some owners of
>     different platforms compile and run  the  attached  little  C
>     source please?
... 
>     Seems Tom is (unfortunately) right. The pipe blocks at 4K.

On NetBSD-1.5S/i386 with just the highly conservative shmem defaults:

Pipe buffer is 4096 bytes
Sys-V message queue buffer is 2048 bytes

Cheers,

Patrick

Sat, 17 Mar 2001 20:29:56 +0000
Patrick Welche <prlw1@newn.cam.ac.uk>
Re: Performance monitor signal handler
Jan Wieck <JanWieck@yahoo.com> writes:
>     Just  to  get  some  evidence  at hand - could some owners of
>     different platforms compile and run  the  attached  little  C
>     source please?
>     (The  program  tests how much data can be stuffed into a pipe
>     or a Sys-V message queue before the writer would block or get
>     an EAGAIN error).

One final followup on this --- I wasted a fair amount of time just
now trying to figure out why Perl 5.6.0 was silently hanging up
in its self-tests (at op/taint, which seems pretty unrelated...).

The upshot: Jan's test program had left a 16k SysV message queue
hanging about, and that queue was filling all available SysV message
space on my machine.  Seems Perl tries to test message-queue sending,
and it was patiently waiting for some message space to come free.

In short, the SysV message queue limits are so tiny that not only
are you quite likely to get bollixed up if you use messages, but
you're likely to bollix anything else that's using message queues too.

			regards, tom lane

Mon, 19 Mar 2001 00:28:45 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor signal handler 
I have a new statistics collection proposal.

I suggest three shared memory areas:

	One per backend to hold the query string and other per-backend stats
	One global area to hold accumulated stats for all backends
	One global circular buffer to hold per-table/object stats

The circular buffer will look like:

	(Loops) Start---------------------------End
                             |
                             current pointer

Loops is incremented every time the pointer reaches "end".

Each statistics record will have a length of five bytes made up of
oid(4) and action(1).  By having the same length for all statistics
records, we don't need to perform any locking of the buffer.  A backend
will grab the current pointer, add five to it, and write into the
reserved 5-byte area.  If two backends write at the same time, one
overwrites the other, but this is just statistics information, so it is
not a great lose.

Only shared memory gives us near-zero cost for write/read.  99% of
backends will not be using stats, so it has to be cheap.

The collector program can read the shared memory stats and keep hashed
values of accumulated stats.  It uses the "Loops" variable to know if it
has read the current information in the buffer.  When it receives a
signal, it can dump its stats to a file in standard COPY format of
<oid><tab><action><tab><count>.  It can also reset its counters with a
signal.

Comments?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Mar 2001 11:09:45 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor signal handler
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Only shared memory gives us near-zero cost for write/read.  99% of
> backends will not be using stats, so it has to be cheap.

Not with a circular buffer it's not cheap, because you need interlocking
on writes.  Your claim that you can get away without that is simply
false.  You won't just get lost messages, you'll get corrupted messages.

> The collector program can read the shared memory stats and keep hashed
> values of accumulated stats.  It uses the "Loops" variable to know if it
> has read the current information in the buffer.

And how does it sleep until the counter has been advanced?  Seems to me
it has to busy-wait (bad) or sleep (worse; if the minimum sleep delay
is 10 ms then it's guaranteed to miss a lot of data under load).

			regards, tom lane

Mon, 19 Mar 2001 11:28:24 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Performance monitor signal handler 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Only shared memory gives us near-zero cost for write/read.  99% of
> > backends will not be using stats, so it has to be cheap.
> 
> Not with a circular buffer it's not cheap, because you need interlocking
> on writes.  Your claim that you can get away without that is simply
> false.  You won't just get lost messages, you'll get corrupted messages.

How do I get corrupt messages if they are all five bytes?  If I write
five bytes, and another does the same, I guess the assembler could
intersperse the writes so the oid gets to be a corrupt value.  Any cheap
way around this, perhaps by skiping/clearing the write on a collision?

> 
> > The collector program can read the shared memory stats and keep hashed
> > values of accumulated stats.  It uses the "Loops" variable to know if it
> > has read the current information in the buffer.
> 
> And how does it sleep until the counter has been advanced?  Seems to me
> it has to busy-wait (bad) or sleep (worse; if the minimum sleep delay
> is 10 ms then it's guaranteed to miss a lot of data under load).

I figured it could just wake up every few seconds and check.  It will
remember the loop counter and current pointer, and read any new
information.  I was thinking of a 20k buffer, which could cover about 4k
events.

Should we think about doing these writes into an OS file, and only
enabling the writes when we know there is a collector reading them,
perhaps using a /tmp file to activate recording.  We could allocation
1MB and be sure not to miss anything, even with a circular setup.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Mar 2001 11:50:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor signal handler
Bruce Momjian wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Only shared memory gives us near-zero cost for write/read.  99% of
> > > backends will not be using stats, so it has to be cheap.
> >
> > Not with a circular buffer it's not cheap, because you need interlocking
> > on writes.  Your claim that you can get away without that is simply
> > false.  You won't just get lost messages, you'll get corrupted messages.
>
> How do I get corrupt messages if they are all five bytes?  If I write
> five bytes, and another does the same, I guess the assembler could
> intersperse the writes so the oid gets to be a corrupt value.  Any cheap
> way around this, perhaps by skiping/clearing the write on a collision?
>
> >
> > > The collector program can read the shared memory stats and keep hashed
> > > values of accumulated stats.  It uses the "Loops" variable to know if it
> > > has read the current information in the buffer.
> >
> > And how does it sleep until the counter has been advanced?  Seems to me
> > it has to busy-wait (bad) or sleep (worse; if the minimum sleep delay
> > is 10 ms then it's guaranteed to miss a lot of data under load).
>
> I figured it could just wake up every few seconds and check.  It will
> remember the loop counter and current pointer, and read any new
> information.  I was thinking of a 20k buffer, which could cover about 4k
> events.

    Here  I  wonder what your EVENT is. With an Oid as identifier
    and a 1 byte (even if it'd be anoter 32-bit value), how  many
    messages do you want to generate to get these statistics:

    -   Number of sequential scans done per table.
    -   Number of tuples returned via sequential scans per table.
    -   Number of buffer cache lookups  done  through  sequential
        scans per table.
    -   Number  of  buffer  cache  hits  for sequential scans per
        table.
    -   Number of tuples inserted per table.
    -   Number of tuples updated per table.
    -   Number of tuples deleted per table.
    -   Number of index scans done per index.
    -   Number of index tuples returned per index.
    -   Number of buffer cache lookups  done  due  to  scans  per
        index.
    -   Number of buffer cache hits per index.
    -   Number  of  valid heap tuples returned via index scan per
        index.
    -   Number of buffer cache lookups done for heap fetches  via
        index scan per index.
    -   Number  of  buffer  cache hits for heap fetches via index
        scan per index.
    -   Number of buffer cache lookups not accountable for any of
        the above.
    -   Number  of  buffer  cache hits not accountable for any of
        the above.

    What I see is that there's a difference in what we  two  want
    to see in the statistics. You're talking about looking at the
    actual querystring and such. That's  information  useful  for
    someone   actually  looking  at  a  server,  to  see  what  a
    particular backend  is  doing.  On  my  notebook  a  parallel
    regression  test  (containing >4,000 queries) passes by under
    1:30, that's more than 40 queries per second. So that doesn't
    tell me much.

    What I'm after is to collect the above data over a week or so
    and then generate a report to identify the hot spots  of  the
    schema.  Which tables/indices cause the most disk I/O, what's
    the average percentage of tuples returned in scans (not  from
    the  query, I mean from the single scan inside of the joins).
    That's the information I need  to  know  where  to  look  for
    possibly  better  qualifications, useless indices that aren't
    worth to maintain and the like.


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


Mon, 19 Mar 2001 13:04:37 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler
> > I figured it could just wake up every few seconds and check.  It will
> > remember the loop counter and current pointer, and read any new
> > information.  I was thinking of a 20k buffer, which could cover about 4k
> > events.
> 
>     Here  I  wonder what your EVENT is. With an Oid as identifier
>     and a 1 byte (even if it'd be anoter 32-bit value), how  many
>     messages do you want to generate to get these statistics:
> 
>     -   Number of sequential scans done per table.
>     -   Number of tuples returned via sequential scans per table.
>     -   Number of buffer cache lookups  done  through  sequential
>         scans per table.
>     -   Number  of  buffer  cache  hits  for sequential scans per
>         table.
>     -   Number of tuples inserted per table.
>     -   Number of tuples updated per table.
>     -   Number of tuples deleted per table.
>     -   Number of index scans done per index.
>     -   Number of index tuples returned per index.
>     -   Number of buffer cache lookups  done  due  to  scans  per
>         index.
>     -   Number of buffer cache hits per index.
>     -   Number  of  valid heap tuples returned via index scan per
>         index.
>     -   Number of buffer cache lookups done for heap fetches  via
>         index scan per index.
>     -   Number  of  buffer  cache hits for heap fetches via index
>         scan per index.
>     -   Number of buffer cache lookups not accountable for any of
>         the above.
>     -   Number  of  buffer  cache hits not accountable for any of
>         the above.
> 
>     What I see is that there's a difference in what we  two  want
>     to see in the statistics. You're talking about looking at the
>     actual querystring and such. That's  information  useful  for
>     someone   actually  looking  at  a  server,  to  see  what  a
>     particular backend  is  doing.  On  my  notebook  a  parallel
>     regression  test  (containing >4,000 queries) passes by under
>     1:30, that's more than 40 queries per second. So that doesn't
>     tell me much.
> 
>     What I'm after is to collect the above data over a week or so
>     and then generate a report to identify the hot spots  of  the
>     schema.  Which tables/indices cause the most disk I/O, what's
>     the average percentage of tuples returned in scans (not  from
>     the  query, I mean from the single scan inside of the joins).
>     That's the information I need  to  know  where  to  look  for
>     possibly  better  qualifications, useless indices that aren't
>     worth to maintain and the like.
> 

I was going to have the per-table stats insert a stat record every time
it does a sequential scan, so it sould be [oid][sequential_scan_value]
and allow the collector to gather that and aggregate it.

I didn't think we wanted each backend to do the aggregation per oid. 
Seems expensive. Maybe we would need a count for things like "number of
rows returned" so it would be [oid][stat_type][value].

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 19 Mar 2001 13:10:16 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor signal handler
I have talked to Jan over the phone, and he has convinced me that UDP is
the proper way to communicate stats to the collector, rather than my
shared memory idea.

The advantages of his UDP approach is that the collector can sleep on
the UDP socket rather than having the collector poll the shared memory
area.  It also has the auto-discard option.  He will make logging
configurable on a per-database level, so it can be turned off when not
in use.

He has a trial UDP implementation that he will post soon.  Also, I asked
him to try DGRAM Unix-domain sockets for performance reasons.  My
Steven's book says it they should be supported.  He can put the socket
file in /data.



> > > I figured it could just wake up every few seconds and check.  It will
> > > remember the loop counter and current pointer, and read any new
> > > information.  I was thinking of a 20k buffer, which could cover about 4k
> > > events.
> > 
> >     Here  I  wonder what your EVENT is. With an Oid as identifier
> >     and a 1 byte (even if it'd be anoter 32-bit value), how  many
> >     messages do you want to generate to get these statistics:
> > 
> >     -   Number of sequential scans done per table.
> >     -   Number of tuples returned via sequential scans per table.
> >     -   Number of buffer cache lookups  done  through  sequential
> >         scans per table.
> >     -   Number  of  buffer  cache  hits  for sequential scans per
> >         table.
> >     -   Number of tuples inserted per table.
> >     -   Number of tuples updated per table.
> >     -   Number of tuples deleted per table.
> >     -   Number of index scans done per index.
> >     -   Number of index tuples returned per index.
> >     -   Number of buffer cache lookups  done  due  to  scans  per
> >         index.
> >     -   Number of buffer cache hits per index.
> >     -   Number  of  valid heap tuples returned via index scan per
> >         index.
> >     -   Number of buffer cache lookups done for heap fetches  via
> >         index scan per index.
> >     -   Number  of  buffer  cache hits for heap fetches via index
> >         scan per index.
> >     -   Number of buffer cache lookups not accountable for any of
> >         the above.
> >     -   Number  of  buffer  cache hits not accountable for any of
> >         the above.
> > 
> >     What I see is that there's a difference in what we  two  want
> >     to see in the statistics. You're talking about looking at the
> >     actual querystring and such. That's  information  useful  for
> >     someone   actually  looking  at  a  server,  to  see  what  a
> >     particular backend  is  doing.  On  my  notebook  a  parallel
> >     regression  test  (containing >4,000 queries) passes by under
> >     1:30, that's more than 40 queries per second. So that doesn't
> >     tell me much.
> > 
> >     What I'm after is to collect the above data over a week or so
> >     and then generate a report to identify the hot spots  of  the
> >     schema.  Which tables/indices cause the most disk I/O, what's
> >     the average percentage of tuples returned in scans (not  from
> >     the  query, I mean from the single scan inside of the joins).
> >     That's the information I need  to  know  where  to  look  for
> >     possibly  better  qualifications, useless indices that aren't
> >     worth to maintain and the like.
> > 
> 
> I was going to have the per-table stats insert a stat record every time
> it does a sequential scan, so it sould be [oid][sequential_scan_value]
> and allow the collector to gather that and aggregate it.
> 
> I didn't think we wanted each backend to do the aggregation per oid. 
> Seems expensive. Maybe we would need a count for things like "number of
> rows returned" so it would be [oid][stat_type][value].
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 20 Mar 2001 16:13:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Performance monitor signal handler
Bruce Momjian wrote:
> I have talked to Jan over the phone, and he has convinced me that UDP is
> the proper way to communicate stats to the collector, rather than my
> shared memory idea.
>
> The advantages of his UDP approach is that the collector can sleep on
> the UDP socket rather than having the collector poll the shared memory
> area.  It also has the auto-discard option.  He will make logging
> configurable on a per-database level, so it can be turned off when not
> in use.
>
> He has a trial UDP implementation that he will post soon.  Also, I asked
> him to try DGRAM Unix-domain sockets for performance reasons.  My
> Steven's book says it they should be supported.  He can put the socket
> file in /data.

"Trial" implementation attached :-)

    First  attachment  is  a patch for various backend files plus
    generating two new source files. If your patch(1) doesn't put
    'em   automatically,  they  go  to  src/include/pgstat.h  and
    src/backend/postmaster/pgstat.c.

    BTW:  tgl  on  2/99  was  right,  the  hash_destroy()  really
    crashes.  Maybe  we  want  to  pull  out  the  fix  I've done
    (includes some new feature for hash table memory  allocation)
    and apply that to 7.1?

    Second   attachment  is  a  tarfile  that  should  unpack  to
    contrib/pgstat_tmp.  I've placed the SQL level functions into
    a shared module for now. The sql script also creates a couple
    of views.

    -   pgstat_all_tables shows scan- and tuple based  statistics
        for all tables.  pgstat_sys_tables and pgstat_user_tables
        filter out (you guess what) system or user tables.

    -   pgstatio_all_tables,       pgstatio_sys_tables        and
        pgstatio_user_tables   show   buffer  IO  statistics  for
        tables.

    -   pgstat_*_indexes and pgstatio_*_indexes are similar  like
        the  above,  just that they give detailed info about each
        single index.

    -   pgstatio_*_sequences shows buffer IO statistics  about  -
        right,   sequences.    Since   sequences  aren't  scanned
        regularely, they have no scan- and tuple related view.

    -   pgstat_activity shows informations  about  all  currently
        running  backends  of the entire instance. The underlying
        function for displaying the  actual  query  returns  NULL
        allways for non-superusers.

    -   pgstat_database shows transaction commit/abort counts and
        cumulated  buffer  IO   statistics   for   all   existing
        databases.

    The  collector  writes  frequently  a  file  data/pgstat.stat
    (approx. every 500 milliseconds as long as there is something
    to  tell,  so  nothing  is  done  if  the entire installation
    sleeps). He also reads this file  on  startup,  so  collected
    statistics survive postmaster restarts.

    TODO:

    -   Are  PF_UNIX  SOCK_DGRAM  sockets  supported  on  all the
        platforms we do?  If not, what's wrong with  the  current
        implementation?

    -   There  is  no way yet to tell the collector about objects
        (relations and  databases)  removed  from  the  database.
        Basically  that  could be done with messages too, but who
        will send them and how can we guarantee that  they'll  be
        generated  even if somebody never queries the statistics?
        Thus, the current collector will grow, and grow, and grow
        until   you   remove   the  pgstat.stat  file  while  the
        postmaster is down.

    -   Also there aren't functions or  messages  implemented  to
        explicitly reset statistics.

    -   Possible additions would be to remember when the backends
        started and collect resource usage (rstat(2)) information
        as well.

    -   The   entire  thing  needs  an  additional  attribute  in
        pg_database that tells the  backends  what  to  tell  the
        collector at all. Just to make them quiet again.

    So far for an actual snapshot. Comments?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #
Thu, 22 Mar 2001 09:22:54 -0500 (EST)
Jan Wieck <JanWieck@Yahoo.com>
Re: Performance monitor signal handler


> -----Original Message-----
> From: Vince Vielhaber [mailto:vev@michvhf.com] 
> Sent: 30 January 2003 19:20
> To: Lamar Owen
> Cc: Tom Lane; Dave Page; Ron Mayer; pgsql-hackers@postgresql.org
> Subject: Re: [mail] Re: [HACKERS] Windows Build System
> 
> 
>  I've 
> been on both sides know that the windows user/developer 
> doesn't hold things to the same standards as the unix user/developer.

I ought to plonk you for a comment like that. Especially coming from the
person who's crap I've been trying to sort out for the last couple of
months.

> Since you're pretty much ignoring my reasoning, I'll give you 
> the same consideration.  The history of windows as a platform 
> has shown itself to be rather fragile compared to unix.

When properly configured, Windows can be reliable, maybe not as much as
Solaris or HPUX but certainly some releases of Linux (which I use as
well). You don't see Oracle or IBM avoiding Windows 'cos it isn't stable
enough.

> Before you respond to this, read Tom Lane's response and 
> reply to that.

*I* did. I volunteered to do some more of the testing we're all so
resistant.

Dave.

Thu, 30 Jan 2003 19:56:30 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: [mail] Re: Windows Build System
On Thu, 30 Jan 2003, Dave Page wrote:

>
>
> > -----Original Message-----
> > From: Vince Vielhaber [mailto:vev@michvhf.com]
> > Sent: 30 January 2003 19:20
> > To: Lamar Owen
> > Cc: Tom Lane; Dave Page; Ron Mayer; pgsql-hackers@postgresql.org
> > Subject: Re: [mail] Re: [HACKERS] Windows Build System
> >
> >
> >  I've
> > been on both sides know that the windows user/developer
> > doesn't hold things to the same standards as the unix user/developer.
>
> I ought to plonk you for a comment like that. Especially coming from the
> person who's crap I've been trying to sort out for the last couple of
> months.

Grow up Dave.  That shit doesn't belong on this or any other list.  If
you didn't want to do something, you shouldn't have volunteered to do it.

Vince.
-- 
 Fast, inexpensive internet service 56k and beyond!  http://www.pop4.net/
   http://www.meanstreamradio.com       http://www.unknown-artists.com
         Internet radio: It's not file sharing, it's just radio.


Thu, 30 Jan 2003 15:05:37 -0500 (EST)
Vince Vielhaber <vev@michvhf.com>
Re: [mail] Re: Windows Build System
On Thu, 2003-01-30 at 13:56, Dave Page wrote:
> When properly configured, Windows can be reliable, maybe not as much as
> Solaris or HPUX but certainly some releases of Linux (which I use as
> well). You don't see Oracle or IBM avoiding Windows 'cos it isn't stable
> enough.

I'm not jumping on one side or the other but I wanted to make clear on
something.  The fact that IBM or Oracle use windows has absolutely zero
to do with reliability or stability.  They are there because the market
is willing to spend money on their product.  Let's face it, the share
holders of each respective company would come unglued if the largest
software audience in the world were completely ignored.

Simple fact is, your example really is pretty far off from supporting
any view.  Bluntly stated, both are in that market because they want to
make money; they're even obligated to do so.


-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting


30 Jan 2003 16:37:22 -0600
Greg Copeland <greg@CopelandConsulting.Net>
Re: [mail] Re: Windows Build System
Greg Copeland wrote:
> On Thu, 2003-01-30 at 13:56, Dave Page wrote:
> > When properly configured, Windows can be reliable, maybe not as much as
> > Solaris or HPUX but certainly some releases of Linux (which I use as
> > well). You don't see Oracle or IBM avoiding Windows 'cos it isn't stable
> > enough.
> 
> I'm not jumping on one side or the other but I wanted to make clear on
> something.  The fact that IBM or Oracle use windows has absolutely zero
> to do with reliability or stability.  They are there because the market
> is willing to spend money on their product.  Let's face it, the share
> holders of each respective company would come unglued if the largest
> software audience in the world were completely ignored.
> 
> Simple fact is, your example really is pretty far off from supporting
> any view.  Bluntly stated, both are in that market because they want to
> make money; they're even obligated to do so.

That's true, but it ignores the question that makes it relevant: has
their appearance in the Windows market tarnished their reputation?
More precisely, has it tarnished their reputation in the *Unix*
community?  The answer, I think, is no.

And that *is* relevant to us, because our concern is about the
reputation of PostgreSQL, and what will happen to it if we release a
native Windows port to the world.


Of course, you could argue that Oracle and IBM didn't have much of a
reputation anyway, and I wouldn't be able to say much to that.  :-)


-- 
Kevin Brown					      kevin@sysexperts.com

Thu, 30 Jan 2003 14:54:19 -0800
Kevin Brown <kevin@sysexperts.com>
Re: [mail] Re: Windows Build System
Kevin Brown wrote:
> 
> Greg Copeland wrote:
> > On Thu, 2003-01-30 at 13:56, Dave Page wrote:
> > > When properly configured, Windows can be reliable, maybe not as much as
> > > Solaris or HPUX but certainly some releases of Linux (which I use as
> > > well). You don't see Oracle or IBM avoiding Windows 'cos it isn't stable
> > > enough.
> >
> > I'm not jumping on one side or the other but I wanted to make clear on
> > something.  The fact that IBM or Oracle use windows has absolutely zero
> > to do with reliability or stability.  They are there because the market
> > is willing to spend money on their product.  Let's face it, the share
> > holders of each respective company would come unglued if the largest
> > software audience in the world were completely ignored.
> >
> > Simple fact is, your example really is pretty far off from supporting
> > any view.  Bluntly stated, both are in that market because they want to
> > make money; they're even obligated to do so.
> 
> That's true, but it ignores the question that makes it relevant: has
> their appearance in the Windows market tarnished their reputation?
> More precisely, has it tarnished their reputation in the *Unix*
> community?  The answer, I think, is no.
> 
> And that *is* relevant to us, because our concern is about the
> reputation of PostgreSQL, and what will happen to it if we release a
> native Windows port to the world.

More to the point, does the unreliable Cygwin port possibly do our
reputation any good? It is known to crash with corruptions under less
than heavy load. 

Looking at the arguments so far, nearly everyone who questions the Win32
port must be vehemently against the Cygwin stuff anyway. So that camp
should be happy to see it flushed down the toilet. And the pro-Win32
people want the native version because they are unhappy with the
stepchild-Cygwin stuff too, so they won't care too much.

Anyone here who likes the Cygwin port or can we yank it out right now?


Jan

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

Fri, 31 Jan 2003 00:29:39 -0500
Jan Wieck <JanWieck@Yahoo.com>
Re: [mail] Re: Windows Build System
Jan Wieck wrote:
> Looking at the arguments so far, nearly everyone who questions the Win32
> port must be vehemently against the Cygwin stuff anyway. So that camp
> should be happy to see it flushed down the toilet. And the pro-Win32
> people want the native version because they are unhappy with the
> stepchild-Cygwin stuff too, so they won't care too much.

What is interesting is that the MySQL folk don't seem to be vehemently against 
it, as a look at their downloads pages indicate that they depend on Cygwin for 
the Windows port of their product.
--
output = ("cbbrowne" "@ntlug.org")
http://www.ntlug.org/~cbbrowne/lisp.html
"What did we agree about a leader??"
"We agreed we wouldn't have one."
"Good.  Now shut up and do as I say..."



Fri, 31 Jan 2003 08:14:27 -0500
cbbrowne@cbbrowne.com
Re: [mail] Re: Windows Build System 
The U.S. Census provides a database of street polygons and other data 
about landmarks, elevation, etc. This was discussed in a separate thread.

The main URL is here:
http://www.census.gov/geo/www/tiger/index.html

My loader was written for the 2000 version, the 2002 version has some 
difference, but it should be easy enough to ad the fields.

On my site, in the downloads section, at the bottom is the tigerua 
loader. It is very raw, just hacked together to load the data. It may 
take a little work to function with 2002 files, I have not looked at 
that yet.

My site:
http://www.mohawksoft.com


Tue, 08 Apr 2003 08:52:22 -0400
mlw <pgsql@mohawksoft.com>
Complex database for testing, U.S. Census Tiger/UA
mlw wrote:
> 
> The U.S. Census provides a database of street polygons and other data
> about landmarks, elevation, etc. This was discussed in a separate thread.
> 
> The main URL is here:
> http://www.census.gov/geo/www/tiger/index.html

While yes, the tiger database (or better it's content) is interesting, I
don't think that it can be counted as a "complex database". Just that
something is big doesn't mean that.

> 
> My loader was written for the 2000 version, the 2002 version has some
> difference, but it should be easy enough to ad the fields.

OT:

Just out of curiosity, do you plan more on this? I was playing around
with the 2000 version a while back, but the Garmin GPS units
unfortunately use a proprietary map format, so one cannot generate his
own detail maps for download. The waypoint and route data protocol is
well known though.


Jan

-- 

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


Tue, 08 Apr 2003 10:32:52 -0400
Jan Wieck <JanWieck@Yahoo.com>
Re: Complex database for testing, U.S. Census Tiger/UA
> mlw wrote:
>> 
>> The U.S. Census provides a database of street polygons and other data
>> about landmarks, elevation, etc. This was discussed in a separate
>> thread.
>> 
>> The main URL is here:
>> http://www.census.gov/geo/www/tiger/index.html
> 
> While yes, the tiger database (or better it's content) is interesting,
> I don't think that it can be counted as a "complex database". Just that
> something is big doesn't mean that.

I guess you are right, but there are a lot of related tables. I wouldn't
call it simple, though. It can get huge, however.
> 
>> 
>> My loader was written for the 2000 version, the 2002 version has some
>> difference, but it should be easy enough to ad the fields.
> 
> OT:
> 
> Just out of curiosity, do you plan more on this? I was playing around
> with the 2000 version a while back, but the Garmin GPS units
> unfortunately use a proprietary map format, so one cannot generate his
> own detail maps for download. The waypoint and route data protocol is
> well known though.

I'm not sure what a Garmin GPS unit is, but the TigerUA DB uses longitude
and latitude. Any reasonable geographical system must somehow map to lat/long.

Actually, I am going to download the latest version and get it installed on
a system. There is a project I plan to work on in the near future, after all
the other crap I gotta do, that will make use of the data.


Tue, 8 Apr 2003 11:12:11 -0400 (EDT)
pgsql@mohawksoft.com
Re: Complex database for testing, U.S. Census Tiger/UA
Jan Wieck wrote:
> mlw wrote:
> > 
> > The U.S. Census provides a database of street polygons and other data
> > about landmarks, elevation, etc. This was discussed in a separate thread.
> > 
> > The main URL is here:
> > http://www.census.gov/geo/www/tiger/index.html
> 
> While yes, the tiger database (or better it's content) is interesting, I
> don't think that it can be counted as a "complex database". Just that
> something is big doesn't mean that.

Just so.

There are doubtless interesting cases that may be tested by virtue of
having a data set that is large, and perhaps "deeply interlinked."

But that only covers cases that have to do with "largeness."  It doesn't
help ensure that PostgreSQL plays well when it gets hit by nested sets
of updates where the challenges involve ensuring the system performs OK
and does not deadlock when hit by complex sets of transactions.

So that an "interesting" database might involve not only a database, but
also a set of transactions that hit multiple tables that are to update
that database.  In effect, something like the "readers/writers" that get
used to test locking semantics.

This is something that would not be able to solely consist of a set of
tables; it would have to include streams of updates.  Something like one
of the TPC benchmarks...
--
output = reverse("moc.enworbbc@" "enworbbc")
http://www3.sympatico.ca/cbbrowne/rdbms.html
"If I  could find  a way to  get [Saddam  Hussein] out of  there, even
putting a  contract out on him,  if the CIA  still did that sort  of a
thing, assuming it ever did, I would be for it."  -- Richard M. Nixon


Tue, 08 Apr 2003 11:24:06 -0400
cbbrowne@cbbrowne.com
Re: Complex database for testing, U.S. Census Tiger/UA 
Around 11:24 on Apr 8, 2003, cbbrowne@cbbrowne.com said:

	I think it was my first application I wrote in python which parsed
the zip files containing these data and shoved it into a postgres system.
I had multiple clients on four or five computers running nonstop for about
two weeks to get it all populated.

	By the time I was done, and got my first index created, I began to
run out of disk space.  I think I only had about 70GB to work with on the
RAID array.

# Jan Wieck wrote:
# > mlw wrote:
# > >
# > > The U.S. Census provides a database of street polygons and other data
# > > about landmarks, elevation, etc. This was discussed in a separate thread.
# > >
# > > The main URL is here:
# > > http://www.census.gov/geo/www/tiger/index.html
# >
# > While yes, the tiger database (or better it's content) is interesting, I
# > don't think that it can be counted as a "complex database". Just that
# > something is big doesn't mean that.
#
# Just so.
#
# There are doubtless interesting cases that may be tested by virtue of
# having a data set that is large, and perhaps "deeply interlinked."
#
# But that only covers cases that have to do with "largeness."  It doesn't
# help ensure that PostgreSQL plays well when it gets hit by nested sets
# of updates where the challenges involve ensuring the system performs OK
# and does not deadlock when hit by complex sets of transactions.
#
# So that an "interesting" database might involve not only a database, but
# also a set of transactions that hit multiple tables that are to update
# that database.  In effect, something like the "readers/writers" that get
# used to test locking semantics.
#
# This is something that would not be able to solely consist of a set of
# tables; it would have to include streams of updates.  Something like one
# of the TPC benchmarks...
# --
# output = reverse("moc.enworbbc@" "enworbbc")
# http://www3.sympatico.ca/cbbrowne/rdbms.html
# "If I  could find  a way to  get [Saddam  Hussein] out of  there, even
# putting a  contract out on him,  if the CIA  still did that sort  of a
# thing, assuming it ever did, I would be for it."  -- Richard M. Nixon
#
#
# ---------------------------(end of broadcast)---------------------------
# TIP 3: if posting/reading through Usenet, please send an appropriate
# subscribe-nomail command to majordomo@postgresql.org so that your
# message can get through to the mailing list cleanly
#
#

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________


Tue, 8 Apr 2003 09:35:10 -0700
Dustin Sallings <dustin@spy.net>
Re: Complex database for testing, U.S. Census Tiger/UA 
Dustin Sallings wrote:
> 	I think it was my first application I wrote in python which parsed
> the zip files containing these data and shoved it into a postgres system.
> I had multiple clients on four or five computers running nonstop for about
> two weeks to get it all populated.
> 
> 	By the time I was done, and got my first index created, I began to
> run out of disk space.  I think I only had about 70GB to work with on the
> RAID array.

But this does not establish that this data represents a meaningful
"transactional" load.

Based on the sources, which presumably involve unique data, the
"transactions" are all touching independent sets of data, and are likely
to be totally uninteresting from the perspective of seeing how the
system works under /TRANSACTION/ load.

TRANSACTION loading will involve doing updates that actually have some
opportunity to trample on one another.  Multiple transactions
concurrently updating a single balance table.  Multiple transactions
concurrently trying to attach links to a table entry.  That sort of
thing.

I remember a while back when MSFT did a "enterprise scalability day,"
where they were trumpeting SQL Server performance on "hundreds of
millions of transactions."  At the time, I was at Sabre, who actually do
tens of millions of transactions per day, for passenger reservations
across lotso airlines.  Microsoft was making loud noises to the effect
that NT Server was wonderful for "enterprise transaction" work; the guys
at work just laughed, because the kind of performance they got involved
considerable amounts of 370 assembler to tune vital bits of the
systems.

What happened in the "scalability tests" was that Microsoft did much the
same thing you did; they had hordes of transactions going through that
were well, basically independent of one another.  They could "scale"
things up trivially by adding extra boxes.  Need to handle 10x the
transactions?  Well, since they don't actually modify any shared
resources, you just need to put in 10x as many servers.

And that's essentially what happens any time TPC-? benchmarks reach the
point of irrelevance; that happens every time someone figures out some
"hack" that is able to successfully partition the work load.  At that
point, they merely need to add a bit of extra hardware, and increasing
performance is as easy as adding extra processor boards.  The real world
doesn't scale so easily...
--
(concatenate 'string "cbbrowne" "@acm.org")
http://cbbrowne.com/info/emacs.html
Send  messages calling for fonts  not  available to the  recipient(s).
This can (in the case of Zmail) totally disable the user's machine and
mail system for up to a whole day in some circumstances.
-- from the Symbolics Guidelines for Sending Mail


Tue, 08 Apr 2003 14:58:42 -0400
cbbrowne@cbbrowne.com
Re: Complex database for testing, U.S. Census Tiger/UA 