Re: Vacuum rate limit in KBps - Mailing list pgsql-hackers

Benedikt Grundmann wrote:
> What I think is missing is a clear way to know if you are vacuuming 
> (and analyzing) enough, and how much you are paying for that.  
>   

Any good way to measure if you're vacuuming a particular table enough 
needs to note how much free space is in that table and its indexes over 
time.  That's why one of the other building blocks in the submission 
queue for 9.2 is a function to make that easier to do.  It's one of the 
important dependencies to get settled before we can move very far toward 
answering "am I vacuuming enough?".

A second piece to that is recording a history of that information over 
time.  Can't predict the future need for something without some record 
of its past to extrapolate from.  That's probably a job better suited 
for an external tool.  The way you'd want to audit it most easily is to 
graph it over time, which isn't the sort of thing PostgreSQL is likely 
to build in.  Also, the proof of whether a suggested implementation for 
a vacuum "meter" was useful or not would be easiest to validate that 
way.  No sense in doing the difficult work of building one until there's 
a working prototype, which is possible to do more quickly in languages 
other than C.

A simple meter might not be possible to create even with some better 
building blocks to base it on.  There is a lot of difference in this 
area that is workload dependent, and there are many types of database 
workloads out there.  The two hardest systems to tune vacuum for that I 
work on have settled on completely different approaches to the problem.  
The only thing I've found so far that is true about both of them is that 
they'd really appreciate easier controls on the maximum rate.

> At the moment we are basically changing the knobs blindly based on
> some back of the envelope calculations and hearsay.  Than sometimes
> month later we find out that eps we haven't been analyzing enough
> and that's why on that particular table the planner is now picking
> a "bad" query.
>   

Unlike VACUUM, ANALYZE is so cheap to run that it's possible to improve 
this situation more easily--just do it a lot more.  Lowering 
autovacuum_analyze_scale_factor is the easiest way.  By default that is 
0.10, requiring approximately a 10% change in the table size before a 
new ANALYZE is done.  I think the lowest production setting I have for 
that somewhere is 0.03 on a roughly terabyte scale database.  There a 
10% change in one the larger tables is well over the point of impacting 
query plans badly.

If your data changes its character quite frequently based on new 
information, I wouldn't be afraid in that case to drop as low as 0.01 
here.  That would give you ANALYZE that happened 10X as often as it does 
now.  You'll waste a moderate amount of CPU and disk resources, but a 
tuning error that leans toward analyzing too frequently isn't that 
expensive.

-- 
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com