Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgUFs-0001OW-NX for pgsql-bugs@arkaria.postgresql.org; Wed, 03 Jun 2020 14:20:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jgUFr-0005SM-GS for pgsql-bugs@arkaria.postgresql.org; Wed, 03 Jun 2020 14:20:31 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jgUFr-0005Q9-4J for pgsql-bugs@lists.postgresql.org; Wed, 03 Jun 2020 14:20:31 +0000 Received: from mail-qt1-x842.google.com ([2607:f8b0:4864:20::842]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jgUFl-0004YC-Ln for pgsql-bugs@lists.postgresql.org; Wed, 03 Jun 2020 14:20:27 +0000 Received: by mail-qt1-x842.google.com with SMTP id d27so2146595qtg.4 for ; Wed, 03 Jun 2020 07:20:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=jepsen-io.20150623.gappssmtp.com; s=20150623; h=subject:to:cc:references:from:message-id:date:user-agent :mime-version:in-reply-to:content-transfer-encoding:content-language; bh=iYUZPoFoMJXkb41nTSXLwL/bEWWSk+abTO7U6ZgzrhQ=; b=V862YBNPqmRUNZkZ7BD4GfOZrzl/CGXj5LbDflPKiNDUS0zh3i7hXzagc6qHyw2FHV fdugAl3JCWNJvj0dLvj6PO3S1hgGO1dfQa6R5P2JNmvCg2zKiCjd1LYxS2oYWQyKs2hM 3iKewbrGqCJRWx8ltuNNr62Qi6qvs7JjOomMRsjjUxUf+ToxQZZ7a24ghJciDyIZZwfS t6g6kVL4O+VHahCxQtY+IlJKFqVOp86d5zrysqjzllX+i2hKizJYd7HBsQOedPBu/HsS 3aHMOW6rVGyg7Rbtaxnm81j4PEkR3NpRBdQUejUIiiQ8LJi3BpMXUAM+/6tERu0VWuCq pw1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding :content-language; bh=iYUZPoFoMJXkb41nTSXLwL/bEWWSk+abTO7U6ZgzrhQ=; b=DF4mzKFn2tNjeKmaO5YqdNCrmGdCZeej3A/vpsi8Fc3kUd6YwjFYW2fLemf3TFiAeJ N4moU6lVDg4SUBcrAHxdDyhkAl7yjvGiwYivVXQqObFiruV52kAH0P4Z5xt2kqLVh6rY oYYSgxBQjGd9aX2gSbacm7UdRnBBcAPQPU443EkEKhPsf0DpajD9vZZeQCWSUoMpSCw1 JHwu90xiJQWntUvh53WhnsTprIbUtNP27a+TLMmsav7dm+FYtNd7xZtYOsin5szEWoA/ +D3Ilm9SxNaVBH/yLzcobrp4xaU2uuOYUI6A4249SkuDsdi7PRUl2dcTJuqX9Ik6ZD4K cTlg== X-Gm-Message-State: AOAM530lkdu9upIvz+68L+GYajC3FJYEtz8b2aeU7LOnxEya5LTsbH0i C0ISb0+UxbT32wFNVSdVGxzqAwmzS1E= X-Google-Smtp-Source: ABdhPJxxUTlqDM3D3dlAD9PoFswmqxchszJdGeBToNpA+YW+3kCNwa33dIiVPeU0naK69HG6ibGA5w== X-Received: by 2002:ac8:226d:: with SMTP id p42mr33444089qtp.1.1591194023951; Wed, 03 Jun 2020 07:20:23 -0700 (PDT) Received: from ?IPv6:2600:2b00:9454:be00:2fe3:fb7e:9bf7:954f? ([2600:2b00:9454:be00:2fe3:fb7e:9bf7:954f]) by smtp.gmail.com with ESMTPSA id 138sm1713239qkk.38.2020.06.03.07.20.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 03 Jun 2020 07:20:23 -0700 (PDT) Subject: Re: Potential G2-item cycles under serializable isolation To: Peter Geoghegan Cc: PostgreSQL mailing lists References: <11bb3199-c685-1cec-63bb-f92848edbe10@jepsen.io> <54c6c503-b939-c4ce-0d54-4f998ac04168@jepsen.io> <3150072e-13b8-7655-211b-17898f894e45@jepsen.io> From: Kyle Kingsbury Message-ID: <4e63a8e6-41c9-2a62-b780-2b56005d2253@jepsen.io> Date: Wed, 3 Jun 2020 10:20:22 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.8.1 MIME-Version: 1.0 In-Reply-To: Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 6/2/20 7:17 PM, Peter Geoghegan wrote: > On Tue, Jun 2, 2020 at 10:24 AM Peter Geoghegan wrote: >> I'd be surprised if your existing test cases needed any adjustment. My >> guess is that this won't take long. > You said it takes about a minute in your opening e-mail; how > consistent is this? I note from the Postgres logs you provided that > Postgres starts accepting connections at 2020-05-31 18:50:27.580, and > shows its last log message at 2020-05-31 18:51:29.781 PDT. So it's > suspiciously close to *exactly* one minute. I set the test duration to 60 seconds for those runs, but it'll break in as little as 10. That's less of a sure thing though. :) > Note that > autovacuum_naptime has as its default '1min'. Your workload probably > generates a lot of index bloat, which may tend to cause autovacuum to > want to delete whole B-Tree leaf pages, which impacts predicate > locking. With the default (debian) postgresql.conf, which has autovacuum_naptime commented out (default 1min), I see anomalies at (just picking a random recent test) 8.16 seconds, 9.76 seconds, and 19.6 seconds.  Another run: 28.0 seconds, 32.3 seconds. > Could you check what happens when you reduce autovacuum_naptime to > (say) '5s' in postgresql.conf? Does that change make the G2-item cycle > issue manifest itself earlier? And can you discern any pattern like > that yourself? It doesn't look like setting autovacuum_naptime makes a difference. > It seems kind of inconvenient to run Jepsen -- I suppose I could use > Docker or something like that, but I don't have experience with it. > What do you think is the simplest workflow for somebody that just > wants to recreate your result on a Debian system? I am really sorry about that--I know it's not convenient. Jepsen's built for testing whole distributed systems, and is probably a bit overkill for testing a single Postgres process. I don't have any experience with Docker, but I think Docker Compose might be a good option for a single-node system? I apologize--I *just* started writing this test against Debian Buster a few days ago, and the existing AWS Marketplace and Docker Compose environments are still on Stretch, so on top of setting up a Jepsen environment you also gotta do a Debian upgrade. :'-O I'll see about writing a version of the test that doesn't use any of the automation, so you can point it at a local postgres instance. Then all you'll need is lein and a jdk. --Kyle