pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kyle <kyle@actarg.com>
Cc: pgsql-sql@postgresql.org
Cc: JanWieck@yahoo.com
Subject: Re: Strange slow behavior in backend
Date: Thu, 14 Dec 2000 08:12:45 -0500 (EST)
Message-ID: <200012141312.IAA04158@jupiter.jw.home> (raw)
In-Reply-To: <18792.976760915@sss.pgh.pa.us>

Tom Lane wrote:
> Kyle <kyle@actarg.com> writes:
> > This worked great until I put a real big file in (about 5M).  Then, when
> > I tried to fetch the file, it seemed really slow (about 60 seconds).  I
> > tried reassembling the file in the frontend instead and my time dropped
> > to about 6 seconds using this TCL fragment (mpg::qlist is an interface
> > to pg_exec that returns a list of tuples):
> > The only difference I can identify is whether the re-assembly TCL code
> > is running as a procedural language (backend) or in the frontend.
> > Anyone have any idea why the difference is so dramatic?
>
> I happened to have handy a 7.1 backend compiled for profiling, so I
> looked into this a little.  I confirm that this seems unreasonably slow.
> As near as I can tell, 98% of the backend runtime is being spent in
> strlen() and strcpy() invoked from Tcl_SetResult invoked from Tcl_Eval
> invoked from the per-result-tuple loop in pltcl_SPI_exec.  Apparently,
> all this is happening because Tcl_Eval thinks it needs to make the
> result of the append command available for its caller.  I changed the
> inner loop to
>
> spi_exec -array d "select data from pg_largeobject where
> loid = $1 order by pageno" {
> append odata $d(data);
> set z z
> }
>
> and voila, the runtime dropped to something reasonable.
>
> So, yes, it would seem that some care in the inner loop of
> pltcl_SPI_exec would help a lot.  It'd be worth if'defing the Tcl_Eval
> call there to use a new-style call when using Tcl 8.  (This could also
> avoid repetitive parsing of the loop body.)  Might want to think about
> the same for the Tcl function as a whole, too.
>
> I was also distressed to notice that pltcl_set_tuple_values does a LOT
> of repetitive work --- it should be fixed so that the syscache and
> function lookups are done only once, not once per tuple.

    Hmmm - seems worse than I thought.

    Again,  let's  wait  with  all  that until we can overhaul it
    including the tupleset  return  mechanism  and  simply  loose
    pre-8.0 compatibility.


Jan

--

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





view thread (4+ messages)

Message-ID: <200012141312.IAA04158@jupiter.jw.home>
Permalink:  ../200012141312.IAA04158@jupiter.jw.home/
Also on:    postgresql.org/message-id/200012141312.IAA04158@jupiter.jw.home

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: janwieck@yahoo.com, tgl@sss.pgh.pa.us, kyle@actarg.com
  Subject: Re: Strange slow behavior in backend
  In-Reply-To: <200012141312.IAA04158@jupiter.jw.home>
  References: <18792.976760915@sss.pgh.pa.us> <200012141312.IAA04158@jupiter.jw.home>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox