Re: Understanding pg_autovacuum CPU Usage

Thomas F.O'Connell wrote:

> I've been using pg_autovacuum on a largish postgres installation
> successfully for several weeks, now. I know that pg_autovacuum has no
> facility for monitoring the system to know whether any particular time
> is a good one for it to do it's thing, but I'm wondering what the
> occasional episodes of 99.9% CPU usage (as reported by top) are.
>
> I keep a pg_autovacuum.log, and when the spikes start, often nothing is
> written to the log. And what ends up being written has been of varying
> amounts of potential strain. Frequently, the tables involved are
> smallish. So I'm wondering: does the CPU usage correspond to any time
> pg_autovacuum arouses from its sleep state and begins looking for what
> it ought to do?
>
> Any thoughts?

This is the first report I have heard of pg_autovacuum causing cpu usage
spikes. When pg_autovacuum wakes up, it loops through all the databases
checking for recent activity and decides if it is time to do something.
I would think that pg_autovacuum wouldn't use much CPU during this
time since it would be waiting a lot on connection startup and query
responses from the server. I suppose that it could use a noticeable
amount of CPU if you had a lot of databases for it to loop through and a
very small connection time. Are you using pooled connections? Also is
this 7.4.x or 8.0 beta?

Can you hook up a debugger and see what it's doing during the CPU spikes?

Thanks for the report,

Matthew