Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1660E1337B8B for ; Wed, 13 Apr 2011 19:19:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 54837-04 for ; Wed, 13 Apr 2011 22:19:28 +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 E0A0F1337B67 for ; Wed, 13 Apr 2011 19:19:27 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by mail.gransy.com (Postfix) with ESMTP id E21252E5A024 for ; Thu, 14 Apr 2011 00:19:26 +0200 (CEST) Received: from mail.gransy.com ([127.0.0.1]) by localhost (nathalia.gransy.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id aIAlINcmPOG4 for ; Thu, 14 Apr 2011 00:19:26 +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 C2C3C2E5A021 for ; Thu, 14 Apr 2011 00:19:26 +0200 (CEST) Message-ID: <4DA6216E.9020907@fuzzy.cz> Date: Thu, 14 Apr 2011 00:19:26 +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: Performance References: <20110412171855.GA14292@tux> <4DA496FA.3070908@fuzzy.cz> <8F22D592-23C1-4A3C-94A5-48363332ADD3@darkstatic.com> <4DA4BFA2.5060601@fuzzy.cz> <4DA4D40A.4010200@fuzzy.cz> <4DA56DA8020000250003C783@gw.wicourts.gov> In-Reply-To: X-Enigmail-Version: 1.1.1 Content-Type: text/plain; charset=ISO-8859-1 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/202 X-Sequence-Number: 43264 Dne 14.4.2011 00:05, Nathan Boley napsal(a): >>> If you model the costing to reflect the reality on your server, good >>> plans will be chosen. >> >> Wouldn't it be "better" to derive those costs from actual performance >> data measured at runtime? >> >> Say, pg could measure random/seq page cost, *per tablespace* even. >> >> Has that been tried? > > FWIW, awhile ago I wrote a simple script to measure this and found > that the *actual* random_page / seq_page cost ratio was much higher > than 4/1. > > The problem is that caching effects have a large effect on the time it > takes to access a random page, and caching effects are very workload > dependent. So anything automated would probably need to optimize the > parameter values over a set of 'typical' queries, which is exactly > what a good DBA does when they set random_page_cost... Plus there's a separate pagecache outside shared_buffers, which adds another layer of complexity. What I was thinking about was a kind of 'autotuning' using real workload. I mean - measure the time it takes to process a request (depends on the application - could be time to load a page, process an invoice, whatever ...) and compute some reasonable metric on it (average, median, variance, ...). Move the cost variables a bit (e.g. the random_page_cost) and see how that influences performance. If it improved, do another step in the same direction, otherwise do step in the other direction (or do no change the values at all). Yes, I've had some lectures on non-linear programming so I'm aware that this won't work if the cost function has multiple extremes (walleys / hills etc.) but I somehow suppose that's not the case of cost estimates. Another issue is that when measuring multiple values (processing of different requests), the decisions may be contradictory so it really can't be fully automatic. regards Tomas