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 1kQabb-0004Eg-51 for pgsql-admin@arkaria.postgresql.org; Thu, 08 Oct 2020 18:25:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kQabY-0003wV-5g for pgsql-admin@arkaria.postgresql.org; Thu, 08 Oct 2020 18:25:28 +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 1kQabX-0003w9-S8 for pgsql-admin@lists.postgresql.org; Thu, 08 Oct 2020 18:25:27 +0000 Received: from mail-oi1-x22b.google.com ([2607:f8b0:4864:20::22b]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kQabO-0000BW-KY for pgsql-admin@lists.postgresql.org; Thu, 08 Oct 2020 18:25:24 +0000 Received: by mail-oi1-x22b.google.com with SMTP id 16so7296146oix.9 for ; Thu, 08 Oct 2020 11:25:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=8xfYw5MyjLBHbhYMDBvm2nEX487HPaN79/bAYNvhLvk=; b=gzboZhtXxyFhA3EtXcU3mOcY0G/xbm+K+sw7PPi2UhBt0bjDpbGq1yhfz0UohGXVpx r7Jvz6IStTb00jG6UIz5xyFqPOOa2P+8iQ8TxYOEeNVoI0j0IUxhUbALO8e2yKMzkJyI F8oV6Ws0SODArIWcRIyTc6FRgpawnvqamDNHDtSczRsJ00uDmPd4EuRhoDs1tUNpmzHA Etu1wdOd8rdK0czw+LFQIr6r6bmZS7jddSqDemEDnOID1iVmOoklsrlRlPTpKC6yHNOY hjMI8gFT1hO+iTQ2+DYcAecjpY85V7TkYbkYA2PJGRN1g6CSao6EbRnQldJ2zP8cj3Yg xGAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=8xfYw5MyjLBHbhYMDBvm2nEX487HPaN79/bAYNvhLvk=; b=sUSlcSJJTKdWjXJo6l8ATjVFtisVqSNP4TF0lx+SjNBw7IPCdMtgisRyBYa0ypKfC2 dA+pYQx5Sj7TNxERfROdHY56TNQB/IDhklk4Q3lus2x75FL8mcLg23uFQEHF7MBdNv9o 7IQQ6BtYX3dvHIGkTRIk/oRhsBz/jy1PT30DJTgZW+NNYyd4bUchVZqQv3cQN1LBUJ3l bd3LDSvUoRtuoGjuHjN1JMiYbsY4Iteo5KejIvyrJfRPSLRG9uibW+ci4m4Zlj1DBPWU kD1y6Hogh3z5d/e+EYDZKw5FzUYnifh59vnMYfxI/kHGCA3G79uhp7wRzWwnXUXpJZTF 06Ug== X-Gm-Message-State: AOAM531+Fi/r2VweMRDetUPjPbC+KgEGO3whrLAPsJW/tki5IoC7DDwR tZ2l9EX2U3bo9aY9mYqiYOsISSXcHdsscw== X-Google-Smtp-Source: ABdhPJyZzGDsjcupRsdXFsXagbaEr4DrSRFgZatG1v897UAI52oQPW8fYw80plRYHS5YiweKibxLAA== X-Received: by 2002:aca:120f:: with SMTP id 15mr69212ois.155.1602181516371; Thu, 08 Oct 2020 11:25:16 -0700 (PDT) Received: from [192.168.1.10] (ip174-67-143-111.no.no.cox.net. [174.67.143.111]) by smtp.googlemail.com with ESMTPSA id r62sm5393685oih.12.2020.10.08.11.25.15 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 08 Oct 2020 11:25:15 -0700 (PDT) Subject: Re: Vacuum not working To: pgsql-admin@lists.postgresql.org References: <1e53e269f55345a387ed6030285a0f30@izum.si> From: Ron Message-ID: <5ca002b5-8610-852d-ab75-19ed1be514b1@gmail.com> Date: Thu, 8 Oct 2020 13:25:14 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101 Thunderbird/68.10.0 MIME-Version: 1.0 In-Reply-To: <1e53e269f55345a387ed6030285a0f30@izum.si> Content-Type: multipart/alternative; boundary="------------15EB0EFC1B25E3DF2AF6BA0D" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------15EB0EFC1B25E3DF2AF6BA0D Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 10/8/20 12:32 PM, Domen Šetar wrote: > > Hi guys, > > I have following problem: I use POWA to collect performance data from 20 > postgresql servers. Vaccum doesn't clean the largest table after POWA > database is growing large. Here are data taken at 19:27: > >                   relname | last_vacuum | last_autovacuum  |     > n_tup      | dead_tup    |  av_threshold  | expect_av > > --------------------------------------------+-------------+------------------+----------------+----------------+----------------+----------- > > powa_all_relations_history_current |             | 2020-10-08 18:24 |    > 162,254,592 | 82,045,219 |        162,280 | * > > Can you please advise me how to tune poasgresql vacuum parameters to get > this table cleaned? All other tables are vacummed as expected. > Is there an open transaction causing the vacuum to not free thee dead tuples? -- Angular momentum makes the world go 'round. --------------15EB0EFC1B25E3DF2AF6BA0D Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 10/8/20 12:32 PM, Domen Šetar wrote:

Hi guys,

 

I have following problem: I use POWA to collect performance data from 20 postgresql servers. Vaccum doesn't clean the largest table after POWA database is growing large. Here are data taken at 19:27:

 

 

                  relname                   | last_vacuum | last_autovacuum  |     n_tup      |    dead_tup    |  av_threshold  | expect_av

--------------------------------------------+-------------+------------------+----------------+----------------+----------------+-----------

powa_all_relations_history_current         |             | 2020-10-08 18:24 |    162,254,592 |     82,045,219 |        162,280 | *

 

Can you please advise me how to tune poasgresql vacuum parameters to get this table cleaned? All other tables are vacummed as expected.


Is there an open transaction causing the vacuum to not free thee dead tuples?

--
Angular momentum makes the world go 'round.
--------------15EB0EFC1B25E3DF2AF6BA0D--