From dev-return-8837-archive-asf-public=cust-asf.ponee.io@airflow.apache.org Thu Jul 4 06:10:40 2019 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [207.244.88.153]) by mx-eu-01.ponee.io (Postfix) with SMTP id 8A4DB180595 for ; Thu, 4 Jul 2019 08:10:40 +0200 (CEST) Received: (qmail 17497 invoked by uid 500); 4 Jul 2019 06:10:38 -0000 Mailing-List: contact dev-help@airflow.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@airflow.apache.org Delivered-To: mailing list dev@airflow.apache.org Received: (qmail 17480 invoked by uid 99); 4 Jul 2019 06:10:36 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 04 Jul 2019 06:10:36 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id D9035C0078 for ; Thu, 4 Jul 2019 06:10:35 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 1.685 X-Spam-Level: * X-Spam-Status: No, score=1.685 tagged_above=-999 required=6.31 tests=[DKIMWL_WL_HIGH=-0.001, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H2=-0.125, SPF_HELO_NONE=0.001, SPF_PASS=-0.001, T_PDS_NO_HELO_DNS=0.01, URIBL_BLOCKED=0.001] autolearn=disabled Authentication-Results: spamd1-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=airbnb.com Received: from mx1-ec2-va.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id lV9lht9ttmn6 for ; Thu, 4 Jul 2019 06:10:33 +0000 (UTC) Received-SPF: Pass (mailfrom) identity=mailfrom; client-ip=209.85.222.67; helo=mail-ua1-f67.google.com; envelope-from=pala.muthiah@airbnb.com; receiver= Received: from mail-ua1-f67.google.com (mail-ua1-f67.google.com [209.85.222.67]) by mx1-ec2-va.apache.org (ASF Mail Server at mx1-ec2-va.apache.org) with ESMTPS id ACE73BC52B for ; Thu, 4 Jul 2019 06:10:33 +0000 (UTC) Received: by mail-ua1-f67.google.com with SMTP id f20so741553ual.0 for ; Wed, 03 Jul 2019 23:10:33 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to; bh=mfOykYPWAfLeud5x2WaQbP/rQT/xl2gMgqFqI0RCojo=; b=CG1PxOeUylXk2Ar1SpOITYZO067311trJA6cbHPL2ir+iPOteq4t3AAMNZm79LdWL9 tRq8dwEsRf3KlevMsuRGhvHD7pZvWaP71vtBSv9MAxuDSc4LhkPnI8KfyxWXqZd8boms 90o2RoxljNoJAeJeIF8yWLhzcgcuHXzGypeqFnEAM/KwKC3yjcbyNbt4JgMQTC2qdiWF k+QXRLaOpmDScO4xhfzVwBu8j66Imn5wS1f8iJYmBk+aieaEmhnhpb7O6hCEF45ry4bZ AMWtxL2PgevXfvWdThogcQrUMHplsvtptrwleU89C4PpnFr/e11AoH8U1cNOmGx3AGKS 0AZA== X-Gm-Message-State: APjAAAWoZSQo2HLz9nnsJUUSUxsVOdSrcKuNz246hwxjyF14v3MCIzHh lQOk9PrswMmxMnCf3np2qdq15H4L4ePJlfpzr/WslQU4 X-Google-Smtp-Source: APXvYqzpso0ad1EONqUjjBktLCbhyRu1fqr6rq1b318ENiIWjTQ5BhZRuwufZS1WiSJpYdL0KOihv1Hx8JOxtZbPvAc= X-Received: by 2002:ab0:18a6:: with SMTP id t38mr19981796uag.83.1562220633014; Wed, 03 Jul 2019 23:10:33 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pala Muthiah Date: Wed, 3 Jul 2019 23:10:22 -0700 Message-ID: Subject: Re: Meta DB transaction isolation level To: dev@airflow.apache.org Content-Type: multipart/alternative; boundary="000000000000e4787a058cd4d519" --000000000000e4787a058cd4d519 Content-Type: text/plain; charset="UTF-8" We found at times some read queries lock up the task instance table, piling up connections and task heartbeat transactions, which takes longer to recover. In READ COMMITTED, such long running and full table locks should be avoided in many cases. Basically we are trying to improve performance and recovery by reducing locks. If the ORM queries that Airflow ends up generating does not have transactions that read same rows multiple times (within the transaction), then consistency level is essentially the same. Of course this is based on rough understanding and i am trying to verify if others have actually implemented this. On Tue, Jul 2, 2019 at 11:57 PM Ash Berlin-Taylor wrote: > Why do you want to change this? Is there something specific you are trying > to achieve with changing this? > > (Periodic reminder: I personally recommend people use Postgres - I've > always found it to be more predictable and stable than Mysql) > > -ash > > > On 3 Jul 2019, at 00:03, Pala Muthiah > wrote: > > > > Hello everyone > > > > Wanted to find out what is the transaction isolation level used in > > airflow's MySQL meta db in your deployments. > > > > Does anybody use READ COMMITTED instead of the default REPEATABLE READ > > transaction isolation level of MySQL? Did you observe any issues? > > > > > > Thanks, > > pala > > --000000000000e4787a058cd4d519--