Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id D5DD51337B61 for ; Sun, 24 Apr 2011 09:37:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 45554-07 for ; Sun, 24 Apr 2011 12:37:20 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (mail.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id 1DDC91337B93 for ; Sun, 24 Apr 2011 09:37:19 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id 712C34A2277 for ; Sun, 24 Apr 2011 14:37:17 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at nathalia.gransy.com Received: from mail.gransy.com ([127.0.0.1]) by localhost (Nathalka.gransy.com.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id dP5X64sE7+lt for ; Sun, 24 Apr 2011 14:37:17 +0200 (CEST) Received: from [192.168.1.221] (ip-94-112-0-189.net.upcbroadband.cz [94.112.0.189]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id EA21C4A2276 for ; Sun, 24 Apr 2011 14:37:16 +0200 (CEST) Message-ID: <4DB4197D.3050105@fuzzy.cz> Date: Sun, 24 Apr 2011 14:37:17 +0200 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.2.13) Gecko/20101219 Lightning/1.0b3pre Thunderbird/3.1.7 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: How to configure a read-only database server? References: <0B4657E5-134C-409B-B0CC-A801F4299D7E@gmail.com> In-Reply-To: X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201104/339 X-Sequence-Number: 43401 Dne 24.4.2011 11:38, Stefan Keller napsal(a): > AFAIK it helps at least bulk loading my data every other time. Yes, but this thread was about setting the DB for read-only workload, so those settings were a bit strange. > So I'm confused and backup again: Given a single-disk virtual Linux > system and a 'read-only' dataset, which is exposed to the internet and > completely replaced from time to time, and expecting SELECT queries > including joins, sorts, equality and range (sub-)queries... > > => What are the suggested postgresql.conf and session parameters for > such a "read-only database" to "Whac-A-Mole" (i.e. to consider :->)? What database size are we talking about? Does that fit into RAM or not? If not, set large shared buffers and effective cache size appropriately. If it fits into memory, you could lower the random_page_cost (but this should be handled by the DB). Or you could create a ramdisk and use it to store the data (in this case lowering random_page_cost makes much more sense). regards Tomas