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

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

From
"Gene Selkov Jr."
Date:
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
questionof speed anymore. Even if COPY terminates, the table and the database containnig it become unusable: queries
runawfully long and return nothing. Looks like I managed to make a killer table, because even a fraction of that table
killsthe 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.

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

From
Bruce Momjian
Date:
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


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

From
"Gene Selkov Jr."
Date:
Bruce Momjian wrote:

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

It is 6.3.2, no patches applied. I will check it in 6.4, the latest versions I can see are

 postgresql.snapshot.tar.gz                      4091 Kb    Mon Oct  5 11:01:00 1998
 postgresql.v6.4-BETA1.tar.gz                    4057 Kb    Mon Sep 14 08:40:00 1998

Which of these should I try?

--Gene

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

From
"Marc G. Fournier"
Date:
On Tue, 6 Oct 1998, Gene Selkov Jr. wrote:

> Bruce Momjian wrote:
>
> > What version is this?  I would be interested to see if you see the same
> > thing in 6.4 beta.
> >
>
> It is 6.3.2, no patches applied. I will check it in 6.4, the latest versions I can see are
>
>  postgresql.snapshot.tar.gz                      4091 Kb    Mon Oct  5 11:01:00 1998
>  postgresql.v6.4-BETA1.tar.gz                    4057 Kb    Mon Sep 14 08:40:00 1998
>
> Which of these should I try?

    Wait a day or so...I'm just testing out a BETA2 right now on a
couple of different platforms, and hope to have a new tar file up
tomorrow...

Marc G. Fournier                               scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org                       ICQ#7615664


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

From
Daniele Orlandi
Date:
Bruce Momjian wrote:
>
> What version is this?  I would be interested to see if you see the same
> thing in 6.4 beta.

Postgresql 6.4, 13-Oct snapshot, importing about80MB of data *without* indexes
from a pg_dump. This is the size of the database file minute per minute starting
from about 13MB already imported:

-rw-------   1 postgres postgres 13434880 Oct 14 00:49 log2
-rw-------   1 postgres postgres 16015360 Oct 14 00:50 log2
-rw-------   1 postgres postgres 22765568 Oct 14 00:51 log2
-rw-------   1 postgres postgres 25034752 Oct 14 00:52 log2
-rw-------   1 postgres postgres 29212672 Oct 14 00:53 log2
-rw-------   1 postgres postgres 32923648 Oct 14 00:54 log2
-rw-------   1 postgres postgres 35717120 Oct 14 00:55 log2
-rw-------   1 postgres postgres 38363136 Oct 14 00:56 log2
-rw-------   1 postgres postgres 41394176 Oct 14 00:57 log2
-rw-------   1 postgres postgres 44310528 Oct 14 00:58 log2
-rw-------   1 postgres postgres 47063040 Oct 14 00:59 log2
-rw-------   1 postgres postgres 49176576 Oct 14 01:00 log2
-rw-------   1 postgres postgres 51142656 Oct 14 01:01 log2
-rw-------   1 postgres postgres 53305344 Oct 14 01:02 log2
-rw-------   1 postgres postgres 55394304 Oct 14 01:03 log2
-rw-------   1 postgres postgres 57581568 Oct 14 01:04 log2
-rw-------   1 postgres postgres 59236352 Oct 14 01:05 log2
-rw-------   1 postgres postgres 60784640 Oct 14 01:06 log2
-rw-------   1 postgres postgres 62578688 Oct 14 01:07 log2
-rw-------   1 postgres postgres 64389120 Oct 14 01:08 log2
-rw-------   1 postgres postgres 66199552 Oct 14 01:09 log2
-rw-------   1 postgres postgres 67723264 Oct 14 01:10 log2
-rw-------   1 postgres postgres 69140480 Oct 14 01:11 log2
-rw-------   1 postgres postgres 70656000 Oct 14 01:12 log2
-rw-------   1 postgres postgres 72122368 Oct 14 01:13 log2
-rw-------   1 postgres postgres 73596928 Oct 14 01:14 log2
-rw-------   1 postgres postgres 74883072 Oct 14 01:15 log2
-rw-------   1 postgres postgres 76201984 Oct 14 01:16 log2
-rw-------   1 postgres postgres 77692928 Oct 14 01:17 log2
-rw-------   1 postgres postgres 79175680 Oct 14 01:18 log2
-rw-------   1 postgres postgres 80650240 Oct 14 01:19 log2
-rw-------   1 postgres postgres 81821696 Oct 14 01:20 log2
-rw-------   1 postgres postgres 83042304 Oct 14 01:21 log2
-rw-------   1 postgres postgres 83574784 Oct 14 01:22 log2

As you may see, the speed decreases as the table size increases.

Bye!

--
 Daniele

-------------------------------------------------------------------------------
"It's God.  No, not Richard Stallman, or Linus Torvalds, but God."
(By Matt Welsh)
-------------------------------------------------------------------------------
 Dal 28-09-1998, con un solo POP abbiamo fatto guadagnare a telecom italia
 1769237 Lire solo in scatti alla risposta. Non male eh ?
-------------------------------------------------------------------------------
 Daniele Orlandi - Utility Line Italia - http://www.orlandi.com
 Via Mezzera 29/A - 20030 - Seveso (MI) - Italy
-------------------------------------------------------------------------------