Re: How to add big amounts of data into database?

"Olonichev Sergei" <olonichev(at)scnsoft(dot)com> writes:
> I tried COPY, first the addition speed was good, but after each minute it
> became worser and worser.

Maybe it would help to try ext3. I tried to reproduce this report and
couldn't, but I was using an ext3 filesystem.

System: stock Red Hat Linux 7.2, IDE drive, ext3 filesystem (or so says
/proc/mounts --- AFAIR I just used the installation default).
Postmaster options were "-i -F -B 1024", stock postgresql.conf.

Test procedure:

[tgl(at)rh1 tgl]$ createdb test1
CREATE DATABASE
[tgl(at)rh1 tgl]$ psql test1
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
test1=# create table foo (f1 int, f2 int);
CREATE TABLE
test1=# \q

[tgl(at)rh1 tgl]$ perl -e 'for ($i=0; $i < 100000000; $i++) { print "$i\t$i\n"; }' | psql -c "copy foo from stdin" test1

In another window, having determined the OID of the database, I let this
run:

[tgl(at)rh1 base]$ while (( 1 )); do du -k 12293914; sleep 10; done | tee sizes

and finally

[tgl(at)rh1 base]$ awk '{print $1, $1-last; last=$1;}' sizes
2160 2160
39000 36840
76576 37576
109928 33352
143128 33200
178432 35304
214956 36524
252032 37076
288684 36652
325408 36724
361244 35836
396168 34924
433300 37132
468444 35144
504016 35572
539356 35340
572708 33352
608284 35576
643720 35436
679332 35612
714052 34720
749456 35404
784520 35064
819684 35164
855112 35428
890328 35216
925532 35204
960748 35216
995912 35164
1031332 35420
1065928 34596
1101276 35348
1135884 34608
1171136 35252
1205936 34800
1241244 35308
1276684 35440
1311984 35300
1346720 34736
1382092 35372
1417024 34932
1452224 35200
1487540 35316
1522916 35376
1558408 35492
1593708 35300
1625912 32204
1661276 35364
1696832 35556
1731632 34800
1767212 35580
1802656 35444
1837648 34992
1873044 35396
1908580 35536
1943376 34796
1978844 35468
2013852 35008
2048768 34916
2083936 35168
2115700 31764
2150228 34528
2185696 35468
2221148 35452
2254812 33664
2290296 35484
2325672 35376
2360580 34908
2395860 35280
2430200 34340
2465360 35160
2500388 35028
2535764 35376
2570856 35092
2606212 35356
2641316 35104
2674692 33376
2709248 34556
2744544 35296
2778964 34420
2814380 35416
2849824 35444
2884572 34748
2919740 35168
2954992 35252
2989704 34712
3024924 35220
3059404 34480
3094008 34604
3126528 32520
3161284 34756
3196488 35204
3231248 34760
3261724 30476
3294936 33212
3329984 35048
3364324 34340
3399428 35104
3433960 34532
3467584 33624
3502360 34776
3536972 34612
3571588 34616
3606736 35148
3641872 35136
3676940 35068
3709692 32752
3744676 34984
3779944 35268
3814968 35024
3849780 34812
3885168 35388
3919528 34360
3954112 34584
3988860 34748
4024028 35168
4057856 33828
4092456 34600
4126988 34532
4161572 34584
4196680 35108
4231276 34596
4266320 35044
4301304 34984
4330732 29428
4330732 0
4330732 0
-- test over here, obviously

So I'm not seeing any consistent slowdown as the table gets bigger.
At least not up to 4 gig or so.

This was using current development sources (CVS tip), but I have no
reason to think our 7.2 release would be any different on this test.

regards, tom lane