Thread: 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 > 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.org > > We 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 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 > > > ---------------------------(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 >
"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 > -----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 >