[lug] Speed up osm2pgsql

[Louis Krupp]

A random and mostly uninformed question:

Would it make sense to extract the data, do the appropriate sort(s), and
then reload the database? Many years ago, the accepted technique for
loading ISAM tables involved sorting the data in reverse key order and then
inserting records. Of course, one of the arguments to the built-in system
sort told it how many tapes to use...

Louis

On Thu, Feb 8, 2018 at 12:08 PM, Lee Woodworth <blug-mail at duboulder.com>
wrote:

> Populating a postgresql database for open street map uses 230GB
> of virtual address space. The swap device is very active with
> lots of writes at an average rate of 20MB/s.
>
> The source parsing phase was going to take 2+ weeks using a
> regular disk for swap. Changing to an ssd speeds things but
> writes 1.4TB (actual device writes) out of a 75TB TBW. The
> wear level reported by smartctl went from 0 to 10 just for
> for this phase.
>
> The post-processing phase is going very slow during a database
> sort where the db is on a raid1. Does anybody know of knobs to
> tweak to speed up larger-than-memory table sorts (100GB)? Or do we
> just need to use an ssd and bite the bullet on having to replace
> them frequently?
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: irc.hackingsociety.org port=6667 channel=#hackingsociety
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lug.boulder.co.us/pipermail/lug/attachments/20180208/268b0fb1/attachment.html>