[OSM-dev] Too many slow queries in db
Stefan de Konink
skinkie at xs4all.nl
Tue Sep 4 01:08:19 BST 2007
On Tue, 4 Sep 2007, Tom Hughes wrote:
> In message <20070904003537.I31320-100000 at xs2.xs4all.nl>
> Stefan de Konink <skinkie at xs4all.nl> wrote:
> > On Mon, 3 Sep 2007, Tom Hughes wrote:
> > > Seriously, if you've got a magic way to make a database query
> > > a 100 million row table in the blink of an eyelid I'll be only
> > > to happy to deploy it.
> > By prioritizing queries?
> How do I prioritise queries in MySQL? Whose are most important
> anyway? Yours? Mine?
The restriction of MySQL is self made, if someone needs to cook and extra
wrapper for this (poor) database to support this, it has to be done. The
one that are most important are the interactive ones. Maybe by spatial
size for example.
> > In a way putting some thing  in front of this *poor* 100 million row
> > database because in the current way the system will never be able to scale?
> How is a proxy supposed to help? Does it cache responses or something?
You could plug in extra hardware without downtime and without coding an
extra OSM specific way. Since this same layer *should* support query
translation you could even think about spatial migration of data. Tricky,
maybe, but if it is done right in the real programming language with one
letter I assume no problems.
> > Although that every query walks trough 100 million rows, *sounds* a bit
> > absurt.
> No, but they frequently read a substantial percentage due to the
> index being one dimensional and the data two dimensional. Combined
> with table level locking you have a recipe for problems.
And this is the reason why some individuals suggested PostGIS in
january 2007 on OSM-Talk? (Read this before I posted my first message, it
was about spatial indexing of this system.)
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).
But for now... this turns people down and starts endless discussions...
first about slowless, then about gpx not working, then about migration of
databases, then downtime, etc.
Catching this all with a proxy (for now) and maybe add some stupid extra
hardware could get you maybe 3 months to test a better solution.
More information about the dev