Understanding pg_autovacuum CPU Usage

[Thomas F.O'Connell]

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?

-tfo
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Matthew T. O'Connor]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Thomas F.O'Connell]

On Sep 1, 2004, at 5:09 PM, Matthew T. O'Connor wrote:

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.
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.
Are you using pooled connections?
No.
Also is this 7.4.x or 8.0 beta?
Oops. Sorry. It's 7.4.5, specifically.
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.

-tfo
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Matthew T. O'Connor]

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
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Thomas F.O'Connell]

On Sep 1, 2004, at 10:27 PM, Matthew T. O'Connor wrote:

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?
Is there an easy way to determine if it's happening every other loop?
Try the simple recompile with the larger update interval first and see
if that's the problem.

How difficult would it be to make this a command-line argument if it
turns out to be a run-time issue?

Thanks for the input.

-tfo
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Matthew T. O'Connor]

Thomas F.O'Connell wrote:

On Sep 1, 2004, at 10:27 PM, Matthew T. O'Connor wrote:

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?

Is there an easy way to determine if it's happening every other loop?

Yes, if you are running pg_autovacuum with a debug setting >= 1 it will
write out a message at the end of every loop.

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

How difficult would it be to make this a command-line argument if it
turns out to be a run-time issue?

Very Easy.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)