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 with handmade RPMs (that's what we wanted) for our new setup with PostgreSQL 9.1 and PostGIS 1.5.8 from the PostgreSQL upstream repository.

This might feel a bit sluggish but here you're going to find the PostGIS IRC chat log with quiet a lot extended debugging and several posts to query logs as a comparison between both versions.

https://gist.github.com/loomsen/da39893428677f059d3d

Anybody else observed this kind of behavior?

Greetings, Hendrik

[quiqua]

After talking to your colleague, it seems to be related to mapnik since mapproxy does no real rendering of tiles (except for some blank/transparent tiles)

[horacer]

Yep, opened an issue over there and will close this one :)