Thread: Recommendation to run vacuum FULL in parallel

On 4/3/19 12:50 AM, Perumal Raj wrote:

Hi ALL

We are  planning to reclaim unused space from 9.2 Version postgres Cluster,

Method : VACUUM FULL

Does every table have so much free space that it's impractical to just let the files just get refilled by normal usage?

DB Size : 500 GB

Expected space to reclaim 150 GB

work_mem : 250 MB

maintenance_work_mem : 20 GB

Question :

1. vacuumdb --j option (Parallel) not available for version 9.2.

  How to run vacuum full in parallel ? At present its taking 8Hrs if i run sequential ( vacuum full verbose;)

2. If we run vacuum full, Do we need to run REINDEX/ANALYZE exclusively ?

3. What is the best way to run VACUUM FULL with less window.

A good way to run any task like this in parallel is to generate X lists of objects, and then process each list in parallel.

Perumal Raj wrote:
> We are  planning to reclaim unused space from 9.2 Version postgres Cluster,
> 
> Method : VACUUM FULL
> DB Size : 500 GB
> Expected space to reclaim 150 GB
> work_mem : 250 MB
> maintenance_work_mem : 20 GB
> 
> Question :
> 
> 1. vacuumdb --j option (Parallel) not available for version 9.2.
>   How to run vacuum full in parallel ? At present its taking 8Hrs if i run sequential ( vacuum full verbose;)

Run several scripts in parallel, where each of them vacuums some bloated tables.
Be warned that VACUUM (FULL) is quite I/O intense, so too much parallelism
might overload your I/O system and harm performance.

> 2. If we run vacuum full, Do we need to run REINDEX/ANALYZE exclusively ?

You don't need to run REINDEX, because that happens automatically.
You can use VACUUM (FULL, ANALYZE) to also gather statistics.

> 3. What is the best way to run VACUUM FULL with less window.

Identify which tables really need it rather than VACUUMing everything.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> Does *every* table have *so much* free space that it's impractical to 
> just
> let the files just get refilled by normal usage?
>
Ideally VACUUM FULL should not require a giant lock on the table.

Sometimes a table's usage pattern involves much more updates than 
inserts, which gradually uses more and more unused space that is never 
used again by postgres, and plain autovacuuming doesn't return it to the 
OS. So DB size (as witnessed by psql's \l+) uses 5-6x times the space it 
actually needs. And using vacuum full is prohibitive because of the 
exclusive lock it takes on the table, preventing both writes and reads. 
Since rewriting a table is a completely internal operation from clients' 
POV, hopefully one day we will see a concurrent version of vacuum full.

On 2019-04-03 13:12:56 +0400, rihad wrote:
> Ideally VACUUM FULL should not require a giant lock on the table.
[...]
> Since rewriting a table is a completely internal operation from
> clients' POV, hopefully one day we will see a concurrent version of
> vacuum full.

There are (at least) pg_repack and pg_squeeze. It would be nice to have
that in the core, though.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

On 04/03/2019 06:40 PM, Michael Lewis wrote:
> "Sometimes a table's usage pattern involves much more updates than
> inserts, which gradually uses more and more unused space that is never
> used again by postgres, and plain autovacuuming doesn't return it to the
> OS."
>
> Can you expound on that? I thought that was exactly what autovacuum 
> did for old versions of rows whether dead because of delete or update, 
> so I am surprised by this statement. I thought vacuum full was only 
> ever needed if storage space is an issue and the table is not expect 
> to quickly re-expand to current size on disk from new churn of tuples.


 From what I understand from the docs updates keep older versions of 
rows intact because other transactions might still use them (this is the 
essence of MVCC), and autovacuuming (plain VACUUM) marks that space as 
available when it is run, so future inserts can reuse it. In case the 
number of updates is much greater than the number of inserts, the unused 
zombie space gradually creeps up.

On 2019-04-03 18:49:02 +0400, rihad wrote:
> On 04/03/2019 06:40 PM, Michael Lewis wrote:
> > "Sometimes a table's usage pattern involves much more updates than
> > inserts, which gradually uses more and more unused space that is never
> > used again by postgres, and plain autovacuuming doesn't return it to the
> > OS."
> >
> > Can you expound on that? I thought that was exactly what autovacuum did
> > for old versions of rows whether dead because of delete or update, so I
> > am surprised by this statement. I thought vacuum full was only ever
> > needed if storage space is an issue and the table is not expect to
> > quickly re-expand to current size on disk from new churn of tuples.
>
>
> From what I understand from the docs updates keep older versions of rows
> intact because other transactions might still use them (this is the essence
> of MVCC), and autovacuuming (plain VACUUM) marks that space as available
> when it is run, so future inserts can reuse it.

And future updates can reuse it, too (an update is very similar to an
insert+delete).

> In case the number of updates is much greater than the number of
> inserts, the unused zombie space gradually creeps up.

Not if autovacuum has a chance to run between updates.

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

> And future updates can reuse it, too (an update is very similar to an
> insert+delete).


Hm, then it's strange our DB takes 6 times as much space compared to 
freshly restored one (only public schema is considered).

> Not if autovacuum has a chance to run between updates.

Ours is run regularly, although we had to tweak it down not to interfere 
with normal database activity, so it takes several hours each run on the 
table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from 
default 0.2.

On 4/3/19 3:45 PM, Perumal Raj wrote:

Hi Stephen 

Thanks for the response ,

Version : 9.2 

We never ran VACUUM FULL in the past, All we are doing just manual vacuum ( Weekly ) .

Based on the Observation ( test run ) , we were able to reclaim 150 GB out of 500 GB .

We are heading to a planned down time soon , So thinking  to run FULL during that time .

Reason behind to run FULL : 1. Reclaim unused space which postgres never using it.

Did you purge a lot of records?

                                                2. Considering  FULL may increase the performance.

Maybe. But choose your tables wisely.

                                                3. Daily backup size and time  will be reduced after reclaiming 150GB.

How are you currently performing backups?  (The size won't change if you're using pg_dump, and it won't change much if you're using pgbackrest with the compression option -- thought it will probably run faster.)

Bottom line:

1. choose your tables wisely.
2. make sure you have enough disk space.
3. Either autovacuum more aggressively or explicitly vacuum certain tables from a cron job.

Thanks,

Raj

On Wed, Apr 3, 2019 at 1:16 PM Stephen Eilert <contact@stepheneilert.com> wrote:

> Ideally VACUUM FULL should not require a giant lock on the table.

It is a massively expensive operation, regardless. Not sure if it is something you want to run in production outside a maintenance window.

I would argue that frequent vacuum full is an antipattern. This will become a matter of superstition in your company.

If db size growth is a problem, make autovacuum more agressive. Or run your manual vacuum job (not full) more often than a week. Daily, if you have to. This will not reclaim disk space as reported by the OS, but it should make the space available for new row versions, so db should mostly stop growing from the OS point of view(mostly, because you may be adding new data, right?). If it is still a problem, then there may be something else going on.

Which PG version is that?

— Stephen

On Apr 3, 2019, 10:02 AM -0700, Perumal Raj <perucinci@gmail.com>, wrote:

Hi All

Thanks for all your valuable  inputs,

Here is some more data,

Though we have 150 GB free space spread across 500 Tables , Every alternative day DB is growing with 1 GB rate.

Also,We have manual vacuum job scheduled to run weekly basis, So seems to be space is not reusing all the time ?

So conclude the requirement here , The only way to parallelism is multiple script. And no need to do REINDEX exclusively.

Question : Do we need to consider  Table dependencies while preparing script in order to avoid table locks during vacuum full ?

At present Maintenance work memory set to 20 GB.

Question : Do we need to tweak any other parameters ?

Note: 

We are planning this activity with Application Downtime only.

Let me know if i missed anything.

Regards,

Raj

 

On Wed, Apr 3, 2019 at 8:42 AM rihad <rihad@mail.ru> wrote:

> And future updates can reuse it, too (an update is very similar to an
> insert+delete).


Hm, then it's strange our DB takes 6 times as much space compared to
freshly restored one (only public schema is considered).

> Not if autovacuum has a chance to run between updates.

Ours is run regularly, although we had to tweak it down not to interfere
with normal database activity, so it takes several hours each run on the
table. We did that by setting autovacuum_vacuum_scale_factor = 0.05 from
default 0.2.

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

On 2019-04-03 19:42:03 +0400, rihad wrote:
> > And future updates can reuse it, too (an update is very similar to an
> > insert+delete).
>
> Hm, then it's strange our DB takes 6 times as much space compared to freshly
> restored one (only public schema is considered).

This is indeed strange if you accumulated that much bloat gradually (as
you wrote). It is much less strange if you did some massive
reorganisations in the past (In one case I witnessed, changes had to be
made to almost every value in 4 or 5 columns of a large table. So the
person doing the updates first issued an update on the first column,
checked that the result looked plausible, then issued an update on the
second column, and so on. The result was of course massive bloat).

        hp

--
   _  | Peter J. Holzer    | we build much bigger, better disasters now
|_|_) |                    | because we have much more sophisticated
| |   | hjp@hjp.at         | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>

