different results inside transactions - Mailing list pgsql-general

Can somebody clue me in on this?  I've been working on this for nearly a
week now and it's driving me bunkers.

I am using PostGIS/Mapserver and trying to get a simple demo running
with just one PostGIS layer.  My layer is never drawn and the best I can
tell is the results from this transaction (not the exact one this is the
simplest form I've narrowed it down to):

begin transaction;
    declare mycursor binary cursor for select asbinary(centerline) from road_segments;
    fetch all from mycursor;
end transaction;

if I just do select asbinary(centerline) from road_segments, I get the
WKB representation of the geometry lines.

if I run the transaction, psql just gives me rows of blank lines.  If I
use PgAdmin, I get

Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 5 rows discarded.

Query returned successfully with no result in 331 ms.

Is this possibly the problem?  Anybody using PostGIS and Mapserver?

Thanks!

Tim

On Tue, Sep 14, 2004 at 11:11:38AM -0700, Jeffrey W. Baker wrote:
> On Tue, 2004-09-14 at 10:28, Vivek Khera wrote:
> > >>>>> "SW" == Shane Wright <Shane> writes:
> >
> > SW> But, we have now taken the plunge and I'm in a position to do some
> > SW> benchmarking to actually get some data.  Basically I was wondering if
> > SW> anyone else had any particular recommendations (or requests) about the
> > SW> most useful kinds of benchmarks to do.
> >
> > I did a bunch of benchmarking on a 14 disk SCSI RAID array comparing
> > RAID 5, 10, and 50.  My tests consisted of doing a full restore of a
> > 30Gb database (including indexes) and comparing the times to do the
> > restore, the time to make the indexes, and the time to vacuum.  Then I
> > ran a bunch of queries.
> >
> > It was damn near impossible to pick a 'better' RAID config, so I just
> > went with RAID5.
> >
> > You can find many of my posts on this topic on the list archives from
> > about august - october of last year.
> >
> > Basically, you have to approach it holistically to tune the system: Pg
> > config parameters, memory, and disk speed are the major factors.
> >
> > That and your schema needs to be not idiotic. :-)
>
> I've recently bee frustrated by this topic, because it seems like you
> can design the hell out of a system, getting everything tuned with micro
> and macro benchmarks, but when you put it in production the thing falls
> apart.
>
> Current issue:
>
> A dual 64-bit Opteron 244 machine with 8GB main memory, two 4-disk RAID5
> arrays (one for database, one for xlogs).  PG's config is extremely
> generous, and in isolated benchmarks it's very fast.
>
> But, in reality, performance is abyssmal.  There's something about what
> PG does inside commits and checkpoints that sends Linux into a catatonic
> state.  For instance here's a snapshot of vmstat during a parallel heavy
> select/insert load:
>
> procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
>  3  0    216  13852  39656 7739724    0    0   820  2664 2868  2557 16  2 74  7
>  0  0    216  17580  39656 7736460    0    0  3024  4700 3458  4313 42  6 52  0
>  0  0    216  16428  39676 7737324    0    0   840  4248 3930  4516  0  4 89  8
>  0  1    216  18620  39672 7736920    0    0  7576   516 2738  3347  1  4 55 39
>  0  0    216  14972  39672 7738960    0    0  1992  2532 2509  2288  2  3 93  3
>  0  0    216  13564  39672 7740592    0    0  1640  2656 2581  2066  1  3 97  0
>  0  0    216  12028  39672 7742292    0    0  1688  3576 2072  1626  1  2 96  0
>  0  0    216  18364  39680 7736164    0    0  1804  3372 1836  1379  1  4 96  0
>  0  0    216  16828  39684 7737588    0    0  1432  2756 2256  1720  1  3 94  2
>  0  0    216  15452  39684 7738812    0    0  1188  2184 2384  1830  1  2 97  0
>  0  1    216  15388  39684 7740104    0    0  1336  2628 2490  1974  2  3 94  2
>  6  0    216  15424  39684 7740240    0    0   104  3472 2757  1940  3  2 92  2
>  0  0    216  14784  39700 7741856    0    0  1668  3320 2718  2332  0  3 97  0
>
> You can see there's not much progress being made there.   In the
> presence of a farily pathetic writeout, there's a tiny trickle of disk
> reads, userspace isn't making any progress, the kernel isn't busy, and
> few processes are in iowait.  So what the heck is going on?
>
> This state of non-progress persists as long as the checkpoint subprocess
> is active.  I'm sure there's some magic way to improve this but I
> haven't found it yet.
>
> PS this is with Linux 2.6.7.
>
> Regards,
> jwb
>
> ---------------------------(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