Date: Mon, 8 Jan 2007 12:22:45 +1100 From: Norberto Meijome <freebsd@meijome.net> To: Kirk Strauser <kirk@strauser.com> Cc: freebsd-questions@freebsd.org Subject: Re: Tuning PostgreSQL for bulk imports Message-ID: <20070108122245.53e3140b@localhost> In-Reply-To: <200701071618.11242.kirk@strauser.com> References: <200701071301.27423.kirk@strauser.com> <enrnt5$9h6$1@sea.gmane.org> <200701071618.11242.kirk@strauser.com>
next in thread | previous in thread | raw e-mail | index | archive | help
On Sun, 7 Jan 2007 16:18:06 -0600
Kirk Strauser <kirk@strauser.com> 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".
Hi Kirk,
I haven't touched PGSQL for a little while...but I remember some comments
in the pgsql lists about disabling ACID features / WAL while big imports are
happening - I could be wrong and this only apply to generating indexes ? Of
course this is a no goer if you still need to you use your DB server for other
tasks during the import.
Something also to consider is , do you *have* to import all that data every
time? ie., can you create data partitions, assign a new table to each
day ,create the appropriate rules, and then only dump from FoxPro the last
day's info? It may not be possible, but it helped me loads when I had to import
60 mill new rows/day. Indexing becomes somewhat easier too (as you should only
have to regenerate index for the new partition)
if cpu is hitting to hard, you can always run nice pgsql while importing this -
it will still take 100% cpu, but yield as soon as something else needs to do
some work . Of course, this wont help if you are IO bound (i think? ). *are*
you CPU bound ?
Let us know what solution you come up with, and good luck :)
_________________________
{Beto|Norberto|Numard} Meijome
"The music business is a cruel and shallow money trench, a long plastic hallway
where thieves and pimps run free, and good men die like dogs. There's also a
negative side." Hunter S. Thompson
I speak for myself, not my employer. Contents may be hot. Slippery when wet.
Reading disclaimers makes you go blind. Writing them is worse. You have been
Warned.
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20070108122245.53e3140b>