From: | Avinash Kumar <avinash(dot)vallarapu(at)gmail(dot)com> |
---|---|
To: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: Diagnosing a massive toast file |
Date: | 2019-08-05 17:48:28 |
Message-ID: | CAN0TujcdPFwWUAJvkpNfRHD6thg-28HuxyZN4XghnH8ojhhg_Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Mon, Aug 5, 2019 at 2:43 PM Wells Oliver <wells(dot)oliver(at)gmail(dot)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 <https://www.postgresql.org/docs/11/monitoring-stats.html>
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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Wells Oliver <wells(dot)oliver(at)gmail(dot)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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
>
--
9000799060
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2019-08-05 18:31:45 | RE: Diagnosing a massive toast file |
Previous Message | Wells Oliver | 2019-08-05 17:43:09 | Re: Diagnosing a massive toast file |