Performance issue with PostGIS >= 2.0

[horacer]

Hi everybody,

after several weeks of debugging our new mapproxy setup we came to the conclusion that there are enormous performance issues when running PostGIS version 2.0 upwards in combination with recent imposm / mapproxy versions and PostgreSQL 9.1 / 9.3. Symptoms are heavy disk I/O when starting the seed process as well as using the demo. Tiles are generated but way to slow. Our SSD RAID0 array is instantly hammered with max throttle heavy writes (~400 MB/s) caused by PostgreSQL processes. We did a lot of PostgreSQL tuning as well, so that in almost no circumstances tmp files are written. To achieve that, we had to raise work_mem to tremendous 2 GB though. We did some EXPLAIN ANALYZE on some queries before and that's the value some queries came up with (almost 2 GB). That said, it's anything but normal.

We used the little imposm-psqldb script to create the database with the appropriate switches.

We also tried each and every possible combination (Distros, PostgreSQL versions) and we finally ended up using CentOS (that's what we wanted) for our new setup with PostgreSQL 9.1 and PostGIS 1.5.8 from the PostgreSQL upstream repository.

Anybody else observed this kind of behavior?

Greetings, Hendrik

[horacer]

Definitely related to MapProxy, not Imposm. Will recreate it there...sorry.