Re: [postgis-users] Query slow down, never completes - Mailing list pgsql-general
| From | Andreas Forø Tollefsen |
|---|---|
| Subject | Re: [postgis-users] Query slow down, never completes |
| Date | |
| Msg-id | CAGMz7DkR+BpfgCMayBBz=4i9GE-xwh7TkwWqPNBoQnwNSv2MQA@mail.gmail.com Whole thread Raw |
| In response to | Re: [postgis-users] Query slow down, never completes (Sandro Santilli <strk@keybit.net>) |
| List | pgsql-general |
Yes, I did a version of the query where i terminated the connection in the loop after one year, and then reconnected to the server for each year in the query.
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@keybit.net>
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@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
pgsql-general by date: