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?
|
Список | 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 по дате отправления: