How to add big amounts of data into database?
How to add big amounts of data into database?
От:
"Olonichev Sergei" <olonichev@scnsoft.com>
Дата:
Hello, Can anyone help me how to add big amount of data into database? I tried COPY, first the addition speed was good, but after each minute it became worser and worser. You can see the table illustrating this below. There were ~10,000,000 records. There was no index created on table. Table was locked in exclusive mode. With the best regards, Sergei Table: First column is size of table in kilobytes. Second - speed of COPY command. Size, Kb | Speed, Kb -------------------- 137416 139856 2440 142260 2404 144624 2364 146936 2312 149220 2284 151460 2240 153672 2212 155840 2168 157988 2148 160108 2120 162176 2068 164248 2072 166284 2036 168300 2016 170312 2012 172296 1984 174260 1964 176196 1936 178112 1916 180000 1888 181868 1868 183708 1840 185532 1824 187336 1804 189128 1792 190900 1772 192668 1768 194432 1764 196152 1720 197884 1732 199580 1696 201260 1680 202952 1692 204608 1656 206276 1668 207916 1640 209564 1648 211192 1628 212800 1608 214412 1612 216004 1592 217596 1592 219160 1564 220736 1576 222312 1576 223860 1548 225412 1552 226928 1516 228496 1568 230000 1504 231500 1500 232964 1464 234276 1312 235112 836 236584 1472 238040 1456 239508 1468 240980 1472 242500 1520 243568 1068 244632 1064 245784 1152 246904 1120 248340 1436 249772 1432 251232 1460 252672 1440 254096 1424 255524 1428 256948 1424 258444 1496 259912 1468 261024 1112 262152 1128 263460 1308 264860 1400 266276 1416 267688 1412 269096 1408 270488 1392 271876 1388 273300 1424 274612 1312 275684 1072 276920 1236 278136 1216 279344 1208 280356 1012 281212 856 281964 752 282652 688 283756 1104 285104 1348 286448 1344 287784 1336 289132 1348 290460 1328 291860 1400 292832 972 293712 880 294464 752 295424 960 296708 1284 297956 1248 299156 1200 300156 1000 300944 788 302240 1296 303104 864 304296 1192 305540 1244 306812 1272 308068 1256 309048 980 309792 744
Re: How to add big amounts of data into database?
От:
"Olonichev Sergei" <olonichev@scnsoft.com>
Дата:
----- Original Message ----- From: "Andrew Sullivan" To: "Olonichev Sergei" Sent: 24 мая 2002 г. 18:50 Subject: Re: [GENERAL] How to add big amounts of data into database? > On Fri, May 24, 2002 at 05:30:09PM +0300, Olonichev Sergei wrote: > > Hello, > > > > Can anyone help me how to add big amount of data into database? > > > > I tried COPY, first the addition speed was good, but after each minute it > > became worser and worser. > > Do you have the indexes still on the table? If so, drop them and > then recreate them afterwards. > > A > > > You can see the table illustrating this below. > > > > There were ~10,000,000 records. !!! > > There was no index created on table. As I mentioned before no indexes were created. BR, Sergei
Re: How to add big amounts of data into database?
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
"Olonichev Sergei" 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
