[postgis-users] Re: [mapserver-users] SDE performance
Paul Ramsey
pramsey at refractions.net
Fri Nov 22 09:14:56 PST 2002
The only thing I have not heard discussed is the mechanism for closing
connections at the end of the day. Every time a new distinct connection
string appears, a new connection is opened, fine, and this is a
layer-by-layer operation, so the database-specific layer object simply
opens the connection and populates the hash appropriately.
But the layers do not know when the whole map drawing session is over,
only the map object knows that, so there needs to be a callback at the
end of the map session which has the correct database-specific closing
operations called on all the open connections. See?
P.
Jan Hartmann wrote:
> (Forwarded. What DO the PostGIS folks think? JH)
>
> Steve Lime wrote:
>
>> Ok, I (call me Steve) will take a look at your proposal. It's sort of
>> along the
>> lines I was thinking although I was thinking of storing connection
>> objects
>> themselves and using a connection strings (or portions thereof) as
>> keys.
>> Any reaction from the PostGIS folks?
>>
>> Steve
>>
>> Stephen Lime
>> Data & Applications Manager
>>
>> Minnesota DNR
>> 500 Lafayette Road
>> St. Paul, MN 55155
>> 651-297-2937
>>
>>
>>>>> Jan Hartmann <jhart at frw.uva.nl> 11/21/02 07:34AM >>>
>>>>
>>
>> (cross-posted to mapserver-dev)
>>
>> Using one database connection for multiple layers could be easier than
>>
>> you think with callback functions. Just create TWO arrays: one with the
>>
>> CONNECTION strings and one an array of function pointers. All pointers
>>
>> are initialized to NULL. With each new CONNECTION, the main program
>> copies the CONNECTION string into the CONNECTION array and passes the
>> array number to the database subprogram (SDE, PostGIS, Oracle). This
>> checks if this connection duplicates a prior one and uses it if it
>> finds one. Else it opens a new connection. In the case of a new
>> CONNECTION it
>>
>> puts the address of a closing function into the second array at the
>> same position as where it got the CONNECTION string from. In the case
>> of an
>>
>> existing CONNECTION it leaves this array element NULL. At the end of
>> the main program the main application loops through the array of
>> function pointers and executes every one that is not NULL.
>>
>> There are two benefits to this:
>> - The main programmer (let's call him Steve) would have to add only a
>> dozen or so lines of pretty trivial code and can put his mind to more
>> urgent matters. Everything else, including the closing mechanisms, has
>>
>> to be done by the database programmers, which is the way it should be
>> - Even more important: database programmers would not be forced to use
>>
>> this mechanism. If they want to close each layer themselves, they are
>> free to do so, as long as they don't touch the functions array.
>> Existing code can be used alongside the new mechanism and adapted when
>> the individual programmers can find time. The only thing that would
>> have to
>>
>> be changed initially would be the manner of passing the CONNECTION to
>> the database program: not a string, but a number pointing into the
>> CONNECTIONstrings array.
>>
>> The following is a working demo:
>>
>> #include <stdio.h>
>>
>> // CONNECTION string array
>> char *connStrings[50];
>> // good old-fashioned C: array of funtion pointers returning int and
>> // accepting int as parameter
>> int (*funcs[50])(int);
>>
>>
>> // closing functions, to be defined by database programmers
>> int closeSDE(i) {
>> printf("SDE closed from connection opened with:
>> %s\n",connStrings[i] );
>> }
>>
>> int closePostGIS(i) {
>> printf("PostGIS closed from connection opened with:
>> %s\n",connStrings[i] );
>> }
>>
>> int closeOracle(i) {
>> printf("Oracle closed from connection opened with:
>> %s\n",connStrings[i] );
>> }
>>
>> main() {
>> int i;
>>
>> // initialize arrays
>> for (i=0;i<50;i++) {
>> connStrings[i] = NULL;
>> funcs[i] = NULL;
>> }
>>
>> // First connection (SDE)
>> connStrings[0] = "Connection string for SDE";
>> // SDE program opens connection and sets:
>> funcs[0] = closeSDE;
>>
>> // Second connection (PostGIS)
>> connStrings[1] = "Connection string for PostGIS";
>> // PostGIS program opens connection and sets:
>> funcs[1] = closePostGIS;
>>
>> // Third and following connections (all SDE)
>> connStrings[2] = "Connection string for SDE";
>> connStrings[3] = "Connection string for SDE";
>> connStrings[4] = "Connection string for SDE";
>> // SDE program uses existing connection and doesn't set closing
>> function
>>
>> // Sixth connection
>> connStrings[5] = "Connection string for Oracle";
>> // Oracle program opens connection and sets:
>> funcs[5] = closeOracle;
>>
>> // Seventh connection
>> connStrings[6] = "Connection string for PostGIS";
>> // PostGIS program uses existing connection and doesn't set closing
>> function
>>
>>
>> // main program closes all connections at the end
>> for (i=0;i<50;i++) {
>> if (funcs[i]) (*funcs[i])(i); //obfuscated but working!
>> }
>> }
>>
>> How about it?
>>
>> Jan Hartmann
>> Department of Geography
>> University of Amsterdam
>> jhart at frw.uva.nl
>> Steve Lime wrote:
>>
>>> I think folks will take it seriously, but the timing may a bit bad
>>
>>
>> with
>>
>>> folks
>>> rushing to polish of work in 3.7. At a minimum this should be logged
>>> in
>>> Bugzilla so it doesn't get lost. If someone would like to work on a
>>
>>
>> fix
>>
>>> that doesn't involve global variables, but rather a connection pool
>>> that
>>> becomes part of the main mapObj then I (as the SDE guy) would be
>>> happy to work to implement it. I don't think it's too hard, just a
>>> bunch
>>> of bookeeping since you might leverage multiple SDE/Oracle or
>>
>>
>> PostGIS
>>
>>> connections in a single run.
>>>
>>> Steve
>>>
>>> Stephen Lime
>>> Data & Applications Manager
>>>
>>> Minnesota DNR
>>> 500 Lafayette Road
>>> St. Paul, MN 55155
>>> 651-297-2937
>>>
>>>
>>>
>>>>>> "C F" <gis_consultant at hotmail.com> 11/20/02 12:24PM >>>
>>>>>
>>>
>>> Regarding MapServer creating a NEW db connection for each db
>>
>>
>> layer....
>>
>>> is anyone else taking this seriously? I thought this would be just the
>>
>>
>>
>>> beginning of the discussion but there doesn't seem to be too much
>>> interest in this besides us few. This seems like a pretty unnecessary
>>> performance constraint. I would think that it would be a fairly easy
>>> fix, but
>>
>>
>> it's
>>
>>> not my place to be making those assumptions... But I just thought I'd
>>
>>
>> bring
>>
>>> it up again in case everyone forgot :)
>>>
>>>
>>>
>>>
>>>> From: Jan Hartmann <jhart at frw.uva.nl>
>>>> To: mapserver-users at lists.gis.umn.edu Subject: Re: [mapserver-users]
>>>> SDE performance
>>>> Date: Wed, 13 Nov 2002 11:57:40 +0100
>>>>
>>>> My impression is that this would not be very hard to implement: just
>>>
>>
>> a
>>
>>>
>>>> global array with pointers to every CONNECTION opened in all LAYERS.
>>>
>>>
>>> The
>>>
>>>> applications itself (PostGIS, SDE, Oracle) could test for already
>>>
>>
>> open
>>
>>>
>>>> connections. The only thing MapServer itself would have to do is
>>>
>>>
>>> closing
>>>
>>>> all connections at the end.
>>>> This would certainly be of great help for Oracle and SDE users;
>>>
>>
>> large
>>
>>>
>>>> connection overheads have been mentioned regularly on this list.
>>>
>>
>> Even
>>
>>> for
>>>
>>>> PostGIS there are advantages; think about reusing complex queries
>>>
>>
>> with
>>
>>>
>>>> spatial operators and TMP tables. IMHO certainly worth looking into!
>>>>
>>>> Jan Hartmann
>>>>
>>>>
>>>> Paul Ramsey wrote:
>>>>
>>>>
>>>>> One possibility would be to abstract the connection making process
>>>>
>>
>> a
>>
>>> bit,
>>>
>>>>> so that a 'getConnection' function first checks a hash to see if
>>>>
>>>
>>> there is
>>>
>>>>> already a connection with parameters which match the requested
>>>>
>>>
>>> connection.
>>>
>>>>> It would probably have to reside on the mapObj though...
>>>>>
>>>>> P.
>>>>>
>>>>
>>>> Jan Hartmann
>>>> Department of Geography
>>>> University of Amsterdam
>>>
>>>
>>>
>>>
>>> _________________________________________________________________
>>> Help STOP SPAM with the new MSN 8 and get 2 months FREE*
>>> http://join.msn.com/?page=features/junkmail
>>>
>>
>>
>>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
--
__
/
| Paul Ramsey
| Refractions Research
| Email: pramsey at refractions.net
| Phone: (250) 885-0632
\_
More information about the postgis-users
mailing list