Обсуждение: Optimize for insertions?

I think that the fastest way that PostgreSQL can load data into a table is
using COPY.

Try exporting the data from mysql in a text format, for example
tab-delimited. Then, use COPY to load it. That should go *much* faster than
inserts.

If that is for some reason not a viable solution, break the inserts into
large blocks and make each one its own transaction (explicit begin/commit).
This tells postgres when to flush to disk, basically. This might not really
help you since you already turned off fsync.

Hope this helps,
    Jeff Davis

On Monday 26 November 2001 01:17 pm, you wrote:
> Hi,
>
> I'm trying to migrate a mysql database to postgresql.  I've got the tables
> all set up, but inserting the data from the mysql dumps is taking forever.
> I've got about 200 million rows in the big table (growing by about 1.5
> million per day), but at the 100 inserts/second I'm getting, this will take
> over 3 weeks.  MySQL on the same machine averages about 1100-1200
> inserts/second (no, I'm not running them both at the same time ;-)
>
> Any thoughts on how to tweak the postmaster for quick inserts?  I've got
> fsync turned off, but that's about all I've tried so far.
>
> I'm running postgresql 7.1.3 on Linux (Redhat 7.2 with ext3) with a  700MHz
> processor, 256MB RAM, and an 80GB IDE HD
>
> Thanks
> -partap

Hi,

What you probably want to do is export the mysql data into delimited text files
and use Postgresql's COPY command. It's much faster than doing straight
INSERT's.

If you have to do inserts for some reason, it might be helpful to group them
into chunks of N insert operations each, and put each chunk in a transaction.
I'm not sure what a good value for N would be though, and this wouldn't be
nearly as fast as the COPY command in any case.

HTH,

Wes Sheldahl



"Gurupartap Davis" <partap%yahoo.com@interlock.lexmark.com> on 11/26/2001
04:17:00 PM

To:   pgsql-general%postgresql.org@interlock.lexmark.com
cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject:  [GENERAL] Optimize for insertions?



Hi,

I'm trying to migrate a mysql database to  postgresql.  I've got the tables all
set up, but inserting the data from  the mysql dumps is taking forever.  I've
got about 200 million rows in the  big table (growing by about 1.5 million per
day), but at the 100  inserts/second I'm getting, this will take over 3 weeks.
MySQL on the  same machine averages about 1100-1200 inserts/second (no, I'm not
running them  both at the same time ;-)

Any thoughts on how to tweak the postmaster for  quick inserts?  I've got fsync
turned off, but that's about all I've tried  so far.

I'm running postgresql 7.1.3 on Linux (Redhat  7.2 with ext3) with a  700MHz
processor, 256MB RAM, and an 80GB IDE  HD

Thanks
-partap

1. Look into using the COPY command. This allows fast bulk imports.
2. Consider tweaking your transactions. If you are not using transactions
(possible considering you are migrating from MySQL), understand that
PostgreSQL creates an implicit transaction for each statement, unless an
explicit transaction is created. That means that you are dealing with the
additional overhead of 200 million transactions on top of the INSERTs
themselves. Of course, doing everything inside a single transaction can be
cumbersome too. Try stuffing a thousand or so INSERTs into a single
transaction and see if that speeds things up.
3. If you created indexes, drop them during your import, and readd them
after you've finished. Maintaining those slows things down a bit as well.

Hope this helps!

Greg

----- Original Message -----
From: Gurupartap Davis
To: pgsql-general@postgresql.org
Sent: Monday, November 26, 2001 4:17 PM
Subject: [GENERAL] Optimize for insertions?


Hi,

I'm trying to migrate a mysql database to postgresql.  I've got the tables
all set up, but inserting the data from the mysql dumps is taking forever.
I've got about 200 million rows in the big table (growing by about 1.5
million per day), but at the 100 inserts/second I'm getting, this will take
over 3 weeks.  MySQL on the same machine averages about 1100-1200
inserts/second (no, I'm not running them both at the same time ;-)

Any thoughts on how to tweak the postmaster for quick inserts?  I've got
fsync turned off, but that's about all I've tried so far.

I'm running postgresql 7.1.3 on Linux (Redhat 7.2 with ext3) with a  700MHz
processor, 256MB RAM, and an 80GB IDE HD

Thanks
-partap

Thanks for the advice, everyone...I've got my mysql dump files (about 300
50MB files) inserting at a rate of about 1 minute each now vs. 1.5 hours
each before(!)

