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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
