different results inside transactions

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly