Thread: 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