pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Performance Tuning
Date: Wed, 09 Feb 2005 22:09:29 -0500
Message-ID: <m3sm45f6vq.fsf@knuth.knuth.cbbrowne.com> (raw)
References: <200502091501.31447.chris.kratz@vistashare.com>
	<420A74BE.8010009@arbash-meinel.com>
	<200502091625.34787.chris.kratz@vistashare.com>
	<opslyfkag4th1vuj@musicbox>

The world rejoiced as lists@boutiquenumerique.com (PFC) wrote:
>> As a side note, I learned something very interesting for our
>> developers  here.
>> We had been doing a drop database and then a reload off a db dump
>> from  our
>> live server for test data.  This takes 8-15 minutes depending on the
>> server
>> (the one above takes about 8 minutes).  I learned through testing
>> that I  can
>> use create database template some_other_database and make a duplicate in
>> about 2.5 minutes. which is a huge gain for us.  We can load a
>> pristine  copy,
>> make a duplicate, do our testing on the duplicate, drop the duplicate and
>> create a new duplicate in less then five mintes.
>
> 	I think thats because postgres just makes a file copy from the
> template.  Thus you could make it 2x faster if you put the template
> in another tablespace on another drive.

I had some small amusement today trying this feature out in one of our
environments today...

We needed to make a copy of one of the databases we're replicating for
the sysadmins to use for some testing.

I figured using the "template" capability was:
 a) Usefully educational to one of the other DBAs, and
 b) Probably a quick way to copy the data over.

We shortly discovered that we had to shut off the Slony-I daemon in
order to get exclusive access to the database; no _big_ deal.

At that point, he hit ENTER, and rather quickly saw...
CREATE DATABASE.

We then discovered that the sysadmins wanted the test DB to be on one
of the other servers.  Oops.  Oh, well, we'll have to do this on the
other server; no big deal.

Entertainment ensued...  "My, that's taking a while..."  At about the
point that we started thinking there might be a problem...

CREATE DATABASE

The entertainment was that the first box is one of those spiffy new
4-way Opteron boxes, whilst the "slow" one was a 4-way Xeon...  Boy,
those Opterons are faster...
-- 
output = reverse("moc.liamg" "@" "enworbbc")
http://cbbrowne.com/info/rdbms.html
"No matter how far you have gone on the wrong road, turn back."
-- Turkish proverb



view thread (15+ messages)  latest in thread

Message-ID: <m3sm45f6vq.fsf@knuth.knuth.cbbrowne.com>
Permalink:  ../m3sm45f6vq.fsf@knuth.knuth.cbbrowne.com/
Also on:    postgresql.org/message-id/m3sm45f6vq.fsf@knuth.knuth.cbbrowne.com

 · 

reply

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: cbbrowne@acm.org
  Subject: Re: Performance Tuning
  In-Reply-To: <m3sm45f6vq.fsf@knuth.knuth.cbbrowne.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