Look also at pg_stat_all_tables.n_dead_tup for tables which are candidates for vacuuming.

Thanks Kevin for the inputs,

In my Case there are 500+ Tables and biggest chunk 30GB ( Table only) + its indexes.

So i have created 6 batches and executed in parallel . All my scripts completed in 2 Hours and my DB size came down from 500GB to 300GB.

Yes i do see CPU spike, But i did whole activity with full apps down time.

Going forward i am going to run vacuum daily basis to maintain the DB size.

Also Table/DB Age came down drastically.

Thanks

Raj

On Thu, Apr 4, 2019 at 12:53 PM Kevin Brannen <KBrannen@efji.com> wrote:

From: Perumal Raj <perucinci@gmail.com>

So conclude the requirement here , The only way to parallelism is multiple script. And no need to do REINDEX exclusively.

Question : Do we need to consider  Table dependencies while preparing script in order to avoid table locks during vacuum full ?

 

We have a small bash script (see below) that get the list of tables and their sizes, sorted smallest to largest, and do “vacuum full” one at a time because (as someone else pointed out) this is very I/O intensive. That order also helps to ensure we finish because some of our installs are at the edge of running out of space (an issue we’re dealing with). I probably wouldn’t have a problem doing 2 at a time, but we do this in the middle of the night when activity is lowest and it only takes 1-2 hours, so we’re good with it. It sounds like you have a lot more data though.

 

You might also consider putting the data into different tablespaces which are spread over multiple disks to help I/O. If you can, use SSD drives, they help with speed quite a bit. 😊

 

Don’t worry about table dependencies. This is a physical operation, not a data operation.

 

HTH,

Kevin

 

    $PGPATH/psql -t -c "

        WITH s AS (SELECT nspname || '.' || relname AS TABLE_NAME, pg_total_relation_size(c.oid) AS total_bytes

                  FROM pg_class c

                  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

                  WHERE relkind = 'r' AND nspname NOT IN ( 'pg_catalog', 'information_schema' )

                  ORDER BY 2 )

        SELECT table_name FROM s

        " |

    while read t ; do echo "" ; echo $t; $PGPATH/vacuumdb -w -z -f -t $t ; done

###

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.