Are you using copy within a transaction?
I don't know how to explain the size difference tho. I have never seen an
overhead difference that large. What type of MySQL tables were you using
and what version?
Have you tried this with Oracle or similar commercial database?
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Shridhar
Daithankar
Sent: Friday, September 20, 2002 9:52 AM
To: Pgsql-hackers@postgresql.org
Subject: [HACKERS] Improving speed of copy
Hi all,
While testing for large databases, I am trying to load 12.5M rows of data
from
a text file and it takes lot longer than mysql even with copy.
Mysql takes 221 sec. v/s 1121 sec. for postgres. For postgresql, that is
around
11.5K rows per second. Each tuple has 23 fields with fixed length of around
100
bytes
I wrote a programs which does inserts in batches but none of thme reaches
performance of copy. I tried 1K/5K/10K/100K rows in a transaction but it can
not cross 2.5K rows/sec.
The machine is 800MHz, P-III/512MB/IDE disk. Postmaster is started with 30K
buffers i.e. around 235MB buffers. Kernel caching paramaters are defaults.
Besides there is issue of space. Mysql takes 1.4GB space for 1.2GB text data
and postgresql takes 3.2GB of space. Even with 40 bytes per row overhead
mentioned in FAQ, that should come to around 1.7GB, counting for 40%
increase
in size. Vacuum was run on database.
Any further help? Especially if batch inserts could be speed up, that would
be
great..
ByeShridhar
--
Alone, adj.: In bad company. -- Ambrose Bierce, "The Devil's Dictionary"
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
