Problem with copying data
Hi,
I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
"COPY FROM" into my database. But I'm waiting for the completion of this
job since more than 24h. I'm working under Linux and with "top" I can
see the two processes which should copy the data but most of the time
they are not working. Very seldom they are using some CPU time and then
get idle again.
I already tried restarting the Postgres server and I also restarted
Linux but nothing seems to work. Do you have any hints how I can find
out what's going on there and why the two processes are idle most of the
time?
Thanks,
Klaas
On Friday 06 April 2007 13:17, Klaas Dellschaft <klaasd@uni-koblenz.de>
wrote:
Hi,
I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
"COPY FROM" into my database. But I'm waiting for the completion of this
job since more than 24h. I'm working under Linux and with "top" I can
see the two processes which should copy the data but most of the time
they are not working. Very seldom they are using some CPU time and then
get idle again.
What does the wait % (%wa) say when they are "idle"? I would generally
assume you're io-bound on a large COPY, especially if the target table is
already indexed. 24-hours seems excessive, though, unless this is a
notebook drive or something.
--
99 percent of lawyers give the rest a bad name
Klaas Dellschaft wrote:
Hi,
I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
"COPY FROM" into my database. But I'm waiting for the completion of this
job since more than 24h. I'm working under Linux and with "top" I can
see the two processes which should copy the data but most of the time
they are not working. Very seldom they are using some CPU time and then
get idle again.
Are there indexes or foreign keys in the tables? Check constraints?
Other things we should know about?
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alan Hodgson <ahodgson@simkin.ca> writes:
On Friday 06 April 2007 13:17, Klaas Dellschaft <klaasd@uni-koblenz.de>
wrote:I'm currently trying to copy two large files (1.6 GB and 3.5 GB) with a
"COPY FROM" into my database. But I'm waiting for the completion of this
job since more than 24h. I'm working under Linux and with "top" I can
see the two processes which should copy the data but most of the time
they are not working. Very seldom they are using some CPU time and then
get idle again.
What does the wait % (%wa) say when they are "idle"? I would generally
assume you're io-bound on a large COPY, especially if the target table is
already indexed. 24-hours seems excessive, though, unless this is a
notebook drive or something.
If there's other things going on in the database, then another
possibility is that the COPY commands are blocked on locks.
I agree that I/O is the most likely time sink though.
regards, tom lane
Are there indexes or foreign keys in the tables? Check constraints?
Other things we should know about?
I think I found the problem. There were indexes on the tables which I
wanted to copy. I remembered the performance tip to add indexes after
copying the data when I saw the activity of my hard drive during
importing the data on my local computer. This feedback of the hard drive
LED was missing during the import on the server ;-)
Thanks,
Klaas