[Geomoose-users] query.php performance advice sought
Dan Little
danlittle at yahoo.com
Sun Jul 4 07:58:12 EDT 2010
This may warrant writing something specific for your implementation. Everything is currently abstracted through mapscript and that may simply not be sufficient for your needs. I'm not sure where the direct but lays and I'm not sure I'd even be able to start to diagnose it without being able to play on a live system that is exhibiting the bug.
----- Original Message ----
> From: Johan Forsman <Johan.Forsman at LA.GOV>
> To: Dan Little <danlittle at yahoo.com>; "bfraser at geoanalytic.com" <bfraser at geoanalytic.com>
> Cc: "geomoose-users at lists.sourceforge.net" <geomoose-users at lists.sourceforge.net>
> Sent: Mon, June 28, 2010 9:13:45 AM
> Subject: RE: [Geomoose-users] query.php performance advice sought
>
> As measured with Firebug, query.php return time with the multi-field template is
> 5 minutes and 14 seconds.
Return time with the simple single-field template
> is 47 seconds.
/Johan.
> -----Original Message-----
>
> From: Dan Little [mailto:
> href="mailto:danlittle at yahoo.com">danlittle at yahoo.com]
> Sent:
> Saturday, June 26, 2010 3:01 AM
> To:
> ymailto="mailto:bfraser at geoanalytic.com"
> href="mailto:bfraser at geoanalytic.com">bfraser at geoanalytic.com
> Cc:
> ymailto="mailto:geomoose-users at lists.sourceforge.net"
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net
>
> Subject: Re: [Geomoose-users] query.php performance advice
> sought
>
> I would also check firebug to see what the return time is
> for the script.
> This very well may be the browser taking a long time to
> render the HTML.
>
>
> ----- Original Message ----
>
> > From: Brent Fraser <
> href="mailto:bfraser at geoanalytic.com">bfraser at geoanalytic.com>
>
> > To:
> href="mailto:bfraser at geoanalytic.com">bfraser at geoanalytic.com
> >
> Cc: "
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net"
> <geomoose-
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net>
>
> > Sent: Tue, June 22, 2010 10:50:55 PM
> > Subject: Re:
> [Geomoose-users] query.php performance advice sought
> >
> >
> Ok, disregards my comment below. Based on your emails the likely
> cause
> > is
> the query PHP and mapscript.
>
>
> Brent
>
> > It could be
> > mapserver's processing of
> the query. See
> >
> >
> http://trac.osgeo.org/mapserver/ticket/3069
> >
> > Try
> mapserver
> > v5.6
> >
> > Best Regards,
> >
> Brent
> > Fraser
> >
> >
> >> Hi Len,
> thanks for the PgAdmin suggestion; I
> > hadn't thought to do
> the
> >> SQL
> >> there to test, only checked
>
> > with a row filter. Here is the result of a
> >> simple SELECT
> query to
> > find all the records with syspop between 0 and
>
> >>
> > 5000.
> >>
> >> SELECT gid, pwsid,
> sysname, pwstype, syspop,
> > parish, dhhregion,
> >>
> sysstatus,
> >>
> > other_sysn, sourcetype, sourcecoun,
> facid, facname,
> > other_facn,
> >>
> wellregid, constr_dat,
> > facstatus, facstatus_, lat, lon,
> datum,
> >>
> > location_d, moreinfo, staticleve,
> groutingde, welldepth,
> >>
> > welldiam,
>
> >> point_x, point_y,
> >
> wkb_geometry
> >> FROM webmaps_sdwis_wells
>
> >>
> > WHERE (syspop > 0 and syspop < 5000);
>
> >>
> >> Total query
> > runtime: 241 ms.
>
> >> 3294 rows retrieved.
> >>
> >> I
> >
> would submit that postgres is working ok with that
> > runtime.
>
> >>
> >> I tested a brief version of your template
> >
> suggestion by pointing to a
> >> simple html that only returns a
> record
> > id. With that I get back 3294
> >> rows
>
> >> of records in my
> > "Search results" tab in 43 seconds. With
> my swanky
> >> formatted
> > template it takes 5 minutes
> and 35 seconds to get the same
> >> results
> >
> back.
> >>
> >> That's hard data that seems to suggest
> that it's
> > the templating that
> >> consumes all the
> time and
> > resources.
> >>
> >> Looks like a
> workaround is prescribed, and I
> > need to learn how to make
>
> >> the
> >> right links from the
> > result list to
> the "more info" template.
> >>
> >> Thanks for
> the
> > help!
> >> /Johan.
> >>
>
> >>> -----Original
> > Message-----
> >>> From:
> Len Kne [mailto:
> > ymailto="mailto:
> ymailto="mailto:lkne at houstoneng.com"
> href="mailto:lkne at houstoneng.com">lkne at houstoneng.com"
> >
> href="mailto:
> href="mailto:lkne at houstoneng.com">lkne at houstoneng.com">
> ymailto="mailto:lkne at houstoneng.com"
> href="mailto:lkne at houstoneng.com">lkne at houstoneng.com]
> >>>
> Sent:
> > Tuesday, June 22, 2010 4:38 PM
> >>> To: Johan
> Forsman;
> > ymailto="mailto:
> ymailto="mailto:geomoose-users at lists.sourceforge.net"
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net"
>
> > href="mailto:
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net">geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
> >>>
> > Subject: RE: query.php performance advice
> sought
> >>>
> >>>
> > Johan
>
> >>>
> >>> Something seems wrong with the
> performance
> > when accessing the Postgres
> >>>
> DB.
> >>> You say there
> > are only 4000 records,
> unless there are a million
> >>> attributes,
> > seems
> like that should be a really quick query. Have you
>
> >>>
> > tried running the query from the command line or
> PGAdmin to see how
> > it
> >>> performs?
>
> >>>
> >>> If you have a large
> > number of
> records with a large mapserver template,
> >>>
> >
> it
> >>> is going to take some time to get that data to
> the
> > client. Could you
> >>> have
>
> >>> a simple template
> > that returns just the syspop
> attribute and then have
> >>>
> > a
> >>>
> link to make a follow-up query if details are
> > needed?
>
> >>>
> >>> Another thing to check out is FastCGI,
> I
> > seen some performance boost
> >>> using
>
> >>>
> > it.
> >>>
> >
> http://www.maptools.org/ms4w/index.phtml?page=README_INSTALL_3.0.html#f-
>
> >>>
> > fastcgi
> >>>
> >>>
> Len
> >>>
> >>>
> > -----Original
> Message-----
> >>> From: Johan Forsman [mailto:
> >
> ymailto="mailto:
> href="mailto:Johan.Forsman at LA.GOV">Johan.Forsman at LA.GOV"
> >
> href="mailto:
> href="mailto:Johan.Forsman at LA.GOV">Johan.Forsman at LA.GOV">
> ymailto="mailto:Johan.Forsman at LA.GOV"
> href="mailto:Johan.Forsman at LA.GOV">Johan.Forsman at LA.GOV]
>
> >>>
> > Sent: Tuesday, June 22, 2010 3:06 PM
>
> >>> To:
> > ymailto="mailto:
> ymailto="mailto:geomoose-users at lists.sourceforge.net"
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net"
>
> > href="mailto:
> href="mailto:geomoose-users at lists.sourceforge.net">geomoose-users at lists.sourceforge.net">geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
> >>>
> > Subject: Re: [Geomoose-users] query.php performance
> advice
> > sought
> >>>
> >>> Please
> pardon the prior incomplete post;
> > I inadvertently pressed
> "send"
> >>> rather than "save draft". Blame
> > it on
> the streaming World Cup coverage
> >>> online. Here is the
> more
> > complete version.
> >>>
> >>>
> Preemptive apologies for a
> > long post. I have attempted to
> collect
> >>> enough
> >>>
> > data for
> the collective to have a chance at
> > theorizing.
>
> >>>
> >>> I am attempting to use the new
> >
> query.php to extract a subset of a
> >>> PostGIS
>
> >>> table
> > based on integer values stored in the "syspop"
> field. My service
> >>>
> > definition looks like this
> at the moment (I made a copy of the
> > original
> >>>
> query.php and changed the name to allow for
> > troubleshooting
> without
> >>> risking destroying the original, hence
> >
> the different script name):
> >>>
> >>>
>
> > <service name="advanced_search">
> >>>
>
> > <url>php/query_sdwp.php</url>
>
> >>>
> > <step
> type="input">
> >>>
> >
> <input type="hidden"
> > name="highlight"
> value="false"/>
> >>>
> >
> <input type="hidden" name="mode"
> > value="search"/>
>
> >>>
> >>>
> >
> <input type="hidden"
> > name="layer0"
> >>>
> value="wells/sdwis_wells"/>
> >>>
> >
> <input type="hidden"
> >
> name="template0"
> >>>
> >
> value="itemquery"/>
> >>>
> >>>
>
> > <input type="hidden"
> name="fieldname0"
> > value="syspop"/>
> >>>
>
> > <input type="user" name="value0" title="Population
>
> > is
> >>> greater than"/>
> >>>
>
> > <input type="hidden"
> name="comparitor0"
> > value="gt"/>
> >>>
>
> >>>
> > <input type="hidden"
> name="operator1"
> > value="and"/>
> >>>
>
> >>>
> > <input type="hidden"
> name="fieldname1"
> > value="syspop"/>
> >>>
>
> >>>
> > <input type="user"
> name="value1"
> > title="and less than"/>
>
> >>>
> > <input type="hidden"
> name="comparitor1"
> > value="lt"/>
> >>>
>
> > </step>
> >>>
> > </service>
>
> >>>
> >>> It's not pretty but it works.
> >
> Sort of. I am having problems with the
> >>> performance to
> such
> > extent it's not usable. I have made some attempts
>
> >>>
> > to
> >>> quantify this below. It's not
> scientific, but it is
> > illustrative.
> >>>
>
> >>> Relevant stats:
> >>>
> > Server hardware
> is 2 x 2.0 GHz Xeon, 3 GB RAM. It's a repurposed
> > Dell
>
> >>> PowerEdge 4600, I think.
> >>> Server OS is
>
> > Ubuntu Server 9.10.
> >>> GeoMoose 2.2 in FGS 9.5 with
> MapServer
> > 5.4.2.
> >>>
> >>> Dataset
> of interest is a point set with
> > 4100 total records, stored
> in
> >>> PostGIS on the same server. An
> > index on the
> syspop field has no
> >>> discernible effect on the
> >
> performance.
> >>>
> >>> During testing I was the
> only user
> > on the server.
> >>>
> >>>
> I turned off highlighting so the
> > query would only run once.
>
> >>>
> >>> I ran two
> > scenarios:
>
> >>> 1) syspop >5000 and syspop
> > <10000
>
> >>> 2) syspop >0 and syspop
> > <5000
>
> >>>
> >>> I observed the server process list to
>
> > determine where the CPU time was
> >>> being
> >
> spent.
> >>>
> >>> I uncommented the existing
> diagnostic
> > lines in query.php to verify the
> >>>
> request and the result count
> > for both scenarios and they are
> both
> >>>
> > correct.
> >>>
>
> >>> I increased the php timeout to 10
> > minutes and the php
> memory to 128
> >>> megs
> >>> to stop
>
> > those errors from interfering. I am logging the php errors and
>
> >>>
> > file is now clear with no errors.
>
> >>>
> >>> These are the
> > results for the two
> scenarios:
> >>>
> >>> Search Layer:
> >
> wells/sdwis_wells
> >>> Template: itemquery
> >>>
> FILTER:
> > syspop > 5000 and syspop < 10000 Total Results: 306
> Stop
> > watch
> >>> time to completion: 13 seconds Time
> spent by postgres: 7
> > seconds with
> >>> CPU
>
> >>> at 88% Time spent by httpd: 6
> > seconds with CPU at
> 92%
> >>>
> >>> Search Layer:
> >
> wells/sdwis_wells
> >>> Template: itemquery
> >>>
> FILTER:
> > syspop > 0 and syspop < 5000
> >>>
> Total Results:
> > 3323
> >>> Stop watch time to
> completion: 5.5 minutes!
> >>>
> > Time spent by
> postgres: 30 seconds with CPU at 88% Time spent by
> > httpd:
>
> >>> 5
> >>> minutes with CPU at
> > 92%
>
> >>>
> >>> With that CPU usage I'd say stuff is
> going
> > on, but what? SQL-wise this
> >>> is
>
> >>> a very simple
> > query, is it not?
>
> >>>
> >>> My baseline comparison is that
> >
> for both scenarios a SQL query from
> >>> Access
>
> >>> 2007
> > on my workstation on the linked table from the
> server, or a row
> >>>
> > filter from PgAdmin, returns
> the expected results
> > instantaneously.
> >>>
> Apples
> >>> and oranges I know, but
> > perhaps
> confirmation that the database can work
> >>> quickly and
>
> > serve the expected results in no time flat?
> >>>
>
> >>> I
> > tried a MapServer direct URL query, with a simple
> template to
> > just
> >>> list
> >>> the
> record IDs in the browser like
> > so:
> >>>
>
> >>> .../cgi-
> >>>
> >
>
> bin/mapserv?map=/opt/fgs/apps/geomoose2/maps/sdwp/wells/wells_webmaps.map&
>
> >>>
> >
>
> layers=sdwis_wells&mode=itemnquery&qlayer=sdwis_wells&qitem=syspop&qstring
>
> >>>
> > =(syspop > 5000 and syspop < 10000)
>
> >>>
> >>> This
> > returns the 306 records to
> the browser window in 3 seconds with
> >>>
> > barely a
> blip in the server CPU usage.
> >>>
> >>>
> Direct
> > URL query with syspop > 0 and syspop < 5000:
>
> >>> Returns the
> > 3323 records to the browser window in 35
> seconds with
> >>>
> > server
> >>> CPU
> (postgres) at 85%. A respectable portion of that time
> > is
>
> >>> transferring
> >>> the data to the browser
>
> > window.
> >>>
> >>> If those URL queries are
> representative
> > of what GM is doing, then I'd
> >>>
> say
> >>> that the
> > 30-second mark is as fast as
> MapServer can query the PostGIS
> >>>
> > and
>
> >>> return the results on this system, but what in the world is
>
> > going on
> >>> during those other 5 minutes? I know GM
> building the
> > formatted results
> >>> with the
> template, but what else? Is there a
> > way to tell?
>
> >>>
> >>> Very oddly with the URL queries:
> >
> The mapfile contains a FILTER
> >>> "sysstatus
>
> >>> =
> > 'Active'" directive to only include the active
> records. If that
> >>>
> > directive is absent the URL
> query works, but if it is present I
> > receive
> >>>
> and error: "Content-type: text/html msDrawMap(): Image
> > handling
> error.
> >>> Failed to draw layer named 'sdwis_wells'".
>
> > Depending on what numbers I
> >>> use
> >>>
> for the lower
> > and upper bounds I can get some records returned
> before
> >>>
> > the
> >>> error.
> MapServer bug? MapServer feature? PEBKAC? Just
> > weird?
>
> >>>
> >>> I don't know that there is anything I
> can
> > do, but please feel free to
> >>> opine at will;
> I am fresh out of
> > ideas. My coding skills are worse than
>
> >>> non-existent; they are
> > dangerous, and regrettably
> there is no budget
> >>>
> > for
> >>>
> that shiny new 26 GHz Core i13, which surely would address
> > some
> part of
> >>> this.
> >>>
> >>>
> Sorry about
> > the long post. After this, I'm thinking it's
> noon
> >>>
> > somewhere...
> >>>
> /Johan.
> >>>
> >>>
> >
> ----------------------
> >>> Johan Forsman
>
> >>>
> > Geologist
> >>> Safe Drinking Water
> Program
> >>> Louisiana
> > Department of Health and
> Hospitals Office of Public Health
> >>>
> > Telephone:
> 225.342.7309
> >>> Telefax:
> > 225.342.7303
>
> >>>
> >>>
> >>>
> >
> ------------------------------------------------------------------------
>
> --
> >>>
> > ----
> >>> ThinkGeek and
> WIRED's GeekDad team up for the Ultimate
> > GeekDad Father's
>
> >>> Day Giveaway. ONE MASSIVE PRIZE to the lucky
> > parental
> unit. See the
> >>> prize
> >>> list
> and
> > enter to win:
> >>>
> >
> http://p.sf.net/sfu/thinkgeek-promo
> >>>
> >
> _______________________________________________
> >>>
> Geomoose-users
> > mailing list
> >>>
> >
> ymailto="mailto:
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net"
>
> > href="mailto:
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net">Geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
> >>>
> >
> > target=_blank
> > >
> href="https://lists.sourceforge.net/lists/listinfo/geomoose-users" target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users
>
> >>
> >>
> >
> ------------------------------------------------------------------------
>
> ------
> >>
> > ThinkGeek and WIRED's GeekDad team up for
> the Ultimate
> >> GeekDad
> > Father's Day Giveaway. ONE
> MASSIVE PRIZE to the
> >> lucky parental
> > unit.
> See the prize list and enter to win:
> >>
> > href="
> href="http://p.sf.net/sfu/thinkgeek-promo" target=_blank
> >http://p.sf.net/sfu/thinkgeek-promo" target=_blank
> >
> >http://p.sf.net/sfu/thinkgeek-promo
> >>
> >
> _______________________________________________
> >>
> Geomoose-users
> > mailing list
> >>
> >
> ymailto="mailto:
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net"
>
> > href="mailto:
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net">Geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
> >>
> >
> > target=_blank
> > >
> href="https://lists.sourceforge.net/lists/listinfo/geomoose-users" target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users
>
> >>
> >
> >
> >
> >
>
> >
> >
> ------------------------------------------------------------------------
>
> ------
> >
> > ThinkGeek and WIRED's GeekDad team up for the
> Ultimate
> > GeekDad Father's
> > Day Giveaway. ONE MASSIVE
> PRIZE to the
> > lucky parental unit. See
> > the prize
> list and enter to win:
> >
> > href="
> href="http://p.sf.net/sfu/thinkgeek-promo" target=_blank
> >http://p.sf.net/sfu/thinkgeek-promo" target=_blank
> >
> >http://p.sf.net/sfu/thinkgeek-promo
> >
> >
> _______________________________________________
> > Geomoose-users
> mailing
> > list
> >
> > href="mailto:
> ymailto="mailto:Geomoose-users at lists.sourceforge.net"
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net">Geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
> >
> >
> > target=_blank
> > >
> href="https://lists.sourceforge.net/lists/listinfo/geomoose-users" target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users
>
> >
>
>
>
>
>
> --------------------------------------------------------------------------
>
> ----
> ThinkGeek
> > and WIRED's GeekDad team up for the
> Ultimate
> GeekDad Father's Day Giveaway.
> > ONE MASSIVE PRIZE
> to the
> lucky parental unit. See the prize list and
> >
> enter to win:
>
> > >
> href="http://p.sf.net/sfu/thinkgeek-promo" target=_blank
> >http://p.sf.net/sfu/thinkgeek-promo
>
> _______________________________________________
> Geomoose-users
>
> > mailing list
>
> > href="mailto:
> ymailto="mailto:Geomoose-users at lists.sourceforge.net"
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net">Geomoose-
>
>
> href="mailto:users at lists.sourceforge.net">users at lists.sourceforge.net
>
>
> > href="
> target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users"
>
> target=_blank
> > >
> href="https://lists.sourceforge.net/lists/listinfo/geomoose-users" target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users
>
>
>
>
>
> --------------------------------------------------------------------------
>
> ----
> This SF.net email is
> sponsored by Sprint
> What will you do first with EVO, the first 4G
> phone?
> Visit
> href="http://sprint.com/first">sprint.com/first --
> http://p.sf.net/sfu/sprint-com-first
>
> _______________________________________________
> Geomoose-users mailing
> list
>
> href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-users at lists.sourceforge.net
>
>
> target=_blank
> >https://lists.sourceforge.net/lists/listinfo/geomoose-users
More information about the Geomoose-users
mailing list