Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi Frederik, I'm running minutely updates on south-east asia. The DB is comparably small. After I did this: On 07.08.2011 21:19, Frederik Ramm wrote: DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); It took some minutes, but then looked fine. The last night it seams import gets stuck. postgres is spending a lot of time in UPDATE. Either there is something missing or fastupdate=off is a bad idea for some systems. Before it worked not too bad. I have a cronjob doing cleanup work including a vacuum which usually completes in minutes. The last night it spent there two hours. I'm reverting the index to the default now... Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
On 07.08.2011 21:19, Frederik Ramm wrote: To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes with DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); This did lead to huge problems on my DB as it was still using the intarray for the updates. Recovered it with the gin__int_ops on the gin index: DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes gin__int_ops) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts gin__int_ops) WITH (fastupdate=off); Documentation suggests to use ALTER INDEX instead. I did not try it as I already broke my DB using the original commands. Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, Stephan Knauss wrote: On 07.08.2011 21:19, Frederik Ramm wrote: To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes with DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); This did lead to huge problems on my DB as it was still using the intarray for the updates. I don't understand. The intarray/no intarray question is completely unrelated to the fastupdate on/off question? Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
On 09.08.2011 00:00, Frederik Ramm wrote: Stephan Knauss wrote: This did lead to huge problems on my DB as it was still using the intarray for the updates. I don't understand. The intarray/no intarray question is completely unrelated to the fastupdate on/off question? The problem was that the INDEX is different. It has the gin__int_ops included on my installation with intarray in place. USING gin (nodes gin__int_ops) I guess without it postgres was not using indexes at all and thus slowing it down that much. Stephan ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, Stephan Knauss wrote: The problem was that the INDEX is different. It has the gin__int_ops included on my installation with intarray in place. Ah, now I see. My instructions on rebuilding the index were not taking the old style into account. Sorry, I overlooked that! So, anyone else still using the old osm2pgsql (i.e. anybody who hasn't updated in the last few days and hasn't been using the osm2pgsql-64 branch before, i.e. most people) wanting to recover from the missing fastupdate=off by dropping and re-creating the index, don't forget the gin__int_ops when building the index. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, this is about a bug in osm2pgsql that will affect you if you * run diff updates (--slim --append) * run PostgreSQL 8.4 or 9.0 * use an osm2pgsql SVN revision = 25198 (2011-01-31) and 26475 (today) * are not using non-standard index tablespaces (-i or --tablespace-slim-index) Running diff updates with these PostgreSQL versions requires FASTUPDATE to be turned off (see http://lists.openstreetmap.org/pipermail/dev/2011-January/021704.html for defaults). I had introduced a fix in r25198 that would automatically switch off FASTUPDATE, but only today I discovered that accidentally this was conditional to setting an index tablespace; if you didn't, then FASTUPDATE was left on which means that your updates will be much slower than need be. To check if your indexes have been created with or without FASTUPDATE, run pg_dump mydatabase --schema-only | grep -i fastupdate you should see something like this: CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); if you don't see anything then your indexes have been created with FASTUPDATE set to on (the default), and you will have to follow the procedure in the posting quoted above to fix that (or install a new osm2pgsql and re-import). A quick fix without any index regeneration is to set your work_mem in postgresql.conf to a ridiculously low value (e.g. 1M instead of 1G) which will thwart the FASTINDEX operation and make things run much faster. Sorry for the cock-up. Bye Frederik PS: If this should prompt you to download a new version of osm2pgsql from SVN, be aware that I have recently renamed the old osm2pgsql to osm2pgsql-intarray and what you will now find in the osm2pgsql directory is the new version that works without intarray. You cannot use that to update a database that has been created with the old, intarray code (it will detect that and abort); but then again to get rid of the above fastupdate problem you wold have to do a new import anyway. -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Frederik Ramm frede...@remote.org wrote: this is about a bug in osm2pgsql that will affect you if you * run diff updates (--slim --append) * run PostgreSQL 8.4 or 9.0 * use an osm2pgsql SVN revision = 25198 (2011-01-31) and 26475 (today) * are not using non-standard index tablespaces (-i or --tablespace-slim-index) Removing the double negative here this will read as: If you are using standard index tablespaces which is the default if no tablespace Option is given! I supose this bug is the cause for the problems many people (including myself) where talking abount on this list in the last few months. Sven -- Trotz der zunehmenden Verbreitung von Linux erfreut sich der Bär, und - dank Knut - insbesondere der Eisbär, deutlich größerer Beliebtheit als der Pinguin. (Gefunden bei http://telepolis.de/) /me is giggls@ircnet, http://sven.gegg.us/ on the Web ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Hi, Sven Geggus wrote: Removing the double negative here this will read as: If you are using standard index tablespaces Yes, that's what I said ;) I supose this bug is the cause for the problems many people (including myself) where talking abount on this list in the last few months. I became suspicious when a system of mine had slow updates but it wasn't disk-bound at all. What you expect to see during a diff update on anything but the fastest mega-SSD boxes is that one CPU is in I/O wait most of the time, which wasn't the case on my box - updates were taking long but only a small portion of that time was spent with I/O. To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes with DROP INDEX planet_osm_ways_nodes; DROP INDEX planet_osm_rels_parts; CREATE INDEX planet_osm_ways_nodes ON planet_osm_ways USING gin (nodes) WITH (fastupdate=off); CREATE INDEX planet_osm_rels_parts ON planet_osm_rels USING gin (parts) WITH (fastupdate=off); This will take a while but it is possible to do that without much adverse effect on rendering (i.e. the DB will slow down because it re-generates the index but these indexes are not used for rendering). Don't attempt to apply a diff update without these indexes in place. Bye Frederik -- Frederik Ramm ## eMail frede...@remote.org ## N49°00'09 E008°23'33 ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] slow diff updates / FASTUPDATE / osm2pgsql bug
Frederik Ramm frede...@remote.org wrote: To be a bit clearer on the procedure to fix things: Either re-import, or re-create indexes Hm, am I right in the assumption that this problem will affect any index of type gin not just the two created by osm2pgsql? I wonder if my hstore tags-column indexes should also use FASTUPDATE=off. Sven -- /* * Wirzenius wrote this portably, Torvalds fucked it up :-) */(taken from /usr/src/linux/lib/vsprintf.c) /me is giggls@ircnet, http://sven.gegg.us/ on the Web ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
