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

How to add big amounts of data into database?

From
"Olonichev Sergei"
Date:
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?

From
"Olonichev Sergei"
Date:
----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: "Olonichev Sergei" <olonichev@scnsoft.com>
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?

From
Tom Lane
Date:
"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