Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hugf5-0003Oi-BR for pgsql-admin@arkaria.postgresql.org; Mon, 05 Aug 2019 17:20:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hugf4-0001zw-6U for pgsql-admin@arkaria.postgresql.org; Mon, 05 Aug 2019 17:20:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hugf3-0001zp-Rg for pgsql-admin@lists.postgresql.org; Mon, 05 Aug 2019 17:20:41 +0000 Received: from mail-io1-xd41.google.com ([2607:f8b0:4864:20::d41]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hugf1-0008Ma-EK for pgsql-admin@lists.postgresql.org; Mon, 05 Aug 2019 17:20:41 +0000 Received: by mail-io1-xd41.google.com with SMTP id g20so168934405ioc.12 for ; Mon, 05 Aug 2019 10:20:38 -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=NARw9IkphJcNSNvoSh6RTCMWpaNbVqBUBXaY3OgjYJo=; b=DNfasxC+EvRYjnBTjQShmub44wTRrpWnSIbHZOsJg7hYGH3uh3Z0TPLgFj35ZzvWGl gSu89U3fuvjDxzNy8GNesGZM82ZRVduiEWpXPQrGpjsuKQdbvRQwkIXUNgY/nS3GERLx PJwp9CKYxsRF/yL28t3QF4BDa4OJMZpSv7ub+QNrUBXs46ykvtruYTFJ4kxilUZ0tgJP 4Mskbz5VyUvWSQw8LEiEo1aXsm4zgqS/hW810Xg6RTQrOxa4mV+CJZoJ7J2z7EkogAt+ 0ajKuRXDMIQOlc1wJoR3yU4WSl3bkv3s6JwDkSBIgkH1tTelNr7wgXmRtu/i9ncfPXYE 0v5g== 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=NARw9IkphJcNSNvoSh6RTCMWpaNbVqBUBXaY3OgjYJo=; b=UWROMXCpmvhHBnqhsA3xf/30HXJJ3sUxaFDMjz6EYF9snCrtJOrDCbV2fQduLbD23K DX8OZPh7/hHqHv9DFDxJvgDIB1EXP7u9vKzQ4P2K+XaNzMmVAOPg+B07cNqV/kJz2fmY PKsxu3BV5bXADra32UT9GMvXGPZRbqGxAyY+aqURC3yL7hwYWWKH/jTw24LSICAHWE3d z1oT7Ex22AWdOpN7ihMF19E5HMPBUjdBLJJXF62uU2M+00cwYkOCUE79DMlOcjBVdvrm MsrL2afD2Dpupn4wyJD7DWOKO1SLJTV5DB9WZgR9EcfEz8kIRHo/iVUWJtq1T2xAhrCo lLJw== X-Gm-Message-State: APjAAAUm2C2Emcc7r2jLGRt56jUdBQV+bCSZyzlwAtMk9NcyJD6e66Yq HKexBweaRQev69rvxLZzDVr9fj2COb8lAQ== X-Google-Smtp-Source: APXvYqzoEnC3sj+5ywZB3+QNHQcC7bTCDKlzIZd/bPCt5ZMRaOQVgFiajrebOqbJcjxiEUmy3UByTg== X-Received: by 2002:a6b:ce19:: with SMTP id p25mr28303991iob.201.1565025636519; Mon, 05 Aug 2019 10:20:36 -0700 (PDT) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id y5sm88401191ioc.86.2019.08.05.10.20.34 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 05 Aug 2019 10:20:34 -0700 (PDT) Subject: Re: Diagnosing a massive toast file To: pgsql-admin@lists.postgresql.org References: <3da6ffe8-28ef-fb9a-e6e8-1f0d243cc8e9@gmail.com> <6018.1565023741@sss.pgh.pa.us> From: Ron Message-ID: Date: Mon, 5 Aug 2019 12:20:33 -0500 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.7.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------566E117D34AB06121335BEA0" 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. --------------566E117D34AB06121335BEA0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit "An open transaction" is the first place to look. On 8/5/19 12:03 PM, Wells Oliver wrote: > As a follow up, n_dead_tup from pg_stat_sys_tables for this TOAST table is > 7447444, live tuples, 623982, and tup_del 20823469. vacuum_count is 0. > > Why can't I free those rows up? > > On Mon, Aug 5, 2019 at 10:00 AM Wells Oliver > wrote: > > Appreciate it, guys. I understand it being large isn't itself a > problem, but relative to history and the lack of real changes, it's > just strange and I'd like to better understand what is going on... > > I tracked it down to a specific table, and then doing a VACUUM FULL > ANALYZE on that table yields: 108765 dead row versions cannot be > removed yet. > > Which strikes me as odd. Any reading I can do to better understand why > so many (relative to the overall table size) dead rows cannot be removed? > > > On Mon, Aug 5, 2019 at 9:49 AM Tom Lane > wrote: > > Wells Oliver > writes: > > Yeah, trying to figure out what actual table is clearly in need > of a vacuum > > b/c of the size of that toast table. > > Something like > > select relname from pg_class > where reltoastrelid = 'pg_toast.pg_toast_NNN'::regclass; > > (or, if you have potential duplicate relnames, select > oid::regclass ...) > > The mere fact that it's big does not indicate a problem, though. > >                         regards, tom lane > > > > -- > Wells Oliver > wells.oliver@gmail.com > > > > -- > Wells Oliver > wells.oliver@gmail.com -- Angular momentum makes the world go 'round. --------------566E117D34AB06121335BEA0 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit "An open transaction" is the first place to look.

On 8/5/19 12:03 PM, Wells Oliver wrote:
As a follow up, n_dead_tup from pg_stat_sys_tables for this TOAST table is 7447444, live tuples, 623982, and tup_del 20823469. vacuum_count is 0.

Why can't I free those rows up?

On Mon, Aug 5, 2019 at 10:00 AM Wells Oliver <wells.oliver@gmail.com> wrote:
Appreciate it, guys. I understand it being large isn't itself a problem, but relative to history and the lack of real changes, it's just strange and I'd like to better understand what is going on...

I tracked it down to a specific table, and then doing a VACUUM FULL ANALYZE on that table yields: 108765 dead row versions cannot be removed yet.

Which strikes me as odd. Any reading I can do to better understand why so many (relative to the overall table size) dead rows cannot be removed?


On Mon, Aug 5, 2019 at 9:49 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> Yeah, trying to figure out what actual table is clearly in need of a vacuum
> b/c of the size of that toast table.

Something like

select relname from pg_class
where reltoastrelid = 'pg_toast.pg_toast_NNN'::regclass;

(or, if you have potential duplicate relnames, select oid::regclass ...)

The mere fact that it's big does not indicate a problem, though.

                        regards, tom lane


--


--

--
Angular momentum makes the world go 'round.
--------------566E117D34AB06121335BEA0--