Thread: Re: [ADMIN] COPY slows down; is it normal?
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.
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
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
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
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 -------------------------------------------------------------------------------