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 1sNIrA-007tiZ-VL for pgsql-novice@arkaria.postgresql.org; Fri, 28 Jun 2024 21:10:09 +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 1sNIr8-006wQU-6s for pgsql-novice@arkaria.postgresql.org; Fri, 28 Jun 2024 21:10:06 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNIr7-006wQL-TL for pgsql-novice@lists.postgresql.org; Fri, 28 Jun 2024 21:10:06 +0000 Received: from mail-yb1-xb36.google.com ([2607:f8b0:4864:20::b36]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sNIr4-00433S-Ui for pgsql-novice@lists.postgresql.org; Fri, 28 Jun 2024 21:10:04 +0000 Received: by mail-yb1-xb36.google.com with SMTP id 3f1490d57ef6-e026a2238d8so1024155276.0 for ; Fri, 28 Jun 2024 14:10:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719609000; x=1720213800; darn=lists.postgresql.org; h=to:date:message-id:subject:mime-version:from:from:to:cc:subject :date:message-id:reply-to; bh=QjDzqSdqSsT606OvLc5eJUPdzuo92+x+73N6Qw15SvI=; b=DoA8iXuNOH1TNhuDBlOstn0vsIGKEwTyZ4Jrx4d3CtCNSFjRL2wSLs+BpRLRrskR+i 3H193BCKfLoMSu5z1CIZz/zpcbEOEKszkjXkoClMgXvgvMpqcenSzguCSuo4PZ8UD1Np S5IYbIje0O21OOg6TJ0Kh080jHLO+rbyXgPWE8n7cHh1WYxwHdgn9WVFAYkTFeA9/hyZ PkEcxKvxv/91QA4TkO0x9z2hWIFd4YWcK9eecJnqbHYtjerKbcbVvUbZS0zwz0THoT53 WMsmyzqxZStKcuGMwO3WZNMHeXmRDAYjg8pHgOjwm2t00G0+bc25z6lSH3srOWJvU5r9 PYtQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719609000; x=1720213800; h=to:date:message-id:subject:mime-version:from:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=QjDzqSdqSsT606OvLc5eJUPdzuo92+x+73N6Qw15SvI=; b=gTD5Zq5vOxNmJOmMdEAjyjizW4KZwb/RX01qSxaZxJOR7WI9gyeHGPDWNSfA9t9ZYI ffJ0KaZZlQ0dJ1Rkm+fni1DOiDayFSIP/z4b5QFD7QyWvJb3YJ523e/WNw81TQ/VvlPq OKFlajX2Tvh2swPIRdl9lM/KLIDo2p+aFUl+kfJS3/ZAsFxNl0lnYd27k1YQWFTblvRt qJUy5s3akik1WfaetRBP/4SVbF/PLbSkY8R0Very/2uirjGdXiej0/60JPWVpRKAbVof gp6zLb9OiJNH/9/7lrFM7ssHVUSmL6GibhUIwvUHFqmXjbAYRpfhtgYsq7z8WUQcGZgm 8zaA== X-Gm-Message-State: AOJu0YzlGCwgA+62iOLM5ARycg82yv2gnYkXxDjOuLB72rh7rlxf+wuY xY45gXZsb5S2DqOh7zrcZ1c1hdF/VlfOzHflw3hZt0r6xHl+hpaALK0pPCgF X-Google-Smtp-Source: AGHT+IELfBpkmPTwCCNPpxEVTG+AnLjfGz/gBoZHAHiPpvcS/MAsdsEv4zdV74WMKRE4a6K3WvStnA== X-Received: by 2002:a25:938f:0:b0:dfa:4d12:7ee0 with SMTP id 3f1490d57ef6-e03040084e0mr18472281276.49.1719608999720; Fri, 28 Jun 2024 14:09:59 -0700 (PDT) Received: from smtpclient.apple ([208.104.195.162]) by smtp.gmail.com with ESMTPSA id 3f1490d57ef6-e0353f5f30esm443195276.57.2024.06.28.14.09.59 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 28 Jun 2024 14:09:59 -0700 (PDT) From: Michael Wallach Content-Type: multipart/alternative; boundary="Apple-Mail=_023D800B-2DE3-4C02-A081-ACE12D46E4D3" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.120.41.1.4\)) Subject: Vacuum Analyze Message-Id: Date: Fri, 28 Jun 2024 17:09:57 -0400 To: pgsql-novice@lists.postgresql.org X-Mailer: Apple Mail (2.3696.120.41.1.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_023D800B-2DE3-4C02-A081-ACE12D46E4D3 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Hi, Having issue with DB where we see SQL get slow, not sure what is causing = but queries that have been running sub second jump to 2-5 secs. Seems = when we manually run VACUUM ANALYZE query perf immediately improves. To = add to this we have a =E2=80=9Cjob=E2=80=9D to trigger VACUUM ANALYZE to = run every day but despite this, some acton against DB results in = performance regarding that is only fixed by again running VACUUM = ANALYZE. What I=E2=80=99m trying to understand is what should I be looking at as = before/after in the tables to determine what exactly VACUUM ANALYZE = might be affecting to identify cause, what is corrected in DB such that = perf improves? The DB is being exercised by custom apps via API so not = sure what is happening that is causing DB to quickly become = non-performant? Have run this query but not sure what in results might explain why after = vacuum perf is better, bad before. Also, anything else in DB we could = look at as before/after? =20 SELECT relname,=20 n_tup_upd as "updates",=20 n_tup_del as "deletes",=20 n_live_tup as "live_tuples",=20 n_dead_tup as "dead_tuples",=20 trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio%", to_char(last_vacuum, 'YYYY-MM-DD HH24:MI:SS') as vacuum_date, to_char(last_analyze, 'YYYY-MM-DD HH24:MI:SS') as analyze_date, to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as = autovacuum_date, to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as = autoanalyze_date FROM pg_stat_all_tables=20 ORDER BY last_autovacuum;=20 Mike W= --Apple-Mail=_023D800B-2DE3-4C02-A081-ACE12D46E4D3 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Hi,

Having = issue with DB where we see SQL get slow, not sure what is causing but = queries that have been running sub second jump to 2-5 secs. Seems when = we manually run VACUUM ANALYZE query perf immediately = improves. To add to this we have a =E2=80=9Cjob=E2=80=9D to trigger = VACUUM ANALYZE to run every day but despite this, some acton against DB = results in performance regarding that is only fixed by again running = VACUUM ANALYZE.

What I=E2=80=99m trying to understand is what should I be = looking at as before/after in the tables to determine what exactly = VACUUM ANALYZE might be affecting to identify cause, what is corrected = in DB such that perf improves? The DB is being exercised by custom apps = via API so not sure what is happening that is causing DB to quickly = become non-performant?

Have run this query but not sure what in results might = explain why after vacuum perf is better, bad before. Also, anything else = in DB we could look at as before/after?
 
SELECT relname, 
       n_tup_upd  as = "updates", 
       n_tup_del  as = "deletes", 
       n_live_tup as = "live_tuples", 
          &nb= sp;        n_dead_tup as = "dead_tuples", 
          &nb= sp;     =    trunc(100*n_dead_tup/(n_live_tup+1))::float = "ratio%",
       to_char(last_vacuum, = 'YYYY-MM-DD HH24:MI:SS') as vacuum_date,
       to_char(last_analyze,= 'YYYY-MM-DD HH24:MI:SS') as analyze_date,
       to_char(last_autovacuum, = 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date,
       to_char(last_autoanal= yze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date
FROM   = pg_stat_all_tables 
ORDER BY last_autovacuum; 
Mike W
= --Apple-Mail=_023D800B-2DE3-4C02-A081-ACE12D46E4D3--