Thread: autovacuum launcher eating too much CPU
Hi, Darcy Buskermolen noticed that when one has many databases, the autovac launcher starts eating too much CPU. I tried it here with 200 databases and indeed it does seem to eat its share. Even with the default naptime, which I wouldn't have thought that was too high (it does make the launcher wake up about three times a second though). I'm looking at a profile and I can't seem to make much sense out of it. It seems to me like the problem is not autovac itself, but rather the pgstat code that reads the stat file from disk. Of course, autovac does need to read the file fairly regularly. Here is the top lines of gprof output. Comments? Is there something here that needs fixing? -- Alvaro Herrera http://www.advogato.org/person/alvherre "Investigación es lo que hago cuando no sé lo que estoy haciendo" (Wernher von Braun)
Attachment
Alvaro Herrera wrote:
> Darcy Buskermolen noticed that when one has many databases, the autovac
> launcher starts eating too much CPU.
>
> I tried it here with 200 databases and indeed it does seem to eat its
> share. Even with the default naptime, which I wouldn't have thought
> that was too high (it does make the launcher wake up about three times a
> second though).
This patch does not solve the whole problem but it alleviates it a bit
by throttling pgstat reads. One problem with it is that the interval
for this increases:
/*
* Check whether pgstat data still says we need to vacuum this table.
* It could have changed if something else processed the table while we
* weren't looking.
*
* FIXME we ignore the possibility that the table was finished being
* vacuumed in the last 500ms (PGSTAT_STAT_INTERVAL). This is a bug.
*/
MemoryContextSwitchTo(AutovacMemCxt);
tab = table_recheck_autovac(relid);
which could be a problem in itself, by causing unnecessary vacuums.
Opinions?
--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"In Europe they call me Niklaus Wirth; in the US they call me Nickel's worth.
That's because in Europe they call me by name, and in the US by value!"
Attachment
On Thursday 13 September 2007 09:16:52 Alvaro Herrera wrote: > Hi, > > Darcy Buskermolen noticed that when one has many databases, the autovac > launcher starts eating too much CPU. Don't forget the memory leak as well. after 3 or 4 days of running I end up with a 2GB+ AVL.. > > I tried it here with 200 databases and indeed it does seem to eat its > share. Even with the default naptime, which I wouldn't have thought > that was too high (it does make the launcher wake up about three times a > second though). > > I'm looking at a profile and I can't seem to make much sense out of it. > It seems to me like the problem is not autovac itself, but rather the > pgstat code that reads the stat file from disk. Of course, autovac does > need to read the file fairly regularly. > > Here is the top lines of gprof output. > > Comments? Is there something here that needs fixing? -- Darcy Buskermolen The PostgreSQL company, Command Prompt Inc. http://www.commandprompt.com/
Darcy Buskermolen wrote: > On Thursday 13 September 2007 09:16:52 Alvaro Herrera wrote: > > Hi, > > > > Darcy Buskermolen noticed that when one has many databases, the autovac > > launcher starts eating too much CPU. > > Don't forget the memory leak as well. after 3 or 4 days of running I end up > with a 2GB+ AVL.. Huh, sorry for not letting you know, I already fixed that :-) (Please grab the latest CVS HEAD and confirm.) -- Alvaro Herrera http://www.advogato.org/person/alvherre "Entristecido, Wutra (canción de Las Barreras) echa a Freyr a rodar y a nosotros al mar"
Alvaro Herrera wrote: > Darcy Buskermolen wrote: > > On Thursday 13 September 2007 09:16:52 Alvaro Herrera wrote: > > > Hi, > > > > > > Darcy Buskermolen noticed that when one has many databases, the autovac > > > launcher starts eating too much CPU. > > > > Don't forget the memory leak as well. after 3 or 4 days of running I end up > > with a 2GB+ AVL.. > > Huh, sorry for not letting you know, I already fixed that :-) (Please > grab the latest CVS HEAD and confirm.) Darcy, please also apply the following patch and see if it reduces the CPU consumption to a reasonable level. What this patch does is keep the pgstats data for 1 second in the autovac launcher. The idea is to avoid reading the data too frequently. I coded it so that it doesn't affect the worker, because it would make the table recheck code less effective. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 "MySQL is a toy compared to PostgreSQL." (Randal L. Schwartz) (http://archives.postgresql.org/pgsql-general/2005-07/msg00517.php)
Attachment
On September 14, 2007 06:36 am, Alvaro Herrera wrote: > Alvaro Herrera wrote: > > Darcy Buskermolen wrote: > > > On Thursday 13 September 2007 09:16:52 Alvaro Herrera wrote: > > > > Hi, > > > > > > > > Darcy Buskermolen noticed that when one has many databases, the > > > > autovac launcher starts eating too much CPU. > > > > > > Don't forget the memory leak as well. after 3 or 4 days of running I > > > end up with a 2GB+ AVL.. > > > > Huh, sorry for not letting you know, I already fixed that :-) (Please > > grab the latest CVS HEAD and confirm.) Ok that looks much better, after running it for 8ish hours I'm not seeing any of the previous footprint growth. > > Darcy, please also apply the following patch and see if it reduces the > CPU consumption to a reasonable level. This is looking much better now too, it's brought the AVL down to near 0% CPU usage. > > What this patch does is keep the pgstats data for 1 second in the > autovac launcher. The idea is to avoid reading the data too frequently. > I coded it so that it doesn't affect the worker, because it would make > the table recheck code less effective. -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997
Darcy Buskermolen wrote: > On September 14, 2007 06:36 am, Alvaro Herrera wrote: > > Darcy, please also apply the following patch and see if it reduces the > > CPU consumption to a reasonable level. > > This is looking much better now too, it's brought the AVL down to near 0% CPU > usage. Thanks, applied. I still feel CPU usage is somewhat excessive but I don't think there's much to be done about it. Maybe I'm just testing with too many databases. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ "Saca el libro que tu religión considere como el indicado para encontrar la oración que traiga paz a tu alma. Luego rebootea el computador y ve si funciona" (Carlos Duclós)
On September 23, 2007 09:12 pm, Alvaro Herrera wrote: > Darcy Buskermolen wrote: > > On September 14, 2007 06:36 am, Alvaro Herrera wrote: > > > Darcy, please also apply the following patch and see if it reduces the > > > CPU consumption to a reasonable level. > > > > This is looking much better now too, it's brought the AVL down to near 0% > > CPU usage. > > Thanks, applied. I still feel CPU usage is somewhat excessive but I > don't think there's much to be done about it. Maybe I'm just testing > with too many databases. My findings were against 83 DB's -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997
Darcy Buskermolen wrote: > On September 23, 2007 09:12 pm, Alvaro Herrera wrote: > > Darcy Buskermolen wrote: > > > On September 14, 2007 06:36 am, Alvaro Herrera wrote: > > > > Darcy, please also apply the following patch and see if it reduces the > > > > CPU consumption to a reasonable level. > > > > > > This is looking much better now too, it's brought the AVL down to near 0% > > > CPU usage. > > > > Thanks, applied. I still feel CPU usage is somewhat excessive but I > > don't think there's much to be done about it. Maybe I'm just testing > > with too many databases. > > My findings were against 83 DB's I was testing with 300 and naptime=10s. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.