Re: Diagnosing a massive toast file
Avinash Kumar <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.admin |
|---|---|
| Message-ID | <CAN0TujcdPFwWUAJvkpNfRHD6thg-28HuxyZN4XghnH8ojhhg_Q@mail.gmail.com> |
On Mon, Aug 5, 2019 at 2:43 PM Wells Oliver <[email protected]> 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 <[email protected]> wrote: > >> Wells Oliver <[email protected]> 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 > [email protected] <[email protected]> > -- 9000799060