Inserting 26 million rows takes 8 hours, how to improve those times?

Поиск
Список
Период
Сортировка
От Jose Vicente Nunez Z
Тема Inserting 26 million rows takes 8 hours, how to improve those times?
Дата
Msg-id 1063717273.5407.16.camel@linux0037
обсуждение исходный текст
Ответы Re: Inserting 26 million rows takes 8 hours, how to improve those times?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
Greetings,

I'm trying to use PostgreSQL to manage big amounts of data; One of the
first things i'm testing is how fast PostgreSQL can load some big CSV
text files.


For that i'm using the PostgreSQL copy tool, but the problem is that is
taking almost 9 hours to load the data:

copy nb_cmo_deal_pools from '/postgres-system/datafile.txt' DELIMITERS
'|';

[root@linux0105 root]# time psql -Upostgres MYDB < load.sql

real    487m47.632s
user    0m0.020s
sys     0m0.000s
[root@linux0105 root]#

[root@linux0105 root]# cat
/postgres-system/datafile.txt|wc -l
26026965

I've already played with filesystem options on my ext3 system and i would like to know:

1) How i can tune PostgreSQL to improve the insertion speed (besides droping indexes and using the fsync option).
2) How 'safe' is to use the fsync=off option on a ext3 journaled system? The journal should give me some protection in
casethe system goes down, isn't it? 
3) I've read several web pages that talk about tunning the sort and buffer options in PosgreSQL, but no good example of
optimizationshere. Does anyone know where i can find more help (My test system has 2GB of RAM 
and i think 1.5 for the database will be fair enough).

I apologize if this not the proper place to post this questions.

Thanks in advance,


--
Jose Vicente Nunez Zuleta (josevnz at newbreak dot com)
Newbreak LLC System Administrator
http://www.newbreak.com
RHCE, SCJD, SCJP


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

Предыдущее
От: Mauricio de Castro
Дата:
Сообщение: Re: Report Generator Proposal
Следующее
От: "A.Bhuvaneswaran"
Дата:
Сообщение: Re: SQL-Statement