COPY taking forever, I just want to end it!
I am trying to copy 5917328 tuples into a single table (I am running it
through a perl script that makes a system call to psql -c sending in the
command). This now seems to still be running 19.5 hours later which is not
reasonable. I am not sure why this is taking so long....I didn't disable
indices so this may be part of the problem.....
However, my questions are:
1- do I kill off all the process below?
[root@india pgsql7.2]# ps -auxwww | grep post
postgres 9316 0.0 0.4 5268 1096 ? S Jul08 0:00
/usr/local/pgsql7.2/bin/postmaster -i -D /usr/local/pgsql7.2/data/
postgres 9317 0.0 0.1 6260 344 ? S Jul08 0:00 postgres:
stats buffer process
postgres 9318 0.0 0.2 5340 540 ? S Jul08 0:00 postgres:
stats collector process
postgres 9721 7.1 1.0 5676 2624 ? D 03:15 82:14 postgres:
beth array [local] COPY
postgres 11202 0.0 1.2 5904 3076 ? S 21:25 0:00 postgres:
beth array [local] idle
root 11279 0.0 0.4 2264 1048 pts/5 S 21:51 0:00 su postgres
postgres 11281 0.0 0.5 2448 1400 pts/5 S 21:51 0:00 bash
root 11406 0.0 0.2 1624 616 pts/5 S 22:25 0:00 grep post
2- Also-why is 11202 idle?
3- Would pg_ctl stop -m fast be a way to end a problem like this?
Thanks-Beth
-----Original Message-----
Ian,
Thank you so much for rapidly responding!
The file seems to have the correct fields (it is a dump from MySQL
database). Where does this NOTICE log to, btw? I didn't start the
postmaster with logging....
What about just killing the processes? How can I end this?
-Beth
Show quoted text
From: Ian Harding [mailto:ianh@tpchd.org]
Sent: Wednesday, July 24, 2002 10:54 AM
To: beth@vizxlabs.com
Subject: Re: [GENERAL] COPY taking forever, I just want to end it!I have found that copy takes forever when there are extra
fields in the data. Copy logs a NOTICE for each record,
something about "Extra junk ignored". That may take up some
of the time, but the delay seems way longer than just logging.Are there such lines in your log?
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: iharding@tpchd.orgWe have only two things to worry about: That things will never get
back to normal, and that they already have."Beth Gatewood" <beth@vizxlabs.com> 07/24/02 10:48AM >>>
I am trying to copy 5917328 tuples into a single table (I am
running it
through a perl script that makes a system call to psql -c
sending in the
command). This now seems to still be running 19.5 hours
later which is not
reasonable. I am not sure why this is taking so long....I
didn't disable
indices so this may be part of the problem.....
However, my questions are:
1- do I kill off all the process below?
[root@india pgsql7.2]# ps -auxwww | grep post
postgres 9316 0.0 0.4 5268 1096 ? S Jul08 0:00
/usr/local/pgsql7.2/bin/postmaster -i -D /usr/local/pgsql7.2/data/
postgres 9317 0.0 0.1 6260 344 ? S Jul08
0:00 postgres:
stats buffer process
postgres 9318 0.0 0.2 5340 540 ? S Jul08
0:00 postgres:
stats collector process
postgres 9721 7.1 1.0 5676 2624 ? D 03:15
82:14 postgres:
beth array [local] COPY
postgres 11202 0.0 1.2 5904 3076 ? S 21:25
0:00 postgres:
beth array [local] idle
root 11279 0.0 0.4 2264 1048 pts/5 S 21:51
0:00 su postgres
postgres 11281 0.0 0.5 2448 1400 pts/5 S 21:51 0:00 bash
root 11406 0.0 0.2 1624 616 pts/5 S 22:25
0:00 grep post2- Also-why is 11202 idle?
3- Would pg_ctl stop -m fast be a way to end a problem like this?
Thanks-Beth
---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Import Notes
Reply to msg id not found: sd3e87c5.007@mail.tpchd.org | Resolved by subject fallback
"Beth Gatewood" <beth@vizxlabs.com> writes:
What about just killing the processes? How can I end this?
SIGINT to the backend should work fine.
I can't think of a reason for COPY to be so slow unless you have
triggers or foreign keys on the table --- any of those?
regards, tom lane
Thanks to all who so kindly replied! Actually the COPY ended after about 20
hours...not so great.
There are no FK...There is a primary key and 3 non-unique indices on the
table. I am going to drop all of those and try it again! The data seems to
be well structured (same number of fields in the table vs the tab delimited
fields coming in...)
Thanks!
Beth
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
Sent: Wednesday, July 24, 2002 1:41 PM
To: Beth Gatewood
Cc: 'Ian Harding'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] COPY taking forever, I just want to end it!"Beth Gatewood" <beth@vizxlabs.com> writes:
What about just killing the processes? How can I end this?
SIGINT to the backend should work fine.
I can't think of a reason for COPY to be so slow unless you have
triggers or foreign keys on the table --- any of those?regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster