From rainer@vanten.com Tue Oct 14 23:08:52 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 00FAD64FD6E for ; Tue, 14 Oct 2008 20:08:52 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 59440-08 for ; Tue, 14 Oct 2008 20:08:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ichigo.office.vanten.com (static-222-230-136-226.b-fam.svips.gol.ne.jp [222.230.136.226]) by postgresql.org (Postfix) with ESMTP id 7D6C964FDE5 for ; Tue, 14 Oct 2008 20:08:45 -0300 (ADT) Received: from rainerxp (rainer-xp.office.vanten.com [192.168.220.186]) by ichigo.office.vanten.com (Postfix) with ESMTP id 5CAA75081C for ; Wed, 15 Oct 2008 08:08:40 +0900 (JST) From: "Rainer Mager" To: Subject: speeding up table creation Date: Wed, 15 Oct 2008 08:08:27 +0900 Message-ID: <000001c92e51$c42afcb0$4c80f610$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0001_01C92E9D.3412A4B0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AckuUcPcfyB1YoPnSyWe5bpQBFmYOQ== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200810/74 X-Sequence-Number: 31655 This is a multipart message in MIME format. ------=_NextPart_000_0001_01C92E9D.3412A4B0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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. We actually tried running Postgres off of a RAM disk and this did help a reasonable amount, but we're running under Windows and setting up the RAM disk is a hassle and all of our developers would need to do it. Any tips would be appreciated. --Rainer ------=_NextPart_000_0001_01C92E9D.3412A4B0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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.

 

We actually tried running Postgres off of a RAM = disk and this did help a reasonable amount, but we’re running under Windows = and setting up the RAM disk is a hassle and all of our developers would need = to do it.

 

 

Any tips would be appreciated.

 

 

--Rainer

