On Thu, Sep 11, 2008 at 01:49:55PM +0200, Andreas Kalsch wrote: > Subject: [OSM-dev] Spatial vs. multi-column indexes for points > > Hey, > > last week I made some experiments with huge datasets of lat/lon > points. I use MySQL 5.0, which partially support GIS extensions, > including R-trees. But it is still not able to make queries based > on the GIS features, so I have to use the normal way - multi-column > indexes on lat/lon columns. It works well but probably there is a > way to make it even quicker ;) > > Has anybody used GIS successfully in MySQL or PGSQL and can tell me > how the performance compares between the two techniques?
I have 2 dbs - one mysql with the main api schema that means the
quadtile approach and the index on that column, and a postgres with
a modified simple schema from osmosis which basically matches the same
tables and organisation as the mysql. Both contain the minutely updated
planet. The postgres uses ~163G the mysql ~196G.
Both run on the very same hardware (Fujitsu Siemens RX/300, Dual Xeon
3.2Ghz 4GB Ram, 4x73GB Disk, Raid0 for the db).
Here are some numbers - to be honest - a part of the time is used in
the perl cgi to convert the database output. In case of the mysql there
is even more done in the perl code to not let the database shuffle the
data together as i dont trust mysql on transactions, temp tables and
stuff.
Its tried via the map api call on the mentioned bbox - The bbox contains
nodes:145100 ways:13695 relations:399
Postgres 8.3, postgis 1.3.3, kernel 2.6.26, Debian/Lenny
time wget -q -O pgsql
http://tiles-two.lab.rfc822.org/api/0.5/map?bbox=2.768555,42.671457,2.944336,42.757990
real 1m26.179s
user 0m0.156s
sys 0m0.940s
ls -la pgsql
-rw-r--r-- 1 flo mways 38277716 2008-09-11 16:53 pgsql
mysql 5.0.32-7etch5, kernel 2.6.18, Debian/Sarge
time wget -q -O mysql
http://tiles-one.lab.rfc822.org/api/0.5/map?bbox=2.768555,42.671457,2.944336,42.757990
real 2m9.143s
user 0m0.068s
sys 0m0.456s
ls -la mysql
-rw-r--r-- 1 flo mways 30658375 2008-09-11 16:28 mysql
The postgres export is larger by 8MByte as it exports usernames and
timestamps which the mysql variant does not do ... The mysql queries are
much simpler than the postgres ones. They dont contain subselects,
temporary tables or unions as the postgres ones do.
My conclusion is that postgres is faster. I dont think these numbers are
easily comparable as the method is different but these can be used as a
hint.
To see the cgi scripts look here:
Mysql: http://tiles-one.lab.rfc822.org/~flo/map.cgi
pgsql: http://tiles-two.lab.rfc822.org/~flo/map-pgsql.cgi
The pgsql is much simpler as i shifted most of the work of selection,
exclusion and merging into the database temp tables.
Flo
--
Florian Lohoff [EMAIL PROTECTED] +49-171-2280134
Those who would give up a little freedom to get a little
security shall soon have neither - Benjamin Franklin
signature.asc
Description: Digital signature
_______________________________________________ dev mailing list [email protected] http://lists.openstreetmap.org/listinfo/dev
