2

I'm using about 30 more or less complex PostGIS layer in my QGIS project. Some of them are PlanetOSM layer generated with osm2pgsql.

The Project loads fast (ca 5 sec). The canvas update by moving or zooming is also fast (2 sec per update). My computer have got enough resources.

Overall, everything seems okay, but sometimes after doing or saving changes on a layer, switching between edit mode or by adding or removing a layer, QGIS freezes for a moment (up to 2 minutes). While hanging, one of my CPU cores is at 100%. It seems, that it does not matter, how big the layer is. Sometimes also adding a empty Postgis layer causes that freezing.

EDIT: Sometimes changes are not saved or the DB does not update the changes.

After restarting the Project, everything works fine... for the first edits.

What could be the problem? How can I monitor whats going on in my database?

System configuration: Xubuntu 14.04, QGIS 2.6, PostgreSQL 9.3.5, PostGIS 2.1.2 Intel i5-4570 CPU @ 3.20GHz, 8GB RAM

6
  • 1
    Are you working with a local database or is it on a server in a network?
    – underdark
    Dec 6, 2014 at 21:08
  • the database is local
    – MartinMap
    Dec 6, 2014 at 21:15
  • 1
    Which process spikes to 100%? Postgres? Cuz I have similar issues , when doing database intense stuff, like creating indexes doing calculations on geometries.
    – Benjamin
    Dec 8, 2014 at 15:35
  • 1
    Have you tried using the Offline Editing plugin to edit your layers? Although your database is local, it may help to utilize a SpatiaLite database to speed things up.
    – Joseph
    Dec 8, 2014 at 15:39
  • Its QGIS taking 100% of the CPU core.
    – MartinMap
    Dec 8, 2014 at 16:15

2 Answers 2

2
+25

Possible Solution: Tuning PostgreSQL

As I said before in the comments, I had the problem of having the I/O on my SSD maxed out, blocking the System. I tuned PostgreSQL with the following config file and it works much better for me now.

Best is to copy paste the code into a new file, place it where the postgres.conf is located (Gentoo /etc/postgresql-9.3/) and call it postgres.conf.include. In postgres.conf at the bottom place an include = 'postgresql.conf.include'

# Logging
log_destination = 'csvlog'
log_directory = 'pg_log'
logging_collector = off
log_filename = 'postgres-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 1GB
#log_min_duration_statement = 250ms
#log_checkpoints = on
#log_connections = on
#log_disconnections = on
#log_lock_waits = on
#log_temp_files = 0

# Memory
shared_buffers = 1GB
work_mem = 256MB
maintenance_work_mem = 512MB
effective_cache_size = 4GB

# Checkpoint ( When to write to disk )
wal_buffers = 16MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min
checkpoint_segments = 8

random_page_cost = 1.1

# Import only!
#autovacuum = off
#fsync = off
#synchronous_commit = off
#full_page_writes = off
#logging_collector = off

Then restart the PostgreSQL Service and check if the system still freezes.

Some quick explaination to the settings: I turned off logging, raised the work_mem for the geospatial calculations, upped the checkpoints to 8 / or 10 min with maxsize 16MB. I also set the parameters according to your Memory (~50% max use)

2
  • That is very nice from you, but I still have the same problems! Maybe, working is a little faster now, but its still hanging.
    – MartinMap
    Dec 8, 2014 at 20:21
  • 1
    Well was worth a try. Also you asked for DB Monitoring tools. Have a look at dba.stackexchange.com/questions/2770/…
    – Benjamin
    Dec 8, 2014 at 23:01
0

That sounds very much like a plugin related issue. Try disabling any plugins you have enabled and see if it resolves the hang.

1
  • I tryed that. I disabled all plugins but the problem is the same.
    – MartinMap
    Dec 1, 2014 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.