Обсуждение: bloatcheck.sql

Поиск
Список
Период
Сортировка

bloatcheck.sql

От
A System Admin
Дата:
Hi PostgreSQL admins,

(PostgreSQL v9.5.9)

The author of this SQL indicates that any output for the hot_update_ratio that falls below 0.95 indicates that action needs to be taken over and above the autovacuuming that is setup for this DB based on its levels of bloat.

My questions are:

1. Is this an accurate statement in your opinion for this and all PostgreSQL v9.5.9 DB's?

2. Given that about 60 of the tables qualify for this statement, what would be the best next step(s) to get them to be above 0.95 and remain there at this point?

3. What specific ongoing/regular step(s) need to occur outside of the hourly and weekly commands currently being run on the DB for it to remain in an optimally performant state?

Hourly:
REINDEX TABLE miq_queue
REINDEX TABLE miq_workers
REINDEX TABLE metrics_$(date -u +"%H" --date='+1 hours ' )

Weekly:
\timing
VACUUM full verbose vms ;
vacuum full verbose binary_blob_parts ;
vacuum full verbose binary_blobs ;
vacuum full verbose customization_specs ;
vacuum full verbose firewall_rules   ;
Vacuum full verbose hosts ;
vacuum full verbose storages ;
vacuum full verbose miq_schedules ;
vacuum full verbose event_logs ;

vacuum full verbose policy_events ;
vacuum full verbose snapshots ;
vacuum full verbose jobs ;
vacuum full verbose networks ;
vacuum full verbose miq_queue ;
vacuum full verbose miq_request_tasks ;
vacuum full verbose miq_workers;
vacuum full verbose miq_servers;
vacuum full verbose miq_searches;
vacuum full verbose miq_scsi_luns ;
vacuum full verbose miq_scsi_targets;
vacuum full verbose storage_files ;
vacuum full verbose taggings ;
vacuum full verbose vim_performance_states;
--vacuum full verbose ems_events ;

section of postgresql.conf:

#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------

autovacuum = on                         # MIQ Value;
#autovacuum = on                        # Enable autovacuum subprocess?  'on'
                                        # requires track_counts to also be on.
log_autovacuum_min_duration = 0         # MIQ Value;
#log_autovacuum_min_duration = -1       # -1 disables, 0 logs all actions and
                                        # their durations, > 0 logs only
                                        # actions running at least this number
                                        # of milliseconds.
#autovacuum_max_workers = 3             # max number of autovacuum subprocesses
                                        # (change requires restart)
autovacuum_naptime = 5min               # MIQ Value;
#autovacuum_naptime = 1min              # time between autovacuum runs
autovacuum_vacuum_threshold = 500       # MIQ Value;
#autovacuum_vacuum_threshold = 50       # min number of row updates before
                                        # vacuum
autovacuum_analyze_threshold = 500      # MIQ Value;
#autovacuum_analyze_threshold = 50      # min number of row updates before
                                        # analyze
autovacuum_vacuum_scale_factor = 0.05   # MIQ Value;
#autovacuum_vacuum_scale_factor = 0.2   # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1  # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000  # maximum XID age before forced vacuum
                                        # (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms    # default vacuum cost delay for
                                        # autovacuum, in milliseconds;
                                        # -1 means use vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1      # default vacuum cost limit for
                                        # autovacuum, -1 means use
                                        # vacuum_cost_limit


#------------------------------------------------------------------------------


Thanks in advance for your advice on this tuning topic.


Вложения

Re: bloatcheck.sql

От
Rui DeSousa
Дата:

> On Mar 24, 2018, at 9:07 PM, A System Admin <asysad@gmail.com> wrote:
>
> Hi PostgreSQL admins,
>
> (PostgreSQL v9.5.9)
>
> The author of this SQL indicates that any output for the hot_update_ratio that falls below 0.95 indicates that action
needsto be taken over and above the autovacuuming that is setup for this DB based on its levels of bloat. 
>
> My questions are:
>
> 1. Is this an accurate statement in your opinion for this and all PostgreSQL v9.5.9 DB's?
>

No.  I think bloat is a quite misunderstood topic; especially, in the Postgres world and unfortunately perpetuated as
Postgres’sachilles heel.  Performance is also a relative term.    

What performance benefit would removing bloat solve? In a well designed transactional system… none.  For reporting with
largetable scans sure. 

Systems should come to an equilibrium and each system will be different thus the bloat factor will be different.  It’s
betterto understand your system rather than rely on folklore.  Work to identify real performance problems rather than
tryingto fix perceived phantom performance issues.   

Also, if you know the equilibrium of your system; then you’ll understand when it gets out of wack due to a bad long
runningquery throwing off the equilibrium.  Long running queries, can prevent vacuum from doing it’s job.   

As an example, if you database consists of heavy updates and long running queries then given boat query would continue
tostate that there is something wrong; however, it just the nature of your given system. 




Re: bloatcheck.sql

От
"David G. Johnston"
Дата:
On Saturday, March 24, 2018, A System Admin <asysad@gmail.com> wrote:
Hourly:
REINDEX TABLE miq_queue
REINDEX TABLE miq_workers
REINDEX TABLE metrics_$(date -u +"%H" --date='+1 hours ' )

Weekly:
\timing
VACUUM full verbose vms ;
vacuum full verbose binary_blob_parts ;

While I don't really have positive advice I do think you need to continue reading and asking questions since neither scheduled reindex nor scheduled vacuum fulls should generally be a necessary component of a maintenance routine.

Either per table or overall, more frequent vacuuming should be setup first - frequent enough to either solve the supposed problem (I'm not sure you really have one...) or until the vacuuming itself causes measurable pain.  Depending on usage patterns manual vacuum (not full) could be done after bulk updates or deletes instead of waiting for the autovaccuum daemon to kick in.

David J.

Re: bloatcheck.sql

От
Laurenz Albe
Дата:
A System Admin wrote:
> The author of this SQL indicates that any output for the hot_update_ratio that falls below
> 0.95 indicates that action needs to be taken over and above the autovacuuming that is setup
> for this DB based on its levels of bloat.
> 
> My questions are:
> 
> 1. Is this an accurate statement in your opinion for this and all PostgreSQL v9.5.9 DB's?

Nothing could be farther from the truth.

HOT update ratio has nothing to do with bloat; if anything, bloat
would *increase* the HOT ratio because there is more free space.

A low HOT update ratio could just mean that you have a lot of
indexes, because no HOT update can take place if an indexed column
is modified.

> 3. What specific ongoing/regular step(s) need to occur outside of the hourly and weekly
> commands currently being run on the DB for it to remain in an optimally performant state?

Neither regular reindexing nor VACUUM (FULL) is recommended,
although it cannot harm if you don't mind the down time.

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