[OSM-dev] osm2pgsql questions

Martijn van Oosterhout kleptog at gmail.com
Wed Sep 30 20:21:22 BST 2009


On Wed, Sep 30, 2009 at 11:27 AM, Frank Broniewski <brfr at metrico.lu> wrote:
> I ask this question mainly because I found some oddities when examining the
> diff process by osm2pgsql. Postgresql and Postgis are tuned according to the
> oms wiki and Postgresql manual ...
> First is a number of postgresql processes which are idle in transaction, this
> usually means that there has been a transaction started with BEGIN but not
> commited. Right now I have 5 processes of this kind and nobody else except
> osm2pgsql is using the database right now. Maybe it is a flaw/problem in
> osm2pgsql?

osm2pgsql has several copies running at once and unfortunatly you can
only run one copy at a time per connection. So osm2pgsql opens a
number of connections. As so yes, you get idle in transaction
messages. This is normal.

> At the start of the diff process I get errors in my postgresql log naming that
> some _tmp tables do not exist and cannot be dropped:
>
> 2009-09-30 08:44:16 CEST ERROR:  table "planet_osm_point_tmp" does not exist
> 2009-09-30 08:44:16 CEST STATEMENT:  DROP TABLE planet_osm_point_tmp;
> 2009-09-30 08:44:16 CEST ERROR:  table "planet_osm_line_tmp" does not exist
> 2009-09-30 08:44:16 CEST STATEMENT:  DROP TABLE planet_osm_line_tmp;
> 2009-09-30 08:44:16 CEST ERROR:  table "planet_osm_polygon_tmp" does not exist
> 2009-09-30 08:44:16 CEST STATEMENT:  DROP TABLE planet_osm_polygon_tmp;
> 2009-09-30 08:44:16 CEST ERROR:  table "planet_osm_roads_tmp" does not exist
> 2009-09-30 08:44:16 CEST STATEMENT:  DROP TABLE planet_osm_roads_tmp;

Hmm, I forget the reason for those....

> osm2pgsql puts out some messages during import and I wonder what
> storage efficiency: 11.34%, hit rate: 29.09%
> mean. Maybe someone can shed some light onto this for me

For efficiency osm2pgsql keeps a cache of nodes. Unfortunatly, this
take more memory than most people have. What this means is that, in
your cache, about 11% of the nodes fit in the cache, and that was good
for 29% of node lookups. Bigger cache = higher hit rate = faster
import.

Have a nice day,
-- 
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/




More information about the dev mailing list