Re: How to add big amounts of data into database?
От | Tom Lane |
---|---|
Тема | Re: How to add big amounts of data into database? |
Дата | |
Msg-id | 21720.1022279098@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | How to add big amounts of data into database? ("Olonichev Sergei" <olonichev@scnsoft.com>) |
Список | pgsql-general |
"Olonichev Sergei" <olonichev@scnsoft.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@rh1 tgl]$ createdb test1 CREATE DATABASE [tgl@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@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@rh1 base]$ while (( 1 )); do du -k 12293914; sleep 10; done | tee sizes and finally [tgl@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
В списке pgsql-general по дате отправления: