[Tilecache] More CustomService.py Questions

[Brian.Walawender at noaa.gov]

Chris,

Thanks for the information.  I wasn't aware of the exploit and I will definitely change my code to prevent that.  

That database is running on another blade in the chassis so I would think the network latency would be low.  I made map_layer the primary_key for that table so it should be indexed by default correct? (I also wanted to prevent duplicate map names).  Here is the  results of the sample query:

iris=# EXPLAIN ANALYZE SELECT wms_layer, wms_url, extension, bbox, max_resolution, debug, srs FROM tilecache_config WHERE map_layer='NAT_N0R_1';
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tilecache_config_pkey on tilecache_config  (cost=0.00..8.28 rows=1 width=187) (actual time=0.041..0.045 rows=1 loops=1)
   Index Cond: (map_layer = 'NAT_N0R_1'::text)
 Total runtime: 0.099 ms
(3 rows)

We are running under the CustomService.py under mod_python.  Each call to Tilecache spawns its own call to CustomService.py correct? So we can have a persistent connection to the database.

We did have to up the number of connections to the database (I quickly maxed it yesterday) because of the number of layers that are requested and the volume of hits.

You can see our site at (not currently using the DB service script):

http://radar.srh.noaa.gov/  

Thanks,

bw

----- Original Message -----
From: Christopher Schmidt <crschmidt at metacarta.com>
Date: Saturday, March 14, 2009 7:30 am
Subject: Re: [Tilecache] More CustomService.py Questions
To: Brian Walawender <Brian.Walawender at noaa.gov>
Cc: tilecache at openlayers.org


> On Fri, Mar 13, 2009 at 04:04:42PM -0500, Brian Walawender wrote:
> > Ok - I've designed a service that reads the configuration 
> information from a
> > Postgres database.  But now I've seem introduced a new bottleneck.  
> My table
> > has 20,500 rows.  Any suggestions to my code below?  It seems that I 
> am
> > quickly maxing out the connections (or at least really slowing down 
> the
> > database).
> 
> I expect that, as someone else suggested, you simply don't have an 
> index on your map_id column. At the psql prompt, try:
> 
>   create index map_id_idx on (map_id);
> 
> It should take about 10s, and your queries after that should be nearly
> instant.
> 
> I'm assuming that this is running under mod_python? Otherwise, you could
> have a problem with the imports being slow.
> 
> Also, is your database local or remote? If it's remote and high latency,
> the setup time to talk to the database could also be a problem.
> 
> Also, you should do this differently:
> 
> >   sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
> > debug, srs FROM tilecache_config WHERE map_layer='"
> >   sqlCMD = sqlCMD + map_layer + "'" 
> 
> sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
>           debug, srs FROM tilecache_config WHERE map_layer=%"
> 
> cursor.execute(sqlCMD, (map_layer,))
> 
> This prevents sql injection attacks by someone typing 'layer='; drop
> database;'
> 
> -- Chris
> 
> > #!/usr/bin/python
> >  
> > # This example code demonstrates how you can construct
> > # a custom service as an alternative to the config file.
> > # This allows you to, for example, determine what layers to
> > # load based on request parameters or something similar --
> > # the config file based loading technique is handy, but 
> > # probably doesn't solve all problems.
> >  
> > from TileCache.Service import Service, modPythonHandler
> > from TileCache.Caches.Disk import Disk
> > import TileCache.Layers.WMS as WMS
> > import re
> > import psycopg2
> > import sys
> >  
> > def handler (req):
> >  
> >   url_args = req.args
> >   tmp_str = url_args.upper()
> >   if tmp_str.find("LAYERS=") > -1:
> >     argList = url_args.split("&")
> >     map_layer = argList[0]
> >     map_layer = map_layer[7:]
> >   else:
> >     url_args = req.unparsed_uri
> >     paramList = url_args.split("/")
> >     map_layer = paramList[4]
> >  
> >   try:
> >     conn = psycopg2.connect("dbname='gis' user='user' host='123.456.789'
> > password=''pass"); 
> >   except:
> >     sys.exit()
> >  
> >   sqlCMD = "SELECT wms_layer, wms_url, extension, bbox, max_resolution,
> > debug, srs FROM tilecache_config WHERE map_layer='"
> >   sqlCMD = sqlCMD + map_layer + "'" 
> >  
> >   cur = conn.cursor()
> >   cur.execute(sqlCMD)
> >   row = cur.fetchone()
> >   conn.close()
> >  
> >   wms_layer = row[0]  
> >   wms_url = row[1]
> >   myExtension = row[2]
> >   myBBox = row[3]
> >   myMaxRes = row[4]
> >   myDebug = row[5]
> >   mySRS = row[6]
> >  
> >   myService = Service (
> >     Disk("/www/html/ridge2/tilecache"),
> >     {
> >       map_layer       : WMS.WMS( map_layer,
> >                     layers = wms_layer, 
> >                     url = wms_url,
> >                     extension = myExtension,
> >                     bbox = myBBox, 
> >                     maxResolution= myMaxRes,
> >                     debug = myDebug,
> >                     srs= mySRS),
> >      }
> >   )
> >  
> >   return modPythonHandler(req, myService)
> >  
> > _________________________________________________
> > Brian Walawender
> > Technique Development Meteorologist
> > Scientific Services Division - Central Region Headquarters
> > 816-268-3114 - Office
> > 816-805-6497 - Cell
> >  
> 
> > _______________________________________________
> > Tilecache mailing list
> > Tilecache at openlayers.org
> > 
> 
> 
> -- 
> Christopher Schmidt
> MetaCartaC