[OSM-dev] Too many slow queries in db
brett at bretth.com
Tue Sep 4 09:08:15 BST 2007
I like the idea of trialling alternative databases. But we need a way
of assessing their suitability.
Can we build a list of possible improvement areas over current
database? This would less any alternatives be measured quantitively.
* The current top 10 slow queries. Assuming an alternative schema still
follows the nodes, segments, ways model this would let us manually run
queries against alternative implementations without porting tools.
* Speed to build indexes.
* Ability to avoid or recover from corruption.
* Ability to partition data. The physical ways the data can be split
(across disks or servers) and logically how the data is partitioned
(chronological time, primary key range, or geo-spatial location) are of
For example, Oracle (most of my past experience is using Oracle) has a
number of features that make it desirable for systems like this.
Features such as clustering, partitioned tables (and indexes) and online
index rebuilds would all be very useful.
Hopefully open source databases can provided some or all of these
features as well but this requires research and testing. For example,
MySQL supports table partitioning but doesn't appear to support online
index rebuilds (perhaps I'm wrong). I know very little about postgresql
or other alternatives.
I would love to see somebody create some alternate schemas for different
database engines and test some of this out. If nobody else does it I'll
do it one day but I won't get there for a while, I have a long list of
feature requests already with osmosis. However I am happy to help out
with data load processing if required.
Raphael Jacquot wrote:
> Stefan de Konink wrote:
>> About the TIGER import: you are completely right the import is no problem.
>> Like the Dutch/AND import it will add more to the database in a time with
>> no time to grow.
>> If what you say is right you are not really reaching hardware limits, just
>> software that isn't smart enough there is ofcourse Oracle and PostgreSQL
>> maybe if someone really wants to try something big: http://monetdb.cwi.nl/
>> (although my suggestion on this one would be: test before production).
> been saying that for a while
More information about the dev