[OSM-dev] [PATCH] Persistant database connection

[Christopher Schmidt]

On Sun, Jul 09, 2006 at 11:42:58PM +0200, Lars Aronsson wrote:
> James Mastros wrote:
> 
> > Currently, the core of OSM is a bit silly: Every time it wants to execute
> > some SQL, it connects to the database, runs the sql, gets the entire result
> > set, and then disconnects from the database.  There's a number of ways in
> > which that's silly, but the simplest one is that there's no reason to keep
> > connecting and disconnecting -- connect when you first need the connection,
> > and disconnect when you're all done.
> 
> Apart from there is no reason to disconnect and connect again, is 
> there any reason to implement the change?  Because you say it's 
> silly, or because it will actually make things faster or better?  
> How much faster or better?  Do you have numbers, do you have an 
> estimate, or can you tell how we can get numbers?

Building a TCP/IP connection for each query, unless there is some
reason, will not improve performance. Because this is a tiny speed
increase on the scale OSM is currently using SQL, it's very difficult to
create hard numbers for it: it won't be visible on the small numbers of
SQL queries currently in use by OSM.

However, there are costs, they're just not measurable on the small
scale. The previous patch I posted to the list (for the limit in the
number of segments or tags per way), however, would increase the number
of queries from a scale of 1-10 to a scale of 500-1000 per /map? query,
which would mean that TCP/IP connection creation would have a measurable
affect. 

I recently experienced this problem on another site I maintain, which is
why I mentioned it as a possible problem with the patch at all.

> I'm not a developer here, so my comments have absolutely no 
> influence on anything.  But I am a developer by profession, and I 
> prefer measurement over guesswork.  If anything, I'd like to get 
> that mindset into OSM.

I agree with this. I have numerically experienced this type of slowdown
before on other sites, but I can not provide a solid example of speed
increase on OSM's current codebase because it's designed to avoid the
problems related to creating many connections.

> I vaguely remember a webhoster telling me (to my surprise at the 
> time) that they recommend *against* persistent MySQL connections, 
> but I can't remember why or what version of MySQL that was.  Does 
> your change leave connections open across HTTP calls?

It shouldn't, but if it does, then it shouldn't be implemented.
Persistence in this case should only be through a single request. Taking
up the connection space does matter - it means MySQL needs to hold open
that connection and the resulting memory increase until it closes. The
patch I posted was tested for that, as I mentioned.

-- 
Christopher Schmidt
Web Developer