Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sA0Op-006uZQ-Ar for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 04:49:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sA0Nq-007u4r-Iu for pgsql-general@arkaria.postgresql.org; Thu, 23 May 2024 04:48:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sA0Nq-007u4j-5i for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 04:48:54 +0000 Received: from mail-vs1-xe33.google.com ([2607:f8b0:4864:20::e33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sA0Nn-001Z2N-K5 for pgsql-general@lists.postgresql.org; Thu, 23 May 2024 04:48:52 +0000 Received: by mail-vs1-xe33.google.com with SMTP id ada2fe7eead31-488e8cb86c9so1672504137.1 for ; Wed, 22 May 2024 21:48:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1716439731; x=1717044531; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=365ODKYP/OQq5ryKFIlyMVZ3wLr34dRD45lcRxJcd80=; b=bGzrcuvobI4cQDM1sE2gA7M7TIAxRfPppRPJT4wLp+xjLQWJmvLid+h3DdIy2MAj6l 9DfnTNffQg54jCW8yF2tGmaqPaOe3BVSU2YEOApANI5XUVbncJfJqeY37i1SX/odyKp/ QbXyOjpdeEKjc0xQgqJi9+36Qo6jnTMSHQ8sBFI4BpbDXBxBV/8kILiVl4cx1vVmmb+U hDX1OUjsY8nt8ypr7mhH6C7OJVNlpKOkZnE+jM/qRwHlQeSiwYvcWJAjDyc3+Sr4A/l8 Sg+lyNiMu3iL8LtDBDwgYsFBQNlUxGyYXI0zQkbzM9CjvWphnHdOkAhvNdpHkrdKEUcj ZoMg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1716439731; x=1717044531; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=365ODKYP/OQq5ryKFIlyMVZ3wLr34dRD45lcRxJcd80=; b=UxliGC3g6IYz2ofXogePEKpgpRKtJikNf0KbPgUTeE3pU+sFygyjJffnxV+vKnadTf KiuJ1w7BiiG34+eqKyTm4C6mpZjUg6u4pg+k55qm1dwd7i8HBlejKriAPXWp9oYXQ+L+ 5kdvVkJ2ae/S6N1g28kPurG7LdEM1lQmgPz8KmhT5ua1G3E3ttD6jECVyG0kPWFB8f+9 RlM8ZHI7/ZUdiwOqetMkdQ0nJr0+3F83U16sr1mGw89AMfjjyRFeH8MeijeO9V/ckKno sdIDtPNtEXf7WfPS6fV3/vA5YcJHiSOvEu3d6TAvbfI25tSsv4ygdVJzeDOwh+XGrQrI ixsg== X-Gm-Message-State: AOJu0YxyXsQgz/0kbH+MSC/wIfwpvs+l2vn/rVaYW00bZ4gCVOYZVaMw sX6cuRg3spKDRIxWdJkf7DJC9e+5FKgu+7oVAHzibL59pLmt3jcgJaaEUiig4TzYGO4X0YtnoSB iyjZoAYYfAJlUkMbK6yJkJFMMM+Moy/ph X-Google-Smtp-Source: AGHT+IHBiupOzBDAfkIMZ1BrUfgyslQiLKu6ea4YM3x3DHq8qh+I5kecxNXoeN4DKiphqufx9Kzvx7vcGerlnbF00ac= X-Received: by 2002:a05:6102:6d5:b0:47f:2478:b763 with SMTP id ada2fe7eead31-4890a2cd8demr4867271137.33.1716439730541; Wed, 22 May 2024 21:48:50 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: sud Date: Thu, 23 May 2024 10:18:39 +0530 Message-ID: Subject: Re: Long running query causing XID limit breach To: Muhammad Salahuddin Manzoor Cc: pgsql-general Content-Type: multipart/alternative; boundary="0000000000006adbfb061917c4fa" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000006adbfb061917c4fa Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, May 23, 2024 at 9:00=E2=80=AFAM Muhammad Salahuddin Manzoor < salahuddin.m@bitnine.net> wrote: > Greetings, > > In high-transaction environments like yours, it may be necessary to > supplement this with manual vacuuming. > > Few Recommendations > > Monitor Long-Running Queries try to optimize. > Optimize Autovacuum. > Partitioning. > Adopt Vacuum Strategy after peak hours. > > We have these big tables already partitioned. So does "vacuum table_name" will endup scanning whole table or just the latest/live partition which is getting loaded currently? and do you mean to say running command "vacuum table_name;" frequently on selective tables that are experiencing heavy DML ? Hope this won't lock the table anyway because the data will be written/read from these tables 24/7. When you say, "optimize autovacuum" does it mean to set a higher value of "autovacuum_max_workers" and "autovacuum_freeze_max_age"? Considering we have ~4 billion rows inserted daily into the table and there is limit of ~2billion to the "Maximumusedtxnids", what threshold should we set for the alerting and to have enough time at hand to fix this issue? --0000000000006adbfb061917c4fa Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, May 23, 2024 at 9:00=E2=80=AFAM M= uhammad Salahuddin Manzoor <= salahuddin.m@bitnine.net> wrote:
Greetin= gs,

In high-transaction environments like yours, it = may be necessary to supplement this with manual vacuuming.

Few Recom= mendations

Monitor Long-Running Queries try to optimize.
Optimize= Autovacuum.
Partitioning.
Adopt Vacuum Strategy after peak hours.

<= /td>
We= have these big tables already partitioned. So does=C2=A0"vacuum table= _name" will endup scanning whole=C2=A0table or just the latest/live pa= rtition which is getting loaded currently? and do you mean to say running c= ommand "vacuum table_name;" frequently=C2=A0on selective tables t= hat=C2=A0are=C2=A0experiencing=C2=A0heavy DML ? Hope=C2=A0this=C2=A0won'= ;t lock the table anyway because the data will be written/read from these t= ables=C2=A024/7.

When you say, "optimize auto= vacuum" does it mean to set a higher value of "autovacuum_= max_workers" and "autovacuum_freeze_max_age"?<= /span>

Considering we have ~4 billio= n rows inserted daily into the table and there is limit of ~2billion to the= "Maximumusedtxnids", what threshold should we set for the alerti= ng and to have enough time at hand to fix this issue?
=
--0000000000006adbfb061917c4fa--