X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3289A8B9C8D for ; Thu, 10 Feb 2005 03:50:25 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67974-06 for ; Thu, 10 Feb 2005 03:50:04 +0000 (GMT) Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.250.190.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8DCD78B9E68 for ; Thu, 10 Feb 2005 03:50:06 +0000 (GMT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id E297531E31; Thu, 10 Feb 2005 04:50:06 +0100 (MET) From: Christopher Browne X-Newsgroups: pgsql.performance Subject: Re: Performance Tuning Date: Wed, 09 Feb 2005 22:09:29 -0500 Organization: cbbrowne Computing Inc Lines: 52 Message-ID: References: <200502091501.31447.chris.kratz@vistashare.com> <420A74BE.8010009@arbash-meinel.com> <200502091625.34787.chris.kratz@vistashare.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4 (Corporate Culture, linux) Cancel-Lock: sha1:yEf519gP5JFEXiiQwn3df/HKHLw= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.085 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/166 X-Sequence-Number: 10495 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