This did not change anything, and the query still halted on the same year.
Like this:
# For each year calculate the distance to border and insert into the borddist table
yearlist = range(1946, 2009, 1)
for x in yearlist:
db1 = psycopg2.connect("host=192.168.1.186 dbname=priogrid user=postgres password=postgres")
cur = db1.cursor()
print str(time.ctime())+ " Creating borddist for year "+str(x)+"."
cur.execute("INSERT INTO borddist(gid, gwcode, gridyear, borddist) SELECT a.gid, a.gwcode, "+str(x)+", MIN(ST_Distance(ST_Transform(a.centroid, 954010), ST_Transform(b.geom, 954010)))/1000 AS borddist \n" \
"FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+" \n" \
"and a.gwcode = c.gwcode and st_intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")
db1.commit()
cur.close()
db1.close()
db1.commit()
print str(time.ctime())+ " Done"
cur.close()
db1.close()
I also followed your suggestion to not write any data. Just do a select, without any select into or insert into.
The same problem occurred.
Script:
cur.execute("SELECT a.gid, a.gwcode, "+str(x)+", "\
"MIN(ST_Distance(a.centroid, b.geom)) "\
"FROM priogridall_geom a, cshapes b, cshapes c WHERE a.gwcode != b.gwcode AND b.gwsyear <= "+str(x)+" AND b.gweyear >= "+str(x)+" "\
"AND a.gwcode = c.gwcode and ST_Intersects(b.geom, c.geom) AND a.gridyear = "+str(x)+" GROUP BY a.gid, a.gwcode;")
db1.commit()
Thank you very much for looking into this. I have used over two weeks to try to figure this out.
The only thing i can do is to run the script for 1/2 the years, then restart the server "service postgresql restart" and then run it for the next 1/2.
Some memory and cpu information.
Here is how it looks in free -m and top when the script have halted.
total used free shared buffers cached
Mem: 5977 5371 605 0 139 4735
-/+ buffers/cache: 495 5481
Swap: 6075 1 6074
top - 09:51:07 up 1 day, 18:44, 2 users, load average: 1.88, 1.32, 1.20
Tasks: 165 total, 2 running, 162 sleeping, 0 stopped, 1 zombie
Cpu(s): 53.2%us, 1.3%sy, 0.0%ni, 45.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 6120848k total, 5505868k used, 614980k free, 143004k buffers
Swap: 6221820k total, 1468k used, 6220352k free, 4849556k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
7810 postgres 20 0 2264m 1.0g 1.0g R 100 18.0 988:09.39 postgres
1417 andreas 20 0 163m 39m 14m S 3 0.7 3:27.13 compiz
907 root 20 0 57092 34m 10m S 2 0.6 1:48.73 Xorg
7088 andreas 20 0 93012 15m 10m S 2 0.3 0:02.80 gnome-terminal
10772 andreas 20 0 204m 96m 23m S 2 1.6 0:19.88 chromium-browse
1525 andreas 20 0 100m 15m 9548 S 1 0.3 5:11.16 unity-panel-ser
10675 andreas 20 0 308m 50m 29m S 1 0.8 0:07.55 chromium-browse
1088 root 20 0 9420 1632 964 S 0 0.0 0:07.01 nmbd
1389 andreas 20 0 5552 2740 700 S 0 0.0 1:17.77 dbus-daemon
10825 andreas 20 0 35836 19m 6276 S 0 0.3 0:02.94 idle-python2.6
1 root 20 0 3040 1780 1220 S 0 0.0 0:02.16 init
2 root 20 0 0 0 0 S 0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 S 0 0.0 0:07.21 ksoftirqd/0
6 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0
7 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0 0.0 0:03.00 ksoftirqd/1
11 root 0 -20 0 0 0 S 0 0.0 0:00.00 cpuset
After i restart the postgresql service:
total used free shared buffers cached
Mem: 5977 4319 1657 0 139 3687
-/+ buffers/cache: 492 5484
Swap: 6075 1 6074
top - 09:52:33 up 1 day, 18:46, 2 users, load average: 1.42, 1.33, 1.22
Tasks: 164 total, 1 running, 162 sleeping, 0 stopped, 1 zombie
Cpu(s): 1.3%us, 0.3%sy, 0.0%ni, 98.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 6120848k total, 4430204k used, 1690644k free, 143092k buffers
Swap: 6221820k total, 1468k used, 6220352k free, 3776880k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
907 root 20 0 52996 30m 6728 S 1 0.5 1:49.78 Xorg
1417 andreas 20 0 163m 39m 14m S 1 0.7 3:28.43 compiz
194 root 20 0 0 0 0 S 0 0.0 0:48.68 usb-storage
1447 root 20 0 5564 1000 712 S 0 0.0 0:23.28 udisks-daemon
1525 andreas 20 0 100m 15m 9548 S 0 0.3 5:11.66 unity-panel-ser
1583 andreas 20 0 33552 16m 8984 S 0 0.3 0:03.21 applet.py
7039 andreas 20 0 44240 23m 6304 S 0 0.4 0:04.51 idle-python2.6
7088 andreas 20 0 93264 15m 10m S 0 0.3 0:03.28 gnome-terminal
1 root 20 0 3040 1780 1220 S 0 0.0 0:02.16 init
2 root 20 0 0 0 0 S 0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 S 0 0.0 0:07.21 ksoftirqd/0
6 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/0
7 root RT 0 0 0 0 S 0 0.0 0:00.00 migration/1
9 root 20 0 0 0 0 S 0 0.0 0:03.00 ksoftirqd/1
2011/10/11 Sandro Santilli <strk@xxxxxxxxxx>
On Tue, Oct 11, 2011 at 05:12:24PM +0200, Andreas Forø Tollefsen wrote:But you mentioned you had stopped the backend and restarted for each year ?
> Hi Sandro,
>
> What i find strange is that it stops processing at different years on my
> desktop and my laptop. While my desktop stops processing at 1980, my slower
> laptop goes on to 1991 before halting.
> I also tried with different postgresql.conf shared_buffers settings without
> making any difference.
> Therefore it is hard to reproduce this for a single year. I can easily
> process 1980 or 1991 if just running the script for that year.
Does the problem still occur if you avoid writing any table (could be an I/O
issue) ?
--strk;
() Free GIS & Flash consultant/developer
/\ http://strk.keybit.net/services.html
_______________________________________________
postgis-users mailing list
postgis-users@xxxxxxxxxxxxxxxxxxxxxxx
http://postgis.refractions.net/mailman/listinfo/postgis-users
