[lug] Speed up osm2pgsql

[Bear Giles]

Do you need the heavy resources for just for the data import or is an
ongoing concern? If the former it may be cheaper to just rent a hefty
server for a few days. If the latter you might be able to reduce your
system load by adding some indexes. PostgreSQL is smart enough to use
index-only selects. It looks odd to create indexes with a few extra columns
but it can give you a big performance boost if you're often only loading
the same few fields out of a much larger row.

I'm also tempted to ask about putting it into a Hadoop cluster but that's
mostly because I've been so focused on then recently.

On Thu, Feb 8, 2018 at 4:53 PM, Zan Lynx <zlynx at acm.org> wrote:

> On February 8, 2018 12:08:35 PM MST, 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
>>
>>
> I just thought of this but maybe an Optane P900 drive? For your swap. The
> drive endurance is much higher than Flash. It is lower latency too.
>
> They are expensive but probably cheaper than 256 GB of RAM.
>
> _______________________________________________
> 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/339c2d95/attachment.html>