------=_NextPart_000_0001_01C92E9D.3412A4B0-- From scrawford@pinpointresearch.com Tue Oct 14 23:56:50 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3CFC164FDE5 for ; Tue, 14 Oct 2008 20:56:50 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 67151-07 for ; Tue, 14 Oct 2008 20:56:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from polaris.pinpointresearch.com (66-7-238-130.static-ip.telepacific.net [66.7.238.130]) by postgresql.org (Postfix) with ESMTP id 5983C64FDDF for ; Tue, 14 Oct 2008 20:56:44 -0300 (ADT) Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 8A252E00EC82; Tue, 14 Oct 2008 16:56:41 -0700 (PDT) Message-ID: <48F531B9.9010302@pinpointresearch.com> Date: Tue, 14 Oct 2008 16:56:41 -0700 From: Steve Crawford User-Agent: Thunderbird 2.0.0.17 (X11/20080925) MIME-Version: 1.0 To: Rainer Mager CC: pgsql-performance@postgresql.org Subject: Re: speeding up table creation References: <000001c92e51$c42afcb0$4c80f610$@com> In-Reply-To: <000001c92e51$c42afcb0$4c80f610$@com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/75 X-Sequence-Number: 31656 Rainer Mager 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. > What is the test part? In other words, do you start with a known initial database with all empty tables then run the tests or is part of the test itself the creation of those tables? How much data is in the initial database if the tables aren't empty. Creating 30 empty tables should take a trivial amount of time. Also, are there other schemas than public? A couple ideas/comments: You cannot keep the data in memory (that is, you can't disable writing to the disk). But since you don't care about data loss, you could turn off fsync in postgresql.conf. From a test perspective you should be fine - it will only be an issue in the event of a crash and then you can just restart with a fresh load. Remember, however, that any performance benchmarks won't translate to production use (of course they don't translate if you are using ramdisk anyway). Note that the system tables are updated whenever you add/delete/modify tables. Make sure they are being vacuumed or your performance will slowly degrade. My approach is to create a database exactly as you want it to be at the start of your tests (fully vacuumed and all) and then use it as a template to be used to create the testdb each time. Then you can just (with appropriate connection options) run "dropdb thetestdb" followed by "createdb --template thetestdbtemplate thetestdb" which is substantially faster than deleting and recreating tables - especially if they contain much data. Cheers, Steve From scott.marlowe@gmail.com Wed Oct 15 00:06:02 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 42AFA64FDE5 for ; Tue, 14 Oct 2008 21:06:02 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 70144-05 for ; Tue, 14 Oct 2008 21:05:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by postgresql.org (Postfix) with ESMTP id 4982364FDDF for ; Tue, 14 Oct 2008 21:05:59 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id j4so1379765wah.19 for ; Tue, 14 Oct 2008 17:05:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=OBbMzu5Cab9waCXTFBcCF1oM2mmfu1/N91hJb+zAuTM=; b=qzwGKWsKSqYq71JTN3jqG5FyJdzGG7drsehNHizM/ZLs0s6v1C2Z3HChcjRqvPu8V9 H/tD/5Exl0qLDBjy9b7bFEpk67Skn4NJwgSWQTUv3katu1DKDgv29SYQj/8Ferx/HCxj iQS/Ho6BLH6MN9mbaMQv3KvQnOLGNxOK4XbBQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=VbUknv80HFW+Ca1pDlAlSQypqwRCFJFYHbPzwtc+rOpc3lxw2t90rvpRr5bHdA19dN ecFCeO02LeY+CvvJ6qlKpvivGZBF4JBejZtB3rqvUsfemKvm7dZJ4mBd2DWXM3qtRr3S cs9RrTXEh/b4+xKihyVL2t/dnhwtLmap0lCTY= Received: by 10.114.151.13 with SMTP id y13mr181780wad.134.1224029157610; Tue, 14 Oct 2008 17:05:57 -0700 (PDT) Received: by 10.114.39.11 with HTTP; Tue, 14 Oct 2008 17:05:57 -0700 (PDT) Message-ID: Date: Tue, 14 Oct 2008 18:05:57 -0600 From: "Scott Marlowe" To: "Rainer Mager" Subject: Re: speeding up table creation Cc: pgsql-performance@postgresql.org In-Reply-To: <000001c92e51$c42afcb0$4c80f610$@com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <000001c92e51$c42afcb0$4c80f610$@com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/76 X-Sequence-Number: 31657 On Tue, Oct 14, 2008 at 5:08 PM, Rainer Mager 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. From craig@postnewspapers.com.au Wed Oct 15 07:02:27 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C670764FE1D for ; Wed, 15 Oct 2008 04:02:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44546-06 for ; Wed, 15 Oct 2008 04:02:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from www.postnewspapers.com.au (www.postnewspapers.com.au [202.61.230.242]) by postgresql.org (Postfix) with ESMTP id 8DD7764FD91 for ; Wed, 15 Oct 2008 04:02:24 -0300 (ADT) Received: from mail.postnewspapers.com.au (202-89-185-120.static.dsl.amnet.net.au [202.89.185.120]) by www.postnewspapers.com.au (Postfix) with ESMTP id 6BA805C09E; Wed, 15 Oct 2008 15:02:18 +0800 (WST) Received: from [10.0.0.53] (wallace.localnet [10.0.0.53]) by mail.postnewspapers.com.au (Postfix) with ESMTP id 52CF81E0434; Wed, 15 Oct 2008 15:02:18 +0800 (WST) Message-ID: <48F5953C.2010108@postnewspapers.com.au> Date: Wed, 15 Oct 2008 15:01:16 +0800 From: Craig Ringer User-Agent: Thunderbird 2.0.0.17 (X11/20080925) MIME-Version: 1.0 To: Steve Crawford Cc: Rainer Mager , pgsql-performance@postgresql.org Subject: Re: speeding up table creation References: <000001c92e51$c42afcb0$4c80f610$@com> <48F531B9.9010302@pinpointresearch.com> In-Reply-To: <48F531B9.9010302@pinpointresearch.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/77 X-Sequence-Number: 31658 Steve Crawford wrote: > You cannot keep the data in memory (that is, you can't disable writing > to the disk). But since you don't care about data loss, you could turn > off fsync in postgresql.conf. From a test perspective you should be fine > - it will only be an issue in the event of a crash and then you can just > restart with a fresh load. Remember, however, that any performance > benchmarks won't translate to production use (of course they don't > translate if you are using ramdisk anyway). Another thing that may really help, if you're not already doing it, is to do all your schema creation inside a single transaction - at least assuming you can't use the template database approach. -- Craig Ringer