Обсуждение: Understanding pg_autovacuum CPU Usage
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
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
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
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
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
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.
Matthew, This is from long ago, but I've finally had some time to continue to investigate the problem of CPU spikes in pg_autovacuum. update_db_list is provably one of the culprits, but it doesn't seem to be the only one. In looking through the code, it doesn't seem as though any vacuuming can be done during the update of the list, correct? But after I increased my debug setting (>= 2 is what's needed, actually, for the update message to show up), I waited around for the next update and, sure enough, CPU shot up. But it never dropped across the course of the entire run of the db_list loop. I'm going to increase UPDATE_INTERVAL per your suggestion, but I should expect heavy CPU usage during each pass through the db_list loop, regardless of whether or not the list has been updated? Thanks! -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Sep 2, 2004, at 6:28 AM, Matthew T. O'Connor wrote: > 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.