On 22/02/2010 21:00, River Tarnell wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Peter Körner:
After an ANALYZE it does an Index Scan, but this
is not really faster:
I've replaced the partial index with a standard index on pending, for
both planet_osm_ways and planet_osm_rel. This seems to improve the
query speed:
osm_mapnik=# explain analyze select id from planet_osm_ways where pending;
QUERY PLAN
-
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using planet_osm_ways_pending on planet_osm_ways (cost=0.00..12.84 rows=1
width=4) (actual time=0.248..0.248 rows=0 loops=1)
Index Cond: (pending = true)
Filter: pending
Total runtime: 0.263 ms
(4 rows)
Does this improve the diff import time at all?
Looking a little closer at the timing of log statements appearing, it
appears as if the the pending calls are no longer the problem. Never the
less, talking to Peter and Jon Burgess on irc last night, it appears as
if we are still seeing some general performance issues. At least on some
queries it seems as if there is up to a factor of 10 difference under
some conditions in comparison between ptolemy and yevaud. As an example
the query "explain analyze select count(*) from planet_osm_roads where
route='ferry';" took 55 seconds on yevaud compare to 150 seconds on
ptolemy. In the cache hot case, i.e. running the statement immediately
again, the time on yevaud went down to 8 seconds, whereas for ptolemy it
still stayed at 105 seconds. So there appears to be a much smaller
effect of caching. There were other queries that showed similar results too.
One potential large difference between the two setups is the 500+ extra
columns introduced by the name:* and wikipedia:* tags in the style. But
at least the raw table sizes don't show that much difference. Tables on
Ptolemy are about 10-20% larger in size. The planet_osm_roads table used
in the query above for example is 1.3Gb compared to 1.1Gb in size.
Jon wanted to post the postgres config of yevaud at some point to see if
there are any of the tunings done on yevaud that might help performance
on ptolemy.
Kai
P.S. Peter, is there a reason you dropped the maxInterval number in the
osmosis configuration down to 15 minutes? It can probably stay larger,
so that in case the diff import ever falls way behind it can catch up in
larger steps. Also, it is probably possible to drop the interval of
calling osmosis down to one minute now if you wish.
- river.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (HP-UX)
iEYEARECAAYFAkuC8IAACgkQIXd7fCuc5vIXuwCdHd1UgVjXPqilZgqMe7cnHOZw
NJgAniq9ioIuI3lNp6mABESHFmp2R/ps
=MMch
-----END PGP SIGNATURE-----
_______________________________________________
Maps-l mailing list
Maps-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/maps-l