[OSM-dev] [PATCH] Persistant database connection
Christopher Schmidt
crschmidt at crschmidt.net
Sun Jul 9 22:58:00 BST 2006
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
More information about the dev
mailing list