From kyle@actarg.com Wed Dec 13 15:28:35 2000 Received: from actarg.com (host25.actarg.com [209.180.91.25] (may be forged)) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBDFSZp12094 for ; Wed, 13 Dec 2000 10:28:35 -0500 (EST) (envelope-from kyle@actarg.com) Received: from chi.actarg.com (IDENT:root@chi.actarg.com [192.168.2.16]) by actarg.com (8.9.3/8.9.3) with ESMTP id IAA20086; Wed, 13 Dec 2000 08:29:01 -0700 Received: from actarg.com (IDENT:kyle@localhost [127.0.0.1]) by chi.actarg.com (8.9.3/8.9.3) with ESMTP id IAA21016; Wed, 13 Dec 2000 08:29:00 -0700 Message-ID: <3A3795BC.44C91514@actarg.com> Date: Wed, 13 Dec 2000 08:29:00 -0700 From: Kyle Organization: Action Target Inc. X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org CC: JanWieck@Yahoo.com Subject: Strange slow behavior in backend Content-Type: multipart/mixed; boundary="------------2AF57863506E9D5EAB861653" X-Archive-Number: 200012/106 X-Sequence-Number: 440 This is a multi-part message in MIME format. --------------2AF57863506E9D5EAB861653 Content-Type: multipart/alternative; boundary="------------68D3014F1402D0E7CCDB4EC0" --------------68D3014F1402D0E7CCDB4EC0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I'm using 7.0.1 with a TCL frontend. I have a schema that splits large files into tuple-sized bites and stores them in a table. This was done before TOAST in order to store large files. I have a backend TCL function that re-assembles the file like this: -- Fetch the specified document data, reassembling the bits back together -- in the right order. -- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype) create function cont_doc_fetch(int4,timestamp,varchar) returns text as ' set odata {} spi_exec -array d "select data from cont_doc_data where crt_by = \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\' order by seq" { append odata $d(data) } return $odata ' LANGUAGE 'pltcl'; 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): set data {} set tuple_list [mpg::qlist "select data from $ca(prefix)_doc_data where crt_by = $crt_by and crt_date = '$crt_date' and ctype = '$ctype' order by seq"] foreach rec $tuple_list { append data [lindex $rec 0] } 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? Jan: Is this the difference between old TCL and new TCL (with multi-port objects)? Or is there something else about the way the backend handles large chunks of data that would mark the difference? --------------68D3014F1402D0E7CCDB4EC0 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I'm using 7.0.1 with a TCL frontend.

I have a schema that splits large files into tuple-sized bites and stores them in a table.  This was done before TOAST in order to store large files.

I have a backend TCL function that re-assembles the file like this:

-- Fetch the specified document data, reassembling the bits back together
-- in the right order.
-- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype)
create function cont_doc_fetch(int4,timestamp,varchar) returns text as '
    set odata {}
    spi_exec -array d "select data from cont_doc_data where crt_by = \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\' order by seq" {
        append odata $d(data)
    }
    return $odata
    ' LANGUAGE 'pltcl';

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):

    set data {}
    set tuple_list [mpg::qlist "select data from $ca(prefix)_doc_data where crt_by = $crt_by and crt_date = '$crt_date' and ctype = '$ctype' order by seq"]
    foreach rec $tuple_list {
        append data [lindex $rec 0]
    }

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?

Jan:
Is this the difference between old TCL and new TCL (with multi-port objects)?  Or is there something else about the way the backend handles large chunks of data that would mark the difference?

