pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbloatcheck.sql
4+ messages / 4 participants
[nested] [flat]
* bloatcheck.sql
@ 2018-03-25 01:07 A System Admin <asysad@gmail.com>
2018-03-25 03:48 ` Re: bloatcheck.sql Rui DeSousa <rui.desousa@icloud.com>
2018-03-25 04:05 ` Re: bloatcheck.sql David G. Johnston <david.g.johnston@gmail.com>
2018-03-25 04:34 ` Re: bloatcheck.sql Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 4+ messages in thread
From: A System Admin @ 2018-03-25 01:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
Attachments:
output.txt (text/plain, 77.8K) download
bloatcheck.sql (application/octet-stream, 1.6K) download
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: bloatcheck.sql
2018-03-25 01:07 bloatcheck.sql A System Admin <asysad@gmail.com>
@ 2018-03-25 03:48 ` Rui DeSousa <rui.desousa@icloud.com>
2 siblings, 0 replies; 4+ messages in thread
From: Rui DeSousa @ 2018-03-25 03:48 UTC (permalink / raw)
To: A System Admin <asysad@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> 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 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?
>
No. I think bloat is a quite misunderstood topic; especially, in the Postgres world and unfortunately perpetuated as Postgres’s achilles heel. Performance is also a relative term.
What performance benefit would removing bloat solve? In a well designed transactional system… none. For reporting with large table scans sure.
Systems should come to an equilibrium and each system will be different thus the bloat factor will be different. It’s better to understand your system rather than rely on folklore. Work to identify real performance problems rather than trying to 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 running query 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 to state that there is something wrong; however, it just the nature of your given system.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: bloatcheck.sql
2018-03-25 01:07 bloatcheck.sql A System Admin <asysad@gmail.com>
@ 2018-03-25 04:05 ` David G. Johnston <david.g.johnston@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2018-03-25 04:05 UTC (permalink / raw)
To: A System Admin <asysad@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: bloatcheck.sql
2018-03-25 01:07 bloatcheck.sql A System Admin <asysad@gmail.com>
@ 2018-03-25 04:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2018-03-25 04:34 UTC (permalink / raw)
To: A System Admin <asysad@gmail.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2018-03-25 04:34 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-25 01:07 bloatcheck.sql A System Admin <asysad@gmail.com>
2018-03-25 03:48 ` Rui DeSousa <rui.desousa@icloud.com>
2018-03-25 04:05 ` David G. Johnston <david.g.johnston@gmail.com>
2018-03-25 04:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox