From: Scott Marlowe <scott.marlowe@gmail.com>
To: Rainer Mager <rainer@vanten.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: speeding up table creation
Date: Tue, 14 Oct 2008 18:05:57 -0600
Message-ID: <dcc563d10810141705s353f7c6tcb451efde7dde70c@mail.gmail.com> (raw)
In-Reply-To: <000001c92e51$c42afcb0$4c80f610$@com>
References: <000001c92e51$c42afcb0$4c80f610$@com>
On Tue, Oct 14, 2008 at 5:08 PM, Rainer Mager <rainer@vanten.com> wrote:
> I have an interesting performance improvement need. As part of the automatic
> test suite we run in our development environment, we re-initialize our test
> database a number of times in order to ensure it is clean before running a
> test. We currently do this by dropping the public schema and then recreating
> our tables (roughly 30 tables total). After that we do normal inserts, etc,
> but never with very much data. My question is, what settings can we tweak to
> improve performance is this scenario? Specifically, if there was a way to
> tell Postgres to keep all operations in memory, that would probably be
> ideal.
I'm not sure we've identified the problem just yet.
Do you have the autovacuum daemon enabled? Are your system catalogs
bloated with dead tuples? Could you approach this by using a template
database that was pre-setup for you and you could just "create
database test with template test_template" or something like that?
Also, if you're recreating a database you might need to analyze it
first before you start firing queries.
PostgreSQL buffers / caches what it can in shared_buffers. The OS
also caches data in kernel cache. Having more memory makes it much
faster. But if it writes, it needs to write. If the database server
where this is happening doesn't have any important data in it, you
might see a boost from disabling fsync, but keep in mind a server that
loses power / crashes can corrupt all your databases in the db server.
If you can fix the problem with any of those suggestions you might
need to throw hardware at the problem.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, rainer@vanten.com
Subject: Re: speeding up table creation
In-Reply-To: <dcc563d10810141705s353f7c6tcb451efde7dde70c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox