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.
