Re: [Potlatch-dev] [OpenStreetMap] #3472: Typing b when the background is OS locator should set source:name
#3472: Typing b when the background is OS locator should set source:name --+- Reporter: Wynndale | Owner: potlatch-dev@… Type: enhancement | Status: closed Priority: minor| Milestone: Component: potlatch2| Version: Resolution: fixed|Keywords: --+- Comment(by stevage): [25115] -- Ticket URL: http://trac.openstreetmap.org/ticket/3472#comment:2 OpenStreetMap http://www.openstreetmap.org/ OpenStreetMap is a free editable map of the whole world ___ Potlatch-dev mailing list Potlatch-dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/potlatch-dev
[OSM-dev] Patch to osm2pgsql for faster updates
Hi All, I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right after a vacuum, but after a few hours of updates, it would be running at less than real-time. After a great deal of experimentation, I found that the GIN indexes on the ways and rels tables were the cause. In PostgreSQL 8.4, the fastupdate feature was introduced for GIN indexes: http://www.postgresql.org/docs/8.4/static/release-8-4.html This feature is on by default and delays updates of GIN indexes until the next vacuum, speeding individual index updates by adding them to a temporary space. I found that this causes slowdowns after a few hours of diffs. This is likely due to each subsequent index reads/updates having to read through the entire temporary update space. If the index is created with with (fastupdate=off), applying diffs is much faster overall, and stable in terms of performance from one diff application to another. Attached is a patch to osm2pgsql's middle-pgsql.c that adds the above term to the GIN index creation statements. This command WILL FAIL if used on PostgreSQL databases earlier than 8.4. Thanks! -Erik Burrows Index: default.style === --- default.style (revision 25113) +++ default.style (working copy) @@ -81,7 +81,20 @@ node,way wood text linear node,way z_order int4 linear # This is calculated during import wayway_area real# This is calculated during import - +node,way tiger:reviewedtext linear +node,way piste:type text linear +node,way piste:status text linear +node,way piste:oneway text linear +node,way piste:certifications text linear +node,way piste:lit text linear +node,way piste:abandoned text linear +node,way gladed text linear +node,way patrolled text linear +node,way piste:lift text linear +node,way piste:priority text linear +node,way piste:difficulty text linear +node,way piste:grooming text linear +node,way piste:lift:occupancy text linear # If you're interested in bicycle routes, you may want the following fields # To make these work you need slim mode or the necessary data won't be remembered. #way lcn_ref text linear Index: middle-pgsql.c === --- middle-pgsql.c (revision 25113) +++ middle-pgsql.c (working copy) @@ -86,7 +86,7 @@ .start = BEGIN;\n, .create = CREATE TABLE %s_ways (id int4 PRIMARY KEY, nodes int4[] not null, tags text[], pending boolean not null);\n, .create_index = CREATE INDEX %s_ways_idx ON %s_ways (id) TABLESPACE %s WHERE pending;\n, -.array_indexes = CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) TABLESPACE %s;\n, +.array_indexes = CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n, .prepare = PREPARE insert_way (int4, int4[], text[], boolean) AS INSERT INTO %s_ways VALUES ($1,$2,$3,$4);\n PREPARE get_way (int4) AS SELECT nodes, tags, array_upper(nodes,1) FROM %s_ways WHERE id = $1;\n PREPARE way_done(int4) AS UPDATE %s_ways SET pending = false WHERE id = $1;\n @@ -103,7 +103,7 @@ .start = BEGIN;\n, .create = CREATE TABLE %s_rels(id int4 PRIMARY KEY, way_off int2, rel_off int2, parts int4[], members text[], tags text[], pending boolean not null);\n, .create_index = CREATE INDEX %s_rels_idx ON %s_rels (id) TABLESPACE %s WHERE pending;\n, -.array_indexes = CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) TABLESPACE %s;\n, +.array_indexes = CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n, .prepare = PREPARE insert_rel (int4, int2, int2, int[], text[], text[]) AS INSERT INTO %s_rels VALUES ($1,$2,$3,$4,$5,$6,false);\n PREPARE get_rel (int4) AS SELECT members, tags, array_upper(members,1)/2 FROM %s_rels WHERE id = $1;\n PREPARE rel_done(int4) AS UPDATE %s_rels SET pending = false WHERE id = $1;\n ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
Corrected patch attached (without my custom change to default.style). Hi All, I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right after a vacuum, but after a few hours of updates, it would be running at less than real-time. After a great deal of experimentation, I found that the GIN indexes on the ways and rels tables were the cause. In PostgreSQL 8.4, the fastupdate feature was introduced for GIN indexes: http://www.postgresql.org/docs/8.4/static/release-8-4.html This feature is on by default and delays updates of GIN indexes until the next vacuum, speeding individual index updates by adding them to a temporary space. I found that this causes slowdowns after a few hours of diffs. This is likely due to each subsequent index reads/updates having to read through the entire temporary update space. If the index is created with with (fastupdate=off), applying diffs is much faster overall, and stable in terms of performance from one diff application to another. Attached is a patch to osm2pgsql's middle-pgsql.c that adds the above term to the GIN index creation statements. This command WILL FAIL if used on PostgreSQL databases earlier than 8.4. Thanks! -Erik Burrows ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev Index: middle-pgsql.c === --- middle-pgsql.c (revision 25113) +++ middle-pgsql.c (working copy) @@ -86,7 +86,7 @@ .start = BEGIN;\n, .create = CREATE TABLE %s_ways (id int4 PRIMARY KEY, nodes int4[] not null, tags text[], pending boolean not null);\n, .create_index = CREATE INDEX %s_ways_idx ON %s_ways (id) TABLESPACE %s WHERE pending;\n, -.array_indexes = CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) TABLESPACE %s;\n, +.array_indexes = CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n, .prepare = PREPARE insert_way (int4, int4[], text[], boolean) AS INSERT INTO %s_ways VALUES ($1,$2,$3,$4);\n PREPARE get_way (int4) AS SELECT nodes, tags, array_upper(nodes,1) FROM %s_ways WHERE id = $1;\n PREPARE way_done(int4) AS UPDATE %s_ways SET pending = false WHERE id = $1;\n @@ -103,7 +103,7 @@ .start = BEGIN;\n, .create = CREATE TABLE %s_rels(id int4 PRIMARY KEY, way_off int2, rel_off int2, parts int4[], members text[], tags text[], pending boolean not null);\n, .create_index = CREATE INDEX %s_rels_idx ON %s_rels (id) TABLESPACE %s WHERE pending;\n, -.array_indexes = CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) TABLESPACE %s;\n, +.array_indexes = CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n, .prepare = PREPARE insert_rel (int4, int2, int2, int[], text[], text[]) AS INSERT INTO %s_rels VALUES ($1,$2,$3,$4,$5,$6,false);\n PREPARE get_rel (int4) AS SELECT members, tags, array_upper(members,1)/2 FROM %s_rels WHERE id = $1;\n PREPARE rel_done(int4) AS UPDATE %s_rels SET pending = false WHERE id = $1;\n ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
On 23/01/11 16:24, Erik Burrows wrote: I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right after a vacuum, but after a few hours of updates, it would be running at less than real-time. After a great deal of experimentation, I found that the GIN indexes on the ways and rels tables were the cause. Note that using Postgres 8.4 for mapnik is not recommended as there are known to be severe performance issues. That's why the main tile server is still using 8.3 although as far as we know the problem (which was never really identified) has been solved in 9.0 judging by some limited testing. In PostgreSQL 8.4, the fastupdate feature was introduced for GIN indexes: http://www.postgresql.org/docs/8.4/static/release-8-4.html This feature is on by default and delays updates of GIN indexes until the next vacuum, speeding individual index updates by adding them to a temporary space. I found that this causes slowdowns after a few hours of diffs. This is likely due to each subsequent index reads/updates having to read through the entire temporary update space. I wonder if in fact this is the performance problem that we saw using an 8.4 database. The general consensus was that it was somehow related to the GIN indexes but we never managed to work out exactly what it was. Tom -- Tom Hughes (t...@compton.nu) http://compton.nu/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
On 23-1-2011 17:44, Tom Hughes wrote: On 23/01/11 16:24, Erik Burrows wrote: I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right [...] I wonder if in fact this is the performance problem that we saw using an 8.4 database. The general consensus was that it was somehow related to the GIN indexes but we never managed to work out exactly what it was. Erik mentions encountering the same issue in 9.0. So either fastupdate=on is more intelligent in 9.0, or we're not on the right track to solve our 8.4 slowdown troubles. -- Lennard ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
8.3 didn't have the fastupdate function at all, so I would imagine it is possible that the fastupdate introduction in 8.4 was the problem, since it is on by default in 8.4 and 9.0. I still have my 8.4 database, so I started it up and am re-creating the GIN indexes with fastupdate=off, to see if that fixes the problem in 8.4 also. It's on my SATA drive, so it will take about a day to complete. I'll let you know on Monday. -Erik On Sun, 2011-01-23 at 18:13 +0100, Lennard wrote: On 23-1-2011 17:44, Tom Hughes wrote: On 23/01/11 16:24, Erik Burrows wrote: I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right [...] I wonder if in fact this is the performance problem that we saw using an 8.4 database. The general consensus was that it was somehow related to the GIN indexes but we never managed to work out exactly what it was. Erik mentions encountering the same issue in 9.0. So either fastupdate=on is more intelligent in 9.0, or we're not on the right track to solve our 8.4 slowdown troubles. ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
On 23/01/11 17:13, Lennard wrote: On 23-1-2011 17:44, Tom Hughes wrote: On 23/01/11 16:24, Erik Burrows wrote: I was having great difficulty with applying the daily diffs to my PostgreSQL database (8.4 and 9.0). I would get good performance right [...] I wonder if in fact this is the performance problem that we saw using an 8.4 database. The general consensus was that it was somehow related to the GIN indexes but we never managed to work out exactly what it was. Erik mentions encountering the same issue in 9.0. So either fastupdate=on is more intelligent in 9.0, or we're not on the right track to solve our 8.4 slowdown troubles. Do we actually know that 9.0 is ok though? I was basing my comments on a single report of some preliminary tests at SOTM last year. Tom -- Tom Hughes (t...@compton.nu) http://compton.nu/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
Do we actually know that 9.0 is ok though? I was basing my comments on a single report of some preliminary tests at SOTM last year. Can someone explain the problem itself? We will set up our xapi database tomorrow and we would like to use 9.0. We can do some testing on 8.* and 9.* if you explain the problem and how to reproduce it. best regards philipp ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
First import ist fast, index creation after it is normal. When the minutely updates start, they're fast at beginning but tend to get slower and slower until they get slower than realtime (eg. 5 minutes of changes take 10 minutes to import). In a normal setup, the diffs take around 1/2 the realtime (eg. 5 minutes of changes are imported in ~2,5 minutes), depending on the disks used. Peter Philipp Borgers schrieb: Do we actually know that 9.0 is ok though? I was basing my comments on a single report of some preliminary tests at SOTM last year. Can someone explain the problem itself? We will set up our xapi database tomorrow and we would like to use 9.0. We can do some testing on 8.* and 9.* if you explain the problem and how to reproduce it. best regards philipp ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Patch to osm2pgsql for faster updates
On 23/01/11 17:59, Philipp Borgers wrote: Do we actually know that 9.0 is ok though? I was basing my comments on a single report of some preliminary tests at SOTM last year. Can someone explain the problem itself? We will set up our xapi database tomorrow and we would like to use 9.0. We can do some testing on 8.* and 9.* if you explain the problem and how to reproduce it. The problem is simply that postgres 8.4 has always seemed to be much slower than 8.3 for doing mapnik rendering work. Tom -- Tom Hughes (t...@compton.nu) http://compton.nu/ ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] Nominatim updates
Hi, On Monday morning I'm intending to switching the existing nominatim service over to an updated code base designed to allow better scaling and additional data sources (tiger, external postcode sets, etc) as well as being the first stage of various other improvements. With any luck the only externally visible difference should be in improvement in US search quality, however it is possible that there will be some other minor differences. If you spot any problems please email me or contact me on IRC (twain47). In particular please let me know if you spot something that used to work and now doesn't. The new service is currently a couple of weeks behind on data but catching up rapidly - I'm expecting it to be fully up to date by the end of the week and once it gets there is should be able to handle updates more quickly and reliably. -- Brian ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] [OSM-talk] Nominatim updates
On Sun, Jan 23, 2011 at 7:55 PM, Brian Quinion openstreet...@brian.quinion.co.uk wrote: Hi, On Monday morning I'm intending to switching the existing nominatim service over to an updated code base designed to allow better scaling and additional data sources (tiger, external postcode sets, etc) as well as being the first stage of various other improvements. That sounds great Brian! ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
