Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XVvk9-0004lb-78 for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Sep 2014 05:00:57 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XVvk8-0003jd-II for pgsql-hackers@arkaria.postgresql.org; Mon, 22 Sep 2014 05:00:56 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XVvk7-0003jV-5G for pgsql-hackers@postgresql.org; Mon, 22 Sep 2014 05:00:55 +0000 Received: from mail-qa0-x231.google.com ([2607:f8b0:400d:c00::231]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1XVvk2-0000Og-BL for pgsql-hackers@postgresql.org; Mon, 22 Sep 2014 05:00:53 +0000 Received: by mail-qa0-f49.google.com with SMTP id j7so2012057qaq.36 for ; Sun, 21 Sep 2014 22:00:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; bh=LIBa98CL4MZNOYSmv5OAOf1YHQ6EKkL+1XoRINZ2b6w=; b=MbvpIyRrfXqRAgM7QyRc7PFiOTLVgsJbDGHafJCpTYLqr8xcG3DCe9SGQpXm1YlMfL mEIJuAoB6fgLGKaaQGO/Zr/9McGKap7G7SemPwNGTZxW99d9JR6IvOWhEtMFJPFgXe2N WBUGVYkOObF49kssBoeK1KltRyZ/ecaVecaSjqcL2EHRnGMm2Jqt9VoVc+zgKVbwkJHd xnQYTTZ+BYix8dI/SKK9JCxFEE6qC+4v4lPeuCUg6EUiqZHSWpiVp4bAVRxUDUvRAi3g RXxTShU2y1oi04ybbWbbId9wpD2qCKUtvYiOLtD3ZvE3j4Bj46UJKTkpsspvsIG7NnB3 y1cA== X-Received: by 10.140.17.9 with SMTP id 9mr19188582qgc.47.1411362047685; Sun, 21 Sep 2014 22:00:47 -0700 (PDT) Received: from [192.168.1.30] (mail.highperformancepostgresql.com. [71.179.240.8]) by mx.google.com with ESMTPSA id p11sm1769705qgp.45.2014.09.21.22.00.46 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Sun, 21 Sep 2014 22:00:46 -0700 (PDT) Message-ID: <541FACFD.7030105@gmail.com> Date: Mon, 22 Sep 2014 01:00:45 -0400 From: Gregory Smith User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 To: Robert Haas , Alvaro Herrera CC: Mark Kirkwood , Amit Kapila , Haribabu Kommi , "pgsql-hackers@postgresql.org" Subject: Re: Per table autovacuum vacuum cost limit behaviour strange References: <20140214213227.GF6342@eldon.alvh.no-ip.org>

<53672F63.3080400@catalyst.net.nz> <53686F22.8030300@catalyst.net.nz> <20140826161911.GE6343@eldon.alvh.no-ip.org> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org On 8/28/14, 12:18 PM, Robert Haas wrote: > At least in situations that I've encountered, it's typical to be able > to determine the frequency with which a given table needs to be > vacuumed to avoid runaway bloat, and from that you can work backwards > to figure out how fast you must process it in MB/s, and from there you > can work backwards to figure out what cost delay will achieve that > effect. But if the system tinkers with the cost delay under the hood, > then you're vacuuming at a different (slower) rate and, of course, the > table bloats. The last time I took a whack at this, I worked toward making all of the parameters operate in terms of target MB/s, for exactly this style of thinking and goal. Those converted into the same old mechanism under the hood and I got the math right to give the same behavior for the simple cases, but that could have been simplified eventually. I consider that line of thinking to be the only useful one here. The answer I like to these values that don't inherit as expected in the GUC tree is to nuke that style of interface altogether in favor of simplifer bandwidth measured one, then perhaps add multiple QoS levels. Certainly no interest in treating the overly complicated innards of cost computation as a bug and fixing them with even more complicated behavior. The part of this I was trying hard to find time to do myself by the next CF was a better bloat measure tool needed to actually see the problem better. With that in hand, and some nasty test cases, I wanted to come back to simplified MB/s vacuum parameters with easier to understand sharing rules again. If other people are hot to go on that topic, I don't care if I actually do the work; I just have a pretty clear view of what I think people want. > The only plausible use case for setting a per-table rate that I can > see is when you actually want the system to use that exact rate for > that particular table. That's the main one, for these must run on > schedule or else jobs. Yes. On 8/29/14, 9:45 AM, Alvaro Herrera wrote: > Anyway it seems to me maybe there is room for a new table storage > parameter, say autovacuum_do_balance which means to participate in the > balancing program or not. If that eliminates some of the hairy edge cases, sure. A useful concept to consider is having a soft limit that most thing work against, along with a total hard limit for the server. When one of these tight schedule queries with !autovacuum_do_balance starts, they must run at their designed speed with no concern for anyone else. Which means: a) Their bandwidth gets pulled out of the regular, soft limit numbers until they're done. Last time I had one of these jobs, once the big important boys were running, everyone else in the regular shared set were capped at vacuum_cost_limit=5 worth of work. Just enough to keep up with system catalog things, and over the course of many hours process small tables. b) If you try to submit multiple locked rate jobs at once, and the total goes over the hard limit, they have to just be aborted. If the rush of users comes back at 8AM, and you can clean the table up by then if you give it 10MB/s, what you cannot do is let some other user decrease your rate such that you're unfinished at 8AM. Then you'll have aggressive AV competing against the user load you were trying to prepare for. It's better to just throw a serious error that forces someone to look at the hard limit budget and adjust the schedule instead. The systems with this sort of problem are getting cleaned up every single day, almost continuously; missing a day is not bad as long as it's noted and fixed again before the next cleanup window. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers