[Geomoose-users] query.php performance advice sought

Mon Jun 28 10:13:45 EDT 2010

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:danlittle at yahoo.com]
> Sent: Saturday, June 26, 2010 3:01 AM
> To: bfraser at geoanalytic.com
> Cc: 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 <bfraser at geoanalytic.com>
> > To: bfraser at geoanalytic.com
> > Cc: "geomoose-users at lists.sourceforge.net" <geomoose-
> 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: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:geomoose-users at lists.sourceforge.net"
> > href="mailto:geomoose-users at lists.sourceforge.net">geomoose-
> 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: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: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
> >>>
> >>> 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:Geomoose-users at lists.sourceforge.net"
> > href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-
> users at lists.sourceforge.net
> >>>
> >
> > 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
> >>
> > ymailto="mailto:Geomoose-users at lists.sourceforge.net"
> > href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-
> users at lists.sourceforge.net
> >>
> >
> > 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:Geomoose-users at lists.sourceforge.net">Geomoose-
> users at lists.sourceforge.net
> >
> >
> > 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:
>
> > >http://p.sf.net/sfu/thinkgeek-promo
> _______________________________________________
> Geomoose-users
> > mailing list
>
> > href="mailto:Geomoose-users at lists.sourceforge.net">Geomoose-
> users at lists.sourceforge.net
>
> > 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 sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> Geomoose-users mailing list
> Geomoose-users at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/geomoose-users