Skip site navigation (1) Skip section navigation (2)

Re: [ADMIN] COPY slows down; is it normal?

From: "Gene Selkov Jr(dot)" <selkovjr(at)mcs(dot)anl(dot)gov>
To: Daniele Orlandi <daniele(at)orlandi(dot)com>
Cc: pgsql-admin(at)postgreSQL(dot)org, pgsql-hackers(at)postgreSQL(dot)org
Subject: Re: [ADMIN] COPY slows down; is it normal?
Date: 1998-10-05 05:21:27
Message-ID: 199810050620.BAA11968@antares.mcs.anl.gov (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-hackers
Thanks Daniele, thanks to everyone who replied, and to those who considered...

Nothing helps. Disabling fsync does speed up COPY, but not with the table in question. It does not even seem to be the question of speed anymore. Even if COPY terminates, the table and the database containnig it become unusable: queries run awfully long and return nothing. Looks like I managed to make a killer table, because even a fraction of that table kills the database it is being copied to.

Just in case it matters, the table I am trying to create is this:

CREATE TABLE key(
       tag char(3), 
       id char(12), 
       rel char(70), 
       pos char(16), 
       item int2, 
       unit char(48),
       data text
       );

Here is the fragment of the data (can provide the whole table for testing):

KM      APS94172-01     CO:KM:KT:VM:MA:KC:CEF:ACC:1     66:15:66:19     1       mmol/l  0.03
KT      APS94172-01     CO:KM:KT:VM:MA:KC:CEF:ACC:1     66:23:66:24     1       NULL    H
VM      APS94172-01     CO:KM:KT:VM:MA:KC:CEF:ACC:1     66:29:66:36     1       umol/min/mg     86
MA      APS94172-01     CO:KM:KT:VM:MA:KC:CEF:ACC:1     66:40:66:45     1       1/min   1.9E3
OR      APS94172-01     NULL    16:4:16:17      1       NULL    `Glicine max`
OCN     APS94172-01     NULL    17:4:17:11      1       NULL    soybean
PS      APS94172-01     NULL    32:5:32:1208    1       NULL    T=4(C)
PS      APS94172-01     NULL    32:5:32:1208    10      NULL    Blue Sepharose CL-6B dye affinity chromatography, elution with 50(mmol/l) imidazole buffer, pH = 7.2, 10(mmol/l) 2-mercaptoethanol, 25(%) glycerol, 500(mmol/l) KCl, 2.0(mmol/l) folic acid, peak fraction

--Gene


Daniele Orlandi wrote:
>
> Gene Selkov Jr. wrote:
> > 
> > Hi,
> > 
> > I am watching the progress of a COPY from a 2 megarow table. It seems to slow 
> > down almost exponentially. The database file grew at about 2 megabytes a 
> > minute in the beginning and now, 20 hours since started, it grows at 80 
> > kilobytes a minute. The table has 7 columns of char and int types, the last 
> > column is text. The scene is a dual 400MHz i586 running RedHat 5.1 and 
> > postgres 6.3.2, as originally distributed, no patches. As far as I can tell 
> > by 'tail | strings | tail' on the database file, it is more than halfway 
> > there, but such exponential slowdown makes me anxious. Other users will have 
> > to take vacation:
> 
> I had the same behaviour trying to copy about 250K records. Just follow the
> suggestions in the documentation (remove indexes, disable fsync etc...).
> 
> It would be nice to understand why this happens, it could be a bug or something
> that could be improved.

Responses

pgsql-hackers by date

Next:From: Billy G. AllieDate: 1998-10-05 05:22:38
Subject: Problem dumping PL/pgsql functions.
Previous:From: Marc G. FournierDate: 1998-10-05 05:05:14
Subject: Re: [HACKERS] Open 6.4 items

pgsql-admin by date

Next:From: Malcolm BeattieDate: 1998-10-05 11:03:11
Subject: FATAL 1: Backend cache invalidation initialization failed
Previous:From: Ian JohnstonDate: 1998-10-03 06:48:17
Subject: problem with Non US date style

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group