Thread: autovacuum launcher eating too much CPU

autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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

Re: autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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

Re: autovacuum launcher eating too much CPU

From
Darcy Buskermolen
Date:
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/


Re: autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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"


Re: autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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

Re: autovacuum launcher eating too much CPU

From
Darcy Buskermolen
Date:
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


Re: autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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)


Re: autovacuum launcher eating too much CPU

From
Darcy Buskermolen
Date:
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


Re: autovacuum launcher eating too much CPU

From
Alvaro Herrera
Date:
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.