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 1oqsPT-0007tl-S2 for pgsql-general@arkaria.postgresql.org; Fri, 04 Nov 2022 08:50:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oqsPS-000254-Ae for pgsql-general@arkaria.postgresql.org; Fri, 04 Nov 2022 08:50:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oqsPR-00024v-UC for pgsql-general@lists.postgresql.org; Fri, 04 Nov 2022 08:50:41 +0000 Received: from mail-ej1-x633.google.com ([2a00:1450:4864:20::633]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oqsPN-0000MF-Cr for pgsql-general@lists.postgresql.org; Fri, 04 Nov 2022 08:50:41 +0000 Received: by mail-ej1-x633.google.com with SMTP id k2so11541067ejr.2 for ; Fri, 04 Nov 2022 01:50:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20210112.gappssmtp.com; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=NUKln5ntleSvlFevlu863dYYSQ/giimtJDpgc71oX/M=; b=em2kTzkOuLGK5g1krAANC4K4kyMFzYg8uzPaR+dgIuHgPaJ5AV3b+3jSHkUVEguTbN DaKv1aWrcvKoH39LdrzYqoNwp+Blarp1zpbsBsWEGSkvbw9ePKDEtQpA3uTlYFR0i99+ 7k1CSLEM/MwjdTp++gc3mszjOiAqERxgIeGH0rSO0Lignl6tzefom4j3JnAOzGHRrt4P ZEl2+ar8hFAtFjXpoYINGhXVmutVYkyMRUUm3ZBLcPZehsvZ6RXSIOZk+KUMEoY8iVGv wyrh+nMuBG3E48jv8epGDL+OAeaz6zJSKJZ4I6SHmur/XcyG5cs3fXj8ziEoWvcqf+Sr UyWw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:mime-version:user-agent:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=NUKln5ntleSvlFevlu863dYYSQ/giimtJDpgc71oX/M=; b=16I9wFCYbLN1H/XAuSlBInEXptEWuQ45XbJ+cEO6sGcmn7wRBYo9Ft/STRYiN+Vduj OwR6Uefhqf8zucHzgttM57PcFCELrgQo6l+xUykuUIITatxPZaKfNsvwwq+P7KaDBdOm ZWlU2YApOWBH8ZSAXfEt0Yg1Q6vADFjGTmc2T2NfgFUXEdVRzq+BRc/wLVvmkzanocSo XevjQCl2kQMvEVMfjojL5KWqrJJ3+YzXrZgadGDDT/DvT50xAdmjgN75n9iyZSzDYWbs I4wuKoOLP9KgNmKEr6c+RtpA96OzkhHzhm5QSekwQf/Lmr4NKlc4/rzl2ccWV5FqVgl+ iZHw== X-Gm-Message-State: ACrzQf1EDsLTghyo6h4V9P37nIz32ofyPe30B8DNurODOvryQDXvRl5O pB5QcxEdES7pFb0gyCXqWHF3ei23/FEw2g== X-Google-Smtp-Source: AMsMyM6jBv1oNauVKyvKUuGit3HALQXi6mlDr0xoeYNM8kP2D/2cqK8/I76aUkWWmcW+GrKTLW16QA== X-Received: by 2002:a17:906:fd85:b0:77b:b538:6472 with SMTP id xa5-20020a170906fd8500b0077bb5386472mr33223282ejb.48.1667551835469; Fri, 04 Nov 2022 01:50:35 -0700 (PDT) Received: from localhost.localdomain ([2001:871:5e:1d8c:788c:407d:490b:9a90]) by smtp.gmail.com with ESMTPSA id c2-20020a17090618a200b0079fe11e97cesm1519421ejf.48.2022.11.04.01.50.34 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 04 Nov 2022 01:50:35 -0700 (PDT) Message-ID: <307bde85482f177cfd0d2bcefc00b61b601a3edc.camel@cybertec.at> Subject: Re: autovacuum hung on simple tables From: Laurenz Albe To: senor , "pgsql-general@lists.postgresql.org" Date: Fri, 04 Nov 2022 09:50:34 +0100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2022-11-04 at 02:50 +0000, senor wrote: > I'm still trying to get a better understanding of the autovacuum process. > This is a different postgres installation as my previous posts and confusing me in new ways. > Still 11.4 running on CentOS 7 and 8 nvme in software raid Not good. That should be 11.17. You are missing over two years of important bug fixes, which could be part of the problem. > This issue started with postgres "...not accepting commands to avoid wraparound...". That does not happen normally. Something must have prevented autovacuum from succeeding: - data corruption that makes VACUUM fail - a prepared transaction or long running transaction that prevents PostgreSQL from cleaning up - a long running session with a temporary table - autovacuum is too slow to cope with the rate at which dead tuples are generated > On this server I was able to stop all access to DB and dedicate resources to only postgres. > I thought I could allow autovacuum to do its thing with a ton of workers. If autovacuum is too slow, this will take a long time. Note that in v11, the default setting for "autovacuum_vacuum_cost_delay" is still 20ms, which makes autovacuum pretty slow. Later, you show that you have "autovacuum_vacuum_cost_delay" set to 0, which is good. Did you change that recently, so that some workers started before that change? You should also crank up "maintenance_work_mem" for autovacuum to be fast. > I think everything boils down to 2 questions: > 1. Can autovacuum or manual vacuum be coerced into dealing with oldest first? Manual VACUUM is the easiest. You can start it on the table with the oldest "relfrozenxid" first. >     1a. Where might I find advice on configuring postgres resources for maximum cpu & > memory maintenance use. In other words quickest path out of "not accepting commands" > land. Besides increasing autovacuum_freeze_max_age. The documentation has a lot about that. The quickest way is to run VACUUM on the table with the oldest "relfrozenxid" manually. I would run VACUUM (VERBOSE), so that you get information if it can clean up or freeze anything. > 2. What can cause autovacuum to stall? Could associated toast or index bne the cause. Potentially data corruption could send a backend into an endless loop. > It appeared that autovacuum was not choosing the tables with the oldest xmin so I > produced an ordered list of oldest tables with: > SELECT oid::regclass, age(relfrozenxid) > FROM pg_class > WHERE relkind IN ('r', 't', 'm') > AND age(relfrozenxid) > 2000000000 > ORDER BY 2 DESC > > The list contained over 6000 tables from pg_toast. They all belonged to daily > reports tables. The reports are created daily and not touched again. > > Most of the autovacuums that did start seem to be hung. Never completing even on the simplest tables. > The newest 2 autovacuums in the list are completing about one every couple seconds. > CPU and disk IO are nearly idle. To see if they are hung, look at "wait_event", "wait_event_type" and "state" in the "pg_stat_activity" rows for the autovacuum workers. High locks on a table have the potential to block an anti-wraparound autovacuum. Again, check for log running and prepared transactions. > I scripted a vacuum loop using the oldest table list. It's extremely slow but it was > making better progress than autovacuum was. > > Using ps I see that there were as many worker processes as defined with autovacuum_max_workers > but pg_stat_activity consistantly showed 19. I killed the script thinking there might be a conflict. > I saw no difference after 30 minutes so restarted script. I am not sure what exactly you are actually doing here, but you should know that there can only be one VACUUM process per table. If there is already an anti-wraparound autovacuum running on the table, a manual VACUUM will simple be blocked until the autovacuum worker is done. > Never saw anything in pg_stat_progress_vacuum. Now that would be weird, except if VACUUM cannot get the required lock on the table. > vacuum settings: >                 name                 |  setting  > -------------------------------------+----------- >  autovacuum                          | on        >  autovacuum_freeze_max_age           | 200000000 >  autovacuum_max_workers              | 40        >  autovacuum_naptime                  | 4         >  autovacuum_vacuum_cost_delay        | 0         >  autovacuum_vacuum_cost_limit        | 5000      >  autovacuum_work_mem                 | -1        >  vacuum_freeze_min_age               | 50000000  >  vacuum_freeze_table_age             | 150000000 > > I'm now thinking that autovacuum getting hung up is what caused the issue to begin with. I see nothing > but the successful vacuums from the script and my own fat-fingering commands in the postgres > logs (set at info). Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com