Обсуждение: Diagnosing a massive toast file
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.
On 8/5/19 11:32 AM, Wells Oliver wrote:
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?
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.
Angular momentum makes the world go 'round.
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.
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
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
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
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
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
--Wells Oliver
wells.oliver@gmail.com
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
"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
--Wells Oliver
wells.oliver@gmail.com
--Wells Oliver
wells.oliver@gmail.com
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.
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
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
--
Wells Oliver
wells.oliver@gmail.com
wells.oliver@gmail.com
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 ?
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
--Wells Oliver
wells.oliver@gmail.com
9000799060
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
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