Обсуждение: Maintenance
Hello team,We have a 12TB db and one table having around 7 TB data , is there any option we can removed the dead tuple and size also increase for that partition without downtime .Vaccum full we can't do because ofexclusive lock in tables during processing.We have critical application so don't get the downtown so how to achieve this please let us knowThank youSunil
Sunil Jadhav schrieb am 08.05.2024 um 11:17: > We have a 12TB db and one table having around 7 TB data , is there > any option we can removed the dead tuple and size also increase for > that partition without downtime . Vaccum full we can't do because of > exclusive lock in tables during processing. > > We have critical application so don't get the downtown so how to > achieve this please let us know Have a look at pg_squeeze or pg_repack but both will create a copy of the table, so if you don't have enough free disk space neither is an option. You probably also want to investigate if making autovacuum more aggressive helps Did you validate that those dead tuples you see really are a problem and are not re-used?
Did you validate that those dead tuples you see really are a problem and
are not re-used?
On 5/8/24 3:10 PM, Ron Johnson wrote:
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed
On 5/8/24 3:10 PM, Ron Johnson wrote:> Don't dead tuples have to be vacuumed away before the space can be reused?
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed
Tuples are not deleted. They are zero’d out and the space becomes available as free tuple space.
I would research your vacuum stats and think to change any auto-vacuum settings per table via ALTER TABLE command AFTER you can vacuum the entire db without performance degradation.
I would not vacuum a 4TB at once. I would chunk it out over schemas, etc. once that is done, vacuum db regularly as needed or set up cron jobs to vacuum the heavy hitter tables.
Adjusting the autovacuum auto-scale too low, 3-4 places right of the decimal, can have performance degradation.
After your maintenance, to reclaim linux space (if wanted), you have to backup, DROP db, then CREATE db, reload backup.
If you are on an LVM, you may want to look at those settings too.
From: Wasim Devale <wasimd60@gmail.com>
Sent: Wednesday, May 8, 2024 7:31 AM
To: vrms <vrms@netcologne.de>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: [EXTERNAL] Re: Maintenance
vrms you are correct.
On Wed, 8 May, 2024, 7:59 pm vrms, <vrms@netcologne.de> wrote:
On 5/8/24 3:10 PM, Ron Johnson wrote:
> Don't dead tuples have to be vacuumed away before the space can be reused?
I think that is correct. As per my understanding ...
VACUUM
- removes dead tuples and makes the consumed space available for future data
- does not free disk space
- does not cause any locks
VACUUM FULL
- removes dead tuples and frees actual disk space
- causes locks on the table being VACUUMed