Also, did you disable fsync? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonah H. Harris Sent: Friday, September 20, 2002 10:15 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [HACKERS] Improving speed of copy
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Shridhar Daithankar Sent: Friday, September 20, 2002 9:52 AM To: [EMAIL PROTECTED] 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.. Bye Shridhar -- 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 [EMAIL PROTECTED]) ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
