Re: how to slow down parts of Pg - Mailing list pgsql-general

From Michael Lewis
Subject Re: how to slow down parts of Pg
Date
Msg-id CAHOFxGp3r9XEat8Q+_PqVU0bniuKOQz9-876yTTqDbaeGfhknA@mail.gmail.com
Whole thread Raw
In response to RE: how to slow down parts of Pg  (Kevin Brannen <KBrannen@efji.com>)
List pgsql-general
Reviewing pg_stat_user_tables will give you an idea of how often autovacuum is cleaning up those tables that "need" that vacuum full on a quarterly basis. You can tune individual tables to have a lower threshold ratio of dead tuples so the system isn't waiting until you have 20% dead rows before vacuuming a table with millions of rows that occupies a GB or more on disk. You might consider changing your nightly analyze to a nightly vacuum analyze, at least for the tables you know can be problematic. The more dense a table is packed, the better cache_hits and other such metrics. Like making dinner, cleanup as you go.

One thing that I think is interesting is that the default cost_delay has been updated with PG12 from 20ms down to 2ms such that all things being equal, much much more work is done by autovacuum in a given second. It may be worth taking a look at.

Another great thing coming to you in PG12 is the option to do reindex concurrently. Then there's no need for pg_repack on indexes.

Good luck sir.

pgsql-general by date:

Previous
From: Kevin Brannen
Date:
Subject: RE: how to slow down parts of Pg
Next
From: Adrian Klaver
Date:
Subject: Re: how to slow down parts of Pg