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

Поиск
Список
Период
Сортировка
От Gene Selkov Jr.
Тема Re: [ADMIN] COPY slows down; is it normal?
Дата
Msg-id 199810050620.BAA11968@antares.mcs.anl.gov
обсуждение исходный текст
Ответы Re: [ADMIN] COPY slows down; is it normal?  (Bruce Momjian <maillist@candle.pha.pa.us>)
Список pgsql-admin
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.

В списке pgsql-admin по дате отправления:

Предыдущее
От: Greg Youngblood
Дата:
Сообщение: Please help with performance tuning on Postgres
Следующее
От: Malcolm Beattie
Дата:
Сообщение: FATAL 1: Backend cache invalidation initialization failed