--------------68D3014F1402D0E7CCDB4EC0-- --------------2AF57863506E9D5EAB861653 Content-Type: text/x-vcard; charset=us-ascii; name="kyle.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Kyle Content-Disposition: attachment; filename="kyle.vcf" begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;;;;;; version:2.1 email;internet:kyle@actarg.com title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard --------------2AF57863506E9D5EAB861653-- From tgl@sss.pgh.pa.us Thu Dec 14 02:28:36 2000 Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBE2SaX77185 for ; Wed, 13 Dec 2000 21:28:36 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id eBE2SZQ18795; Wed, 13 Dec 2000 21:28:35 -0500 (EST) To: Kyle cc: pgsql-sql@postgresql.org, JanWieck@Yahoo.com Subject: Re: Strange slow behavior in backend In-reply-to: <3A3795BC.44C91514@actarg.com> References: <3A3795BC.44C91514@actarg.com> Comments: In-reply-to Kyle message dated "Wed, 13 Dec 2000 08:29:00 -0700" Date: Wed, 13 Dec 2000 21:28:35 -0500 Message-ID: <18792.976760915@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200012/135 X-Sequence-Number: 469 Kyle 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. regards, tom lane From janwieck@yahoo.com Thu Dec 14 15:14:39 2000 Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBEFEd004440 for ; Thu, 14 Dec 2000 10:14:39 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from jupiter.jw.home (IDENT:root@A58bc.pppool.de [213.6.88.188]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id KAA93121 for ; Thu, 14 Dec 2000 10:38:15 GMT (envelope-from janwieck@Yahoo.com) Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id IAA04143; Thu, 14 Dec 2000 08:08:49 -0500 From: Jan Wieck Message-Id: <200012141308.IAA04143@jupiter.jw.home> Subject: Re: Strange slow behavior in backend In-Reply-To: <3A3795BC.44C91514@actarg.com> from Kyle at "Dec 13, 2000 08:29:00 am" To: Kyle Date: Thu, 14 Dec 2000 08:08:48 -0500 (EST) CC: pgsql-sql@postgresql.org, JanWieck@yahoo.com X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/145 X-Sequence-Number: 479 Kyle wrote: > I'm using 7.0.1 with a TCL frontend. > > I have a schema that splits large files into tuple-sized bites and > stores them in a table. This was done before TOAST in order to store > large files. > > I have a backend TCL function that re-assembles the file like this: > > -- Fetch the specified document data, reassembling the bits back > together > -- in the right order. > -- Calling sequence: cont_doc_fetch(crt_by,crt_date,ctype) > create function cont_doc_fetch(int4,timestamp,varchar) returns text as ' > > set odata {} > spi_exec -array d "select data from cont_doc_data where crt_by = > \'$1\' and crt_date = \'$2\' and ctype = \'[quote $3]\' order by seq" { > append odata $d(data) > } > return $odata > ' LANGUAGE 'pltcl'; > > 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): > > set data {} > set tuple_list [mpg::qlist "select data from $ca(prefix)_doc_data > where crt_by = $crt_by and crt_date = '$crt_date' and ctype = '$ctype' > order by seq"] > foreach rec $tuple_list { > append data [lindex $rec 0] > } > > 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? > > Jan: > Is this the difference between old TCL and new TCL (with multi-port > objects)? Or is there something else about the way the backend handles > large chunks of data that would mark the difference? That's it. It shouldn't have to do with the amount of data invoked, but with the number of tuples returned by spi_exec and spi_execp commands. Due to backwards compatibility, all commands in the PL/Tcl handler still use the old string interface. Thus, the procedure text for each tuple (in your case "append odata $d(data)") is evaluated from it's string representation again and again, needs to be interpreted and precompiled for each single tuple by the Tcl interpreter. I think using Tcl_Obj's here would cause a substantial improvement. I plan to do a major overhaul of PL/Tcl after we have an interface for functions returning tuple sets. This will include loosing the backward compatibility to pre-8.0 releases of Tcl because of using the Tcl_Obj interface only. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # From janwieck@yahoo.com Thu Dec 14 15:14:37 2000 Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBEFEb004422 for ; Thu, 14 Dec 2000 10:14:37 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from jupiter.jw.home (IDENT:root@A58bc.pppool.de [213.6.88.188]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id KAA93095 for ; Thu, 14 Dec 2000 10:36:42 GMT (envelope-from janwieck@Yahoo.com) Received: (from janwieck@localhost) by jupiter.jw.home (8.9.3/8.9.3) id IAA04158; Thu, 14 Dec 2000 08:12:45 -0500 From: Jan Wieck Message-Id: <200012141312.IAA04158@jupiter.jw.home> Subject: Re: Strange slow behavior in backend In-Reply-To: <18792.976760915@sss.pgh.pa.us> from Tom Lane at "Dec 13, 2000 09:28:35 pm" To: Tom Lane Date: Thu, 14 Dec 2000 08:12:45 -0500 (EST) CC: Kyle , pgsql-sql@postgresql.org, JanWieck@yahoo.com X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/144 X-Sequence-Number: 478 Tom Lane wrote: > Kyle 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 #