[OSM-dev] Planned Maintenance

Brett Henderson brett at bretth.com
Fri Jul 27 01:02:19 BST 2007


Tom Hughes wrote:
> In order to allow some database schema changes to be completed I plan
> to place the API in read only mode at midnight BST (11pm GMT) this
> Saturday (28th July).
>
> Test runs on another machine suggest that the changes could take up
> to twelve hours so the API should hopefully be back online by midday
> on Sunday if not before.
>
> The web site will still be operational during this period - the slippy
> map will still work, you will still be able to login (and even to upload
> traces). Read calls to the API will still work.
>
> Attempts to create/update/delete nodes, segments and ways will fail
> with a "503 Service Unavailable" error. The edit tab will also be
> disabled during the maintenance.
>
> Any questions, please shout now.
>
> Tom
>   
Hi Tom is it possible to get an idea of what the changes are?  I checked 
the rails_port\db\migrate directory and didn't see any new scripts.

I'm particularly interested to see what impact any changes will have on 
my replication queries.

On a related question, if Osmosis is to be viable I'll need timestamp 
indexing so I can efficiently query for changed records within a 
specific time period.  These are the indexes I created locally.
ALTER TABLE `osm`.`nodes` ADD INDEX `nodes_tstamp_idx` USING 
BTREE(`timestamp`);
ALTER TABLE `osm`.`segments` ADD INDEX `segments_tstamp_idx` USING 
BTREE(`timestamp`);
ALTER TABLE `osm`.`ways` ADD INDEX `ways_tstamp_idx` USING 
BTREE(`timestamp`);

Is there any way I can submit these for inclusion on the primary database?

Without these indexes, even queries returning no data can take minutes 
to execute.  With the indexes added queries returning no data run in 
less than 100ms.  This will let Osmosis scale down to very small 
replication intervals.  Reading a changeset for a complete day (just 
using data available in a planet) and writing it to a delta file took 
approximate 40 seconds.  It will take longer on the main database due to 
the larger size of the history table.

As an example, here is the node replication query (Note that the 
interval end timestamp is specified twice in the query).  The inner 
query identifies all nodes that have changed during the time interval, 
the outer query then retrieves the complete history of these nodes up 
until the end of the time interval.  I need the complete history so that 
I can identify if the node was created or modified during the time interval.
SELECT n.id, n.timestamp, n.latitude, n.longitude, n.tags, n.visible
FROM nodes n
INNER JOIN (
    SELECT id
    FROM nodes
    WHERE timestamp >= '2007-07-11 13:00:00' AND timestamp < '2007-07-11 
13:17:00'
    GROUP BY id
) idList ON n.id = idList.id
WHERE n.timestamp < '2007-07-11 13:17:00'
ORDER BY n.id, n.timestamp






More information about the dev mailing list