Re: [ADMIN] COPY slows down; is it normal?
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 ---
Re: [ADMIN] COPY slows down; is it normal?
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 KbMon Oct 5 11:01:00 1998
postgresql.v6.4-BETA1.tar.gz4057 KbMon 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 [EMAIL PROTECTED]
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664
Re: [ADMIN] COPY slows down; is it normal?
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 the question of speed anymore. Even if COPY terminates, the table and the database containnig it become unusable: queries run awfully long and return nothing. Looks like I managed to make a killer table, because even a fraction of that table 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 NULL16:4:16:17 1 NULL`Glicine max` OCN APS94172-01 NULL17:4:17:11 1 NULLsoybean PS APS94172-01 NULL32:5:32:12081 NULLT=4(C) PS APS94172-01 NULL32:5:32:120810 NULLBlue Sepharose CL-6B dye affinity chromatography, elution with 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 [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[ADMIN] COPY slows down; is it normal?
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: PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND 14778 postgres 10 0 2840 2588 2124 R 0 98.2 1.0 1273m postgres Last time I tried to load the same table without the last column, COPY took 4 hours and hash on one of the columns took 7. Is there any room for improvement? Thanks, Gene
