On Fri, Sep 30, 2016 at 4:49 PM, Jim Nasby <jim.na...@bluetreble.com> wrote:
> On 9/29/16 6:11 AM, Alex Ignatov (postgrespro) wrote: > >> With millions of tables you have to set autovacuum_max_workers >> sky-high =). We have some situation when at thousands of tables >> autovacuum can’t vacuum all tables that need it. Simply it vacuums some >> of most modified table and never reach others. Only manual vacuum can >> help with this situation. With wraparound issue it can be a nightmare >> > > Specifically, autovac isn't going to start worrying about anti-wrap > vacuums until tables start hitting autovacuum_freeze_max_age (or > autovacuum_multixact_freeze_max_age). Any tables that hit that threshold > go to the front of the line for being vacuumed. (But keep in mind that > there is no universal line, just what each worker computes on it's own when > it's started). > > Where things will completely fall apart for you is if a lot of tables all > have roughly the same relfrozenxid (or relminmxid), like they would > immediately after a large load. In that scenario you'll suddenly have loads > of work for autovac to do, all at the same time. That will make the > database, DBAs and you Very Unhappy (tm). > > Somehow, some way, you *must* do a vacuum of the entire database. Luckily > the freeze map in 9.6 means you'd only have to do that one time (assuming > the data really is static). In any older version, (auto)vacuum will need to > eventually *read everything in every table* at least once every ~2B > transactions. > Data is not static. The 4M tables fall into one of two groups. Group A contains 2M tables. INSERT will occur ~100 times/day and maximum number of records anticipated will be 200k. Periodic DELETE's will occur removing "old" records. Age is something the client sets and I have no way of saying 1 or 10k records will be removed. Group B contains the other 2M tables. Maximum records ~140k and UPSERT will be the only mechanism used to populate and maintain. Periodic DELETE's may run on these tables as well removing "old" records. Will a set of tables require vacuum'ing at the same time? Quite possibly but I have no way to say 2 or 200k tables will need it. When you say "must do a vacuum of the entire database", are you saying the entire database must be vacuum'd as a whole per 2B transactions or all tables must be vacuum'd eventually at least once? I want to be absolutely clear on what you're saying. > There is one potentially significant difference between autovac and manual > vacuums here; autovac treats toast tables as just another table, with their > own stats and their own freeze needs. If you're generating a lot of toast > records that might make a difference. > I do not anticipate TOAST entering the picture. No single column or record > 8KB or even approaching it. We have a few databases that (ab)use pg_toast and I want to avoid those complications. -Greg
