[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