Обсуждение: Diagnosing a massive toast file

Поиск
Список
Период
Сортировка

Diagnosing a massive toast file

От
Wells Oliver
Дата:
Hi all, I have not seen this before, but one our toast tables has gotten to 15GB, the biggest table in the DB. I don't know why, is there some way I can diagnose what's causing this?

 I don't think I've ever seen a toast table even half of this size, so it's concerning.

--

Re: Diagnosing a massive toast file

От
Ron
Дата:
On 8/5/19 11:32 AM, Wells Oliver wrote:
Hi all, I have not seen this before, but one our toast tables has gotten to 15GB, the biggest table in the DB. I don't know why, is there some way I can diagnose what's causing this?

 I don't think I've ever seen a toast table even half of this size, so it's concerning.

We've got lots of toast tables that big.  Are you asking how to find the "human readable name" associated with the toast table?

And have you vacuumed it lately?

--
Angular momentum makes the world go 'round.

Re: Diagnosing a massive toast file

От
Wells Oliver
Дата:
Yeah, trying to figure out what actual table is clearly in need of a vacuum b/c of the size of that toast table.

On Mon, Aug 5, 2019 at 9:36 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 8/5/19 11:32 AM, Wells Oliver wrote:
Hi all, I have not seen this before, but one our toast tables has gotten to 15GB, the biggest table in the DB. I don't know why, is there some way I can diagnose what's causing this?

 I don't think I've ever seen a toast table even half of this size, so it's concerning.

We've got lots of toast tables that big.  Are you asking how to find the "human readable name" associated with the toast table?

And have you vacuumed it lately?

--
Angular momentum makes the world go 'round.


--

Re: Diagnosing a massive toast file

От
Tom Lane
Дата:
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



Re: Diagnosing a massive toast file

От
Wells Oliver
Дата:
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


--

Re: Diagnosing a massive toast file

От
Wells Oliver
Дата:
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


--


--

Re: Diagnosing a massive toast file

От
Ron
Дата:
"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.

Re: Diagnosing a massive toast file

От
Tom Lane
Дата:
Wells Oliver <wells.oliver@gmail.com> writes:
> 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?

Old open transaction somewhere (possibly a prepared transaction?).
Or a replication slot that's holding back the xmin horizon due to
not keeping up.

            regards, tom lane



Re: Diagnosing a massive toast file

От
Wells Oliver
Дата:
Thanks, that was it exactly. PGAdmin session opened for a week. Argh. Gotta have some conversations with some folks.

Do you guys have any kind of regular monitoring in place to flag users who don't politely close their connections?

On Mon, Aug 5, 2019 at 10:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> 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?

Old open transaction somewhere (possibly a prepared transaction?).
Or a replication slot that's holding back the xmin horizon due to
not keeping up.

                        regards, tom lane


--

Re: Diagnosing a massive toast file

От
Avinash Kumar
Дата:


On Mon, Aug 5, 2019 at 2:43 PM Wells Oliver <wells.oliver@gmail.com> wrote:
Thanks, that was it exactly. PGAdmin session opened for a week. Argh. Gotta have some conversations with some folks.

Do you guys have any kind of regular monitoring in place to flag users who don't politely close their connections?
pg_stat_activity view would do the trick for you. 
Search for the connections that are running long for more than a few hours ? or days ?
See if any idle in transactions that have now() - state_change, more than a few mins ? or hours ? or days ?
 

On Mon, Aug 5, 2019 at 10:24 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Wells Oliver <wells.oliver@gmail.com> writes:
> 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?

Old open transaction somewhere (possibly a prepared transaction?).
Or a replication slot that's holding back the xmin horizon due to
not keeping up.

                        regards, tom lane


--


--
9000799060

RE: Diagnosing a massive toast file

От
Igor Neyman
Дата:

From: Wells Oliver [mailto:wells.oliver@gmail.com]
Sent: Monday, August 05, 2019 1:43 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Subject: Re: Diagnosing a massive toast file

Thanks, that was it exactly. PGAdmin session opened for a week. Argh. Gotta have some conversations with some folks.

 

Do you guys have any kind of regular monitoring in place to flag users who don't politely close their connections?

--

Wells Oliver
wells.oliver@gmail.com

 

 

idle_in_transaction_session_timeout GUC is your friend.

Read on it in the docs.

 

Regards,

Igor Neyman

 

 

 

Re: Diagnosing a massive toast file

От
Tom Lane
Дата:
Igor Neyman <ineyman@perceptron.com> writes:
> From: Wells Oliver [mailto:wells.oliver@gmail.com]
>> Do you guys have any kind of regular monitoring in place to flag users who don't politely close their connections?

> idle_in_transaction_session_timeout GUC is your friend.

Good idea, though it's only been around since 9.6.

Also, if you just want to monitor rather than impose a hard limit,
I imagine you can get Nagios to watch for this.

            regards, tom lane