Alvaro Herrera wrote:
Matthew T. O'Connor wrote:
On first blush, I'm not sure I like this as it doesn't directly attack
the table starvation problem, and I think it could be a net loss of speed.
VACUUM is I/O bound, as such, just sending multiple vacuum commands at a
DB isn't going to make things faster, you are now going to have multiple
processes reading from multiple tables at the same time. I think in
general this is a bad thing (unless we someday account for I/O made
available from multiple tablespaces).
Yeah, I understand that. However, I think that can be remedied by using
a reasonable autovacuum_vacuum_cost_delay setting, so that each worker
uses less than the total I/O available. The main point of the proposal
is to allow multiple workers on a DB while also allowing multiple
databases to be processed in parallel.
So you are telling people to choose an autovacuum_delay so high that
they need to run multiple autovacuums at once to keep up? I'm probably
being to dramatic, but it seems inconsistent.
I think we can extend the current autovacuum stats to add one more
column that specifies "is hot" or something to that effect. Then when
the AV launcher sends a worker to a DB, it will first look for tables
marked as hot and work on them. While working on hot tables, the
launcher need not send any additional workers to this database, if the
launcher notices that a worker is working on regular tables, it can send
another worker which will look for hot tables to working, if the worker
doesn't find any hot tables that need work, then it exits leaving the
original working to continue plodding along.
How would you define what's a "hot" table?
I wasn't clear, I would have the Admin specified it, and we can store it
as an additional column in the pg_autovacuum_settings table. Or perhaps
if the table is below some size threshold and autovacuum seems that it
needs to be vacuumed every time it checks it 10 times in a row or
something like that.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