I'm worried about creating indexes after all the data is in there,
though...I'm going to need 4-5 indexes.  Isn't that going to take a hella
long time? How are indexes created anyhow? I would assume a linear search
through every record in the table...would vacuum help at all?  how bout
vacuum analyze?



----- Original Message -----
From: "Gregory Wood" <gregw@com-stock.com>
To: "Gurupartap Davis" <partap@yahoo.com>
Cc: "PostgreSQL-General" <pgsql-general@postgresql.org>
Sent: Monday, November 26, 2001 3:09 PM
Subject: Re: [GENERAL] Optimize for insertions?


> 1. Look into using the COPY command. This allows fast bulk imports.
> 2. Consider tweaking your transactions. If you are not using transactions
> (possible considering you are migrating from MySQL), understand that
> PostgreSQL creates an implicit transaction for each statement, unless an
> explicit transaction is created. That means that you are dealing with the
> additional overhead of 200 million transactions on top of the INSERTs
> themselves. Of course, doing everything inside a single transaction can be
> cumbersome too. Try stuffing a thousand or so INSERTs into a single
> transaction and see if that speeds things up.
> 3. If you created indexes, drop them during your import, and readd them
> after you've finished. Maintaining those slows things down a bit as well.
>
> Hope this helps!
>
> Greg
>
> ----- Original Message -----
> From: Gurupartap Davis
> To: pgsql-general@postgresql.org
> Sent: Monday, November 26, 2001 4:17 PM
> Subject: [GENERAL] Optimize for insertions?
>
>
> Hi,
>
> I'm trying to migrate a mysql database to postgresql.  I've got the tables
> all set up, but inserting the data from the mysql dumps is taking forever.
> I've got about 200 million rows in the big table (growing by about 1.5
> million per day), but at the 100 inserts/second I'm getting, this will
take
> over 3 weeks.  MySQL on the same machine averages about 1100-1200
> inserts/second (no, I'm not running them both at the same time ;-)
>
> Any thoughts on how to tweak the postmaster for quick inserts?  I've got
> fsync turned off, but that's about all I've tried so far.
>
> I'm running postgresql 7.1.3 on Linux (Redhat 7.2 with ext3) with a
700MHz
> processor, 256MB RAM, and an 80GB IDE HD
>
> Thanks
> -partap
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

On Mon, 26 Nov 2001, Gurupartap Davis wrote:

> I'm worried about creating indexes after all the data is in there,
> though...I'm going to need 4-5 indexes.  Isn't that going to take a hella
> long time? How are indexes created anyhow? I would assume a linear search
> through every record in the table...would vacuum help at all?  how bout
> vacuum analyze?

I am somewhat of a newbie, but I was told that 'copy' doesn't use the
indexes so you could create your indexes and when you are all done with
the copy commands then run a vacuum analyze.

I do bulk uploads daily from a Foxpro system, been 7million my biggest
table, and I have found that a plain vacuum is helpfull after each bulk upload.
When I am done with all my bulk uploads, 3 files, then I do one vacuum analyze.

In theory you could just do a single vacuum analyze when you are all done,
but for me a vacuum after each upload seems to help. In particular I am
still trying to decide whether to use truncate or delete's. When I use
delete old the old records seems to still "be around" so the vacuum gets
rid of them. The main advantage of truncate is that it is a fast way to
empty a table, it's main drawback is that it can not be rolled back.