Search Postgresql Archives

Re: Vacuum and freeing dead rows

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 7/5/19 3:16 AM, Simon T wrote:
Hi,

I have a very heavily updated table in a Postgres 9.6.10 database with
lots of disk bloat. Every row is updated about once a minute, and
little to no inserts. Approx 18k rows total. The table has bloated
from ~1700 KB to about 6 GB over a few weeks time. I'm trying to
understand why vacuum hasn't made dead rows available for re-use.

[snip]
And in case it is relevant:

appdb=# SELECT pid, datname, usename, state, backend_xmin
appdb-# FROM pg_stat_activity
appdb-# WHERE backend_xmin IS NOT NULL
appdb-# ORDER BY age(backend_xmin) DESC;
  pid | datname | usename | state | backend_xmin
-------+---------------+----------+---------------------+--------------
 10921 | appdb | app | idle in transaction | 3501305052

"idle IN TRANSACTION" is never good.  Transactions should always be as short as possible.

 10919 | appdb | app | idle in transaction | 3501305052
 10916 | appdb | app | idle in transaction | 3501305052
 27935 | appdb | app | idle in transaction | 3501305052
 24500 | appdb | postgres | active | 3501305052
 10914 | appdb | app | active | 3501305052
 20671 | appdb | postgres | active | 3501305052
 11817 | appdb | app | active | 3501305052
  1988 | appdb | app | active | 3501305052
 15041 | appdb | postgres | active | 3501305052
  9916 | appdb | postgres | active | 3501305052
 10912 | appdb | app | idle in transaction | 3501305052
 10909 | appdb | app | idle in transaction | 3501305052
(13 rows)

Add backend_start to that query.  I'd kill any idle transactions are more than 30 minutes old.  (Of course, since they're "idle IN TRANSACTION", you'd lose stuff.

--
Angular momentum makes the world go 'round.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux