Re: R: Vacuum full: alternatives?

[Adarsh Sharma <eddy.adarsh@xxxxxxxxx>]

On Mon, Jun 20, 2016 at 6:20 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Mon, Jun 20, 2016 at 8:29 AM, Rakesh Kumar <rakeshkumar464a3@xxxxxxxxx> wrote:

But then autovaccum avoids this. Granted it won't give back free space to OS, but it won't let it grow too (or am I missing something).

---

From: Job <Job@xxxxxxxxxxxxxxxxxxxx>
To: Rakesh Kumar <rakeshkumar464a3@xxxxxxxxx>; "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, June 20, 2016 5:39 AM
Subject: R: [GENERAL] Vacuum full: alternatives?

Hi Rakesh,

 

if i do not free disk space, after some days disk can become full.

Everyday we have a lot of pg_bulkload and delete.

 

Thank you!
Francesco

 

---

Da: pgsql-general-owner@xxxxxxxxxxxxxx [pgsql-general-owner@xxxxxxxxxxxxxx] per conto di Rakesh Kumar [rakeshkumar464a3@xxxxxxxxx]
Inviato: lunedì 20 giugno 2016 11.34
A: pgsql-general@xxxxxxxxxxxxxx
Oggetto: Re: Vacuum full: alternatives?

Any reason why you need the space back? What is wrong with space remaining constant at 4GB.

---

From: Job <Job@xxxxxxxxxxxxxxxxxxxx>
To: "pgsql-general@xxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, June 20, 2016 5:18 AM
Subject: Vacuum full: alternatives?

Hello, 

we have a table with an heavy traffic of pg_bulkload and delete of records.
The size pass, in only one day, for example for 1Gb to 4Gb and then 1Gb back.

We have important problems on size and the only way to gain free space is issueing a vacuum full <table>.
But the operation is very slow, sometimes 2/4 hours, and table is not available for services as it is locked.

We do not delete everything at one (in this case the truncate woudl resolve the problem).

The autovacuum is not able (same for normal vacuum) to free the spaces.

Are there some suggestions or another way to manage this?

Thank you!
Francesco

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

> but it won't let it grow too (or am I missing something).

Yes, you are missing something. By partioning and {Vacuum Full only the table with data no longer needed}, the rest of the data remains available to the users
AND space is reclaimed by the O/S, so it's the best of both worlds.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

---

Few things you can try :

1. Partition your table daily

2. Tune your autovacuum parameters if you think autovacuum is not keeping up with the fragmentation speed. fore.g :

alter table table_name set (autovacuum_enabled=true, autovacuum_vacuum_threshold=5000, autovacuum_analyze_threshold=5000, autovacuum_vacuum_scale_factor=0.1, autovacuum_analyze_scale_factor=0.2);

Read this before tuning : https://www.postgresql.org/docs/9.1/static/runtime-config-autovacuum.html

3. If you can recreate/alter your table, create/alter with a fillfactor of 20 so that your deleted rows resides in the same page.It might use extra space but you will face less fragmentation problems.
link : https://www.postgresql.org/docs/8.3/static/sql-createtable.html

However, i have faced one problem in past where we have streaming replication setup of one master and 4 slaves. After all these tunings , autovacuum is not able to remove dead tuples and queries are getting slower and slower.

After stopping all applications and streaming replicated slaves, i was able to defrag the table properly. The doc says autovacuum will not remove any dead tuples if it has any reference to those  dead tuples anywhere but i am not sure how to find those dead tuples which are still being referenced :)

Thanks,

Adarsh Sharma