<div dir="ltr"><div><div>Paul,<br><br></div>The resultFormat parameter was already there back in 2003 with Postgres 7.4 when PQexecWithParams was introduced [1]. I hope the current minimum supported version of Postgres does not predate that.<br><br></div>Another question I have before I do a PR: what is the usefulness of all those #if TRANSFER_ENCODING? It cannot even be modified through cmake. I'm very tempted to remove all that.<br><div><div><br></div><div>Thanks for your feedback.<br><br>[1] <a href="http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-MAIN">http://www.postgresql.org/docs/7.4/static/libpq-exec.html#LIBPQ-EXEC-MAIN</a><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Dec 14, 2015 at 8:15 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Please bear in mind that the original implementation using binary<br>
cursors was deliberately *removed* (by me) because it added so much<br>
complexity to the code base. (In terms of having to maintain a correct<br>
transaction state throughout the query lifecycle.)<br>
<br>
However, your patch is very small, I see. Perhaps pgsql support of<br>
binary has improved a lot since the days of binary cursors. (Which<br>
could only be used in transaction, at the time.)<br>
<br>
Adding TWKB to mapnik has substantially improved network performance [1] for us.<br>
<br>
<a href="http://blog.cartodb.com/smaller-faster/" rel="noreferrer" target="_blank">http://blog.cartodb.com/smaller-faster/</a><br>
<br>
Removing precision in map rendering works like a charm, I'd highly<br>
recommend it. However, Mapserver's dual nature as both a feature and<br>
map server would make a change to support that more tricky than the<br>
Mapnik change.<br>
<br>
The only issue I'd have to this being a PR is some careful research as<br>
to when this RESULTSET_TYPE flag was added to PQexecParams I suppose<br>
we could have missed it all those years ago, and thus used binary<br>
cursors, but it could also be an add-on as of a certain date. Knowing<br>
when it was added would be good. (Probably long enough ago that we<br>
don't even need to #ifdef a version test on it, but I would like<br>
confirmation.)<br>
<br>
ATB,<br>
<br>
P<br>
<div class="HOEnZb"><div class="h5"><br>
On Mon, Dec 14, 2015 at 7:10 AM, Patrick Valsecchi<br>
<<a href="mailto:patrick.valsecchi@camptocamp.com">patrick.valsecchi@camptocamp.com</a>> wrote:<br>
> Daniel: I do agree with your analysis.<br>
><br>
> Even: That is a bit out of scope for this patch and I'm no fan of reducing<br>
> the precision, even for WMS.<br>
><br>
> On Fri, Dec 11, 2015 at 5:06 PM, Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>><br>
> wrote:<br>
>><br>
>> Le vendredi 11 décembre 2015 13:38:56, Daniel Morissette a écrit :<br>
>> > Hi Patrick,<br>
>> ><br>
>> > We have run into similar behaviors with a quite similar setup on Amazon<br>
>> > a little while ago, in our case we had a dedicated DB server with SSD<br>
>> > (like yours) and multiple rendering nodes connecting to it, and adding<br>
>> > more nodes or more powerful ones still resulted in a constant throughput<br>
>> > and the additional rendering nodes CPU running mostly idle. After much<br>
>> > hair pulling we finally found out that we were hitting the 1 Gbps limit<br>
>> > of the network connection between the DB and rendering node.<br>
>> ><br>
>> > I suspect you are running into the same thing here: if you could verify<br>
>> > that the size of the original vs binary response is about the double,<br>
>> > then that would confirm this possibility. We used PGBouncer to look at<br>
>> > the volume of data transferred by the DB server, it produces output like<br>
>> > this:<br>
>> ><br>
>> > 2014-06-06 18:49:<a href="tel:44.597%2027891" value="+14459727891">44.597 27891</a> LOG Stats: 852 req/s, in 435114 b/s, out<br>
>> > 83424883 b/s,query 7133 us<br>
>> > 2014-06-06 18:50:44.597 27891 LOG Stats: 818 req/s, in 418046 b/s, out<br>
>> > 79177139 b/s,query 5761 us<br>
>> > 2014-06-06 18:52:44.598 27891 LOG Stats: 898 req/s, in 458041 b/s, out<br>
>> > 88441708 b/s,query 7949 us<br>
>> ><br>
>> ><br>
>> > Then to really confirm it, look at the number of hits per second in each<br>
>> > test case (instead of the processing time per request). You will likely<br>
>> > notice that it is constant for a given zoom level once you hit the<br>
>> > network limit no matter how many parallel requests you have. This is<br>
>> > because the number of hits per seconds that can be served are limited by<br>
>> > the volume of data per second that the DB server can deliver to<br>
>> > MapServer (around ~800Mbits/sec for a 1 Gbps connection).<br>
>> ><br>
>> > In our case, if I remember correctly, we used to hit a limit of about 40<br>
>> > maps/second, where each map was averaging 2MB of data... so we were<br>
>> > capping at 4 hits x 2MB ~= 80MBytes per second from the DB (or<br>
>> > ~800Mbits/sec) ... which is as I wrote above in line with a 1Gbps limit.<br>
>><br>
>> If bandwidth is the bottleneck, an even more efficient solution than the<br>
>> binary<br>
>> WKB representation would be to use <a href="http://postgis.net/docs/manual-" rel="noreferrer" target="_blank">http://postgis.net/docs/manual-</a><br>
>> dev/ST_AsTWKB.html . This would require a TWKB decoder to be developped in<br>
>> MapServer and be likely only appropriate for WMS like scenarios where you<br>
>> can<br>
>> afford loosing some precision to coordinates values.<br>
>><br>
>> Even<br>
>><br>
>> ><br>
>> > Good luck!<br>
>> ><br>
>> > Daniel<br>
>> ><br>
>> > On 2015-12-11 4:13 AM, Patrick Valsecchi wrote:<br>
>> > > Hi,<br>
>> > ><br>
>> > > I was a bit disapointed by the perfs of reading the features from<br>
>> > > PostGIS compared to reading them from a local Shapefile. So I've<br>
>> > > pocked<br>
>> > > my nose around the code and I saw that geometries were fetched from<br>
>> > > the<br>
>> > > DB in EWKB encoded in hex.<br>
>> > ><br>
>> > > So I went ahead and changed the code to use libpq's binary mode when<br>
>> > > we<br>
>> > > are fetching features. To avoid conversion nightmares when fetching<br>
>> > > attributes, I'm tricking Postgres into sending them in their textual<br>
>> > > form. That way, the behavior of MapServer should not change regarding<br>
>> > > the other columns.<br>
>> > ><br>
>> > > The patch can be found in github [1].<br>
>> > ><br>
>> > > General testing algo:<br>
>> > > for each zoom level<br>
>> > > start at the same time N threads that does<br>
>> > > for each iteration<br>
>> > > compute random BBOX<br>
>> > > get the 1000x1000 image (throw the content out, but read it)<br>
>> > > Wait for all the threads to stop<br>
>> > > Collect the stats from the threads (throwing out the slowest result<br>
>> > > and<br>
>> > > the fastest result).<br>
>> > ><br>
>> > > The DB is on a db.m4.large from Amazon with SSD disk. Mapserver is on<br>
>> > > apache with fcgid (up to 20 processes) and runs on a m4.xlarge. The<br>
>> > > measures are taken from another Amazon machine to avoid hitting the<br>
>> > > bandwidth limit too fast.<br>
>> > ><br>
>> > > Times are for one query from one thread and are given in milliseconds<br>
>> > > along with their standard deviation. Each time, I did a full run<br>
>> > > before<br>
>> > > taking measures.<br>
>> > ><br>
>> > > Data used is the swiss villages (total 2479 features) with polygon<br>
>> > > contours in a 1.7MB shapefile file [2], imported as is in PostGIS.<br>
>> > ><br>
>> > > nbThreads=1 nbIterations=20<br>
>> > > zoom level 1.00 4.00 16.00 32.00<br>
>> > > original 964± 14 222± 126 66± 18 68± 19<br>
>> > > binary 807± 13 194± 111 87± 28 79± 24<br>
>> > > shapefile 554± 94 187± 107 72± 26 56± 3<br>
>> > ><br>
>> > ><br>
>> > > nbThreads=5 nbIterations=20<br>
>> > > zoom level 1.00 4.00 16.00 32.00<br>
>> > > original 3686± 946 403± 264 84± 37 70± 22<br>
>> > > binary 1710± 486 340± 242 105± 59 89± 32<br>
>> > > shapefile 519± 225 278± 166 91± 58 80± 34<br>
>> > ><br>
>> > > nbThreads=10 nbIterations=20<br>
>> > > zoom level 1.00 4.00 16.00 32.00<br>
>> > > original 7287±1936 800± 575 119± 79 110± 81<br>
>> > > binary 3737± 647 471± 294 123± 70 110± 54<br>
>> > > shapefile 884± 241 412± 269 111± 119 98± 57<br>
>> > ><br>
>> > > nbThreads=20 nbIterations=20<br>
>> > > zoom level 1.00 4.00 16.00 32.00<br>
>> > > original 14969±2507 1643±1221 239± 231 166± 103<br>
>> > > binary 7649± 730 857± 576 210± 121 181± 77<br>
>> > > shapefile 1455± 438 483± 326 143± 97 126± 75<br>
>> > ><br>
>> > > What is what:<br>
>> > > * original: Mapserver 7.0 (git 157fa47)<br>
>> > > * binary: Same as above with a small patch to configure libpg to use<br>
>> > ><br>
>> > > binary transfer.<br>
>> > ><br>
>> > > * shapefile: Same as original, but using a shapefile on the local<br>
>> > > disk<br>
>> > ><br>
>> > > (just here for comparison).<br>
>> > ><br>
>> > > We can see that when the machine gets parallel queries, we quickly get<br>
>> > > a<br>
>> > > factor 2 in perfs when there are a lot of features (low zoom level).<br>
>> > > There is no measurable negative impact at higher zoom levels and lower<br>
>> > > loads.<br>
>> > ><br>
>> > > Now, what do you guys think? Do you see in risk? Or should do a pull<br>
>> > > request?<br>
>> > ><br>
>> > > Thanks.<br>
>> > ><br>
>> > ><br>
>> > > [1]<br>
>> > ><br>
>> > > <a href="https://github.com/pvalsecc/mapserver/commit/45bd3d5795c9108618c37cc8c747" rel="noreferrer" target="_blank">https://github.com/pvalsecc/mapserver/commit/45bd3d5795c9108618c37cc8c747</a><br>
>> > > 2809cff54d16 [2]<br>
>> > ><br>
>> > > <a href="http://www.swisstopo.admin.ch/internet/swisstopo/en/home/products/landsca" rel="noreferrer" target="_blank">http://www.swisstopo.admin.ch/internet/swisstopo/en/home/products/landsca</a><br>
>> > > pe/swissBOUNDARIES3D.html<br>
>> > ><br>
>> > ><br>
>> > ><br>
>> > > _______________________________________________<br>
>> > > mapserver-dev mailing list<br>
>> > > <a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a><br>
>> > > <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-dev" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-dev</a><br>
>><br>
>> --<br>
>> Spatialys - Geospatial professional services<br>
>> <a href="http://www.spatialys.com" rel="noreferrer" target="_blank">http://www.spatialys.com</a><br>
>> _______________________________________________<br>
>> mapserver-dev mailing list<br>
>> <a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-dev" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-dev</a><br>
><br>
><br>
><br>
> _______________________________________________<br>
> mapserver-dev mailing list<br>
> <a href="mailto:mapserver-dev@lists.osgeo.org">mapserver-dev@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/mapserver-dev" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-dev</a><br>
</div></div></blockquote></div><br></div>