Обсуждение: Vacuum not deleting tuples when lockless
![](https://share.polymail.io/v2/z/a/NWI5YzNmOGFhYTIw/oTyq1okzxIl9rDEUYvlZd1IbliqBIw6LD5sS-dqfFp9CukmhRCDm4xEgpz8dpsrEhW9QkjJIj7Awc4V8y5HiZjgOHd_iFeLNCC3_XUXEFGcoQc4nxc_QvSj7W0qiGJq61GI3cMCfP5Fkob0nFtAY5ZnVQO9wVzvqg6kWd28=.png)
```
DETAIL: 113257 dead row versions cannot be removed yet.
```
I've been investigating the reasons for vacuum not being able to do it's work and I found that generally the problem is caused by open transactions referencing the dead tuples. I also found that locking can be a problem as well.
I used this query to check the locks:
```
```
I'm not sure where else to look for a potential issue that could be causing this problem. A few days back we had to use re_pack on the given table to solve our performance issues.
Martín Fernández <fmartin91@gmail.com> writes: > * > Hello, > > We are experiencing some `vacuum` issues with a given table > (potentially more). When a manual vacuum runs on the given table it > seems that the `vacuum` process is not doing the expected cleanup. > > ``` > DETAIL: 113257 dead row versions cannot be removed yet. > ``` > > I've been investigating the reasons for vacuum not being able to do > it's work and I found that generally the problem is caused by open > transactions referencing the dead tuples. I also found that locking > can be a problem as well. > > I did check that no long running transaction was happening and no > locking was happening before running `vacuum` on the given table. > > I used this query to check the locks: > > ``` > SELECT query, state,locktype,mode > FROM pg_locks > JOIN pg_stat_activity > USING (pid) > WHERE relation::regclass = 'my_table'::regclass > AND granted IS TRUE; > ``` > > I'm not sure where else to look for a potential issue that could be > causing this problem. A few days back we had to use re_pack on the Also check for... * Old open prepared xacts. * Inactive replication slots. * Standbys confi'd with large vacuum clean up delay and feedback enabled. HTH > given table to solve our performance issues. > > Important thing to clarify is that we are using postgresql 9.2.24 > > Thanks in advance! > > Best, > Martín > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
=?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91@gmail.com> writes: > We are experiencing some `vacuum` issues with a given table (potentially more). When a manual vacuum runs on the giventable it seems that the `vacuum` process is not doing the expected cleanup. > DETAIL: 113257 dead row versions cannot be removed yet. Locks don't really have anything to do with that: what does matter is how old is the oldest open transaction, because that determines the "event horizon" that dead row versions have to fall below before they can be removed. That oldest transaction might not be holding any locks at the moment, but it doesn't matter, because in principle it could ask to read this table later --- and it should see the table's contents as of its snapshot. Serializable transactions are worse than repeatable-read transactions for this purpose, because the former will keep a snapshot as of their start time. As Jerry mentioned, replication slots can also act like open transactions for this purpose, though I don't recall how much of that behavior is present in 9.2.x. regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > =?UTF-8?q?Mart=C3=ADn_Fern=C3=A1ndez?= <fmartin91@gmail.com> writes: > >> We are experiencing some `vacuum` issues with a given table >> (potentially more). When a manual vacuum runs on the given table it >> seems that the `vacuum` process is not doing the expected cleanup. > >> DETAIL: 113257 dead row versions cannot be removed yet. > > Locks don't really have anything to do with that: what does matter is > how old is the oldest open transaction, because that determines the > "event horizon" that dead row versions have to fall below before they > can be removed. That oldest transaction might not be holding any locks > at the moment, but it doesn't matter, because in principle it could ask > to read this table later --- and it should see the table's contents as > of its snapshot. > > Serializable transactions are worse than repeatable-read transactions > for this purpose, because the former will keep a snapshot as of their > start time. > > As Jerry mentioned, replication slots can also act like open transactions > for this purpose, though I don't recall how much of that behavior is > present in 9.2.x. Oops, didn't notice OP was on 9.2! Presume none, since I don't think we got rep slots till 9.4 :-) > > regards, tom lane > > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 312.241.7800
![](https://share.polymail.io/v2/z/a/NWI5ZDc5MmNhYTIw/eDZLnWZVV7pB3OZ1aK16HRI5LJ7R9Ze9MOdJFUFHx0ig3ewZXowYUUfOZR3PRTfeg2kbyifrtjaIEW2qNlazHmnCbuFZUoNzP3SdSMOK5OVx1UPXrT3Td8DV921fwr5xNy0JXPov69U_oivR83QNs6LbgJ7lEIWX_cTvjys=.png)
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Martín Fernández <fmartin91@gmail.com> writes:
>
>> We are experiencing some `vacuum` issues with a given table
>> (potentially more). When a manual vacuum runs on the given table it
>> seems that the `vacuum` process is not doing the expected cleanup.
>
>> DETAIL: 113257 dead row versions cannot be removed yet.
>
> Locks don't really have anything to do with that: what does matter is
> how old is the oldest open transaction, because that determines the
> "event horizon" that dead row versions have to fall below before they
> can be removed. That oldest transaction might not be holding any locks
> at the moment, but it doesn't matter, because in principle it could ask
> to read this table later --- and it should see the table's contents as
> of its snapshot.
>
> Serializable transactions are worse than repeatable-read transactions
> for this purpose, because the former will keep a snapshot as of their
> start time.
>
> As Jerry mentioned, replication slots can also act like open transactions
> for this purpose, though I don't recall how much of that behavior is
> present in 9.2.x.
Oops, didn't notice OP was on 9.2! Presume none, since I don't think we
got rep slots till 9.4 :-)
>
> regards, tom lane
>
>
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800