Re: Performance Tuning

The world rejoiced as lists(at)boutiquenumerique(dot)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