[OSM-dev] Limitation on downloading ways via /map API Call

[Ben Gimpert]

> When writing a get_connection function, to make it persist across the
> instance, I would typically do:
> 
> class OSM():
>   def get_connection(self):
>     if self.dbh: return self.dbh
>     try:
>       self.dbh = MySQL.connect()
>       return self.dbh
>     except MySQLError, E:
>       mysql_error(e)
> 

Whichever way this goes, here's Christopher's code ala' Ruby:

module OSM
	# ...

	@@dbh = nil  # one handle per VM

	def create_connection
		begin
			@@dbh = Mysql.real_connect($DBSERVER, $USERNAME, $PASSWORD, $DATABASE)
		rescue MysqlError =>ex
			mysql_error(ex)
		end
	end

	def get_connection
		@@dbh || create_connection
	end

	# ...
end

(Note the feeling of elation on being able to do what I want with
indenting... <snicker>)

		Ben


On Mon, 10 Jul 06 @06:05am, James Mastros wrote:
> On Sun, Jul 09, 2006 at 09:12:43PM +0100, SteveC wrote:
> > * @ 09/07/06 05:12:50 PM crschmidt at crschmidt.net wrote:
> > > This seems to me to be ignoring the fact that we have a get_multi
> > > function which does exactly what this SQL does, for a single way: given
> > 
> > No, it's not ignoring it. It's making a balanced decision about calling
> > the way function hundreds of times or trying to grab all that data at
> > once. Up until today, as you know, it wasn't apparent that the
> > group_concat functions would have a limit.
> 
> No, but it is apparent that group_concat doesn't really "fit" in the design
> of SQL in general, and that joining all the disseperate records into a
> string in the SQL server, and then splitting them back up in the Ruby code
> is going to be sub-optimal.  The server is designed to give back multiple
> records, not to do string manipulation.
> 
> 
> 
> > > This doesn't seem to me to be very hard to fix: all the SQL currently
> > > uses the call_sql function, which uses get_connection, which currently is:
> > 
> > Actually, you would want a pool of db connections not just one.
> 
> No, you want to have one db connection per simulintanious request.  There is
> something of an issue of running out of mysql server handles.  However, it's
> highly unlikely we'll ever reach that limit.  On the other hand, tearing up
> the connection each time is always going to be bad, no matter what the
> server load.  Note that different forks of the apache server are the only
> way to have simulintanious requests, and there is one ruby interpreter per
> fork.
> 
> Note, by the way, the first bullet point in
> http://dev.mysql.com/doc/refman/5.1/en/tips.html -- the MySql manual,
> 7.2.19. Other Optimization Tips.  "Use persistant connections".
> 
> If in doubt, of course, benchmark, by all means.
> 
>    -=- James Mastros
> 
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev