Re: Understanding pg_autovacuum CPU Usage

Thomas F.O'Connell wrote:
> Well, I don't have a lot of databases, but I do have tens of thousands
> of tables, many of which have hundreds of thousands of rows. I don't
> know if that plays into things.

Number of rows is irrelevant, but the number of tables might not be. It
could be that the process of checking it's list of tables against the
server might be slow when used with lots of tables. Does this cpu spike
happen every other loop? If so then that is the culprit. Editing
pg_autovacuum.c and changing:

#define UPDATE_INTERVAL 2

to some higher value. The default UPDATE_INTERVAL tells pg_autovacuum
to check for added / removed tables every 2 loops.

> Oops. Sorry. It's 7.4.5, specifically.

Ok

>> Can you hook up a debugger and see what it's doing during the CPU spikes?
>
> That's going to be a little tricky because it's a production
> environment. So far, the spikes haven't hurt too much because they don't
> last very long. I'll see if I can get anything similar to occur in our
> development environment.

Try the simple recompile with the larger update interval first and see
if that's the problem.

Matthew