[postgis-users] S...L...o.....w..@^ performance on Solaris/SPARC

Thu Feb 14 14:21:40 PST 2008

Dear PostGIS'ers

I've started to look at performance of PostGreSQL/PostGIS on our
Solaris/SPARC (T2000 server with 8GB RAM and SAS Disks using Solaris
10).
I know these boxes are better at web serving and not so great at
database serving, but this seems a bit ridiculous.
It was pretty bad (~36 secs for common queries) so I decided to bite the
bullet (last night) and upgrade to PostGreSQL 8.3.0, Proj 4.6.0, GEOS
3.0.0 and PostGIS 1.3.2.
All went smashingly (well, at least I thought) ! Until I "tested"
performance again (hoping it was going to improve). Kudos to Paul (I
think) who put together the simple "magic upgrade" path instructions.

Here are the results -
(Before upgrade)
test=# explain analyze select a.filename from herb_ortho_index_poly a,
estate127_bdy b where b.name_caps like 'KOOM%' and ST_Within(a.the_geom,
b.the_geom);
                                                           QUERY PLAN

------------------------------------------------------------------------
---------------------------------------------------------
 Nested Loop  (cost=318.76..1997.86 rows=15440 width=42) (actual
time=24386.877..35513.845 rows=8 loops=1)
   Join Filter: st_within(a.the_geom, b.the_geom)
   ->  Seq Scan on herb_ortho_index_poly a  (cost=0.00..636.90 rows=5790
width=74) (actual time=0.022..21.949 rows=9771 loops=1)
   ->  Materialize  (cost=318.76..318.84 rows=8 width=32) (actual
time=0.001..0.009 rows=3 loops=9771)
         ->  Seq Scan on estate127_bdy b  (cost=0.00..318.75 rows=8
width=32) (actual time=5.475..44.241 rows=3 loops=1)
               Filter: ((name_caps)::text ~~ 'KOOM%'::text)
 Total runtime: 35514.085 ms
(7 rows)
@^....@^........@^......(snails pace...)

(After Upgrade) 
test=# select postgis_full_version();
                                               postgis_full_version
------------------------------------------------------------------------
------------------------------------------
 POSTGIS="1.3.2" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.6.0, 21 Dec 2007"
USE_STATS (procs from 1.2.1 need upgrade)
(1 row)

test=# explain analyze select a.filename from herb_ortho_index_poly a,
estate127_bdy b where b.name_caps like 'KOOM%' and ST_Within(a.the_geom,
b.the_geom);
                                                            QUERY PLAN
------------------------------------------------------------------------
----------------------------------------------------------
 Nested Loop  (cost=289.31..1294.72 rows=6514 width=16) (actual
time=24376.602..35325.759 rows=8 loops=1)
   Join Filter: st_within(a.the_geom, b.the_geom)
   ->  Seq Scan on herb_ortho_index_poly a  (cost=0.00..565.71 rows=9771
width=134) (actual time=0.020..22.089 rows=9771 loops=1)
   ->  Materialize  (cost=289.31..289.33 rows=2 width=2825) (actual
time=0.001..0.007 rows=3 loops=9771)
         ->  Seq Scan on estate127_bdy b  (cost=0.00..289.31 rows=2
width=2825) (actual time=4.367..31.726 rows=3 loops=1)
               Filter: ((name_caps)::text ~~ 'KOOM%'::text)
 Total runtime: 35326.335 ms
(7 rows)

I have followed the optimisation parameters available from Sun and other
places on the net. I've also indexed the spatial and non-spatial columns
appropriately.
I've even pre-loaded liblwgeom into PostGreSQL on startup.

Even our "put together with pieces laying around in the backyard" Ubuntu
server poo's on our megabuck production server !
(Test box)
test=# explain test=# select postgis_full_version();
                                                postgis_full_version
------------------------------------------------------------------------
---------------------------------------------
 POSTGIS="1.3.2" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
USE_STATS (procs from 1.3.0RC5 need upgrade)
(1 row)

analyze select a.filename from herb_ortho_index_poly a, estate127_bdy b
where b.name_caps like 'KOOM%' and ST_Within(a.the_geom, b.the_geom);

QUERY PLAN                                                
------------------------------------------------------------------------
------------------------------------------------------------------------
-------------------------
 Nested Loop  (cost=0.00..296.60 rows=4 width=16) (actual
time=176.402..732.686 rows=8 loops=1)
   Join Filter: _st_within(a.the_geom, b.the_geom)
   ->  Seq Scan on estate127_bdy b  (cost=0.00..288.31 rows=1
width=11300) (actual time=1.047..8.541 rows=3 loops=1)
         Filter: ((name_caps)::text ~~ 'KOOM%'::text)
   ->  Index Scan using herb_ortho_index_poly_the_geom_gist on
herb_ortho_index_poly a  (cost=0.00..8.27 rows=1 width=488) (actual
time=14.202..118.670 rows=25 loops=3)
         Index Cond: (a.the_geom && b.the_geom)
         Filter: (a.the_geom && b.the_geom)
 Total runtime: 732.775 ms
(8 rows)

Now that's a 50x performance difference.
Any hints at what I could do to a) look at why it might be going slow
and b) speed it up ?
What does the "Procs from xxx need upgrade" indicate ?

Option B - Move ALL our PostGIS operations to our "back-yard" server.

Any hints appreciated.
Cheers,

Shaun Kolomeitz,
Senior Technical Officer
Systems & Support Branch
Parks Division
Environmental Protection Agency
Queensland Parks and Wildlife Division

+----------------------------------------------------------------+
Think B4U Print
1 ream of paper = 6% of a tree and 5.4kg CO2 in the atmosphere
3 sheets of A4 paper = 1 litre of water
+----------------------------------------------------------------+

___________________________
Disclaimer

WARNING: This e-mail (including any attachments) has originated from a Queensland Government department and may contain information that is confidential, private, or covered by legal professional privilege, and may be protected by copyright.  

You may use this e-mail only if you are the person(s) it was intended to be sent to and if you use it in an authorised way.  No one is allowed to use, review, alter, transmit, disclose, distribute, print or copy this e-mail without appropriate authority.  If you have received this e-mail in error, please inform the sender immediately by phone or e-mail and delete this e-mail, including any copies, from your computer system network and destroy any hardcopies.

Unless otherwise stated, this e-mail represents the views of the sender and not the views of the Environmental Protection Agency.

Although this e-mail has been checked for the presence of computer viruses, the Environmental Protection Agency provides no warranty that all viruses have been detected and cleaned. Any use of this e-mail could harm your computer system.  It is your responsibility to ensure that this e-mail does not contain and is not affected by computer viruses, defects or interference by third parties or replication problems (including incompatibility with your computer system).

E-mails sent to and from the Environmental Protection Agency will be electronically stored, managed and may be audited, in accordance with the law and Queensland Government Information Standards (IS31, IS38, IS40, IS41 and IS42) to the extent they are consistent with the law.

___________________________

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080215/c11688fd/attachment.html>