Date: Sun, 7 Jan 2007 16:18:06 -0600 From: Kirk Strauser <kirk@strauser.com> To: freebsd-questions@freebsd.org Subject: Re: Tuning PostgreSQL for bulk imports Message-ID: <200701071618.11242.kirk@strauser.com> In-Reply-To: <enrnt5$9h6$1@sea.gmane.org> References: <200701071301.27423.kirk@strauser.com> <enrnt5$9h6$1@sea.gmane.org>
next in thread | previous in thread | raw e-mail | index | archive | help
[-- Attachment #1 --] On Sunday 07 January 2007 15:15, Ivan Voras wrote: > There's a dedicated mailing list for PostgreSQL performance: > pgsql-performance/at/postgresql.org, which can give you really good > advice, but here's some tips: I've read, read, and re-read the general tuning tips, and done as much as seemed reasonable. I was sort of hoping for a FreeBSD-specific magic "go-fast switch". > - What might help you is to keep the WAL (write-ahead-log, i.e. journal) > files on a completely separate (and fast) drive from the rest of the > database, to allow parallelism and speed. For best results, format it > with 32k blocks/8k fragments. Thanks for the idea. Assuming I actually get my wish of a matched set of 4 high speed drives, would I be better off setting one aside for the journal, or striping them all together so everything benefits? > - If you don't specifically need the atomicity of transactions, you > might divide your import into many small transactions, for example one > for every 100,000 rows instead of doing 8 million at once. Would that actually make a difference in total elapsed time spent importing? > Also, what version of PostgreSQL are you using? As a general rule, the > newer the version, the faster it is. This is especially true if you're > using 7.x - go to 8.1.5 immediately (but don't use 8.2 until 8.2.1 gets > out). We're already running 8.2 because it fixed some problems we were having with 8.1.5. Other than the excessively long import times, it's absolutely screaming and we couldn't be more pleased. -- Kirk Strauser [-- Attachment #2 --] -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (FreeBSD) iD8DBQBFoXGj5sRg+Y0CpvERAgv6AKCP4zG4AOqen3WC9Vd0SdguQkur8QCdGRVL UxAkM66Iy+44TOLMBUZaJtE= =TZNE -----END PGP SIGNATURE-----
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200701071618.11242.kirk>