Thread: Autovacuum Problem

On 03/04/2018 09:36, Kein Name wrote:
> However no space seems to be freed to the system.
>
> Is there any way a bloody newbie can debug this behaviour?

VACUUM <> VACUUM FULL
Normally running VACUUM via autovacuum should help reuse free space but not actually return it to the filesystem / OS
(unlessit happens to be the last blocks in the data file(s)).
 
Ppl in normal/average type of installations/workloads no longer (since 8.2) run VACUUM (or VACUUM FULL) by hand, except
inextra ordinary cases where e.g. need for free space is urgent.
 

> Any help is appreciated!
>
> Thanks
> Stefan
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

> VACUUM <> VACUUM FULL
> Normally running VACUUM via autovacuum should help reuse free space but not actually return it to the filesystem / OS (unless it happens to be the last blocks in the data file(s)).
> Ppl in normal/average type of installations/workloads no longer (since 8.2) run VACUUM (or VACUUM FULL) by hand, except in extra ordinary cases where e.g. need for free space is urgent.

Hmmmm then when is the space released to the system when the manual VACUUM FULL run is not done? Any way to "force" this?

2018-04-03 8:49 GMT+02:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:

On 03/04/2018 09:36, Kein Name wrote:

However no space seems to be freed to the system.

Is there any way a bloody newbie can debug this behaviour?

VACUUM <> VACUUM FULL
Normally running VACUUM via autovacuum should help reuse free space but not actually return it to the filesystem / OS (unless it happens to be the last blocks in the data file(s)).
Ppl in normal/average type of installations/workloads no longer (since 8.2) run VACUUM (or VACUUM FULL) by hand, except in extra ordinary cases where e.g. need for free space is urgent.

Any help is appreciated!

Thanks
Stefan

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

> Why would you want that? Do you have any control over the application? Any "special" patterns used in the app?

Drive is running full :/

Sadly I have no control and knowledge whatsoever over/about the application.

I tuned the autovacuum parameters now for the critical tables, to have it run more often and using greater ressources while doing so.... but I am unsure if this helps with the bloating.

2018-04-03 9:39 GMT+02:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:

On 03/04/2018 10:00, Kein Name wrote:

> VACUUM <> VACUUM FULL
> Normally running VACUUM via autovacuum should help reuse free space but not actually return it to the filesystem / OS (unless it happens to be the last blocks in the data file(s)).
> Ppl in normal/average type of installations/workloads no longer (since 8.2) run VACUUM (or VACUUM FULL) by hand, except in extra ordinary cases where e.g. need for free space is urgent.

Hmmmm then when is the space released to the system when the manual VACUUM FULL run is not done? Any way to "force" this?

Why would you want that? Do you have any control over the application? Any "special" patterns used in the app?

2018-04-03 8:49 GMT+02:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>:

On 03/04/2018 09:36, Kein Name wrote:

However no space seems to be freed to the system.

Is there any way a bloody newbie can debug this behaviour?

VACUUM <> VACUUM FULL
Normally running VACUUM via autovacuum should help reuse free space but not actually return it to the filesystem / OS (unless it happens to be the last blocks in the data file(s)).
Ppl in normal/average type of installations/workloads no longer (since 8.2) run VACUUM (or VACUUM FULL) by hand, except in extra ordinary cases where e.g. need for free space is urgent.

Any help is appreciated!

Thanks
Stefan

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt

>
> However no space seems to be freed to the system.
>
> Is there any way a bloody newbie can debug this behaviour?

In our experience, autovacuum is able to contain bloating of table data, 
but not bloating of indexes.

You could see where the bloating is by running the following queries:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

select c.relname, (pgstattuple(c.relname)).*
from pg_class c
join pg_namespace n on (n.oid = c.relnamespace and n.nspname = 'public')
where c.relkind = 'r'
order by c.reltuples desc;

select c.relname, (pgstatindex(c.relname)).*
from pg_class c
inner join pg_namespace n on (n.oid = c.relnamespace and n.nspname = 
'public')
where c.relkind = 'i'
order by c.reltuples desc;


The first SELECT query gives some information about table data, the 
second SELECT query is about indexes. The queries will take quite some 
time to run, because they will have to scan through every data page on 
disk. Look at the following columns:

pgstattuple.free_percent - percent of table data which is allocated on 
disk, but does not currently contain any actual data (describes table 
data bloat).

pgstatindex.avg_leaf_density - percent of stored leaf index data within 
the totally allocated on disk (describes index bloat). It's 90% by 
default. It's not good when it drops too much (e.g. to 50% or 10%).

Look at the biggest tables/indexes first. In our case, from 1000+ tables 
there are only few which contribute most to DB size bloating. But in 
general that would depend on your specific case, and the write patterns 
which you application does. You can then do VACUUM FULL for those 
specific tables, or REINDEX TABLE/INDEX for specific tables/indexes. 
VACUUM FULL or REINDEX TABLE may work faster than doing REINDEX INDEX 
for several indexes of the same table. I do not know any efficient way 
to prevent bloating of indexes.

Regards,
Vitaliy