Re: Optimize for insertions?

Поиск
Список
Период
Сортировка
От Jeff Davis
Тема Re: Optimize for insertions?
Дата
Msg-id 200111262211.OAA27228@smtp.ucsd.edu
обсуждение исходный текст
Ответ на Optimize for insertions?  ("Gurupartap Davis" <partap@yahoo.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Gurupartap Davis"
Дата:
Сообщение: Optimize for insertions?
Следующее
От: David A Dickson
Дата:
Сообщение: Pl/Pgsql triger procedure calls with parameters