[postgis-users] S...L...o.....w..@^ performance on Solaris/SPARC
Paul Ramsey
pramsey at cleverelephant.ca
Thu Feb 14 14:42:17 PST 2008
Shaun,
Your backyard server is building a query plan that uses an index, and
your megabuck server is not.
Now, *why* that would be, is another story. Perhaps your Sun-special
configuration is being over-optimistic about how fast a sequence scan
is? That is probably one part of your performance tale.
The other part is that your backyard server probably has less memory
and slower disks, but a faster CPU clock? Since much of the
ST_Within() test is computational (particularly if you're not using an
index!) a slower CPU can make a big difference (but only linearly with
clock speed, really).
Once you get out of your bad query plan, the next revision of PostGIS/
GEOS with prepared geometry should speed up this kind of query even
more. I got a 6x improvement on my case, some cases report 20x
returns. However, this is not the solution to your root problem.
Paul
On Feb 14, 2008, at 2:21 PM, Shaun Kolomeitz wrote:
> 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 Queens_______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
More information about the postgis-users
mailing list