Re: [ADMIN] COPY slows down; is it normal? - Mailing list pgsql-admin

What version is this?  I would be interested to see if you see the same
thing in 6.4 beta.


>
> 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
thequestion of speed anymore. Even if COPY terminates, the table and the database containnig it become unusable:
queriesrun awfully long and return nothing. Looks like I managed to make a killer table, because even a fraction of
thattable 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,
elutionwith 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.
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026